SQL預存程序(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.    沒有使用參數的預存程序

create procedure com_select
as
select * from Company
GO

b.    有參數的預存程序

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)    選擇所有的記錄

create procedure sellinfo_select
as
select * from sellinfo
GO

(2)    刪除指定的ID記錄

CREATE PROCEDURE sellinfo_delete
@sell_id bigint
as
delete from [sellinfo]
where
sell_id=@sell_id
GO
(3)更新所對應的記錄

程式碼：

create procedure prosmallclass_update_id
@smallid int,
@smallname char(50)
as 
update [ProductCats]
set
PdtCat_Name = @smallname
where 
PdtCat_id =@smallid
GO
(4)驗證登陸

 

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