asp.net sql存儲(chǔ)過(guò)程

更新時(shí)間：2009年11月22日 00:03:51 作者：

在ASP.Net項(xiàng)目中使用存儲(chǔ)過(guò)程,首先可以提高數(shù)據(jù)庫(kù)的安全性，其次可以提高運(yùn)行SQL代碼運(yùn)行的速度，在大型項(xiàng)目中一般是必不可少的。

Visual Studio.Net為SQL的存儲(chǔ)過(guò)程提供了強(qiáng)大的支持，您既可以通過(guò)visual studio.net來(lái)新建存儲(chǔ)過(guò)程，也可以直接在Sql Server的查詢分析器中運(yùn)行，還可以通過(guò)企業(yè)管理器創(chuàng)建，使用起來(lái)也非常方便。大家一直都誤認(rèn)為SQL存儲(chǔ)過(guò)程是一個(gè)比較“高深”的技術(shù)，其實(shí)掌握一般的語(yǔ)法是沒(méi)有什么大問(wèn)題的，而我們?cè)谑褂么鎯?chǔ)教程中也主要是增刪減的操作，學(xué)會(huì)使用一般的T-SQL就很容易上手了。
我們先來(lái)看一下在Sql-server中是如何創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的吧，我們可以使用SQL命令語(yǔ)句創(chuàng)建，也可以通過(guò)SQL server中的企業(yè)管理器來(lái)創(chuàng)建，但其實(shí)都是離不開(kāi)自己寫(xiě)語(yǔ)句的，當(dāng)然系統(tǒng)存儲(chǔ)過(guò)程我們就不用去動(dòng)它了（存儲(chǔ)過(guò)程分為系統(tǒng)存儲(chǔ)過(guò)程，本地存儲(chǔ)過(guò)程，臨時(shí)存儲(chǔ)過(guò)程，遠(yuǎn)程存儲(chǔ)過(guò)程，擴(kuò)展存儲(chǔ)過(guò)程），而本地存儲(chǔ)過(guò)程就是我們自己編寫(xiě)的存儲(chǔ)過(guò)程，其實(shí)也叫用戶存儲(chǔ)過(guò)程。
當(dāng)創(chuàng)建存儲(chǔ)過(guò)程時(shí)需要確定存儲(chǔ)過(guò)程的三個(gè)組成部分
所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)
被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句
返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗
一、存儲(chǔ)過(guò)程創(chuàng)建
1.用企業(yè)管理器來(lái)創(chuàng)建存儲(chǔ)過(guò)程;
我們先打開(kāi)企業(yè)管理器，找到我們要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，如圖一:
我們可以看到，在數(shù)據(jù)庫(kù)里面有一個(gè)存儲(chǔ)過(guò)程的項(xiàng)目，我們要用到的就是它了，選中存儲(chǔ)過(guò)程這項(xiàng)，我們可以看到數(shù)據(jù)庫(kù)中里面本身就有很多存儲(chǔ)過(guò)程存在的了，不過(guò)這些都是數(shù)據(jù)庫(kù)本身自帶的，我們可以看到他的類型是系統(tǒng)（如圖二），如果我們自己創(chuàng)建的存儲(chǔ)過(guò)程，類型就是用戶了.

在控制樹(shù)的左邊，我們選中存儲(chǔ)過(guò)程后單擊鼠標(biāo)右鍵，可以看到有一個(gè)“創(chuàng)建存儲(chǔ)過(guò)程的選項(xiàng)”
選中這項(xiàng)，就會(huì)出現(xiàn)新的窗口了，這個(gè)窗口就是用來(lái)寫(xiě)存儲(chǔ)過(guò)程的了

