ASP.Net学习之常用SQL存储过程

在ASP.Net项目中使用存储过程,首先可以提高数据库的安全性，其次可以提高运行SQL代码运行的速度，在大型项目中一般是必不可少的。Visual Studio.Net为SQL的存储过程提供了强大的支持，您既可以通过visual studio.net来新建存储过程，也可以直接在Sql Server的查询分析器中运行，还可以通过企业管理器创建，使用起来也非常方便。大家一直都误认为SQL存储过程是一个比较“高深”的技术，其实掌握一般的语法是没有什么大问题的，而我们在使用存储教程中也主要是增删减的操作，学会使用一般的T-SQL就很容易上手了。
我们先来看一下在Sql-server中是如何创建一个存储过程的吧，我们可以使用SQL命令语句创建，也可以通过SQL server中的企业管理器来创建，但其实都是离不开自己写语句的，当然系统存储过程我们就不用去动它了（存储过程分为系统存储过程 ，本地存储过程，临时存储过程，远程存储过程，扩展存储过程），而本地存儲過程就是我們自己編寫的存储过程，其实也叫用户存储过程。

当创建存储过程时需要确定存储过程的三个组成部分
所有的输入参数以及传给调用者的输出参数
被执行的针对数据库的操作语句包括调用其它存储过程的语句
返回给调用者的状态值以指明调用是成功还是失败
一、存储过程创建
1.用企业管理器来创建存储过程;
我们先打开企业管理器，找到我们要创建存储过程的数据库，如图一:





我们可以看到，在数据库里面有一个存储过程的项目，我们要用到的就是它了，选中存储过程这项，我们可以看到数据库中里面本身就有很多存储过程存在的了，不过这些都是数据库本身自带的，我们可以看到他的类型是系统（如图二），如果我们自己创建的存储过程，类型就是用户了.




在控制树的左边，我们选中存储过程后单击鼠标右键，可以看到有一个“创建存储过程的选项”





选中这项，就会出现新的窗口了，这个窗口就是用来写存储过程的了




在实际运用过程中，我们所创建的存储过程并不是想像中的这么复杂，而上面的这些参数也不是都要用上，但一般我们在用查询分析器创建存储过程前，都会通过下面这条语句来查询一下，在数据库中是否已经存在相同命名的存储过程,如果存在的话，则先删除。

If exists(select name from sysobjects where name=’存储过程名’ and type=’p’)
Drop procedure ‘存储过程名’
Go

在这里，我们也就知道了如何删除一个存储过程了，就是用drop procedure关键字 + 存储过程名.

我们先列举几个常见的存储过程：

a.    没有使用参数的存储过程
/*
用途：查询所有的公司名录
德仔创建于2006-3-29
*/
create procedure com_select
as
select * from Company
GO

b.    有参数的存储过程
/*
选择对应的admin
创建者:德仔
创建日期:2006-4-20
*/
create procedure admin_select
@adminusername char(50),
@adminpassword char(50)
as
select * from superadmin where
[Admin_Name]=@adminusername and [Admin_Password]=@adminpassword
GO


c.    在该存储过程中使用了OUTPUT 保留字有返回值的存储过程
create procedure salequa
 @stor_id char 4 ,
@sum smallint output
as
select 
ord_num, ord_date,
 payterms, title_id,
 qty
from sales
where stor_id = @stor_id
select @sum = sum qty
from sales
where stor_id = @stor_id
go


上面的几个存储过程是基本的存储过程，同时我们可以看到在存储过程中注释是用/* 注释 */形式.


我们下一次再讲讲存储过程在net中的使用吧
 二、存储过程使用篇

