小议主子表INT自增主键插入记录的方法

主子表最常见的大概就是用在进销存、 MRP 、 ERP 里面,比如一张销售订单,订单 Order(ID,OrderDate) ,订单明细 OrderDetail(OrderID, ProductID, Num,Price) 这个大概就是最简单的主子表了,两个表通过 ID 与 OrderID 建立关联,这里主键 ID 是自增的 INT 类型, OrderID 是表 OrderDetail 的外键。当然,键的选择方法很多,现在我们选择的是在 sql 里面最简单的方法。

对于这样的表结构,我们最常见的问题就是保存的时候怎样处理键值的问题,因为两个表关联非常的紧密,我们进行保存的时候需要把它们放在一个事务里面,这时问题就会出现, Order 表中的 ID 是自动增长型的字段。现在需要我们录入一张订单,包括在 Order 表中插入一条记录以及在 OrderDetail 表中插入若干条记录。因为 Order 表中的 ID 是自动增长型的字段,那么我们在记录正式插入到数据库之前无法事先得知它的取值,只有在更新后才能知道数据库为它分配的是什么值,然后再用这个 ID 作为 OrderDetail 表的 OrderID 的值,最后更新 OderDetail 表。但是,为了确保数据的一致性, Order 与 OrderDetail 在更新时必须在事务保护下同时进行,即确保两表同时更行成功,这个就会有点困扰。

解决这类问题常见的主要有两类方法:

一种是微软在网上书店里使用的方法,使用了四个存储过程。改装一下,使之符合现在的例子

-- 存储过程一

CREATE PROCEDURE InsertOrder

@Id INT = NULL OUTPUT,

@OrderDate DATETIME = NULL,

@ProductIDList NVARCHAR(4000) = NULL,

@NumList NVARCHAR(4000) = NULL,

@PriceList NVARCHAR(4000) = NULL

AS

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

-- 插入主表

INSERT Orders(OrderDate) select @OrderDate

SELECT @Id = @@IDENTITY

-- 插入子表

IF @ProductIDList IS NOT NULL

EXECUTE InsertOrderDetailsByList @Id, @ProductIdList, @numList, @PriceList

COMMIT TRANSACTION

RETURN 0

-- 存储过程二

CREATE PROCEDURE InsertOrderDetailsByList

@Id INT,

@ProductIDList NVARCHAR(4000) = NULL,

@NumList NVARCHAR(4000) = NULL,

@PriceList NVARCHAR(4000) = NULL

AS

SET NOCOUNT ON

DECLARE @Length INT

DECLARE @FirstProductIdWord NVARCHAR(4000)

DECLARE @FirstNumWord NVARCHAR(4000)

DECLARE @FirstPriceWord NVARCHAR(4000)

DECLARE @ProductId INT

DECLARE @Num INT

DECLARE @Price MONEY

SELECT @Length = DATALENGTH(@ProductIDList)

WHILE @Length > 0

BEGIN

EXECUTE @Length = PopFirstWord @@ProductIDList OUTPUT, @FirstProductIdWord OUTPUT

EXECUTE PopFirstWord @NumList OUTPUT, @FirstNumWord OUTPUT

EXECUTE PopFirstWord @PriceList OUTPUT, @FirstPriceWord OUTPUT

IF @Length > 0

BEGIN

SELECT @ProductId = CONVERT(INT, @FirstProductIdWord)

SELECT @Num = CONVERT(INT, @FirstNumWord)

SELECT @Price = CONVERT(MONEY, @FirstPriceWord)

EXECUTE InsertOrderDetail @Id, @ProductId, @Price, @Num

END

END

-- 存储过程三

CREATE PROCEDURE PopFirstWord

@SourceString NVARCHAR(4000) = NULL OUTPUT,

@FirstWord NVARCHAR(4000) = NULL OUTPUT

AS

SET NOCOUNT ON

DECLARE @Oldword NVARCHAR(4000)

DECLARE @Length INT

DECLARE @CommaLocation INT

SELECT @Oldword = @SourceString

IF NOT @Oldword IS NULL

BEGIN

SELECT @CommaLocation = CHARINDEX(',',@Oldword)

SELECT @Length = DATALENGTH(@Oldword)

IF @CommaLocation = 0

BEGIN

SELECT @FirstWord = @Oldword

SELECT @SourceString = NULL

RETURN @Length

END

SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)

SELECT @SourceString = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)

RETURN @Length - @CommaLocation

END

RETURN 0

------------------------------------------------

-- 存储过程四

CREATE PROCEDURE InsertOrderDetail

@OrderId INT = NULL,

@ProductId INT = NULL,

@Price MONEY = NULL,

@Num INT = NULL

AS

SET NOCOUNT ON

INSERT OrderDetail(OrderId,ProductId,Price,Num)

SELECT @OrderId,@ProductId,@Price,@Num

RETURN 0

插入时,传入的子表数据都是长度为 4000 的 NVARCHAR 类型,各个字段使用“,”分割,然后调用 PopFirstWord 分拆后分别调用 InsertOrderDetail 进行保存,因为在 InsertOrder 中进行了事务处理,数据的安全性也比较有保障,几个存储过程设计的精巧别致,很有意思,但是子表的几个数据大小不能超过 4000 字符,恐怕不大保险。

第二种方法是我比较常用的,为了方便,就不用存储过程了,这个例子用的是 VB.NET 。

‘ 处理数据的类

Public class DbTools

private Const _IDENTITY_SQL As String = "SELECT @@IDENTITY AS ID"

private Const _ID_FOR_REPLACE As String = "_ID_FOR_REPLACE"

‘对主子表插入记录

Public Function InsFatherSonRec(ByVal main_sql As String, ByVal ParamArray arParam() As String) As Integer

Dim conn As New SqlConnection(StrConn)

Dim ID AS INTEGER

conn.Open()

Dim trans As SqlTransaction = conn.BeginTransaction

Try

' 主记录

myDBTools.SqlData.ExecuteNonQuery(trans, CommandType.Text, main_sql)

' 返回新增 ID 号

ID = myDBTools.SqlData.ExecuteScalar(trans, CommandType.Text, _IDENTITY_SQL)

' 从记录

If Not arParam Is Nothing Then

For Each sql In arParam

' 将刚获得的 ID 号代入

sql = sql.Replace(_ID_FOR_REPLACE, ID)

myDBTools.SqlData.ExecuteNonQuery(trans, CommandType.Text, sql)

Next

End If

trans.Commit()

Catch e As Exception

trans.Rollback()

Finally

conn.Close()

End Try

Return ID

End Function

End class

上面这段代码里有 myDBTools ,是对常见的数据库操作封装后的类,这个类对数据库进行直接的操作,有经验的 .NET 数据库程序员基本上都会有,一些著名的例子程序一般也都提供。

上面的是通用部分,下面是对具体单据的操作

Publid class Order

Public _OrderDate as date ‘主表记录

Public ChildDt as datatable ‘子表记录,结构与 OrderDetail 一致

Public function Save() as integer

Dim str as string

Dim i as integer

Dim arParam() As String

Dim str as string= ” insert into Order(OrderDate) values( ‘” & _OrderDate & “’ ) ”

If not Childdt is nothing then

arParam = New String(ChildDT.Rows.Count - 1) {}

for i=0 to Childdt.rows.count-1

arparam(i)= ” insert into OrderDetail(OrderID,ProductID,Num,Price) Values(_ID_FOR_REPLACE, ” & drow( “ ProductID) & “ , ” & drow( “ Num ” ) & “ , ” drow( “ price ” ) & “ ) ”

next i

End if

Return (new dbtools). InsFatherSonRec(str,arparam)

End class

上面的两个例子为了方便解释,去掉了一些检验验证过程,有兴趣的朋友可以参照网上书店的例子研究第一种方法,或者根据自己的需要对第二种方法进行修改。

Published At
Categories with Web编程
Tagged with
comments powered by Disqus