1 , 在介绍Sql Server 的事务之前 , 我想先谈谈.net中利用System.Data.SqlClient里面的相关类 , 实现事务.春节福利啊
public void ProTranandler()
{
using (SqlConnection conn = new SqlConnection(this.CONN_LINK))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand scd = new SqlCommand();
scd.Connection = conn;
//--------------------------------------------------------------
//--------------调用"存储过程"
//scd.CommandText = "ProSql";
//scd.CommandType = CommandType.StoredProcedure;
//SqlParameter sqtIN = new SqlParameter("@inParam", SqlDbType.Int);
//sqtIN.Direction = ParameterDirection.Input;
//sqtIN.Value = 1;
//SqlParameter sqtOUT = new SqlParameter("@ok", SqlDbType.Int);
//sqtOUT.Direction = ParameterDirection.Output;
//scd.Parameters.Add(sqtIN);
//scd.Parameters.Add(sqtOUT);
try
{
scd.Transaction = tran;
scd.CommandText = "SQL1";
scd.ExecuteNonQuery();
scd.CommandText = "SQL2";
scd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
Console.WriteLine(e.Message);
}
finally
{
scd.Dispose();
conn.Close();
}
}
}注意 : SQL1 和SQL2要么都执行 , 要么都不执行.但只不是最好的方案 , 我喜欢用SQL的事务,优点是不需要编译,执行速度快 , 代码集成重用性高.
create proc ProTest @InParam int , @InParamStr varChar(50), @OutParam int output as begin tran declare @error int set @error = 0 --ＳＱＬ１ set @error = @@ERROR + @error --SQL2 set @error = @@ERROR + @error if @error > 0 begin set @OutParam = 0 rollback tran end else begin set @OutParam = 1 commit tran end
上面的注释代码 , 解释了如何调用
