MSSQL存储过程与C#调用存储过程详解

将一段T-SQL脚本进行封装，用来完成一个独立的逻辑操作。我们可以将存储过程理解为一段可编程的T-SQL脚本，这段脚本经创建编译，保存在数据库中。当我们需要调用这个逻辑操作的时候，我们只需要按照当时编写的要求传入相对应的参数便能完成相应的数据库操作。

一般情况下，在我们使用ADO.NET与数据库交互的时候，我们是使用具体的SQL语句来完成增删改查的简单操作，但是，如果操作复杂一些呢？如果一个业务逻辑，需要几次数据库连接操作或多次增删改查来完成呢？那么此时，我们为了编程的可读性和简便性，可以将这一系列的逻辑操作封装成一个存储过程来完成，然后，我们只需要按照我们编写的存储过程的要求，传入相应的参数(或不传)就能实现相应的业务逻辑。可以说，存储过程在完成复杂业务逻辑的时候减少了对于数据库的多次连接，能够很大程度上节约数据库资源。

create procedure 存储过程名
    参数1,参数2,参数3....
as
begin
    存储过程的处理逻辑...
end

create procedure Trim
    @strToTrim varchar(50)
as
begin
    select LTRIm(RTRIm(@strToTrim))
end

以上我们只讲了如果在SqlServer中创建存储过程，那么如何在SqlServer中执行存储过程呢？

--执行[案例1]中的存储过程--
exec Trim '  I am Chinese,I love my country  '

--执行结果--
'I am Chinese,I love my country'

--SqlServer中执行存储过程的基本语法--
exec 存储过程名 参数1,参数2,参数3....

 在以上案例中(案例2)中我们演示了在SqlServer中执行存储过程的基本语法，我们对案例1中的存储过程传入参数"   I am Chinese,I love my country    "该参数前后是有空格的，而在执行存储过程后，完整的去掉了前后的空格。

在很多业务场景中，我们不仅仅是传入参数，我们希望像C#的out参数一样，能够将传入的参数赋予一个新值并且我们能够得到。比如，我们在做分页查询的时候，我们不仅仅只需要分页的数据，我们还需要数据的总条数，以便于我们计算总页数。在这样的业务场景下，很多人是通过查询语句返回两个结果集，一个结果集是分页的数据，另外一个结果集只包含一个数据，就是数据总行数。当然，并非这样做是不可行的，但是，一整个DataTable表只包含一个一行一列的数据，就是数据总行数，这多多少少有点资源浪费的感觉，也不太科学。如果我们能够通过像函数的out参数一样，传入一个参数，这个参数在执行完查询分页数据的存储过程之后就能被赋予总行数数据，这多少让人更加欣慰。

create procedure 存储过程名
    参数1,参数2,参数3....,参数4 out
as
begin
    业务逻辑代码....
    select 参数4=xxx
end

create procedure getStudentsByPage
    @pageIndex int,@pageSize int,@rowCount int out
as
begin
    select @rowCount=COUNT(*) from Student;
    select * from
        (select *,ROW_NUMBER() OVER(order by SID asc) as rowNumber from Student) as T1 where rowNumber between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize;
end

通过exec在SqlServer中执行该存储过程，获取第1页，且每页为10行数据的数据集

declare @rowCount int;
exec getStudentsByPage 1,10,@rowCount out
print @rowCount

以下是执行的结果，我就不贴表格了，直接帖图：

 以上我们得到了数据集和总数据行数15。

 其实使用ADO.NET调用存储过程基本上跟基础的ADO.NET差不多，只是需要将SqlCommand对象的类型改成存储过程类型，且out参数需要在SqlParameter中设置Direction，这么说可能有点抽象，还是上代码吧。

为了方便演示，我们封装成一个单独的函数，该函数入参为pageIndex和pageSize分别代表要查询的页码和每页显示的数据行数。

public void GetStudentByPage(int pageIndex,int pageSize)
{
    //声明数据库连接字符串
    string connStr="server=.;database=studyDB;uid=sa;pwd=123456";
    //声明要调用的存储过程名
    string storedProcedureToRun="getStudentsByPage";
    //声明并初始化要查询到的分页数据集
    DataTable dt=new DataTable();
    //声明并初始化数据总行数
    int dataRowCount=0;
    //创建SqlDataAdapter
    using(SqlDataAdapter ada=new SqlDataAdapter(storedProcedureToRun,connStr))
    {
        //将SqlDataAdapter下的SqlCommand对象的命令类型设置为存储过程类型
        ada.SelectCommand.CommandType=CommandType.StoredProcedure;
        //创建该存储过程需要的参数
        SqlParameter pIndex=new SqlParameter("@pageIndex",pageIndex);
        SqlParameter pSize=new SqlParameter("@pageSize",pageSize);
        //请注意以下这行，rowCount是要作为存储过程的out参数使用，所以我们使用的是SqlParameter构造函数的另外一个重载，而不是像之前两个参数那样直接赋值。
        SqlParameter rowCount=new Sqlparameter("@rowCount",SqlDbType.Int);
        //指定rowCount参数为存储过程的out参数
        rowCount.Direction=ParameterDirection.OutPut;
        //将所有存储过程需要用到的参数传给SqlDataAdapter
        ada.SelectCommand.Parameters.Add(pIndex);
        ada.SelectCommand.Parameters.Add(pSize);
        ada.SelectCommand.Parameters.Add(rowCount);
        //使用SqlDataAdapter执行存储过程并填充查询到的分页数据集到变量dt
        ada.Fill(dt);
        //赋值数据总行数
        dataRowCount=int.Parse(rowCount.Value.ToString());
    }
    //经过以上处理变量dt中储存的就是查询到的分页数据,dataRowCount中存储的就是数据总行数
}

以上是笔者关于存储过程的理解和总结，初学者有问题请一起讨论，大牛请不要嘲笑此文过于浅显，本人知识技术世界里的一粒微尘......