SQL 事务与 .net 事务
1.SQL事务是SQL Server 自身的事务,在存储过程中使用
CREATE PROCEDURE test AS BEGIN TRAN UPDATE Product SET ProductName='热销'+ProductName UPDATE User SET UserName=''+UserName IF @@ERROR=0 COMMIT TRAN ELSE ROLLBACK TRAN
2.ADO.NET事务
public void ADONetTran() { using (SqlConnection conn = new SqlConnection()) { SqlCommand com = new SqlCommand(); try { conn.ConnectionString = "Data Source=LocalHost;Initial Catelog=NorthWind; User ID=sa; Password=sa"; com.Connection = conn; conn.Open(); com.CommandText = "Update Region set RegionDescription=@RegionDescription Where RegionID=@RegionID"; com.CommandType = CommandType.Text; com.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@RegionDescription", "test"), new SqlParameter("@RegionID", "1") }); //开始事务 com.Transaction = conn.BeginTransaction(); com.ExecuteNonQuery(); com.CommandText = "Insert into Region (RegionID,,RegionDescription)values(@RegionID,@RegionDescription)"; com.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@RegionDescription", "test"), new SqlParameter("@RegionID", "1") }); com.ExecuteNonQuery(); com.Transaction.Commit(); } catch (Exception) { com.Transaction.Rollback(); throw; } } }
3.TransactionScope
public void ADONetTran() { using (SqlConnection conn = new SqlConnection()) { SqlCommand com = new SqlCommand(); try { using (TransactionScope ts=new TransactionScope ()) { conn.ConnectionString = "Data Source=LocalHost;Initial Catelog=NorthWind; User ID=sa; Password=sa"; com.Connection = conn; conn.Open(); com.CommandText = "Update Region set RegionDescription=@RegionDescription Where RegionID=@RegionID"; com.CommandType = CommandType.Text; com.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@RegionDescription", "test"), new SqlParameter("@RegionID", "1") }); com.ExecuteNonQuery(); com.CommandText = "Insert into Region (RegionID,,RegionDescription)values(@RegionID,@RegionDescription)"; com.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@RegionDescription", "test"), new SqlParameter("@RegionID", "1") }); com.ExecuteNonQuery(); ts.Complete(); } } catch (Exception) { throw; } } }