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

一，存储过程的概念

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

延伸理解

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

二，存储过程的简单编写语法

create procedure 存储过程名
    参数1,参数2,参数3....
as
begin
    存储过程的处理逻辑...
end
/*
注意：
1,以上语法是在SqlServer中进行编写和执行。
2,编写的存储过程在对应数据库的[可编程性]-[存储过程]中能够找到。
*/

案例1：编写一个将传入参数左边和右边去空格的存储过程

create procedure Trim
    @strToTrim varchar(50)
as
begin
    select LTRIM(RTRIM(@strToTrim))
end
/*
数据库中只有单独的左边去空格LTRIM()和右边去空格RTRIM()函数，我们可以自己封装一个左右去空格的存储过程Trim
*/

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

案例2：SqlServer中执行(案例1中)的存储过程

--执行[案例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    "该参数前后是有空格的，而在执行存储过程后，完整的去掉了前后的空格。

三，带out参数的存储过程

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

创建带out参数存储过程的基本语法

create procedure 存储过程名
    参数1,参数2,参数3....,参数4 out
as
begin
    业务逻辑代码....
    select 参数4=xxx
end
/*
在存储过程中动态改变参数4的值即可
特别需要注意的是既然是out参数，那么在声明该参数之后一定要后面标识一个out关键字
/*

案例3：编写一个分页查询学生表的存储过程，并通过out参数返回总数据条数

以下是数据准备(感兴趣的同学者可以自己在SqlServer上做上类似的数据)：

Student表

SID	SName	SAge	SScore	SGender
21F02BAE-201F-49BE-85A9-58953A13E4F4	刘备	45	90.00	1
2DBF9A5A-8B54-4B14-9709-177BEF259188	小乔	23	62.00	2
3362E03A-F017-46CD-811E-284CF7303DF2	关羽	42	80.00	1
346B4690-5D11-4ED5-BD4F-B5F85E087A18	张昭	59	89.00	1
3D1E74F2-04F0-4F8A-BCF9-8175402A02E9	大乔	24	68.00	2
6B756C2D-59BF-441D-A736-B4F5EB92144C	姜维	19	82.00	1
728A1029-1AB7-47AE-8470-8BEF91686EA2	司马懿	38	98.00	1
85B86AFF-45D1-431B-BB3F-437183C60AED	孙尚香	28	76.00	2
89C2143C-75DD-48C7-8326-C4CCCFB78788	于禁	39	50.00	1
8D87FDF4-E4BE-4F2D-BF0B-705ADE2B5479	孙权	20	88.00	1
AA3568A3-1DD4-4FE0-B3B0-3967A1FE713E	诸葛亮	27	99.00	1
B1155CED-9714-4268-8075-573AB59EBE43	甄宓	29	70.00	2
C6B119EB-06BA-40EA-BF78-FCE5EDD6DFE6	曹操	41	92.00	1
E0FDA280-D39B-4D63-B507-D75810A6EC35	郭嘉	26	97.00	1
EFCD52C7-E0AB-44A7-B7BF-4361F164852E	张飞	38	46.00	1

 

 以上数据表(Student)我创建在了我本地SqlServer的库名为studyDB的数据库中。

以下是我编写的分页查询存储过程：

/*
解释：
1,@pageIndex是页码。
2,@pageSize是每页显示的数据行数。
3,@rowCount是out参数，用于储存数据总行数。
4,(@pageIndex-1)*@pageSize+1是要查询到的初始行码数
5,@pageIndex*@pageSize是最后行码数
*/
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

/*
注意：以上Sql语句是通过子查询完成的分页查询，当然，也有其他的办法
*/

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

declare @rowCount int;
exec getStudentsByPage 1,10,@rowCount out
print @rowCount
/*
注意：一定需要为@rowCount指定out标识
*/

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

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

四，使用ADO.NET调用存储过程

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

案例4：使用ADO.NET调用案例3中的存储过程，获得指定页码和指定每页数据条数的数据与总数据条数

为了方便演示，我们封装成一个单独的函数，该函数入参为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中存储的就是数据总行数
}
/*
注意：
1，以上类使用的命名空间情况如下：
SqlDataAdapter---System.Data.SqlClient
枚举SqlDbType和枚举ParameterDirection---System.Data
2,本实例是使用SqlDataAdapter来执行存储过程，实际上也可以使用ADO.NET的其他类来完成这个功能，只不过本人觉得这样可能更加简便。
3,本函数没有返回值，只是把需要的数据存储在了变量dt和dataRowCount中，只是便于演示而已，实际使用该函数的时候肯定是需要返回值的，请同学者加以变通
*/

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