使用TransactionScope,将一段代码包含在事务内
2007-03-21 08:46 ruinet 阅读(781) 评论(0) 编辑 收藏 举报将一段代码包含在事务内,使用TransactionScope,该事务处理是在.NET Framework 2.0 版中是新增的。
在使用时要添加system.transactions.dll 和System.Transactions引用
在asp.net 2.0 中你可以把你要处理的一段代码包含在
Using scope As New Transactions.TransactionScope()和scope.Complete()(成功提交事务)之间,这样非常简单的实现事务处理
下面是我在实际中运用的一段代码.
1 Public Function AddExpense(ByVal WorkerId As String, ByVal dtItem As DataTable, ByVal Month As String) As Boolean
2 Dim strSQL As String
3 Dim da As SqlDataAdapter
4 Dim cmd As New SqlCommand
5 Dim FID As Int32
6 Try
7 Using scope As New Transactions.TransactionScope()
8
9 If Not MyConnect() Then
10 Return False
11 End If
12
13 strSQL = "insert into SMCGZ_Expense (Month,WorkerID) values ('" + Month + "','" + WorkerId + "')"
14
15 With cmd
16 .CommandText = strSQL
17 .Connection = Conn
18 .ExecuteNonQuery()
19 End With
20
21 strSQL = "select Max(ID) from SMCGZ_Expense"
22 cmd.CommandText = strSQL
23 FID = cmd.ExecuteScalar()
24
25 If FID < 0 Then
26 Return False
27 End If
28
29 strSQL = "insert into SMCGZ_ExpenseItem (FID,Date,Time,OneDay,Content,Breakfast,Lunch,Live,TripRelation,Supper,Taxi,Car,Plane,Motor,LunchSubsidy,WorkFee,RelationsFee,ServiceFee,Others)" & _
30 "Values (" & FID & ",@Date,@Time,@OneDay,@Content,@Breakfast,@Lunch,@Live,@TripRelation,@Supper,@Taxi,@Car,@Plane,@Motor,@LunchSubsidy,@WorkFee,@RelationFee,@ServiceFee,@Others)"
31 cmd = New SqlCommand(strSQL, Conn)
32 cmd.Parameters.Add("@Date", SqlDbType.DateTime)
33 cmd.Parameters.Add("@Time", SqlDbType.NVarChar)
34 cmd.Parameters.Add("@OneDay", SqlDbType.Bit)
35 cmd.Parameters.Add("@Content", SqlDbType.NVarChar)
36 cmd.Parameters.Add("@Breakfast", SqlDbType.Int)
37 cmd.Parameters.Add("@Lunch", SqlDbType.Int)
38 cmd.Parameters.Add("@Live", SqlDbType.Int)
39 cmd.Parameters.Add("@TripRelation", SqlDbType.Int)
40 cmd.Parameters.Add("@Supper", SqlDbType.Int)
41 cmd.Parameters.Add("@Taxi", SqlDbType.Int)
42 cmd.Parameters.Add("@Car", SqlDbType.Int)
43 cmd.Parameters.Add("@Plane", SqlDbType.Int)
44 cmd.Parameters.Add("@Motor", SqlDbType.Int)
45 cmd.Parameters.Add("@LunchSubsidy", SqlDbType.Int)
46 cmd.Parameters.Add("@WorkFee", SqlDbType.Int)
47 cmd.Parameters.Add("@RelationFee", SqlDbType.Int)
48 cmd.Parameters.Add("@ServiceFee", SqlDbType.Int)
49 cmd.Parameters.Add("@Others", SqlDbType.Int)
50
51 cmd.Parameters.Item("@Date").SourceColumn = dtItem.Columns("Date").ColumnName
52 cmd.Parameters.Item("@Time").SourceColumn = dtItem.Columns("Time").ColumnName
53 cmd.Parameters.Item("@OneDay").SourceColumn = dtItem.Columns("OneDay").ColumnName
54 cmd.Parameters.Item("@Content").SourceColumn = dtItem.Columns("Content").ColumnName
55 cmd.Parameters.Item("@Breakfast").SourceColumn = dtItem.Columns("Breakfast").ColumnName
56 cmd.Parameters.Item("@Lunch").SourceColumn = dtItem.Columns("Lunch").ColumnName
57 cmd.Parameters.Item("@Live").SourceColumn = dtItem.Columns("Live").ColumnName
58 cmd.Parameters.Item("@TripRelation").SourceColumn = dtItem.Columns("TripRelation").ColumnName
59 cmd.Parameters.Item("@Supper").SourceColumn = dtItem.Columns("Supper").ColumnName
60 cmd.Parameters.Item("@Taxi").SourceColumn = dtItem.Columns("Taxi").ColumnName
61 cmd.Parameters.Item("@Car").SourceColumn = dtItem.Columns("Car").ColumnName
62 cmd.Parameters.Item("@Plane").SourceColumn = dtItem.Columns("Plane").ColumnName
63 cmd.Parameters.Item("@Motor").SourceColumn = dtItem.Columns("Motor").ColumnName
64 cmd.Parameters.Item("@LunchSubsidy").SourceColumn = dtItem.Columns("LunchSubsidy").ColumnName
65 cmd.Parameters.Item("@WorkFee").SourceColumn = dtItem.Columns("WorkFee").ColumnName
66 cmd.Parameters.Item("@RelationFee").SourceColumn = dtItem.Columns("RelationFee").ColumnName
67 cmd.Parameters.Item("@ServiceFee").SourceColumn = dtItem.Columns("ServiceFee").ColumnName
68 cmd.Parameters.Item("@Others").SourceColumn = dtItem.Columns("Others").ColumnName
69
70 da = New SqlDataAdapter
71 da.InsertCommand = cmd
72 da.Update(dtItem)
73
74 scope.Complete()
75 Return True
76 End Using
77 Catch ex As Exception
78 Throw ex
79 Finally
80 Conn.Close()
81 End Try
82 End Function
2 Dim strSQL As String
3 Dim da As SqlDataAdapter
4 Dim cmd As New SqlCommand
5 Dim FID As Int32
6 Try
7 Using scope As New Transactions.TransactionScope()
8
9 If Not MyConnect() Then
10 Return False
11 End If
12
13 strSQL = "insert into SMCGZ_Expense (Month,WorkerID) values ('" + Month + "','" + WorkerId + "')"
14
15 With cmd
16 .CommandText = strSQL
17 .Connection = Conn
18 .ExecuteNonQuery()
19 End With
20
21 strSQL = "select Max(ID) from SMCGZ_Expense"
22 cmd.CommandText = strSQL
23 FID = cmd.ExecuteScalar()
24
25 If FID < 0 Then
26 Return False
27 End If
28
29 strSQL = "insert into SMCGZ_ExpenseItem (FID,Date,Time,OneDay,Content,Breakfast,Lunch,Live,TripRelation,Supper,Taxi,Car,Plane,Motor,LunchSubsidy,WorkFee,RelationsFee,ServiceFee,Others)" & _
30 "Values (" & FID & ",@Date,@Time,@OneDay,@Content,@Breakfast,@Lunch,@Live,@TripRelation,@Supper,@Taxi,@Car,@Plane,@Motor,@LunchSubsidy,@WorkFee,@RelationFee,@ServiceFee,@Others)"
31 cmd = New SqlCommand(strSQL, Conn)
32 cmd.Parameters.Add("@Date", SqlDbType.DateTime)
33 cmd.Parameters.Add("@Time", SqlDbType.NVarChar)
34 cmd.Parameters.Add("@OneDay", SqlDbType.Bit)
35 cmd.Parameters.Add("@Content", SqlDbType.NVarChar)
36 cmd.Parameters.Add("@Breakfast", SqlDbType.Int)
37 cmd.Parameters.Add("@Lunch", SqlDbType.Int)
38 cmd.Parameters.Add("@Live", SqlDbType.Int)
39 cmd.Parameters.Add("@TripRelation", SqlDbType.Int)
40 cmd.Parameters.Add("@Supper", SqlDbType.Int)
41 cmd.Parameters.Add("@Taxi", SqlDbType.Int)
42 cmd.Parameters.Add("@Car", SqlDbType.Int)
43 cmd.Parameters.Add("@Plane", SqlDbType.Int)
44 cmd.Parameters.Add("@Motor", SqlDbType.Int)
45 cmd.Parameters.Add("@LunchSubsidy", SqlDbType.Int)
46 cmd.Parameters.Add("@WorkFee", SqlDbType.Int)
47 cmd.Parameters.Add("@RelationFee", SqlDbType.Int)
48 cmd.Parameters.Add("@ServiceFee", SqlDbType.Int)
49 cmd.Parameters.Add("@Others", SqlDbType.Int)
50
51 cmd.Parameters.Item("@Date").SourceColumn = dtItem.Columns("Date").ColumnName
52 cmd.Parameters.Item("@Time").SourceColumn = dtItem.Columns("Time").ColumnName
53 cmd.Parameters.Item("@OneDay").SourceColumn = dtItem.Columns("OneDay").ColumnName
54 cmd.Parameters.Item("@Content").SourceColumn = dtItem.Columns("Content").ColumnName
55 cmd.Parameters.Item("@Breakfast").SourceColumn = dtItem.Columns("Breakfast").ColumnName
56 cmd.Parameters.Item("@Lunch").SourceColumn = dtItem.Columns("Lunch").ColumnName
57 cmd.Parameters.Item("@Live").SourceColumn = dtItem.Columns("Live").ColumnName
58 cmd.Parameters.Item("@TripRelation").SourceColumn = dtItem.Columns("TripRelation").ColumnName
59 cmd.Parameters.Item("@Supper").SourceColumn = dtItem.Columns("Supper").ColumnName
60 cmd.Parameters.Item("@Taxi").SourceColumn = dtItem.Columns("Taxi").ColumnName
61 cmd.Parameters.Item("@Car").SourceColumn = dtItem.Columns("Car").ColumnName
62 cmd.Parameters.Item("@Plane").SourceColumn = dtItem.Columns("Plane").ColumnName
63 cmd.Parameters.Item("@Motor").SourceColumn = dtItem.Columns("Motor").ColumnName
64 cmd.Parameters.Item("@LunchSubsidy").SourceColumn = dtItem.Columns("LunchSubsidy").ColumnName
65 cmd.Parameters.Item("@WorkFee").SourceColumn = dtItem.Columns("WorkFee").ColumnName
66 cmd.Parameters.Item("@RelationFee").SourceColumn = dtItem.Columns("RelationFee").ColumnName
67 cmd.Parameters.Item("@ServiceFee").SourceColumn = dtItem.Columns("ServiceFee").ColumnName
68 cmd.Parameters.Item("@Others").SourceColumn = dtItem.Columns("Others").ColumnName
69
70 da = New SqlDataAdapter
71 da.InsertCommand = cmd
72 da.Update(dtItem)
73
74 scope.Complete()
75 Return True
76 End Using
77 Catch ex As Exception
78 Throw ex
79 Finally
80 Conn.Close()
81 End Try
82 End Function