1.    在SQL中执行
执行已创建的存储过程使用EXECUTE 命令其语法如下
[EXECUTE]
{[@return_statur=]
{procedure_name[;number] | @procedure_name_var}
[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n]
[WITH RECOMPILE]
各参数的含义如下
@return_status
是可选的整型变量用来存储存储过程向调用者返回的值
@procedure_name_var
是一变量名用来代表存储过程的名字
其它参数据和保留字的含义与CREATE PROCEDURE 中介绍的一样

例如我们有一个存储过程名为student_list_info要执行,在查询分析器中你只要写
Execute student_list_info
Go
就可以了
如果存储过程中包含有返回值的存储过程，那我们就必须指定参数值.看下面这个例子
此例摘自《SQLserver程序员指南》一书
create procedure salequa @stor_id char 4 ,@sum smallint output
as
select ord_num, ord_date, payterms, title_id, qty
from sales
where stor_id = @stor_id
select @sum = sum qty
from sales
where stor_id = @stor_id
go

要执行此存储过程，则我们要指定参数@sort_id，@sum的参数值.
declare @totalqua smallint
execute salequa '7131',@totalqua output
if @totalqua<=50
select '销售信息'='销售等级为3 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>50 and @totalqua<=100
select '销售信息'='销售等级为2 销售量为'+rtrim cast @totalqua as varchar 20
if @totalqua>100
select '销售信息'='销售等级为1 销售量为'+rtrim cast @totalqua as varchar 20
运行结果为
ord_num ord_date payterms title_id qty
-------------------- --------------------------- ------------ -------- ------
N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20
N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20
P3087a 1993-05-29 00:00:00.000 Net 60 PS2106 25
P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15
P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 25
6 row s affected
销售信息
-----------------------------------------
销售等级为1 销售量为130

2.    在ASP.NET中使用存储过程
要在ASP.Net(这里以c#为说明)中使用存储过程，首先要查看一下页面中是否引用了 System.Data.Sqlclient;当然数据库连接是必不可少的。我们知，一般我们在Asp.Net中调用数据的步骤是这样的：

新建一个数据库连接对象(一般用SqlConnection)→用Open()方法打开我们要操作的数据库→创建一个SqlCommand或SqlDataAdapter对象→对SQL命令或存储过程用ExecuteNonQuery()方法或ExecuteReader()方法进行执行数据操作→读取或输入数据至数据库→用Close()方法关闭连接.


由此可知,在使用存储过程前，我们要用SqlCommand对象或SqlDataAdapter对象使填充DataSet或共它在运用存储过程中有很大的作用.但其运用的方法是跟在Net中直接执行Sql语句区别并不是很大的，我们可以通过例子来说明是乍样调用存储过程的.
(1)    采用SqlCommand对象

string  spid = Request.QueryString[ " supplyid " ].Trim();
SqlConnection conndb = new  SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ " conn " ]);
            conndb.Open();
            SqlCommand strselect  =   new  SqlCommand( " supplyinfo_select_supplyid " ,conndb);
            strselect.CommandType =  CommandType.StoredProcedure;
            strselect.Parameters.Add( " @supply_ID " ,spid);
            SqlDataReader reader  =  strselect.ExecuteReader();
             if (reader.Read())
             ... {
                LblId.Text=reader["Supply_Id"].ToString().Trim();
                LblTitle.Text=reader["Supply_Subject"].ToString().Trim();
                LblBigclass.Text=reader["Supply_CatID"].ToString().Trim();
                LblDesc.Text=reader["Supply_Details"].ToString().Trim();
                LblPurType.Text=reader["Supply_PurchaseType"].ToString().Trim();
                if(int.Parse(reader["Supply_Ischecked"].ToString().Trim())==1)
                ...{
                    LblIschk.Text="已通过审核";
                }
                else
                ...{
                    LblIschk.Text="没有通过审核";
                }
                if(int.Parse(reader["Supply_Isrcmd"].ToString().Trim())==1)
                ...{
                    LblIsrcmd.Text="已设置为推荐";
                }
                else
                ...{
                    LblIsrcmd.Text="没有设置为推荐";
                }
                switch(reader["Supply_Reader_Level"].ToString().Trim())
                ...{
                    case "0":
                        LblLevel.Text="设置所有人都可以看到此信息";
                        break;
                    case "1":
                        LblLevel.Text="设置注册会员可以看到此信息";
                        break;
                    case "2":
                        LblLevel.Text="设置VIP会员可以看到此信息";
                        break;
                }
            }

由上可以看到，利用SqlCommand对象调用存储过程的关键语句是:
SqlCommand strselect = new SqlCommand("supplyinfo_select_supplyid",conndb);
strselect.CommandType= CommandType.StoredProcedure;
strselect.Parameters.Add("@supply_ID",spid);
简单解释：声明一个SqlCommand对像,通过SqlCommand调用存储过程supplyinfo_select_supplyid,
同时包含了一个输入参数@supply_id,其值是变量spid,同时通过ExecuteReader()方法，查询数据相关的数据，通过label控件，将数据显示出来.

(2)采用SqlDataAdapter对象

程序代码：

[ 复制代码到剪贴板 ]

private void buycatalog()
        {
            SqlConnection conndb= new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
            conndb.Open();
            SqlDataAdapter  strselect = new SqlDataAdapter("productclass",conndb);

            strselect.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet ds = new DataSet();

            strselect.Fill(ds);

            DlstBuycatalog.DataSource =ds;

            DlstBuycatalog.DataKeyField ="PdtCat_ID";

            DlstBuycatalog.DataBind();

            conndb.Close();
        }

