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

    对于这样的表结构，我们最常见的问题就是保存的时候怎样处理键值的问题，因为两个表关联非常的紧密，我们进行保存的时候需要把它们放在一个事务里面，这时问题就会出现，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

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