在實(shí)際運(yùn)用過(guò)程中，我們所創(chuàng)建的存儲(chǔ)過(guò)程并不是想像中的這么復(fù)雜，而上面的這些參數(shù)也不是都要用上，但一般我們?cè)谟貌樵兎治銎鲃?chuàng)建存儲(chǔ)過(guò)程前，都會(huì)通過(guò)下面這條語(yǔ)句來(lái)查詢一下，在數(shù)據(jù)庫(kù)中是否已經(jīng)存在相同命名的存儲(chǔ)過(guò)程,如果存在的話，則先刪除。
If exists(select name from sysobjects where name='存儲(chǔ)過(guò)程名' and type='p')
Drop procedure ‘存儲(chǔ)過(guò)程名'
Go
在這里，我們也就知道了如何刪除一個(gè)存儲(chǔ)過(guò)程了，就是用drop procedure關(guān)鍵字 + 存儲(chǔ)過(guò)程名.
我們先列舉幾個(gè)常見(jiàn)的存儲(chǔ)過(guò)程：
a. 沒(méi)有使用參數(shù)的存儲(chǔ)過(guò)程

復(fù)制代碼代碼如下:

 
/* 
用途：查詢所有的公司名錄 
德仔創(chuàng)建于2006-3-29 
*/ 
create procedure com_select 
as 
select * from Company 
GO 
b. 有參數(shù)的存儲(chǔ)過(guò)程 
/* 
選擇對(duì)應(yīng)的admin 
創(chuàng)建者:德仔 
創(chuàng)建日期: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. 在該存儲(chǔ)過(guò)程中使用了OUTPUT 保留字有返回值的存儲(chǔ)過(guò)程 
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 

上面的幾個(gè)存儲(chǔ)過(guò)程是基本的存儲(chǔ)過(guò)程，同時(shí)我們可以看到在存儲(chǔ)過(guò)程中注釋是用/* 注釋 */形式.

