使用C#三层架构调用数据库存储过程

1.要想调用存储过程，那么就得先在sql中创建存储过程

1.1不带参不带返回值

---
--判断是否存在
if exists(select * from sysobjects where name = 'proc_AssetInfo')
drop proc proc_AssetInfo
go
--创建一个关于资产信息的存储过程
create proc proc_AssetInfo
as
--在这里写一系列的操作
select * from Assetlnfo
go
--在sql中这样调用，通过存储过程的名字调用
exec proc_AssetInfo

1.2带输入参数，带返回值

create proc proc_AssetInfo
--输入参数不用写declare 
@AssetName varchar(20)
as 
	declare @Counts int
	select * from Assetlnfo where AssetName=@AssetName
	select @Counts=count(*) from Assetlnfo
	--返回值
	return @Counts
go
--sql中这样调用并输出
declare @Counts int
exec @Counts = proc_AssetInfo '冰箱'
print Counts 

1.3–带输入参数，带输出参数，带返回值

create proc proc_AssetInfo
@AssetName varchar(20),输入参数不用写declare 
@AssetID int out --输出参数后面加上out/output
as 
begin
	declare @Counts int
	select * from Assetlnfo where AssetName=@AssetName
	select @AssetID=AssetID from (select * from Assetlnfo where AssetName=@AssetName) a
	select @Counts=count(*) from Assetlnfo 
	--返回值
	return @Counts
end
go
--调用，一定要定义变量去接收它所返回的值和输出的值
declare @AssetID int ,@Countss int
exec @Countss = proc_AssetInfo'冰箱',@AssetID out
print '资产ID是'+convert(varchar(10),@AssetID)
print '资产数量是'+ convert(varchar(10), @Countss)

以上这几种存储过程不限，根据自己想要执行什么样的操作去修改里面的内容。存储过程中也可以写事务

2.创建好存储过程，打开我们正在写的项目，在DBHelper中编写以下代码

2.1存储过程中有查询的结果集所调用的

 public static DataTable ExecuteQueryProc(string sql, params SqlParameter[] paras)
 {
     using (SqlConnection conn = new SqlConnection(connStr))
     {
         conn.Open();
         SqlCommand cmd = new SqlCommand(sql, conn);
         if (paras != null)
         {
             cmd.Parameters.AddRange(paras);
         }
         //在这里指定执行的是存储过程
         cmd.CommandType = CommandType.StoredProcedure;
         //执行完存储过程所得的结果集接收一下
         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
         DataTable dt = new DataTable();
         adapter.Fill(dt);
         return dt;
     }
 }

2.2存储过程中没有查询的结果集所调用的

 public static int ExecuteNonQueryProc(string sql, params SqlParameter[] paras)
 {
     using (SqlConnection conn = new SqlConnection(connStr))
     {
         conn.Open();
         SqlCommand cmd = new SqlCommand(sql, conn);
         if (paras != null)
         {
             cmd.Parameters.AddRange(paras);
         }
          //在这里指定执行的是存储过程
         cmd.CommandType = CommandType.StoredProcedure;
         //没有结果集直接返回执行完所影响的行数
         return cmd.ExecuteNonQuery();
     }
 }

3.写完DBHelper后，然后在数据访问层对上面写的存储过程进行调用

public int AddAsset(Assetlnfo assetlnfo)
{
//跟执行定义sql语句一样，这里只是吧T-SQL语句换成存储过程的名字
   string sql = "proc_AssetInfo";
   
   //使用SqlParamete可以传入参数给带有输入参数的存储过程传参
   //如果存储过程中没有输入参数、输出参数和返回结果，可以不用写SqlParameter，
    SqlParameter[] pars =
    {
        new SqlParameter("@AssetTypeID",assetlnfo.AssetTypeID),
        new SqlParameter("@AssetName",assetlnfo.AssetName),
        new SqlParameter("@AssetModel",assetlnfo.AssetModel),
        new SqlParameter("@AssetCompany",assetlnfo.AssetCompany),
        new SqlParameter("@AssetReMark",assetlnfo.AssetReMark),
		//也可以在里面自定义一些参数，前提是存储过程中得有返回值或者输出参数
        new SqlParameter("@returnVale",SqlDbType.Int)  //SqlDbType指定参数的类型
        new SqlParameter("@outputVale",SqlDbType.Int) //输出或返回的是什么类型就指定是什么类型
    };
    
    //指定SqlParameter里面的参数是什么样的值，这里指定ReturnValue：是一个返回的值
    pars[5].Direction = ParameterDirection.ReturnValue;
    //指定SqlParameter里面的参数是什么样的值，这里指定output：是一个输出参数的值
    pars[6].Direction = ParameterDirection.output;
    
    //在调用DBHelper中方法时，第二个参数可以传null，也可以不用写
    int con = DBHelper.ExecuteNonQueryProc(sql, pars);//（1）
    datatable dt = DBHelper.ExecuteQueryProc(sql, pars);// （2）
    //两种方法，根据自己的需求去调用
    
    //执行完存储过程获取到SqlParameter中对应的自定义参数的值
    int rturnvalue = Convert.ToInt32(pars[5].Value);
    int outputvalue = Convert.ToInt32(pars[6].Value);
    return value;
}

当然我这里方法名是AddAsset 想要对数据库中资产信息表添加，这里就可以使用第一种调用DBHelper的方法，所返回的是一个影响的行数。在存储过程中的操作中可能会出现错误的现象，就可以在存储过程中的事务对其进行一个回滚，让还没执行之前是什么样，回滚后就是什么样，保持数据的一致性和完整性，执行完之后接收到的就是一个没有影响过行数的一个结果，这样就不会乱套