this is my sql-transcation pattern in C#.
using (SqlConnection cn = new SqlConnection(connString)) {
cn.Open();
using (SqlTransaction tn = cn.BeginTransaction()) {
try {
using (SqlCommand cmd = cn.CreateCommand()) {
cmd.Transaction = tn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter()).Direction
= ParameterDirection.ReturnValue;
cmd.CommandText = "MyStoredProcedure1";
cmd.ExecuteNonQuery();
if ((int)cmd.Parameters[0].Value != 0)
throw new Exception("MyStoredProcedure1 Failed.");
cmd.CommandText = "MyStoredProcedure2";
cmd.ExecuteNonQuery();
if ((int)cmd.Parameters[0].Value != 0)
throw new Exception("MyStoredProcedure2 Failed.");
cmd.CommandText = "MyStoredProcedure3";
cmd.ExecuteNonQuery();
if ((int)cmd.Parameters[0].Value != 0)
throw new Exception("MyStoredProcedure3 Failed.");
cmd.CommandText = "MyStoredProcedure4";
cmd.ExecuteNonQuery();
if ((int)cmd.Parameters[0].Value != 0)
throw new Exception("MyStoredProcedure4 Failed.");
tn.Commit();
}
} catch (Exception ex) {
tn.Rollback();
}
}
}
you can handle it by StoredProcedure's ReturnValue,like this
Create Proc MyStoredProcedure3
AS begin
Update myTable set MyField1='value' where MyKey='key'
IF @@ROWCOUNT = 0
RETURN 1
ELSE
RETURN 0
end
hope it can help u. :)