在.NET中使用SQL預存程序 )

◆預存程序允許標準組件式編程

◆預存程序能夠實現較快的執行速度

◆預存程序能夠減少網路流量

◆預存程序可被作為一種安全機制來充分利用
本文作者將向大家介紹.NET資料庫應用程式中預存程序的應用，以及如何將它與ADO.NET中的SqlDataAdapter對象、DataSet對象等結合使用以提高.NET資料庫應用程式的總體效能。

三．建立一個簡單的預存程序：

這裡我將向大家介紹如何運用Visual Studio.NET IDE來建立預存程序。運用Visual Studio.NET IDE建立預存程序是非常容易和直觀的，你只要在伺服器總管中導向到Pubs資料庫並展開節點，就會發現包括預存程序在內的各種資料庫物件

在預存程序節點上點擊右鍵便可彈出一個菜單，其中包含了;建立預存程序”的命令。建立一個預存程序後，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的值在原來的表中必須存在，然後點擊;確定”按鈕便可更新資料了。

圖3
五．建立簡單預存程序的資料庫應用程式：

下面我們就運用上述的不帶參數的預存程序來一個資料庫應用程式，其中還用到了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對象並不會將該串連關閉)：

public DataSet GetPublisherInfo()
{
// 調用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資料庫中擷取的相應資料，程式運行圖示如下：

圖4

六．建立帶參數的預存程序的資料庫應用程式：

上面我們建立了一個不帶參數的預存程序的應用程式，下面我們就來建立一個更加複雜的資料庫應用程式。在實際的資料庫應用中，我們往往需要擷取資料並更新、插入或刪除資料，這時我們就需要用到帶有參數的預存程序了，同時在運用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
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

接著，我們在上面的應用程式中調用該預存程序以更新發行商的名稱。首先，在原有應用程式的基礎上完善其商務邏輯類－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();；
}
}

到此為止，應用程式的商務邏輯類和主表單類都已經更新完畢，現在的應用程式能根據使用者的改用更新資料庫中的相關內容了。運行程式5所示，你在更新了資料後還可以開啟SQL Server中的相應表並驗證資料有沒有更新成功。

圖5

七．總結：

本文向大家介紹了預存程序的基本知識以及在.NET資料庫應用程式中如何結合SqlDataAdapter對象、DataSet對象等構建資料驅動的應用程式。在本文中，我們運用到了兩類預存程序：一類為簡單的不帶參數的預存程序，其運用方法相對容易；另一類為帶有參數的預存程序，在調用該類預存程序時還得運用到SqlParameter對象。同時，我們不難發現將資料更新商務邏輯封裝在預存程序中是一種很好的設計方法，它能提高應用程式的可管理性、可擴充性以及資料庫的安全性。類似的，插入資料以及刪除資料的商務邏輯也可以封裝在預存程序中並以相似的方法在應用程式中被運用。最後，希望本文對大家有不少協助。