三层调用sql存储过程

首先在数据库中创建存储过程

create database daily
go
use daily
create table bank
(
id int primary key identity(1,1),
name varchar(15) not null,
money int default(0) check(money>=0)
)
insert into bank values('王华',100)
insert into bank values('李丽',0)

update bank set money=100 where id=1
select * from bank

go

create proc exchange 
@from int,
@to int,
@money int,
@error int output
as
begin tran
set @error=0
begin try
update bank set money=money-@money where id=@from
set @error=@@ERROR+@error
update bank set money=money+@money where id=@to
set @error=@@ERROR+@error
end try
begin catch
set @error=@error+ERROR_NUMBER()
end catch
if @error<>0
begin
 rollback tran
 print'转账失败'
 end
else
begin
 commit tran
 print'转账成功'
 end
 go

 declare @error int
 exec exchange 2,1,40,@error output

 drop proc exchange

打开vs修改dbhelp的非查询

public static int Excute(string sql, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(connst))
            {
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    comm.CommandType = System.Data.CommandType.StoredProcedure;//定义指令类型为存储过程
                    if (sqlParameters != null && sqlParameters.Count() > 0)
                        comm.Parameters.AddRange(sqlParameters);//传参数
                    conn.Open();
                    int num = 0;//定义num用来判断程序是否执行成功
                    try//
                    {
                        num = comm.ExecuteNonQuery();//执行存储过程
                    }
                    catch
                    {
                        num = 0;//获取执行后返回结果
                    }
                    finally
                    {
                        conn.Close();//结束
                    }
                    return num;
                }
            }

在数据访问层中编写执行存储过程的方法，然后在BLL中也编写相应方法

public static int Nonquery(int from,int to,int money)//int可以为bool
        {
            SqlParameter[] parameters =
            {
                new SqlParameter ("@from",from),
                new SqlParameter("@to",to),
                new SqlParameter("@money",money),
                new SqlParameter("@error",0)
            };
            parameters[3].Direction = System.Data.ParameterDirection.Output;//获取到下标为3的输出参数
            dbhelp.Excute("exchange", parameters);//执行
            return Convert.ToInt32(parameters[3].Value);//输出该参数
        }
    }

在UI层调用

 protected void bt_a_Click(object sender, EventArgs e)//按钮点击后执行
        {
            int from=Convert.ToInt32(this.a.Text);
            int to =Convert.ToInt32(this.b.Text);
            int money =Convert.ToInt32(this.c.Text);
            int num = bankBLL.Nonquery(from,to,money);//获取参数
            if (num==0)//判断num数值确定是否成功执行
            {
                Response.Write("<script>alert('转账成功')</script>");
            }
            else
            {
                Response.Write("<script>alert('转账失败')</script>");
            }
        }
    }