以上这个方法，就是通过SqlDataAdapter对像调用了SQL中存储过程productclass，通过DataSet将数据填充在ds中,同时指定DataList控件DlstBuycatalog的数据源是ds,主键是PdtCat_Id,最后再重新绑定Datalist控件.由这个方法我们可以看到用SqlDataAdapter调用存储过程中的关键是：
SqlDataAdapter  strselect = new SqlDataAdapter("productclass",conndb);
strselect.SelectCommand.CommandType = CommandType.StoredProcedure;
当存储过程中有参数时，我们又应该乍样做呢？其实这个跟SqlCommand的差不多，我们只要再加一句
Strselect.SelectCommand.Parameter.Add(“@pdt_name”,txtpdtname.Text());
就可以了，其中@pdt_name是在存储过程中声明的参数变量名，而txtpdtname.text()是在.net中赋于变量@pdt_name的值了。认真看一下下面这个存储过程就很清楚了:
由上面我们可以知道在调用存储过程中，最关键的对象是Command对象,这个对象可以通过ExecuteReader()方法执行数据查询，还可以返回一个单一值的查询，还可以通过ExecuteScalar()方法进行相关的数据统计，还可以通过ExecuteNonQuery()方法进行数据更新，增删改的执行操作，而在执行这些SQL操作时，往往是与相关的控件DataGrid ，DataList,Repeat控件结合使用的.

 (3)常用的一些存储过程例子
以下是自己在最近所做的一个项目中所用到的一些存储过程，可能由于自己水平有限，有些写得不是很规范，不过大部分都实现到我想要的结果了，这些存储过程都可以正常执行,把这些发出来给大家(数据库因保密请见谅)，希望对大家用用，同时希望指正其中的错误，谢谢。

(1)    选择所有的记录

 

/**/ /*
作者：德仔
用途：查询sellinfo里所有的记录
日期：2006-3-23
*/
create   procedure  sellinfo_select
as
select   *   from  sellinfo
GO

(2)    删除指定的ID记录

程序代码：

/**/ /*
作者：德仔
用途：删除sellinfo里由输入参数@sell_id指定的ID记录
日期：2006-3-23
*/
CREATE   PROCEDURE  sellinfo_delete 
@sell_id   bigint
as
delete   from   [ sellinfo ]
where
sell_id = @sell_id
GO

(3)更新所对应的记录

程序代码：

/**/ /*
作者：德仔
用途：修改相对应的小类名
日期：2006-4-5
*/
create   procedure  prosmallclass_update_id
@smallid   int ,
@smallname   char ( 50 )
as  
update   [ ProductCats ]
set
PdtCat_Name  =   @smallname
where  
PdtCat_id  = @smallid
GO

(4)验证登陆

/**/ /*
作者：德仔
用途：通过得到的@user_name @user_password验证登陆
日期：2006-3-21
*/
CREATE   procedure  user_login
@user_name   varchar ( 50 ),
@user_password   varchar ( 50 )
as
select   *   from  usercompany  where   [ User_Name ]   =   @User_Name   and   [ User_Pwd ]   =   @User_Password
if   @@rowcount > 0
begin
update    [ users ]   set  user_LoginTimes = user_LoginTimes + 1   where   [ User_Name ]   =   @User_Name   and   [ User_Pwd ]   =   @User_Password
end
GO

(5)密码修改

程序代码：

/**/ /*
作者：德仔
用途：先查到user的密码，再修改新密码
日期：2006-3-23
*/
create   procedure  user_pwd
@user_name   varchar ( 30 ),
@user_oldpwd   varchar ( 30 ),
@user_newpwd   varchar ( 30 ),
@iOutput   int  output
as
if   exists ( select   *   from  users  where   User_Name = @user_name   and  user_pwd = @user_oldpwd )
begin
update  users  set  user_pwd = @user_newpwd   where   User_Name = @user_name   and  user_pwd = @user_oldpwd
set   @iOutput   =   1
end  
else
set   @ioutput   =   - 1
GO

(6)增加新记录

程序代码：

/**/ /*
作者：德仔
用途：添加一条新留言
日期：2006-4-8
*/
CREATE   procedure  gb_add
@gbusername   char ( 50 ),
@gbusermemberid   char ( 50 ),
@gbuseremail   char ( 50 ),
@gbusersubject   char ( 50 ),
@gbusercontent   char ( 1500 )
as
insert  gb
(
gbusername,
gbusermemberid,
gbuseremail,
gbsubject,
gbcontent

)
values
(
@gbusername ,
@gbusermemberid ,
@gbuseremail ,
@gbusersubject ,
@gbusercontent
)
GO

