存储过程 事物 实现转账

     sqlserver数据库

create proc usp_zhuanzhang @outputnumber varchar(10),@inputnumber varchar(10),@money money,@result bit output as
begin
begin tran
begin try
update T_zhuanzhang set  outinmoney=outinmoney-@money wherenumber=@outputnumber and @money<outinmoney;
update T_zhuanzhang set  outinmoney=outinmoney+@money wherenumber=@inputnumber and @money<outinmoney;
set @result=1;
commit
end try
begin catch
set @result=0;
rollback
end catch
end

 

ADO

protected void Button1_Click(object sender, EventArgs e)
        {
            string constr = @"Data Source=PC-20121107KEFI\MSAS;Initial Catalog=Company;User ID=sa;Password=12345";
            using (SqlConnection conn = new SqlConnection(constr)) {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    string usp_zhuanzhang = "usp_zhuanzhang";
                    cmd.CommandText=usp_zhuanzhang;
                    cmd.Parameters.Add(new SqlParameter("@outputnumber",TextBox1.Text));
                    cmd.Parameters.Add(new SqlParameter("@inputnumber",TextBox3.Text));
                    cmd.Parameters.Add(new SqlParameter("@money",TextBox2.Text));
                    SqlParameter prm=new SqlParameter("@result",SqlDbType.Bit);
                    cmd.Parameters.Add(prm);
                    prm.Direction=ParameterDirection.Output;
                    SqlTransaction tran =new SqlTransaction();                   
                    cmd.ExecuteNonQuery();
                   
                    if ((bool)prm.Value==true)
                    {
                        Label4.Text = "转账成功！";
                        tran.Commit();
                    }
                    else {
                        Label4.Text = "转账失败！";
                        tran.Rollback();
                    }
                }
            }
        }