我們下一次再講講存儲(chǔ)過(guò)程在net中的使用吧
----------------
二、存儲(chǔ)過(guò)程使用篇
1. 在SQL中執(zhí)行
執(zhí)行已創(chuàng)建的存儲(chǔ)過(guò)程使用EXECUTE 命令其語(yǔ)法如下
[EXECUTE]
{[@return_statur=]
{procedure_name[;number] | @procedure_name_var}
[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n]
[WITH RECOMPILE]
各參數(shù)的含義如下
@return_status
是可選的整型變量用來(lái)存儲(chǔ)存儲(chǔ)過(guò)程向調(diào)用者返回的值
@procedure_name_var
是一變量名用來(lái)代表存儲(chǔ)過(guò)程的名字
其它參數(shù)據(jù)和保留字的含義與CREATE PROCEDURE 中介紹的一樣
例如我們有一個(gè)存儲(chǔ)過(guò)程名為student_list_info要執(zhí)行,在查詢分析器中你只要寫(xiě)
Execute student_list_info
Go
就可以了
如果存儲(chǔ)過(guò)程中包含有返回值的存儲(chǔ)過(guò)程，那我們就必須指定參數(shù)值.看下面這個(gè)例子
此例摘自《SQLserver程序員指南》一書(shū)
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
要執(zhí)行此存儲(chǔ)過(guò)程，則我們要指定參數(shù)@sort_id，@sum的參數(shù)值.
declare @totalqua smallint
execute salequa '7131',@totalqua output
if @totalqua<=50
select '銷售信息'='銷售等級(jí)為3 銷售量為'+rtrim cast @totalqua as varchar 20
if @totalqua>50 and @totalqua<=100
select '銷售信息'='銷售等級(jí)為2 銷售量為'+rtrim cast @totalqua as varchar 20
if @totalqua>100
select '銷售信息'='銷售等級(jí)為1 銷售量為'+rtrim cast @totalqua as varchar 20
運(yùn)行結(jié)果為
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
銷售信息
-----------------------------------------
銷售等級(jí)為1 銷售量為130
2. 在ASP.NET中使用存儲(chǔ)過(guò)程
要在ASP.Net(這里以c#為說(shuō)明)中使用存儲(chǔ)過(guò)程，首先要查看一下頁(yè)面中是否引用了 System.Data.Sqlclient;當(dāng)然數(shù)據(jù)庫(kù)連接是必不可少的。我們知，一般我們?cè)贏sp.Net中調(diào)用數(shù)據(jù)的步驟是這樣的：
新建一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象(一般用SqlConnection)→用Open()方法打開(kāi)我們要操作的數(shù)據(jù)庫(kù)→創(chuàng)建一個(gè)SqlCommand或SqlDataAdapter對(duì)象→對(duì)SQL命令或存儲(chǔ)過(guò)程用ExecuteNonQuery()方法或ExecuteReader()方法進(jìn)行執(zhí)行數(shù)據(jù)操作→讀取或輸入數(shù)據(jù)至數(shù)據(jù)庫(kù)→用Close()方法關(guān)閉連接.

由此可知,在使用存儲(chǔ)過(guò)程前，我們要用SqlCommand對(duì)象或SqlDataAdapter對(duì)象使填充DataSet或共它在運(yùn)用存儲(chǔ)過(guò)程中有很大的作用.但其運(yùn)用的方法是跟在Net中直接執(zhí)行Sql語(yǔ)句區(qū)別并不是很大的，我們可以通過(guò)例子來(lái)說(shuō)明是乍樣調(diào)用存儲(chǔ)過(guò)程的.
(1) 采用SqlCommand對(duì)象
程序代碼：

復(fù)制代碼代碼如下:

 
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="已通過(guò)審核"; 
} 
else 
{ 
LblIschk.Text="沒(méi)有通過(guò)審核"; 
} 
if(int.Parse(reader["Supply_Isrcmd"].ToString().Trim())==1) 
{ 
LblIsrcmd.Text="已設(shè)置為推薦"; 
} 
else 
{ 
LblIsrcmd.Text="沒(méi)有設(shè)置為推薦"; 
} 
switch(reader["Supply_Reader_Level"].ToString().Trim()) 
{ 
case "0": 
LblLevel.Text="設(shè)置所有人都可以看到此信息"; 
break; 
case "1": 
LblLevel.Text="設(shè)置注冊(cè)會(huì)員可以看到此信息"; 
break; 
case "2": 
LblLevel.Text="設(shè)置VIP會(huì)員可以看到此信息"; 
break; 
} 
} 

由上可以看到，利用SqlCommand對(duì)象調(diào)用存儲(chǔ)過(guò)程的關(guān)鍵語(yǔ)句是:
SqlCommand strselect = new SqlCommand("supplyinfo_select_supplyid",conndb);
strselect.CommandType= CommandType.StoredProcedure;
strselect.Parameters.Add("@supply_ID",spid);
簡(jiǎn)單解釋：聲明一個(gè)SqlCommand對(duì)像,通過(guò)SqlCommand調(diào)用存儲(chǔ)過(guò)程supplyinfo_select_supplyid,
同時(shí)包含了一個(gè)輸入?yún)?shù)@supply_id,其值是變量spid,同時(shí)通過(guò)ExecuteReader()方法，查詢數(shù)據(jù)相關(guān)的數(shù)據(jù)，通過(guò)label控件，將數(shù)據(jù)顯示出來(lái).
(2)采用SqlDataAdapter對(duì)象

程序代碼：

復(fù)制代碼代碼如下:

 
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(); 
} 

