使用Transaction访问数据库(C#,TransactionScope,.NET 2.0)
来源:互联网 发布:d3.js 拓扑关系图 编辑:程序博客网 时间:2024/06/02 07:28
来源于:http://www.cppblog.com/ganmuren/archive/2011/03/07/141263.html
针对SQL2005和.NET 2.0的事物机制有了新的突破
传统数据库事物访问机制,代码如下:
1 public void TransactionTest()
2 {
3 string connectionString = "";
4 IDbConnection connection = new SqlConnection(connectionString);
5 connection.Open();
6 IDbCommand command = new SqlCommand();
7 command.Connection = connection;
8 IDbTransaction transaction;
9 transaction = connection.BeginTransaction(); //Enlisting database
10 command.Transaction = transaction;
11 try
12 {
13 /**//* Interact with database here, then commit the transaction
14 */
15 transaction.Commit();
16 }
17 catch
18 {
19 transaction.Rollback(); //Abort transaction
20 }
21 finally
22 {
23 connection.Close();
24 }
25 }
26
2 {
3 string connectionString = "";
4 IDbConnection connection = new SqlConnection(connectionString);
5 connection.Open();
6 IDbCommand command = new SqlCommand();
7 command.Connection = connection;
8 IDbTransaction transaction;
9 transaction = connection.BeginTransaction(); //Enlisting database
10 command.Transaction = transaction;
11 try
12 {
13 /**//* Interact with database here, then commit the transaction
14 */
15 transaction.Commit();
16 }
17 catch
18 {
19 transaction.Rollback(); //Abort transaction
20 }
21 finally
22 {
23 connection.Close();
24 }
25 }
26
或者这种,其实都差不多
1 using System.Data.SqlClient
2 DataTable dt = new DataTable();
3 SqlConnection cnn = new SqlConnection("连接字符串");
4 SqlCommand cm = new SqlCommand();
5 cm.Connection = cnn;
6 cnn.Open();
//一定要BeginTransaction()
7 SqlTransaction trans = cnn.BeginTransaction();
8 try
9 {
10 foreach(DataRow dr in dt.Rows)
11 {
12 cm.CommandText = "update [表] set [数量] = @amount where productID = @productID";
13 cm.Parameters.Add("@amount",SqlDbType.Int);
14 cm.Parameters["@amount"].Value = Convert.ToInt32(dr["amount"]);
15 cm.Parameters.Add("@productID",SqlDbType.VarChar);
16 cm.Parameters["@productID"].Value = dr["productID"].ToString();
17 cm.ExecuteNonQuery();
18 }
19 trans.Commit();
20 }
21 catch
22 {
23 trans.Rollback();
24 }
25 finally
26 {
27 cnn.Close();
28 trans.Dispose();
29 cnn.Dispose();
30 }
31 }
2 DataTable dt = new DataTable();
3 SqlConnection cnn = new SqlConnection("连接字符串");
4 SqlCommand cm = new SqlCommand();
5 cm.Connection = cnn;
6 cnn.Open();
//一定要BeginTransaction()
7 SqlTransaction trans = cnn.BeginTransaction();
8 try
9 {
10 foreach(DataRow dr in dt.Rows)
11 {
12 cm.CommandText = "update [表] set [数量] = @amount where productID = @productID";
13 cm.Parameters.Add("@amount",SqlDbType.Int);
14 cm.Parameters["@amount"].Value = Convert.ToInt32(dr["amount"]);
15 cm.Parameters.Add("@productID",SqlDbType.VarChar);
16 cm.Parameters["@productID"].Value = dr["productID"].ToString();
17 cm.ExecuteNonQuery();
18 }
19 trans.Commit();
20 }
21 catch
22 {
23 trans.Rollback();
24 }
25 finally
26 {
27 cnn.Close();
28 trans.Dispose();
29 cnn.Dispose();
30 }
31 }
至少需要try catch,
如今使用TransactionScope
1 using(TransactionScope scope = new TransactionScope())
2 {
3 /**//* Perform transactional work here */
4 //No errors - commit transaction
5 scope.Complete();
6 }
7
2 {
3 /**//* Perform transactional work here */
4 //No errors - commit transaction
5 scope.Complete();
6 }
7
一句using中的new,然后scape.complete()就解决了。
TransactionScope是Transaction的精简版,也能很好的完成工作。
另外对嵌套事务和事务的隔离级别也提供了支持
1 using(TransactionScope scope1 = new TransactionScope())
2 //Default is Required
3 {
4 using(TransactionScope scope2 = new
5 TransactionScope(TransactionScopeOption.Required))
6 {}
7 using(TransactionScope scope3 = new
8 TransactionScope(TransactionScopeOption.RequiresNew))
9 {}
10 using(TransactionScope scope4 = new
11 TransactionScope(TransactionScopeOption.Suppress))
12 {}
13
14 }
15
2 //Default is Required
3 {
4 using(TransactionScope scope2 = new
5 TransactionScope(TransactionScopeOption.Required))
6 {}
7 using(TransactionScope scope3 = new
8 TransactionScope(TransactionScopeOption.RequiresNew))
9 {}
10 using(TransactionScope scope4 = new
11 TransactionScope(TransactionScopeOption.Suppress))
12 {}
13
14 }
15
下面是对嵌套事务的说明:
嵌套事务的具体请看:http://perhaps.cnblogs.com/archive/2005/08/17/216863.html
需要注意的是:使用transaction时,尽量在其中做必需要的操作,其它和数据库无关的操作就放在transaction外面。毕竟SQL事务处理时,是一种独占的状态。尽快使用完毕释放资源非常重要。
- 使用Transaction访问数据库(C#,TransactionScope,.NET 2.0)
- .net 2.0 的 Distributed Transactions(使用TransactionScope )
- .net如何使用 TransactionScope
- 解决.NET使用TransactionScope,操作多个oracle数据库时报6107错误
- 使用ADO.NET访问数据库
- 使用ADO.NET访问数据库
- 使用ADO.NET访问数据库
- 使用perl.net访问数据库
- 使用COBOL.net访问数据库
- .NET下使用Oracle数据库、数据库访问技术(上)
- .NET下使用Oracle数据库、数据库访问技术(下)
- C#.NET访问数据库的通用类(Access篇)
- C#.NET访问数据库的通用类(Access篇)
- .NET中DataReader与DataSet访问SqlServer数据库(C#)
- 使用TransactionScope完成事务(本机)
- .Net(c#) 客户端访问-Apace实时数据库
- Visual C++.NET数据库访问技术
- C#.net免客户端访问Oracle数据库
- Java分页代码(JFIS)
- android 进度条
- JavaScriptの实现
- C#创建COM组件
- Android中Intent中如何传递对象
- 使用Transaction访问数据库(C#,TransactionScope,.NET 2.0)
- css方框模型(盒模型Box Model)
- struts1 logic标签的使用
- GSM/GPRS MODEM 的上网设置
- Oracle 小记
- android 自动化测试
- 摘自林少波的 毕业5年决定你的一生
- WOW降低延迟输入
- html头部属性全接触