使用C#建立SQL Server的預存程序

介紹
通常，開發人員使用的是T-SQL來建立SQL Server的預存程序、函數和觸發器。 而現在的SQL Server 2005已經完全支援.NET通用語言運行時（CLR）了。 這就意味著，你可以使用.NET的語言，如C#、VB.NET之類的來開發SQL Server的預存程序、函數和觸發器。 SQL Server 和 CLR 的整合給我們帶來了n多好處，如Just-In-Time 編譯、型別安全、增強的安全性以及增強編程模型等。 本文中，我將向大家示範如何使用C#建立SQL Server的預存程序。

背景
我們在使用SQL Server預存程序時，最常做的工作就是從資料庫中讀取或儲存資料。 其常用應用如下：
    ·執行一些簡單的邏輯，沒有任何傳回值。 也沒有輸出參數。
    ·執行一些邏輯，並通過一個或更多的輸出參數返回結果。
    ·執行一些邏輯，並返回從表中讀取的一條或多條記錄。
    ·執行一些邏輯，並返回一行或多行記錄。 這些記錄不是從表中讀取的，而是你自訂的一些資料行。

為了示範如何用C#開發出這幾種應用的SQL Server預存程序，我將一個一個地舉出樣本。

啟用CLR整合
在你開始用C#寫預存程序之前，必須要啟用你的SQL Server的CLR整合特性。 預設情況它是不啟用的。 開啟你的SQL Server Management Studio並執行如下指令碼。
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
這裡，我們執行了系統預存程序“sp_configure”，為其提供的兩個參數分別為：“clr enabled”和“1”。如果要停用CLR整合的話也是執行這個預存程序，只不過第二個參數要變為“0”而已。另外，為了使新的設定產生效果，不要忘記調用“RECONFIGURE”。

SQL Server項目
現在開啟Visual Studio，並從檔案菜單中選擇“建立項目”。 在“建立項目”對話方塊中選擇“Visual C#”下的“Database”。 然後選擇“SQL Server項目”模板。

起好項目名稱後就單擊“確定”按鈕。

很快，你所建立的項目就要求你選擇一個SQL Server資料庫。

按照提示一步一步地做就好了，就算你選擇了取消，也可以在“項目”–“屬性”對話方塊中再一次選擇資料庫。 舉個例子，假如你的電腦上有一個Northwind資料庫，那麼就在“建立資料庫引用”對話方塊中選中它，然後單擊“確定”按鈕。 之後，SQL Server項目在部署的時候就會將我們開發的預存程序寫入這個資料庫（繼續往後看你就清楚是怎麼回事了）。

接下來，按右鍵你建立的這個項目，選擇“添加”-“預存程序”。 然後將會出現如所示的對話方塊：

選擇“預存程序”模板，並起一個合適的名字，然後單擊“添加”按鈕。

添加完後你就會發現，實際上這是建立了一個已經匯入了需要用到的命名空間的類。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
注意一下加粗顯示的命名空間（譯者註：後兩個using）。 System.Data.SqlTypes命名空間包含了很多不同的類型，它們可以用來代替SQL Server的資料類型。 Microsoft.SqlServer.Server命名空間下的類負責SQL Server的CLR整合。

沒有傳回值的預存程序
在這一節中，我們將會看到如何寫一個執行了一些邏輯，但是卻沒有任何傳回值和輸出參數的預存程序。 在這個例子裡，我們將建立一個名為“ChangeCompanyName”的預存程序，它用來修改Customers表中CompanyName欄位的值。 這個預存程序需要兩個參數 – CustomerID（需要更改公司名稱的客戶的ID）和CompanyName（新的公司名稱）。 “ChangeCompanyName”預存程序完成後的代碼如下：
[SqlProcedure]
public static void ChangeCompanyName
(SqlString CustomerID, SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "update customers set
companyname=@p1 where customerid=@p2";
SqlParameter p1 = new SqlParameter("@p1", CompanyName);
SqlParameter p2 = new SqlParameter("@p2", CustomerID);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
int i=cmd.ExecuteNonQuery();
cnn.Close();
SqlContext.Pipe.Send(i.ToString());
}
仔細看一下這個ChangeCompanyName()方法。 它是一個靜態方法並且沒有傳回值（void）。 它需要兩個名為CustomerID和CompanyName的參數。 請注意這兩個參數的資料類型都是SqlString。 SqlString可以用來代替SQL Server中的nvarchar資料類型。 這個方法用了一個[SqlProcedure]屬性來修飾。 該屬性用於標記ChangeCompanyName()方法是一個SQL Server預存程序。

