A brief discussion on the master table int self-added primary key Insert Record method

Insert master table The most common is probably used in Invoicing, MRP, ERP inside, such as a sales orders, order orders (Id,orderdate), Order Details OrderDetail (OrderID, ProductID, Num,price) This is probably the simplest master table, two tables are associated with OrderID by ID, where the primary key ID is the self increasing int type, OrderID is the foreign key of the table OrderDetail. Of course, there are many keys to choose from, and now we are choosing the simplest method in SQL.

For such a table structure, our most common problem is how to deal with key values when we save, because two tables are closely related, we need to put them in a transaction when we save them, and then the problem arises, and the ID in the order table is the automatically growing field. Now we need to enter an order, including inserting a record in the Orders table and inserting several records into the OrderDetail table. Because the ID in the Order table is a field that is automatically growing, we cannot know the value of the record before it is formally inserted into the database, and only after the update can we see what value the database assigns to it, and then use this ID as the OrderID value of the OrderDetail table. Finally, update the Oderdetail table. However, to ensure data consistency, the order and OrderDetail must be updated at the same time under transaction protection, that is, to make sure that the two tables are more successful at the same time, this is a bit disturbing.

There are two main types of approaches to solving such problems:

One is the method used by Microsoft in the online bookstore, which uses four stored procedures. Modify it to fit the present example.

--Stored procedure a

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 primary table

INSERT Orders (OrderDate) Select @OrderDate

SELECT @Id = @ @IDENTITY

--Insert child table

IF @ProductIDList is not NULL

EXECUTE insertorderdetailsbylist @Id, @ProductIdList, @numList, @PriceList

COMMIT TRANSACTION

return 0

--Stored Procedure two

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

--Stored Procedure three

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

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

--Stored Procedure four

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

When inserted, the Passed-in child table data is the nvarchar type of length 4000, each field uses "," split, and then calls the Popfirstword split and then calls InsertOrderDetail for saving, Because in the InsertOrder transaction processing, data security is also more secure, a few stored process design exquisite chic, very interesting, but the child table several data size can not exceed 4000 characters, I am afraid not very insurance.

The second method is that I am more commonly used, in order to be convenient, do not use the stored procedure, this example uses is vb.net.

' Classes that process data

public class DbTools

Private Const _identity_sql as String = "SELECT @ @IDENTITY as ID"

Private Const _id_for_replace as String = "_id_for_replace"

' Insert record on master table

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

' Master record

MyDBTools.SqlData.ExecuteNonQuery (trans, CommandType.Text, Main_sql)

' Returns the new ID number

ID = MyDBTools.SqlData.ExecuteScalar (trans, CommandType.Text, _identity_sql)

' From the record

If not Arparam are nothing Then

For all SQL in Arparam

' Will just get the ID number into

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

The above code has mydbtools, is the common database operations encapsulated class, this class for the database direct operation, experienced. NET database programmers will basically have, some well-known examples of programs are generally available.

The above is the general part, the following is the operation of the specific documents

Publid class Order

Public _orderdate as Date ' master table record

Public Childdt as DataTable ' child table records, structure consistent with 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 are 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

Two examples above to facilitate interpretation, the removal of some validation process, interested friends can refer to the online bookstore example to study the first method, or according to their own