一．前言：

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象，任何一個設計良好的數(shù)據(jù)庫應用程序都應該用到存儲過程。總的來說，存儲過程具有以下一些優(yōu)點：

◆存儲過程允許標準組件式編程

◆存儲過程能夠實現(xiàn)較快的執(zhí)行速度

◆存儲過程能夠減少網(wǎng)絡流量

◆存儲過程可被作為一種安全機制來充分利用

本文作者將向大家介紹.NET數(shù)據(jù)庫應用程序中存儲過程的應用，以及如何將它與ADO.NET中的SqlDataAdapter對象、DataSet對象等結合使用以提高.NET數(shù)據(jù)庫應用程序的總體性能。

二．系統(tǒng)要求：

開發(fā)工具：Visual Studio.NET

數(shù)據(jù)庫管理系統(tǒng)：SQL Server 2000（其中包含了示例程序所用到的Pubs數(shù)據(jù)庫）

三．創(chuàng)建一個簡單的存儲過程：

這里我將向大家介紹如何運用Visual Studio.NET IDE來創(chuàng)建存儲過程。運用Visual Studio.NET IDE創(chuàng)建存儲過程是非常容易和直觀的，你只要在服務器資源管理器中導向到Pubs數(shù)據(jù)庫并展開節(jié)點，就會發(fā)現(xiàn)包括存儲過程在內的各種數(shù)據(jù)庫對象，如圖1所示。
 

在存儲過程節(jié)點上點擊右鍵便可彈出一個菜單，其中包含了“新建存儲過程”的命令。新建一個存儲過程后，IDE中的代碼編輯窗口便出現(xiàn)如下所示的代碼模板：

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
 

上面的代碼模板符合簡化的創(chuàng)建存儲過程的語法規(guī)則，完整的語法規(guī)則如下：

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
 

限于篇幅，各個參數(shù)的含義在此就不多作介紹了，有興趣的讀者可以參考有關SQL Server 2000數(shù)據(jù)庫管理系統(tǒng)的資料。

下面我對該代碼模板中的各個語法成分略作介紹。CREATE PROCEDURE聲明創(chuàng)建一個存儲過程，后面跟著該存儲過程的名稱。“/*……*/”中的成分是該存儲過程的參數(shù)，可包括輸入?yún)��?shù)和輸出參數(shù)。AS關鍵字后面的內容是該存儲過程的主體部分，其中是任何數(shù)量和類型的包含在存儲過程中的SQL語句。RETURN關鍵字表明存儲過程結束并能返回整型狀態(tài)值給調用者。下面我們就來創(chuàng)建一個簡單的不帶參數(shù)的存儲過程并運用之：

CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN

創(chuàng)建以上存儲過程后，保存之。保存完畢，與該存儲過程相對應的節(jié)點就會出現(xiàn)在服務器資源管理器中。同時請注意代碼編輯窗口中的CREATE關鍵字變?yōu)锳LTER關鍵字了，該關鍵字是用于更改任何現(xiàn)有的存儲過程的。要運行上述存儲過程，只要點擊其節(jié)點并在右鍵彈出菜單中選擇“運行存儲過程”，運行的結果圖示如下：

四．創(chuàng)建一個帶參數(shù)的存儲過程：

以上我們創(chuàng)建了一個簡單的不帶參數(shù)的存儲過程，而在實際的應用中往往會用到很多帶有參數(shù)的存儲過程。帶有參數(shù)的存儲過程一般是用于更新數(shù)據(jù)或是插入數(shù)據(jù)的。下面我們可以運用同樣的操作方法創(chuàng)建一個帶參數(shù)的存儲過程：

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
 

在上面的創(chuàng)建存儲過程的代碼中，我們通過在名稱前添加一個“@”標志來聲明存儲過程的局部變量－參數(shù)，同時還聲明了各個參數(shù)的類型，確定了各個參數(shù)的方向值，也即表明該參數(shù)是輸入型的還是輸出型的或者是輸入輸出型的或者是返回值型的。用戶通過相應的存儲過程名稱以及正確有效的參數(shù)便可調用該存儲過程了。還有，你可以通過運用OUTPUT關鍵字在參數(shù)中添加輸出型的參數(shù)，具體方法請參考上面的語法規(guī)則。輸出型的參數(shù)能返回給調用者相關的信息。

