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

二、存储过程使用篇

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

例如我们有一个存储过程名为student_list_info要执行,在查询分析器中你只要写
Executestudent_list_info
Go
就可以了
如果存储过程中包含有返回值的存储过程，那我们就必须指定参数值.看下面这个例子
此例摘自《SQLserver程序员指南》一书
createproceduresalequa@stor_idchar4,@sumsmallintoutput
as
selectord_num,ord_date,payterms,title_id,qty
fromsales
wherestor_id=@stor_id
select@sum=sumqty
fromsales
wherestor_id=@stor_id
go

要执行此存储过程，则我们要指定参数@sort_id，@sum的参数值.
declare@totalquasmallint
executesalequa'7131',@totalquaoutput
if@totalqua<=50
select'销售信息'='销售等级为3销售量为'+rtrimcast@totalquaasvarchar20
if@totalqua>50and@totalqua<=100
select'销售信息'='销售等级为2销售量为'+rtrimcast@totalquaasvarchar20
if@totalqua>100
select'销售信息'='销售等级为1销售量为'+rtrimcast@totalquaasvarchar20
运行结果为
ord_numord_datepaytermstitle_idqty
-------------------------------------------------------------------------
N9140081994-09-1400:00:00.000Net30PS209120
N9140141994-09-1400:00:00.000Net30MC302125
P3087a1993-05-2900:00:00.000Net60PS137220
P3087a1993-05-2900:00:00.000Net60PS210625
P3087a1993-05-2900:00:00.000Net60PS333315
P3087a1993-05-2900:00:00.000Net60PS777725
6rowsaffected
销售信息
-----------------------------------------
销售等级为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对象

window.attachEvent("onload",function (){AutoSizeDIV('CODE_6266')})

程序代码：

[ 复制代码到剪贴板 ]

