存儲過程 事物 實作轉賬

     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  [email protected] where[email protected] and @money<outinmoney;

update T_zhuanzhang set  [email protected] where[email protected] 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();

                    }

                }

            }

        }