Sqlserver中的储存过程

最新推荐文章于 2022-11-24 15:27:44 发布

banyongzhan5405

最新推荐文章于 2022-11-24 15:27:44 发布

阅读量181

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/wyy1234/p/9028796.html

版权

一、什么是存储过程（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','123@qq.com') 
return @@rowcount
go

--执行存储过程
exec InsertUserInfo

在C#中调用存储过程的代码

 1  using (SqlConnection conn = new SqlConnection(connStr))
 2             {
 3                 using (SqlCommand com=new SqlCommand("pro_insertUserInfo",conn))
 4                 {
 5                     conn.Open();
 6                     com.CommandType = CommandType.StoredProcedure;//设置CommandType
 7                     //创建一个接受返回值的参数，设置该参数是返回值类型
 8                     SqlParameter par = new SqlParameter("count", SqlDbType.Int);
 9                     par.Direction = ParameterDirection.ReturnValue;
10                     com.Parameters.Add(par);
11 
12                     int comResult = com.ExecuteNonQuery();//com.ExecuteNonQuery返回受影响的行数 1
13                     Console.WriteLine(comResult);
14                     Console.WriteLine(par.Value.ToString());//通过返回值获取受影响的行数 1
15                     Console.ReadKey();
16                 }
17             }

2.3 有输入输出参数的简单查询(添加用户，如果用户名存在时不添加)

--插入一个新用户，返回受影响行数
create proc pro_insertUserInfo2
@username nvarchar(20),
@userpass nvarchar(20),
@email nvarchar(50)='123@qq.com',
@count int out --记录受影响的行数
as
declare @c int
select  @c = COUNT(*) from UserInfo where UserName=@username
if(@c!=0)--用户名存在的话不添加，受影响行数为0
    set @count=0
else--用户名不存在执行添加操作，受影响行数为1
    begin
        insert into UserInfo (UserName,UserPass,Email) values (@username,@userpass,@email)
        set @count=1
    end
go

--执行
exec pro_insertUserInfo2 'newuser','123','1234@qq.com' null

在C#中调用存储过程的代码

 1 using (SqlConnection conn = new SqlConnection(connStr))
 2             {
 3                 using (SqlCommand com=new SqlCommand("pro_insertUserInfo2",conn))
 4                 {
 5                     conn.Open();
 6                     com.CommandType = CommandType.StoredProcedure;//设置CommandType
 7                     //创建参数集合
 8                     SqlParameter[] pars = {
 9                        new SqlParameter ("@username",SqlDbType.NVarChar,20){Value="ls"},
10                        new SqlParameter("@userpass",SqlDbType.NVarChar,20){Value="123"},
11                        new SqlParameter("@count",SqlDbType.Int){Direction=ParameterDirection.Output}
12                    };
13                     com.Parameters.AddRange(pars);
14                     int comResult = com.ExecuteNonQuery();//第一次执行为1（添加一行），第二次执行结果为-1（未执行）
15                     Console.WriteLine(comResult);
16                     //通过out获取受影响的行数
17                     Console.WriteLine(pars[2].Value.ToString()); //第一次执行=1，第二次=0
18                     Console.ReadKey();
19                 }
20             }
21         }

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[0];//第一个select的结果集
                DataTable dt1 = ds.Tables[1];//第二个select的结果集               
            }