stringspid=Request.QueryString["supplyid"].Trim();
SqlConnectionconndb=newSqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
conndb.Open();
SqlCommandstrselect=newSqlCommand("supplyinfo_select_supplyid",conndb);
strselect.CommandType=CommandType.StoredProcedure;
strselect.Parameters.Add("@supply_ID",spid);
SqlDataReaderreader=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对象调用存储过程的关键语句是:
SqlCommandstrselect=newSqlCommand("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对象


window.attachEvent("onload",function (){AutoSizeDIV('CODE_6635')})

程序代码：

[ 复制代码到剪贴板 ]

privatevoidbuycatalog()
{
SqlConnectionconndb=newSqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
conndb.Open();
SqlDataAdapterstrselect=newSqlDataAdapter("productclass",conndb);

strselect.SelectCommand.CommandType=CommandType.StoredProcedure;

DataSetds=newDataSet();

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调用存储过程中的关键是：
SqlDataAdapterstrselect=newSqlDataAdapter("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)选择所有的记录

window.attachEvent("onload",function (){AutoSizeDIV('CODE_4316')})

程序代码：

[ 复制代码到剪贴板 ]

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

(2)删除指定的ID记录


window.attachEvent("onload",function (){AutoSizeDIV('CODE_4415')})

程序代码：

[ 复制代码到剪贴板 ]

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

(3)更新所对应的记录


window.attachEvent("onload",function (){AutoSizeDIV('CODE_8197')})

程序代码：

[ 复制代码到剪贴板 ]

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

(4)验证登陆


window.attachEvent("onload",function (){AutoSizeDIV('CODE_2112')})

程序代码：

[ 复制代码到剪贴板 ]

/*
作者：德仔
用途：通过得到的@user_name@user_password验证登陆
日期：2006-3-21
*/
CREATEprocedureuser_login
@user_namevarchar(50),
@user_passwordvarchar(50)
as
select*fromusercompanywhere[User_Name]=@User_Nameand[User_Pwd]=@User_Password
if@@rowcount>0
begin
update[users]setuser_LoginTimes=user_LoginTimes+1where[User_Name]=@User_Nameand[User_Pwd]=@User_Password
end
GO

(5)密码修改


window.attachEvent("onload",function (){AutoSizeDIV('CODE_8085')})

程序代码：

[ 复制代码到剪贴板 ]

/*
作者：德仔
用途：先查到user的密码，再修改新密码
日期：2006-3-23
*/
createprocedureuser_pwd
@user_namevarchar(30),
@user_oldpwdvarchar(30),
@user_newpwdvarchar(30),
@iOutputintoutput
as
ifexists(select*fromuserswhereUser_Name=@user_nameanduser_pwd=@user_oldpwd)
begin
updateuserssetuser_pwd=@user_newpwdwhereUser_Name=@user_nameanduser_pwd=@user_oldpwd
set@iOutput=1
end
else
set@ioutput=-1
GO

(6)增加新记录


window.attachEvent("onload",function (){AutoSizeDIV('CODE_8515')})

程序代码：

[ 复制代码到剪贴板 ]

/*
作者：德仔
用途：添加一条新留言
日期：2006-4-8
*/
CREATEproceduregb_add
@gbusernamechar(50),
@gbusermemberidchar(50),
@gbuseremailchar(50),
@gbusersubjectchar(50),
@gbusercontentchar(1500)
as
insertgb
(
gbusername,
gbusermemberid,
gbuseremail,
gbsubject,
gbcontent

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

(7)统计数据


window.attachEvent("onload",function (){AutoSizeDIV('CODE_7671')})

程序代码：

[ 复制代码到剪贴板 ]

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

CREATEproceduredatacount
as
declare@MemberCountint
declare@MemberVipint
declare@MemberNormint
declare@MemberUnchkRegint
declare@MemberLblRegChkint

declare@CompanyCountint
declare@CompanyRcmdint

declare@SellCountint
declare@SellRcmdint
declare@SellUnchkint
declare@SellChkint

declare@CountSupplyint
declare@SupplyRcmdint
declare@SupplyUnchkint
declare@SupplyChkint

declare@NewsCountint
declare@NewsRcmdint
declare@NewsClassCountint

declare@SupplyClassint
declare@SellClassint
declare@MsgCountint

declare@ProBigclassint
declare@proSmallclassint

select@MemberCount=count(User_Id)fromUsers
select@MemberVip=count(User_Id)fromUserswhereUser_Level=2
select@MemberNorm=count(User_Id)fromUserswhereUser_Level=1
select@MemberUnchkReg=count(user_id)fromuserswhereuser_IsChecked=0
select@MemberLblRegChk=count(user_id)fromuserswhereuser_IsChecked=1

select@CompanyCount=count(COM_id)fromCompany
select@CompanyRcmd=count(COM_id)fromCompanywhereCOM_IsRcmd=1

select@SellCount=count(Sell_Id)fromsellinfo
select@SellRcmd=count(Sell_Id)fromsellinfowhereSell_IsRcmd=1
select@SellUnchk=count(Sell_Id)fromsellinfowhereSell_Ischecked=0
select@SellChk=count(Sell_Id)fromsellinfowhereSell_Ischecked=1

select@CountSupply=count(Supply_Id)fromsupplyInfo
select@SupplyRcmd=count(Supply_Id)fromsupplyInfowhereSupply_Isrcmd=1
select@SupplyUnchk=count(Supply_Id)fromsupplyInfowhereSupply_Ischecked=0
select@SupplyChk=count(Supply_Id)fromsupplyInfowhereSupply_Ischecked=1

select@NewsCount=count(news_id)fromnews
select@NewsRcmd=count(news_id)fromnewswhereNews_Recommand=1
select@NewsClassCount=count(news_id)fromnews

select@proBigclass=count(PdtCat_SortId)fromproductcatswherePdtCat_SortId=0
select@proSmallClass=count(PdtCat_SortId)fromproductcatswherePdtCat_SortId<>0

select@MsgCount=count(Msg_id)fromMSg
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)模糊查询


window.attachEvent("onload",function (){AutoSizeDIV('CODE_2362')})

程序代码：

[ 复制代码到剪贴板 ]

/*
作者：德仔
用途：用来进行查询sell_info
日期：2006-4-10
*/
CREATEPROCEDUREsellinfo_search
@keywordnvarchar(20)
AS
selectsell_subjectfromsellinfowheresell_subjectlike'%'+@keyword+'%'
GO

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


完整教程下载