一．前言：
預存程序（Stored Procedure）是一組為了完成特定功能的SQL語句集，經過編譯後儲存在資料庫中。使用者透過指定預存程序的名字並給出參數（如果該預存程序帶有參數）來執行它。預存程序是資料庫中的重要對象，任何一個設計良好的資料庫應用程式都應該用到預存程序。總的來說，預存程序有以下一些優點：
◆預存程序可讓標準元件式程式設計
◆預存程序能達到較快的執行速度
◆預存程序能夠減少網路流量
◆預存程序可被當作安全機制來充分利用
本文作者將向大家介紹.NET資料庫應用程式中預存程序的應用，以及如何將它與ADO.NET中的SqlDataAdapter物件、DataSet物件等結合使用以提高.NET資料庫應用程式的整體效能。
二．系統需求：
開發工具：Visual Studio.NET
資料庫管理系統：SQL Server 2000（其中包含了範例程式所用到的Pubs資料庫）
三．建立一個簡單的預存程序：
這裡我將向大家介紹如何運用Visual Studio.NET IDE來建立預存程序。運用Visual Studio.NET IDE建立預存程序是非常容易且直覺的，你只要在伺服器資源管理器中導向到Pubs資料庫並展開節點，就會發現包括預存程序在內的各種資料庫對象，如圖1所示。
在預存程序節點上點選右鍵即可彈出一個選單，其中包含了「新預存程序」的指令。新建一個預存程序後，IDE中的程式碼編輯視窗便出現如下圖所示的程式碼範本：
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
上面的程式碼範本符合簡化的建立預存程序的語法規則，完整的語法規則如下：
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
限於篇幅，各參數的意義在此就不多作介紹了，有興趣的讀者可以參考有關SQL Server 2000資料庫管理系統的資料。
下面我對此程式碼範本中的各個語法成分略作介紹。 CREATE PROCEDURE聲明建立一個預存程序，後面跟著該預存程序的名稱。 「/*……*/」中的成分是此預存程序的參數，可包括輸入參數和輸出參數。 AS關鍵字後面的內容是這個預存程序的主體部分，其中是任何數量和類型的包含在預存程序中的SQL語句。 RETURN關鍵字表示預存程序結束並能傳回整數狀態值給呼叫者。下面我們就來建立一個簡單的不含參數的預存程序並運用之：
CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN
建立以上預存程序後，儲存之。保存完畢，與該預存程序相對應的節點就會出現在伺服器資源管理器中。同時請注意程式碼編輯視窗中的CREATE關鍵字變成ALTER關鍵字了，該關鍵字是用於更改任何現有的預存程序的。要運行上述預存過程，只要點擊其節點並在右鍵彈出選單中選擇“運行預存程序”，運行的結果圖示如下：
四．創建一個帶有參數的預存程序：
以上我們創建了一個簡單的不帶參數的預存過程，而在實際的應用中往往會用到很多帶有參數的預存過程。有參數的預存程序一般是用來更新資料或是插入資料的。下面我們可以運用同樣的操作方法來建立一個帶有參數的預存程序：
CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state,
country = @country
WHERE ( pub_id = @pub_id )
RETURN
在上面的建立預存程序的程式碼中，我們透過在名稱前面加上一個「@」標誌來宣告預存程序的局部變數－參數，同時也宣告了各個參數的類型，確定了各個參數的方向值，也即表示此參數是輸入型的還是輸出型的或是輸入輸出型的或是傳回值型的。使用者透過對應的預存程序名稱以及正確有效的參數便可呼叫該預存程序了。還有，你可以運用OUTPUT關鍵字在參數中加入輸出型的參數，具體方法請參考上面的語法規則。輸出型的參數能傳回給呼叫者相關的資訊。
上面的儲存過程能更新publishers表中對應出版商的資訊。你可以點擊該預存程序的節點，在右鍵彈出選單中選擇「運行預存程序」來執行它。一旦執行，IDE中便會彈出一個輸入出版商資訊的對話框（如圖3所示）。在該對話方塊中填入正確有效的更新訊息，注意pub_id的值在原來的表格中必須存在，然後點擊「確定」按鈕即可更新資料了。
五．創建簡單預存程序的資料庫應用程式：
下面我們就運用上述的不含參數的預存程序來一個資料庫應用程序，其中也用到了ADO.NET中的SqlDataAdapter物件以及DataSet物件。其中的SqlDataAdapter物件作為SQL Server資料庫和DataSet物件的橋樑將兩者連結在一起。 SqlDataAdapter物件包含了兩個常用的方法：Fill（）方法和Update（）方法。其中的Fill（）方法能從資料庫取得對應資料並填入DataSet物件中，而Update（）方法顧名思義就是更新資料集的意思了。在呼叫Fill（）方法以前，我們必須設定好SqlDataAdapter物件的SelectCommand屬性，該屬性其實是一個SqlCommand物件。 SelectCommand屬性中包含有效的SQL語句，並能據此從資料庫中取得對應資料並填入DataSet物件中。
首先，我們建立一個Windows Forms應用程序，程式語言為C#。在Visual Studio.NET中建立一個新的專案後，為專案新增一個新的類別－Publishers類，該類別封裝了連接到後台資料庫並取得資料集物件的業務邏輯。步驟如下：
1．新增必要的命名空間參考：using System.Data.SqlClient;
2．在該類別中加入以下一些必要的變數：
private SqlConnection cnPubs;
private SqlCommand cmdPubs;
private SqlDataAdapter daPubs;
private DataSet dsPubs;
3．在該類別的建構子中完成連接後台資料庫，取得SqlDataAdapter物件等業務邏輯：
public Publishers()
{
try
{
// 建立一個資料庫連接對象
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
// 建立一個SqlCommand對象，並指明其命令類型為預存程序
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
// 建立一個SqlDataAdapter對象，設定其SelectCommand屬性為上面的SqlCommand對象
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
// 建立一個DataSet對象
dsPubs = new DataSet();
}
catch( Exception ) {}
}
4．最後為此類別提供一個GetPublisherInfo（）方法，該方法用SqlDataAdapter對象填充DataSet對象並返回填充後的DataSet對象，方法如下（值得注意的是：SqlDataAdapter對象會隱式地打開資料庫連接並在獲取資料後隱式地關閉連接，這就是說DataSet物件是工作在非連接模式下的
。
{
// 呼叫SqlDataAdapter物件的Fill（）方法並傳回資料集對象
daPubs.Fill( dsPubs );
return dsPubs;
}
完成Publishers類別的設計後，我們為主視窗新增一個DataGrid控制項並用它來顯示DataSet物件中的資料。首先為主窗體類別加入以下成員變數：
private Publishers pubs;
private DataSet ds;
之後，修改主窗體類別的建構子如下：
public Form1()
{
//
// Windows 窗體設計器支援所必需的
//
InitializeComponent();
//
// TODO: 在InitializeComponent 呼叫後加入任何建構子程式碼
// pubs = new Publishers();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
這樣應用程式一啟動主窗體的DataGrid控制項中便顯示了運用上述不含參數的預存程序從Pubs資料庫取得的對應數據，程式運作圖示如下：
六．創建帶有參數的預存程序的資料庫應用程式：
上面我們創建了一個不帶參數的預存程序的應用程序，下面我們就來創建一個更加複雜的資料庫應用程式。在實際的資料庫應用中，我們往往需要取得資料並更新、插入或刪除數據，這時我們就需要用到帶有參數的預存程序了，同時在運用SqlDataAdapter物件時，我們會呼叫它的Update（）方法。此Update（）方法會自動根據DataSet物件中的DataTable物件內各筆記錄的變更情況完成對應操作。 SqlDataAdapter物件也包含了UpdateCommand、InsertCommand、DeleteCommand等屬性，這些屬性其實都是SqlCommand物件。 Update（）方法會根據操作的型別選用對應的屬性。
在運用帶有參數的預存程序建立資料庫應用程式時，我們一般都要用到SqlParameter類，該類別封裝了各種與Sql參數相關的屬性和方法。其中的屬性包括了ParameterName，SqlDBType，Direction，Size，Value，SourceColumn以及SourceVersion等。其中ParameterName，SqlDBType，Direction，Size等屬性是用來匹配預存程序中定義的參數的。例如下面定義的SqlParameter物件就是用來匹配前面定義的up_UpdatePublisherInfo預存程序中的「@pub_id 」參數的。
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
在上面的定義中，雖然Direction屬性沒有明確地給出，但是它的預設值為Input，所以也滿足了我們的需要。而如果一個SqlParameter物件的Direction屬性為InputOutput或Output或ReturnValue，那麼其Direction屬性就必須明確地說明了，例如下面的程式碼就明確地宣告了一個SqlParameter物件的Direction屬性為Output。
oParam.Direction = ParameterDirection.Output;
其中的SourceColumn屬性是用來匹配一個DataTable物件中的DataColumn物件的，這種匹配能在呼叫Update（）方法更新DataTable物件時隱式地導入所需的SqlParameter物件。如果在定義時沒有宣告該屬性，那麼你必須在程式碼中明確地說明SqlParameter物件的SourceColumn屬性。
其中的SourceVersion屬性的預設值是DataRow物件對應欄位中的目前值，也就是要更新到資料庫中的值。當然，SourceVersion屬性也可以指向DataRow物件對應欄位中的原始值，也也就是從資料庫中取得的初始值。在資料庫事務處理系統中，資料的同步性問題非常重要，以下我們來建立一個能偵測資料同步性的預存程序。
CREATE PROCEDURE dbo.up_UpdatePublisherName
(
@pub_id char(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
AS
if exists(select pub_id
from publishers
其中 (pub_id = @pub_id) AND (pub_name = @Original_pub_name))
Begin
UPDATE publishers SET pub_name = @pub_name
WHERE (pub_id = @pub_id)
End
RETURN
接著，我們在上面的應用程式中呼叫該預存程序以更新發行商的名稱。首先，在原有應用程式的基礎上完善其業務邏輯類別－Publishers類：
1．新增一個新的SqlCommand對象，該物件能作為SqlDataAdapter物件的UpdateCommand屬性被使用：
private SqlCommand cmdUpdPubs;
2．更新該類別的建構子Publishers（）函數，加入以下內容：
// 建立另一個SqlCommand對象，引用更新發行商名稱的預存程序
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
// 為上面的SqlCommand物件加入必要的參數
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name" );
SqlParameter updParam = new SqlParameter
( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add( updParam );
3．指定SqlDataAdapter物件的UpdateCommand屬性為上面定義的SqlCommand物件：
daPubs.UpdateCommand = cmdUpdPubs;
4．新增方法UpdatePublisherName（）：
public void UpdatePublisherName( DataSet dsChanges )
{
// 更新所有改動
daPubs.Update( dsChanges );
}
在應用程式的業務邏輯類別完善之後，在主窗體上新增一個名為「更新資料集」的按鈕，並新增此按鈕的事件回應函數如下：
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo(); } }
到目前為止，應用程式的業務邏輯類別和主窗體類別都已經更新完畢，現在的應用程式可以根據使用者的改用更新資料庫中的相關內容了。
七．總結：
本文向大家介紹了預存程序的基本知識以及在.NET資料庫應用程式中如何結合SqlDataAdapter物件、DataSet物件等建構資料驅動的應用程式。在本文中，我們運用到了兩類預存程序：一類為簡單的不含參數的預存過程，其運用方法相對容易；另一類為帶有參數的預存程序，在呼叫該類預存程序時還得運用到SqlParameter物件。同時，我們不難發現將資料更新業務邏輯封裝在預存程序中是一種很好的設計方法，它能提高應用程式的可管理性、可擴充性以及資料庫的安全性。類似的，插入資料以及刪除資料的業務邏輯也可以封裝在預存程序中並以相似的方法在應用程式中運用。最後，希望本文對大家有不少幫助。