asp.net sql存儲過程

在控制樹的左邊，我們選中存儲過程后單擊鼠標右鍵，可以看到有一個“創建存儲過程的選項”
選中這項，就會出現新的窗口了，這個窗口就是用來寫存儲過程的了

在實際運用過程中，我們所創建的存儲過程并不是想像中的這么復雜，而上面的這些參數也不是都要用上，但一般我們在用查詢分析器創建存儲過程前，都會通過下面這條語句來查詢一下，在數據庫中是否已經存在相同命名的存儲過程,如果存在的話，則先刪除。
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 
運行

由此可知,在使用存儲過程前，我們要用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中的一點個人經驗，因個人水平所限，不免有錯，歡迎大家指正，并請多多指教!
 
 




聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com