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