在方法內我們建立了一個SqlConnection對象，並設定其連接字串為“context connection = true”。 “上下文串連”可以讓你使用當前登入到資料庫的使用者作為你的登入資料庫的驗證資訊。 本例中，ChangeCompanyName()方法將會轉換為預存程序，然後儲存到Northwind資料庫裡。 所以在這裡的“上下文串連”指的就是Northwind資料庫。 這樣你就不需要再寫任何關於登入資料庫的驗證資訊了。

接下來是開啟資料庫連接。 然後通過設定SqlCommand對象的Connection和CommandText屬性，讓其執行更新操作。 同時，我們還需要設定兩個參數。 這樣通過調用ExecuteNonQuery()方法就可以執行更新操作了。 再接下來就是關閉串連。

最後，將ExecuteNonQuery()方法的傳回值發送到用戶端。 當然你也可以不做這一步。 現在我們來瞭解一下SqlContext類的使用。 SqlContext類用於在服務端和用戶端之間傳遞處理結果。 本例使用了Send()方法發送一個字串返回給調用者。

返回從表中讀取的一條或多條記錄的預存程序
我們在使用預存程序時，經常會SELECT一條或多條記錄。 你可以採用兩種方法來建立這樣的預存程序。

首先我們建立一個名為GetAllCustomers()的方法，代碼如下：
[SqlProcedure]
public static void GetAllCustomers()
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers";
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
cnn.Close();
}
這個GetAllCustomers()方法用了一個[SqlProcedure]屬性來修飾。 在方法內建立一個SqlConnection和一個SqlCommand對象。 然後使用ExecuteReader()方法來執行SELECT語句。 接下來用Send()方法將取得的SqlDataReader資料發送到用戶端。 最後就是關閉SqlDataReader和SqlConnection。 在這種方法中，是我們自己建立的SqlDataReader。 其實，我們也可以把這個任務交給SqlContext類去完成，代碼如下：
[SqlProcedure]
public static void GetCustomerByID
(SqlString CustomerID)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers
where customerid=@p1";
SqlParameter p1 = new SqlParameter("@p1", CustomerID);
cmd.Parameters.Add(p1);
SqlContext.Pipe.ExecuteAndSend(cmd);
cnn.Close();
}
GetCustomerByID()方法需要一個參數 – CustomerID，它將從Customers表中返回某個customer的記錄。 這個方法內的代碼，除了ExecuteAndSend()方法外，你應該都已經比較熟悉了。 ExecuteAndSend()方法接收一個SqlCommand對象作為參數，執行它就會返回資料集給用戶端。

有輸出參數的預存程序
我們在使用預存程序時，經常會通過輸出參數返回一個經過計算的值。 所以，現在讓我們來看一看如何建立具有一個或多個輸出參數的預存程序。
[SqlProcedure]
public static void GetCompanyName
(SqlString CustomerID,out SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context connection=true");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select companyname from
customers where customerid=@p1";
SqlParameter p1 = new SqlParameter
("@p1", CustomerID);
cmd.Parameters.Add(p1);
object obj = cmd.ExecuteScalar();
cnn.Close();
CompanyName = obj.ToString();
}
這是一個名為GetCompanyName()的方法，它需要兩個參數。 第一個參數是CustomerID，它是一個輸入參數；第二個參數是CompanyName，它是一個輸出參數（用關鍵字out來指明）。 這兩個參數都是SqlString類型的。 GetCompanyName()方法會接收一個CustomerID參數，然後返回CompanyName（作為輸出參數）。

