SQL事务与ADO.NET事务

- 日理万妓 2021-12-16 11:27 210阅读 0赞

**1 SQL事务**  
 sql事务是使用SQL server自身的事务：在存储过程中直接使用Begin Tran，Rollback Tran，Commit Tran实现事务：  
优点：执行效率最佳  
限制：事务上下文仅在数据库中调用，难以实现复杂的业务逻辑。  
Demo：（所有demo,都以SQL Server自带的Northwind数据的表Region为例）  
带事务的储存过程:  
CREATEPROCEDURE dbo.SPTransaction  
![None.gif][] (  
![None.gif][]@UpdateID**int**,  
@UpdateValue**nchar**(**50**),  
![None.gif][]@InsertID**int**,  
![None.gif][]@InsertValue**nchar**(**50**)  
![None.gif][] )  
![None.gif][]AS  
![None.gif][]beginTran  
![None.gif][]Update Region Set RegionDescription=@UpdateValuewhere[RegionID][]=@UpdateID![None.gif][]  
![None.gif][]insertinto Region Values (@InsertID,@InsertValue)![None.gif][]  
![None.gif][]declare@RegionError**int**  
![None.gif][]select@RegionError=**@@error**  
![None.gif][]if(@RegionError=**0**)  
![None.gif][]COMMITTran  
![None.gif][]else  
![None.gif][]ROLLBACKTran  
![None.gif][]GO  
执行带事务的储存过程:  
///<summary>  
/// SQL事务:  
///</summary>  
![None.gif][]publicvoid SQLTran()  
\{  
SqlConnection conn =new SqlConnection("Data Source=127.0.0.1;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=123;");  
SqlCommand cmd =new SqlCommand();  
cmd.CommandText ="SPTransaction";  
cmd.CommandType = CommandType.StoredProcedure;  
cmd.Connection = conn;  
conn.Open();  
SqlParameter\[\] paras=new SqlParameter\[\]\{  
new SqlParameter ("@UpdateID",SqlDbType.Int,32),  
new SqlParameter ("@UpdateValue",SqlDbType .NChar,50),  
new SqlParameter ("@InsertID",SqlDbType.Int ,32),  
new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)\};  
paras\[0\].Value ="2";  
paras\[1\].Value ="Update Value1";  
paras\[2\].Value ="6";  
paras\[3\].Value ="Insert Value1";  
foreach (SqlParameter para in paras )  
\{  
cmd.Parameters.Add(para);  
\}  
cmd.ExecuteNonQuery();  
\}  
**2 ADO.net事务**  
Ado.net事务可能是大家一般都用的  
优点：简单，效率和数据库事务差不多。  
缺点：事务不能跨数据库，只能在一个数据库连接上。如果是两个数据库上就不能使用该事务了。  
Demo:  
 ///<summary>  
/// 一般的ADO.net 事务  
///</summary>  
![None.gif][]publicvoid ADONetTran1()  
\{  
SqlConnection conn =new SqlConnection("Data Source=127.0.0.1;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=123;");  
SqlCommand cmd =new SqlCommand();  
try  
\{  
cmd.CommandText ="Update Region Set RegionDescription=@UpdateValue where RegionID=@UpdateID";  
cmd.CommandType = CommandType.Text;  
cmd.Connection = conn;  
conn.Open();  
SqlParameter\[\] paras =new SqlParameter\[\]\{  
new SqlParameter ("@UpdateID",SqlDbType.Int,32),  
new SqlParameter ("@UpdateValue",SqlDbType .NChar,50)\};  
paras\[0\].Value ="2";  
paras\[1\].Value ="Update Value12";  
  
foreach (SqlParameter para in paras)  
\{  
cmd.Parameters.Add(para);  
\}  
//开始事务  
 cmd.Transaction = conn.BeginTransaction();  
cmd.ExecuteNonQuery();  
  
  
cmd.CommandText ="insert into Region values(@InsertID,@InsertValue)";  
cmd.CommandType = CommandType.Text;  
  
paras =new SqlParameter\[\]\{  
new SqlParameter ("@InsertID",SqlDbType.Int ,32),  
new SqlParameter ("@InsertValue",SqlDbType.NChar ,50)\};  
paras\[0\].Value ="7";  
paras\[1\].Value ="Insert Value";  
cmd.Parameters.Clear();  
foreach (SqlParameter para in paras)  
\{  
cmd.Parameters.Add(para);  
\}  
cmd.ExecuteNonQuery();  
//提交事务  
 cmd.Transaction.Commit();  
\}  
catch  
\{  
//回滚事务  
 cmd.Transaction.Rollback();  
throw;  
\}  
finally  
\{  
conn.Close();  
\}  
\}

转载于:https://www.cnblogs.com/30ErLi/archive/2010/09/17/1829456.html

[None.gif]: /images/20211214/ec68055c726845a68a2a5cb9be646032.png
[RegionID]: mailto:RegionID=@UpdateID