sql server中使用事务
银行转账的例子，用户0001向用户0002转帐10000元
begin transaction --开始
begin try
update NUser set Money = Money - 10000 where UserID='0001'
raiserror('更新有错误',16,1)
update NUser set Money = Money + 10000 where UserID='0002'
commit --提交
end try
begin catch
if @@TRANCOUNT >0 and @@error >0
select ERROR_LINE(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE()
rollback--回滚
end catch
C#编码实现事务
SqlTransaction sqltran = null;
try
{
//1 获取界面用户输入数据
string userid_out = textBoxOut.Text.Trim();//转出用户账号
string userid_in = textBoxIn.Text.Trim();//转入用户账号
decimal amt = Convert.ToDecimal(textBoxAmt.Text.Trim());
//2 打开数据库连接对象
string connstr = "server=(local)\\SqlExpress;Integrated Security=SSPI;database = DB";
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = connstr;
if (sqlconn.State != ConnectionState.Open)
{
sqlconn.Open();
sqltran = sqlconn.BeginTransaction();//开始事务
}
//3 转入账户金额处理
SqlParameter[] cmdparams_in = new SqlParameter[]{
new SqlParameter("@UserID",userid_in),
new SqlParameter("@Amt",amt)
};
SqlCommand sqlcmd_in = new SqlCommand();
sqlcmd_in.Connection = sqlconn;
sqlcmd_in.CommandText = "update NUser set money = money + @Amt where UserID = @UserID";
sqlcmd_in.CommandType = CommandType.Text;
sqlcmd_in.Parameters.AddRange(cmdparams_in);
sqlcmd_in.Transaction = sqltran;
int in_rows = sqlcmd_in.ExecuteNonQuery();
if (in_rows == 0)
{
throw new Exception("转入账户不存在。");
}
//4 转出账户金额处理
SqlParameter[] cmdparams_out = new SqlParameter[]{
new SqlParameter("@UserID",userid_out),
new SqlParameter("@Amt",amt)
};
SqlCommand sqlcmd_out = new SqlCommand();
sqlcmd_out.Connection = sqlconn;
sqlcmd_out.CommandText = "update NUser set money = money - @Amt where UserID = @UserID";
sqlcmd_out.CommandType = CommandType.Text;
sqlcmd_out.Parameters.AddRange(cmdparams_out);
sqlcmd_out.Transaction = sqltran;
int out_rows = sqlcmd_out.ExecuteNonQuery();
if (out_rows == 0)
{
throw new Exception("转出账户不存在。");
}
sqltran.Commit(); //提交事务
//5 提示处理结果
MessageBox.Show("转账成功。");
}
catch (Exception ex)
{
if (sqltran != null)
{
sqltran.Rollback();
}
MessageBox.Show(ex.Message);
}
| 
转播