(7)统计数据

 程序代码：

 

/**/ /*
作者：德仔
用途：用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数
日期：2006-3-23
*/

CREATE   procedure  datacount
as
declare   @MemberCount   int
declare   @MemberVip   int
declare   @MemberNorm   int
declare   @MemberUnchkReg   int
declare   @MemberLblRegChk   int

declare   @CompanyCount   int
declare   @CompanyRcmd   int

declare   @SellCount   int
declare   @SellRcmd   int
declare   @SellUnchk   int
declare   @SellChk   int

declare   @CountSupply   int
declare   @SupplyRcmd   int
declare   @SupplyUnchk   int
declare   @SupplyChk   int

declare   @NewsCount   int
declare   @NewsRcmd   int
declare   @NewsClassCount   int

declare   @SupplyClass   int
declare   @SellClass   int
declare   @MsgCount   int

declare   @ProBigclass   int  
declare   @proSmallclass   int

select   @MemberCount =   count ( User_Id ) from  Users
select   @MemberVip = count ( User_Id ) from  Users  where  User_Level  = 2
select   @MemberNorm = count ( User_Id ) from  Users  where  User_Level  = 1
select   @MemberUnchkReg = count ( user_id )  from  users  where  user_IsChecked = 0
select   @MemberLblRegChk = count ( user_id )  from  users  where  user_IsChecked = 1

select   @CompanyCount = count (COM_id)  from  Company 
select   @CompanyRcmd = count (COM_id)  from  Company  where  COM_IsRcmd = 1

select   @SellCount   = count (Sell_Id)  from  sellinfo
select   @SellRcmd   = count (Sell_Id)  from  sellinfo  where  Sell_IsRcmd = 1
select   @SellUnchk   = count (Sell_Id)  from  sellinfo  where  Sell_Ischecked  =   0
select   @SellChk   = count (Sell_Id)  from  sellinfo  where  Sell_Ischecked  =   1

select   @CountSupply   = count (Supply_Id) from  supplyInfo
select   @SupplyRcmd   = count (Supply_Id) from  supplyInfo  where  Supply_Isrcmd = 1
select   @SupplyUnchk   = count (Supply_Id) from  supplyInfo  where   Supply_Ischecked = 0
select   @SupplyChk   = count (Supply_Id) from  supplyInfo  where  Supply_Ischecked = 1

select   @NewsCount   = count (news_id)  from  news
select   @NewsRcmd   = count (news_id)  from  news  where  News_Recommand = 1
select   @NewsClassCount   = count (news_id)  from  news

select   @proBigclass   =   count (PdtCat_SortId)  from  productcats  where  PdtCat_SortId = 0
select   @proSmallClass    =   count (PdtCat_SortId) from  productcats  where  PdtCat_SortId <> 0

select   @MsgCount    =   count (Msg_id)  from  MSg
select  
MemberCount = @MemberCount ,
MemberVip = @MemberVip ,
MemberNorm = @MemberNorm ,
MemberUnchkReg = @MemberUnchkReg ,
MemberLblRegChk = @MemberLblRegChk ,
CompanyCount = @CompanyCount ,
CompanyRcmd = @CompanyRcmd ,
SellCount = @SellCount ,
SellRcmd = @SellRcmd ,
SellUnchk = @SellUnchk ,
SellChk = @SellChk ,
CountSupply  = @CountSupply ,
SupplyRcmd  = @SupplyRcmd ,
SupplyUnchk = @SupplyUnchk ,
SupplyChk  = @SupplyChk ,
NewsCount = @NewsCount ,
NewsRcmd = @NewsRcmd ,
NewsClassCount = @NewsClassCount ,
probigclass = @probigclass ,
prosmallclass = @prosmallclass ,
MsgCount  =   @MsgCount
GO

(8)模糊查询

程序代码：   /**/ /* 作者：德仔 用途：用来进行查询sell_info 日期：2006-4-10 */ CREATE   PROCEDURE  sellinfo_search   @keyword   nvarchar  ( 20 ) AS select  sell_subject  from  sellinfo  where  sell_subject  like   ' % '   +   @keyword   +   ' % ' GO

以上只是自己在学习asp.net中的一点个人经验，因个人水平所限，不免有错，欢迎大家指正，并请多多指教!