一、什么是存储过程（Stored Procedure）
存储过程是一段存储在数据库的“子程序”，本质是一个可重复使用的SQL代码块，可以理解为数据库端的“方法”。
存储过程的好处：
①提高性能：由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
②提高通信速率：网络通信中传输的内容是存储过程名字，相比传输大量的sql语句网络的要通信量小，提高通信速率。
③提高安全性能：存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。
二、Sql Server使用存储过程
例子使用的UserInfo表只有 UserName,UserPass,Email和主键 Id 列
2.1 简单的无参查询（查询用户名和密码）
--创建查询用户名和密码的存储过程
create proc pro_getUserList
as
select username,userpass from UserInfo
go --执行
exec pro_getUserList
在C#中调用存储过程的代码
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter("pro_getUserList", conn);
//设置CommandType
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
//结果集存入dt中
adapter.Fill(dt);
//遍历显示结果集
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["username"]+"---"+row["userpass"]);
}
Console.ReadKey();
}
2.2 有返回值的简单插入用户（插入一条新纪录，返回受影响的行数）
--创建名为InsertUserInfo的存储过程
create proc InsertUserInfo
as
insert into userinfo (username,userpass,email) values ('newuser','','123@qq.com')
return @@rowcount
go --执行存储过程
exec InsertUserInfo
在C#中调用存储过程的代码
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand com=new SqlCommand("pro_insertUserInfo",conn))
{
conn.Open();
com.CommandType = CommandType.StoredProcedure;//设置CommandType
//创建一个接受返回值的参数，设置该参数是返回值类型
SqlParameter par = new SqlParameter("count", SqlDbType.Int);
par.Direction = ParameterDirection.ReturnValue;
com.Parameters.Add(par); int comResult = com.ExecuteNonQuery();//com.ExecuteNonQuery返回受影响的行数 1
Console.WriteLine(comResult);
Console.WriteLine(par.Value.ToString());//通过返回值获取受影响的行数 1
Console.ReadKey();
}
}
2.3 有输入、输出、返回值的简单查询
一个简单栗子：输入省份名查询该省的城市列表，out返回总的城市数目，retrun返回总的省份数目，栗子只是演示存储过程的结果接收方式，并没有太大的实际意义。
注意一点 return参数必须是int类型的
CREATE PROCEDURE [dbo].[proc_select_cities_by_provice]
@provicename nvarchar(50),
@citycount int out
AS
BEGIN
SET NOCOUNT ON;
--根据输入参数proviceName查询城市列表
SELECT c.id cid,c.Name cname,p.Id pid,p.Name pname
from Cities c join Provices p on c.ProviceId=p.Id where p.Name=@provicename; --out返回城市的总数
select @citycount=count(1) from Cities; declare @provicecount int;
select @provicecount=count(1) from Provices;
--返回省份的总数
return @provicecount;
END
GO
在C#中调用存储过程的代码
static void Main(string[] args)
{
string connstr = "your sqlconnstring";
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand com = conn.CreateCommand())
{
com.CommandText = "proc_select_cities_by_provice";
com.CommandType = System.Data.CommandType.StoredProcedure; SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@provicename",System.Data.SqlDbType.NVarChar,){ Value="河南"},
new SqlParameter("@citycount",System.Data.SqlDbType.Int){Direction=System.Data.ParameterDirection.Output},
new SqlParameter("@provicecount",System.Data.SqlDbType.Int){Direction=System.Data.ParameterDirection.ReturnValue} };
com.Parameters.AddRange(pars);
//接收城市列表
using (SqlDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(
$"cid:{reader["cid"].ToString()}," +
$"cname:{reader["cname"].ToString()}," +
$"pid:{reader["pid"].ToString()}," +
$"pname:{reader["pname"].ToString()}");
}
}
//out参数输出
Console.WriteLine($"out:{com.Parameters["@citycount"].Value.ToString()}");
//return参数输出
Console.WriteLine($"out:{com.Parameters["@provicecount"].Value.ToString()}");
}
}
}
}
2.4 返回多个结果集
--返回多个结果集
create proc pro_GetLists
as
select * from userInfo --取所有信息
select username,userpass from UserInfo --只取用户名和密码
go --执行
exec pro_GetLists
C#调用储存过程
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlDataAdapter adapter = new SqlDataAdapter("pro_GetLists", conn);
DataSet ds = new DataSet();
//ds中是所有的结果
adapter.Fill(ds);
DataTable dt0=ds.Tables[];//第一个select的结果集
DataTable dt1 = ds.Tables[];//第二个select的结果集
}
备注：在存储过程中return只能返回int类型，out(output)可以返回多种类型,执行到return的时候存储过程即结束,而out的变量可以重复设置。
存储过程中的return和out参数值，在C#中都是通过参数来接收的，select的结果集可以用DataTable或者DataSet进行接收。
本文参考：