上面的存儲過程能更新publishers表中相應出版商的信息。你可以通過點擊該存儲過程的節(jié)點，在右鍵彈出菜單中選擇“運行存儲過程”來執(zhí)行它。一旦執(zhí)行，IDE中便彈出一個輸入出版商信息的對話框（如圖3所示）。在該對話框中填入正確有效的更新信息，注意pub_id的值在原來的表中必須存在，然后點擊“確定”按鈕便可更新數(shù)據(jù)了。

五．創(chuàng)建簡單存儲過程的數(shù)據(jù)庫應用程序：

下面我們就運用上述的不帶參數(shù)的存儲過程來一個數(shù)據(jù)庫應用程序，其中還用到了ADO.NET中的SqlDataAdapter對象以及DataSet對象。其中的SqlDataAdapter對象作為SQL Server數(shù)據(jù)庫和DataSet對象的橋梁將兩者聯(lián)系在一起。SqlDataAdapter對象包含了兩個常用的方法：Fill（）方法和Update（）方法。其中的Fill（）方法能從數(shù)據(jù)庫中獲取相應數(shù)據(jù)并填充到DataSet對象中，而Update（）方法顧名思義就是更新數(shù)據(jù)集的意思了。在調用Fill（）方法以前，我們必須設置好SqlDataAdapter對象的SelectCommand屬性，該屬性其實是一個SqlCommand對象。SelectCommand屬性中包含有效的SQL語句，并能據(jù)此從數(shù)據(jù)庫中獲取相應數(shù)據(jù)并填充到DataSet對象中。

首先，我們創(chuàng)建一個Windows Forms應用程序，編程語言為C#。在Visual Studio.NET中創(chuàng)建一個新的項目后，給該項目添加一個新的類－Publishers類，該類封裝了連接到后臺數(shù)據(jù)庫并獲取數(shù)據(jù)集對象的業(yè)務邏輯。步驟如下：

1．添加必要的命名空間引用：using System.Data.SqlClient;

2．給該類添加如下一些必要的變量：

private SqlConnection cnPubs;
private SqlCommand cmdPubs;
private SqlDataAdapter daPubs;
private DataSet dsPubs;
 

3．在該類的構造函數(shù)中完成連接后臺數(shù)據(jù)庫，獲取SqlDataAdapter對象等業(yè)務邏輯：

public Publishers()
{
try
{
// 創(chuàng)建一個數(shù)據(jù)庫連接對象
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
// 創(chuàng)建一個SqlCommand對象，并指明其命令類型為存儲過程
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
// 創(chuàng)建一個SqlDataAdapter對象，設定其SelectCommand屬性為上面的SqlCommand對象
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
// 創(chuàng)建一個DataSet對象
dsPubs = new DataSet();
}
catch( Exception ) {}
}
 

4．最后為該類提供一個GetPublisherInfo（）方法，該方法用SqlDataAdapter對象填充DataSet對象并返回填充后的DataSet對象，方法如下（值得注意的是：SqlDataAdapter對象會隱式地打開數(shù)據(jù)庫連接并在獲取數(shù)據(jù)后隱式地關閉連接，這就是說DataSet對象是工作在非連接模式下的。而當你顯式地打開數(shù)據(jù)庫連接并獲取數(shù)據(jù)后，SqlDataAdapter對象并不會將該連接關閉）：

public DataSet GetPublisherInfo()
{
// 調用SqlDataAdapter對象的Fill（）方法并返回數(shù)據(jù)集對象
daPubs.Fill( dsPubs );
return dsPubs;
}
 

完成Publishers類的設計后，我們給主窗體添加一個DataGrid控件并用它來顯示DataSet對象中的數(shù)據(jù)。首先給主窗體類添加如下成員變量：