以上這個(gè)方法，就是通過(guò)SqlDataAdapter對(duì)像調(diào)用了SQL中存儲(chǔ)過(guò)程productclass，通過(guò)DataSet將數(shù)據(jù)填充在ds中,同時(shí)指定DataList控件DlstBuycatalog的數(shù)據(jù)源是ds,主鍵是PdtCat_Id,最后再重新綁定Datalist控件.由這個(gè)方法我們可以看到用SqlDataAdapter調(diào)用存儲(chǔ)過(guò)程中的關(guān)鍵是：
SqlDataAdapter strselect = new SqlDataAdapter("productclass",conndb);
strselect.SelectCommand.CommandType = CommandType.StoredProcedure;
當(dāng)存儲(chǔ)過(guò)程中有參數(shù)時(shí)，我們又應(yīng)該乍樣做呢？其實(shí)這個(gè)跟SqlCommand的差不多，我們只要再加一句
Strselect.SelectCommand.Parameter.Add(“@pdt_name”,txtpdtname.Text());
就可以了，其中@pdt_name是在存儲(chǔ)過(guò)程中聲明的參數(shù)變量名，而txtpdtname.text()是在.net中賦于變量@pdt_name的值了。認(rèn)真看一下下面這個(gè)存儲(chǔ)過(guò)程就很清楚了:
由上面我們可以知道在調(diào)用存儲(chǔ)過(guò)程中，最關(guān)鍵的對(duì)象是Command對(duì)象,這個(gè)對(duì)象可以通過(guò)ExecuteReader()方法執(zhí)行數(shù)據(jù)查詢，還可以返回一個(gè)單一值的查詢，還可以通過(guò)ExecuteScalar()方法進(jìn)行相關(guān)的數(shù)據(jù)統(tǒng)計(jì)，還可以通過(guò)ExecuteNonQuery()方法進(jìn)行數(shù)據(jù)更新，增刪改的執(zhí)行操作，而在執(zhí)行這些SQL操作時(shí)，往往是與相關(guān)的控件DataGrid ，DataList,Repeat控件結(jié)合使用的.
(3)常用的一些存儲(chǔ)過(guò)程例子
以下是自己在最近所做的一個(gè)項(xiàng)目中所用到的一些存儲(chǔ)過(guò)程，可能由于自己水平有限，有些寫(xiě)得不是很規(guī)范，不過(guò)大部分都實(shí)現(xiàn)到我想要的結(jié)果了，這些存儲(chǔ)過(guò)程都可以正常執(zhí)行,把這些發(fā)出來(lái)給大家(數(shù)據(jù)庫(kù)因保密請(qǐng)見(jiàn)諒)，希望對(duì)大家用用，同時(shí)希望指正其中的錯(cuò)誤，謝謝。
(1) 選擇所有的記錄
程序代碼：

復(fù)制代碼代碼如下:

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

(2) 刪除指定的ID記錄

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：刪除sellinfo里由輸入?yún)?shù)@sell_id指定的ID記錄 
日期：2006-3-23 
*/ 
CREATE PROCEDURE sellinfo_delete 
@sell_id bigint 
as 
delete from [sellinfo] 
where 
sell_id=@sell_id 
GO 

(3)更新所對(duì)應(yīng)的記錄

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：修改相對(duì)應(yīng)的小類名 
日期：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)驗(yàn)證登陸

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：通過(guò)得到的@user_name @user_password驗(yàn)證登陸 
日期：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)密碼修改

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：先查到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)增加新記錄

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：添加一條新留言 
日期：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)統(tǒng)計(jì)數(shù)據(jù)

程序代碼： [ 復(fù)制代碼到剪貼板 ]

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：用來(lái)統(tǒng)計(jì)站上所有的信息總數(shù),包括新聞,產(chǎn)品,公司,等的總數(shù) 
日期：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)模糊查詢

程序代碼：

復(fù)制代碼代碼如下:

 
/* 
作者：德仔 
用途：用來(lái)進(jìn)行查詢sell_info 
日期：2006-4-10 
*/ 
CREATE PROCEDURE sellinfo_search 
@keyword nvarchar (20) 
AS 
select sell_subject from sellinfo where sell_subject like '%' + @keyword + '%' 
GO 

以上只是自己在學(xué)習(xí)asp.net中的一點(diǎn)個(gè)人經(jīng)驗(yàn)，因個(gè)人水平所限，不免有錯(cuò)，歡迎大家指正，并請(qǐng)多多指教!

您可能感興趣的文章: