小议主子表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

 

 

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