private Publishers pubs;
private DataSet ds;
之后，修改主窗體類的構造函數(shù)如下：
public Form1()
{
//
// Windows 窗體設計器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 調用后添加任何構造函數(shù)代碼
// pubs = new Publishers();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
 

這樣該應用程序一啟動主窗體的DataGrid控件中便顯示了運用上述不帶參數(shù)的存儲過程從Pubs數(shù)據(jù)庫中獲取的相應數(shù)據(jù)，程序運行圖示如下：

六．創(chuàng)建帶參數(shù)的存儲過程的數(shù)據(jù)庫應用程序：

上面我們創(chuàng)建了一個不帶參數(shù)的存儲過程的應用程序，下面我們就來創(chuàng)建一個更加復雜的數(shù)據(jù)庫應用程序。在實際的數(shù)據(jù)庫應用中，我們往往需要獲取數(shù)據(jù)并更新、插入或刪除數(shù)據(jù)，這時我們就需要用到帶有參數(shù)的存儲過程了，同時在運用SqlDataAdapter對象時，我們會調用它的Update（）方法。該Update（）方法會自動根據(jù)DataSet對象中的DataTable對象內各條記錄的變化情況完成相應操作。SqlDataAdapter對象還包含了UpdateCommand、InsertCommand、DeleteCommand等屬性，這些屬性其實都是SqlCommand對象。Update（）方法會根據(jù)操作的類型選用相應的屬性。

在運用帶有參數(shù)的存儲過程建立數(shù)據(jù)庫應用程序時，我們一般都要用到SqlParameter類，該類封裝了各種與Sql參數(shù)相關的屬性和方法。其中的屬性包括了ParameterName，SqlDBType，Direction，Size，Value，SourceColumn以及SourceVersion等。其中ParameterName，SqlDBType，Direction，Size等屬性是用于匹配存儲過程中定義的參數(shù)的。比如下面定義的SqlParameter對象就是用來匹配前面定義的up_UpdatePublisherInfo存儲過程中的“@pub_id”參數(shù)的。

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對象相應字段中的當前值，也就是要更新到數(shù)據(jù)庫中的值。當然，SourceVersion屬性也可以指向DataRow對象相應字段中的原始值，也即從數(shù)據(jù)庫中獲取的初始值。在數(shù)據(jù)庫事務處理系統(tǒng)中，數(shù)據(jù)的同步性問題非常重要，下面我們來建立一個能檢測數(shù)據(jù)同步性的存儲過程。

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
where (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
 

接著，我們在上面的應用程序中調用該存儲過程以更新發(fā)行商的名稱。首先，在原有應用程序的基礎上完善其業(yè)務邏輯類－Publishers類：

1．添加一個新的SqlCommand對象，該對象能作為SqlDataAdapter對象的 UpdateCommand屬性被使用：
private SqlCommand cmdUpdPubs;
2．更新該類的構造函數(shù)Publishers（）函數(shù)，添加以下內容：
// 創(chuàng)建另一個SqlCommand對象，該對象引用更新發(fā)行商名稱的存儲過程
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
// 為上面的SqlCommand對象添加必要的參數(shù)
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 );
}
 

應用程序的業(yè)務邏輯類完善之后，在主窗體上添加一個名為“更新數(shù)據(jù)集”的按鈕，并添加該按鈕的事件響應函數(shù)如下：

private void button1_Click(object sender, System.EventArgs e) { if( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo(); } } 

到此為止，應用程序的業(yè)務邏輯類和主窗體類都已經(jīng)更新完畢，現(xiàn)在的應用程序能根據(jù)用戶的改用更新數(shù)據(jù)庫中的相關內容了。

七．總結：

本文向大家介紹了存儲過程的基本知識以及在.NET數(shù)據(jù)庫應用程序中如何結合SqlDataAdapter對象、DataSet對象等構建數(shù)據(jù)驅動的應用程序。在本文中，我們運用到了兩類存儲過程：一類為簡單的不帶參數(shù)的存儲過程，其運用方法相對容易；另一類為帶有參數(shù)的存儲過程，在調用該類存儲過程時還得運用到SqlParameter對象。同時，我們不難發(fā)現(xiàn)將數(shù)據(jù)更新業(yè)務邏輯封裝在存儲過程中是一種很好的設計方法，它能提高應用程序的可管理性、可擴展性以及數(shù)據(jù)庫的安全性。類似的，插入數(shù)據(jù)以及刪除數(shù)據(jù)的業(yè)務邏輯也可以封裝在存儲過程中并以相似的方法在應用程序中被運用。最后，希望本文對大家有不少幫助。