asp.net|存储过程在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