該方法內的代碼首先設定了SqlConnection和SqlCommand對象。 然後，使用ExecuteScalar()方法來執行SELECT語句。 ExecuteScalar()方法返回的值是一個object類型，它其實就是公司名稱。 最後將輸出參數CompanyName設定為這個值。

返回一行或多行自訂資料的預存程序
我們在使用預存程序時，更多的還是從某些表中讀取資料。 但是，某些情況下我們需要的資料可能不在任何錶裡。 例如，你可能會基於某些計算來產生一個資料表格。 因為它的資料不是從表中獲得的，所以上面的方法就不在適用了。 幸運的是，SQL Server的CLR整合特性給我們提供了一個解決這個問題的方法。 請看如下代碼：
[SqlProcedure]
public static void GetCustomRow()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar,50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar,50);
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.Send(record);
}
GetCustomRow()方法會返回一條記錄並發送給用戶端。 這個方法首先聲明了一個SqlMetaData對象。 當你要用到自訂欄的時候，就可以使用這個SqlMetaData類。 在我們的樣本中，建立了兩個類型為NVarChar，長度為50的列。然後建立了一個SqlDataRecord對象。 SqlDataRecord類可以用來表示一個自訂行。 它的建構函式需要一個SqlMetaData數組作為參數。 SqlDataRecord對象的SetString()方法用來設定列的值。 另外，還有許多不同的類似SetString()這樣的方法，可以用來處理不同的資料類型。 最後，調用Send()方法將SqlDataRecord對象發送到用戶端。

在上面的樣本中，我們只返回了一行資料給調用者。 那麼，如果要返回多行呢？ 請看下面的代碼：
[SqlProcedure]
public static void GetMultipleCustomRows()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar, 50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar, 50);
SqlDataRecord record = new SqlDataRecord(metadata);
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.SendResultsRow(record);
record.SetString(0, "ANATR");
record.SetString(1, "Ana Trujillo Emparedados y helados");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
GetMultipleCustomRows()方法將會返回多個SqlDataRecord對象到用戶端。 接下來建立自訂欄和設定列的值都和之前的例子一樣。 但是，我們使用的是SendResutlsStart()方法來傳輸資料。 SendResultsRow()方法也是發送一個SqlDataRecord對象到用戶端，但是我們可以多次調用它，從而做到發送多條記錄。 最後，調用SendResultsEnd()方法用來標記已經完成資料轉送操作。

我們已經開發完了預存程序。 現在就可以將這個項目編譯為一個程式集（.DLL）。 但是我們的工作並沒有到此結束。 我們還需要部署這個程式集和預存程序到SQL Server資料庫。 有兩種方法可以完成這個工作 – 手動和自動。 手動方法是使用T-SQL語句註冊你的程式集，並將預存程序部署到SQL Server資料庫中。 在本例中，我將使用自動的方法來部署預存程序到SQL Server資料庫。

按右鍵你的項目，然後在菜單中選擇“部署”選項。

如此就會自動地完成註冊程式集和部署預存程序的工作。 注意，只有在你建立項目時添加了資料庫引用的時候，才會出現“部署”選項。 如果因為某些原因你沒能添加資料庫引用，那麼你可以通過項目屬性對話方塊來設定它。

如果你在SQL Server Management Studio查看Northwind資料庫的話，那麼就應該可以看到和相似的結果。

注意，在預存程序節點下出現了我們建立的所有方法（有“鎖”表徵圖的），並且在程式集節點下出現了我們的程式集。

就是這些東西，很簡單吧。 現在你就可以在你的程式中調用這些預存程序了。 你也可以在SQL Server Management Studio中來測試它們。

 

原文：http://www.knowsky.com/345516.html