C# 使用DataSet+dataAdapter操作后台数据库之事务处理方法1，欢迎拍砖

以下代码展示了使用DataSet+dataAdapter操作后台数据库的方法，并提供了事务处理。

代码

1 using System;
2   using System.Web.Services;
3   using System.Data;
4   using System.Data.OleDb;
5
6   namespace TransactionTest
7 {
8 /// <summary>
9 /// Service1 的摘要说明
10 /// </summary>
11   [WebService(Namespace = " http://tempuri.org/ " )]
12 [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
13 [System.ComponentModel.ToolboxItem( false )]
14 // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
15 // [System.Web.Script.Services.ScriptService]
16   public class TransWsTest : System.Web.Services.WebService
17 {
18 // 私有属性
19   private OleDbConnection _DBConn;
20 private OleDbTransaction _DBTrans;
21
22 [WebMethod]
23 public string HelloWorld()
24 {
25 _DBConn.Close();
26 return " Hello World " ;
27 }
28
29 [WebMethod]
30 public string TransTest_Insert()
31 {
32 try
33 {
34
35 InsertData( " 马侃 " , " 研发部 " );
36 InsertData( " 李伟 " , " 研发部 " );
37 // InsertData("马侃", "研发部");
38
39 // 若不启用事务，则屏蔽此行
40   _DBTrans.Commit();
41
42 return " OK " ;
43 }
44 catch (Exception ex)
45 {
46 // 若不启用事务，则屏蔽此行
47   _DBTrans.Rollback();
48
49 return ex.Message;
50 }
51 finally
52 {
53 _DBConn.Close();
54 }
55 }
56
57 [WebMethod]
58 public string TransTest_Update()
59 {
60 try
61 {
62
63 UpdateData( " 马侃 " , " 研发部 " , " 马强 " , " 研发部 " );
64 UpdateData( " 李伟 " , " 研发部 " , " 马强 " , " 研发部 " );
65
66 // 若不启用事务，则屏蔽此行
67   _DBTrans.Commit();
68
69 return " OK " ;
70 }
71 catch (Exception ex)
72 {
73 // 若不启用事务，则屏蔽此行
74   _DBTrans.Rollback();
75
76 return ex.Message;
77 }
78 finally
79 {
80 _DBConn.Close();
81 }
82 }
83
84 // Insert Data
85 private void InsertData( string name, string department)
86 {
87 string sqlstr = " select name,department from TransTest where 1=2 " ;
88
89 DataSet ds = new DataSet();
90 OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlstr, _DBConn);
91
92 // 若不启用事务，则屏蔽此行
93 dataAdapter.SelectCommand.Transaction = _DBTrans;
94
95 OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter);
96 dataAdapter.Fill(ds, " TransTest " );
97
98 DataTable table = ds.Tables[ 0 ];
99
100 DataRow addRow = table.NewRow();
101 addRow[ " name " ] = name;
102 addRow[ " department " ] = department;
103
104 table.Rows.Add(addRow);
105
106 dataAdapter.Update(table.GetChanges());
107
108 table.AcceptChanges();
109 }
110
111 // Update Data
112 private void UpdateData( string oldname, string olddepartment, string newname, string newdepartment)
113 {
114 string sqlstr = " select id,name,department from TransTest where name=' " + oldname + " ' and department=' " + olddepartment + " ' " ;
115
116 DataSet ds = new DataSet();
117 OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlstr, _DBConn);
118
119 // 若不启用事务，则屏蔽此行
120 dataAdapter.SelectCommand.Transaction = _DBTrans;
121
122 OleDbCommandBuilder cb = new OleDbCommandBuilder(dataAdapter);
123 dataAdapter.Fill(ds, " TransTest " );
124
125 DataTable table = ds.Tables[ 0 ];
126
127 DataRow currRow = table.Rows[ 0 ];
128 currRow[ " name " ] = newname;
129 currRow[ " department " ] = newdepartment;
130
131 dataAdapter.Update(table.GetChanges());
132
133 table.AcceptChanges();
134 }
135
136 // Delete Data
137 private void DeleteData()
138 {
139
140 }
141
142 // Constructor Function
143 public TransWsTest()
144 {
145 _DBConn = fGetConn();
146 _DBConn.Open();
147
148 // 若不启用事务，则屏蔽此行
149 _DBTrans = _DBConn.BeginTransaction();
150 }
151
152 // 获取数据库链接对象Connection
153 private OleDbConnection fGetConn()
154 {
155 string connStr = " provider=msdaora.1;user id=bitsap;password=bitservice;data source=ttonline " ;
156
157 OleDbConnection conn = new OleDbConnection(connStr);
158
159 return conn;
160 }
161 }
162 }
163

 

我们可以在DataAdapter中使用一个事务，在遇到错误之前回滚DataAdapter所进行的更新。

 

如这个解决方案所示，使用一个CommandBuilder产生这个DataAdapter的更新逻辑，并把这个事务与DataAdapter的SelectCommand对象相关联，如下面代码所示：

dataAdapter.SelectCommand.Transaction = _DBTrans;

 

如果在DataAdapter中使用自定义的更新逻辑，则这个事务必须与这个DataAdapter的DeleteCommand、InsertCommand和UpdateCommand相关联，而不是和SelectCommand相关联，如下面代码所示：

dataAdapter.DeleteCommand.Transaction = _DBTrans;

dataAdapter.InsertCommand.Transaction = _DBTrans;

dataAdapter.UpdateCommand.Transaction = _DBTrans;

 

请各位高手拍砖，不当之处，请指正，谢谢。

转载于:https://www.cnblogs.com/advocate/archive/2010/06/25/1765455.html