应用VS2005 SQL事务和批量插入数据

 1 '创建表
 2dtAgenda = New DataTable
 3      dtAgenda.Columns.Add("ID", GetType(System.Int32))
 4      dtAgenda.Columns.Add("FromDate", GetType(System.DateTime))
 5      dtAgenda.Columns.Add("ToDate", GetType(System.DateTime))
 6      dtAgenda.Columns.Add("Region", GetType(System.String))
 7      dtAgenda.Columns.Add("Company", GetType(System.String))
 8      dtAgenda.Columns.Add("Content", GetType(System.String))
 9
10'================================
11  dtCost = New DataTable
12      dtCost.Columns.Add("ID", GetType(System.Int32))
13      dtCost.Columns.Add("FromDate", GetType(System.DateTime))
14      dtCost.Columns.Add("ToDate", GetType(System.DateTime))
15      dtCost.Columns.Add("Ticket", GetType(System.Int32))
16      dtCost.Columns.Add("Live", GetType(System.Int32))
17      dtCost.Columns.Add("Allowance", GetType(System.Int32))
18      dtCost.Columns.Add("Public", GetType(System.Int32))
19      dtCost.Columns.Add("Others", GetType(System.Int32))
20

 1  Dim tr As SqlTransaction = Nothing
 2       Dim cmd As New SqlCommand
 3       Dim insertCmd As SqlCommand
 4       Dim strSQL As String
 5       Dim da As SqlDataAdapter
 6       Dim FID As Int32 = Nothing
 7 
 8       Try
 9         If Not MyConnect() Then
10           Return False
11         End If
12 
13         strSQL = "insert into SMCGZ_BussinessTrip (WorkerID,FromDate,ToDate,ApplyDate) Values ('" + WorkerID + "','" + FromDate + "','" + ToDate + "','" + Now.Date.ToString + "')"
14 
15         tr = Conn.BeginTransaction
16         With cmd
17           .CommandText = strSQL
18           .Transaction = tr
19           .Connection = Conn
20           .ExecuteNonQuery()
21         End With
22 
23         strSQL = "select top(1) ID from SMCGZ_BussinessTrip order by ID DESC"
24         cmd.CommandText = strSQL
25         FID = cmd.ExecuteScalar()
26 
27 
28         strSQL = "insert into SMCGZ_BussinessContent (FID,FromDate,ToDate,Region,Company,Content) values (" & FID & ",@FromDate,@ToDate,@Region,@Company,@Content)"
29         da = New SqlDataAdapter()
30         insertCmd = New SqlCommand(strSQL, Conn)
31 
32         insertCmd.Parameters.Add("@FromDate", SqlDbType.DateTime)
33         insertCmd.Parameters.Add("@ToDate", SqlDbType.DateTime)
34         insertCmd.Parameters.Add("@Region", SqlDbType.NVarChar)
35         insertCmd.Parameters.Add("@Company", SqlDbType.NVarChar)
36         insertCmd.Parameters.Add("@Content", SqlDbType.NVarChar)
37 
38         insertCmd.Parameters.Item("@FromDate").SourceColumn = dtAgenda.Columns("FromDate").ColumnName
39         insertCmd.Parameters.Item("@ToDate").SourceColumn = dtAgenda.Columns("ToDate").ColumnName
40         insertCmd.Parameters.Item("@Region").SourceColumn = dtAgenda.Columns("Region").ColumnName
41         insertCmd.Parameters.Item("@Company").SourceColumn = dtAgenda.Columns("Company").ColumnName
42         insertCmd.Parameters.Item("@Content").SourceColumn = dtAgenda.Columns("Content").ColumnName
43 
44        
45         da.InsertCommand = insertCmd
46         da.InsertCommand.Transaction = tr
47         da.Update(dtAgenda)
48 
49 
50 
51         strSQL = "insert into SMCGZ_BussinessCost (FID,FromDate,ToDate,Ticket,Live,Allowance,[Public],Others) values (" & FID & ",@FromDate,@ToDate,@Ticket,@Live,@Allowance,@Public,@Others)"
52         insertCmd = New SqlCommand(strSQL, Conn)
53         insertCmd.Parameters.Add("@FromDate", SqlDbType.DateTime)
54         insertCmd.Parameters.Add("@ToDate", SqlDbType.DateTime)
55         insertCmd.Parameters.Add("@Ticket", SqlDbType.Int)
56         insertCmd.Parameters.Add("@Live", SqlDbType.Int)
57         insertCmd.Parameters.Add("@Allowance", SqlDbType.Int)
58         insertCmd.Parameters.Add("@Public", SqlDbType.Int)
59         insertCmd.Parameters.Add("@Others", SqlDbType.Int)
60 
61         insertCmd.Parameters.Item("@FromDate").SourceColumn = dtCost.Columns("FromDate").ColumnName
62         insertCmd.Parameters.Item("@ToDate").SourceColumn = dtCost.Columns("ToDate").ColumnName
63         insertCmd.Parameters.Item("@Ticket").SourceColumn = dtCost.Columns("Ticket").ColumnName
64         insertCmd.Parameters.Item("@Live").SourceColumn = dtCost.Columns("Live").ColumnName
65         insertCmd.Parameters.Item("@Allowance").SourceColumn = dtCost.Columns("Allowance").ColumnName
66         insertCmd.Parameters.Item("@Public").SourceColumn = dtCost.Columns("Public").ColumnName
67         insertCmd.Parameters.Item("@Others").SourceColumn = dtCost.Columns("Others").ColumnName
68 
69         da.InsertCommand = insertCmd
70         da.InsertCommand.Transaction = tr
71         da.Update(dtCost)
72 
73         tr.Commit()
74 
75         Return True
76 
77 
78       Catch ex As Exception
79         tr.Rollback()
80         Throw ex
81 
82       Finally
83         Conn.Close()
84 
85       End Try