使用C#建立SQL Server的存儲過程（Visual Studio 2005 + SQL Server 2005）

<a href="http://www.dotnetbips.com/articles/70eff218-3da0-4f6f-8f8d-eeea65193f2c.aspx" target="_blank">[原文源碼下載下傳]</a>

[翻譯]使用C#建立SQL Server的存儲過程（Visual Studio 2005 + SQL Server 2005）

原文釋出日期：2007.06.17

介紹

通常，開發人員使用的是T-SQL來建立SQL Server的存儲過程、函數和觸發器。 而現在的SQL Server 2005已經完全支援.NET通用語言運作時（CLR）了。 這就意味着，你可以使用.NET的語言，如C#、VB.NET之類的來開發SQL Server的存儲過程、函數和觸發器。 SQL Server 和 CLR 的內建給我們帶來了n多好處，如實時編譯、類型安全、增強的安全性以及增強的程式設計模型等。 本文中，我将向大家示範如何使用C#建立SQL Server的存儲過程。

背景

我們在使用SQL Server存儲過程時，最常做的工作就是從資料庫中讀取或儲存資料。 其常用應用如下：

    ·執行一些簡單的邏輯，沒有任何傳回值。 也沒有輸出參數。

    ·執行一些邏輯，并通過一個或更多的輸出參數傳回結果。

    ·執行一些邏輯，并傳回從表中讀取的一條或多條記錄。

    ·執行一些邏輯，并傳回一行或多行記錄。 這些記錄不是從表中讀取的，而是你自定義的一些資料行。

為了示範如何用C#開發出這幾種應用的SQL Server存儲過程，我将一個一個地舉出示例。

啟用CLR內建

在你開始用C#寫存儲過程之前，必須要啟用你的SQL Server的CLR內建特性。 預設情況它是不啟用的。 打開你的SQL Server Management Studio并執行如下腳本。

sp_configure 'clr enabled', 1 

GO 

RECONFIGURE 

這裡，我們執行了系統存儲過程“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()的方法，代碼如下：

public static void GetAllCustomers()

cmd.CommandText = "select * from customers";

SqlDataReader reader = cmd.ExecuteReader();

SqlContext.Pipe.Send(reader);

reader.Close();

這個GetAllCustomers()方法用了一個[SqlProcedure]屬性來修飾。 在方法内建立一個SqlConnection和一個SqlCommand對象。 然後使用ExecuteReader()方法來執行SELECT語句。 接下來用Send()方法将取得的SqlDataReader資料發送到用戶端。 最後就是關閉SqlDataReader和SqlConnection。 在這種方法中，是我們自己建立的SqlDataReader。 其實，我們也可以把這個任務交給SqlContext類去完成，代碼如下：

public static void GetCustomerByID

(SqlString CustomerID)

cmd.CommandText = "select * from customers 

where customerid=@p1";

SqlParameter p1 = new SqlParameter("@p1", CustomerID);

SqlContext.Pipe.ExecuteAndSend(cmd);

GetCustomerByID()方法需要一個參數 – CustomerID，它将從Customers表中傳回某個customer的記錄。 這個方法内的代碼，除了ExecuteAndSend()方法外，你應該都已經比較熟悉了。 ExecuteAndSend()方法接收一個SqlCommand對象作為參數，執行它就會傳回資料集給用戶端。

有輸出參數的存儲過程

我們在使用存儲過程時，經常會通過輸出參數傳回一個經過計算的值。 是以，現在讓我們來看一看如何建立具有一個或多個輸出參數的存儲過程。

public static void GetCompanyName

(SqlString CustomerID,out SqlString CompanyName)

cmd.CommandText = "select companyname from 

customers where customerid=@p1";

SqlParameter p1 = new SqlParameter

("@p1", CustomerID);

object obj = cmd.ExecuteScalar();

CompanyName = obj.ToString();

這是一個名為GetCompanyName()的方法，它需要兩個參數。 第一個參數是CustomerID，它是一個輸入參數；第二個參數是CompanyName，它是一個輸出參數（用關鍵字out來指明）。 這兩個參數都是SqlString類型的。 GetCompanyName()方法會接收一個CustomerID參數，然後傳回CompanyName（作為輸出參數）。

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

傳回一行或多行自定義資料的存儲過程

我們在使用存儲過程時，更多的還是從某些表中讀取資料。 但是，某些情況下我們需要的資料可能不在任何表裡。 例如，你可能會基于某些計算來生成一個資料表格。 因為它的資料不是從表中獲得的，是以上面的方法就不在适用了。 幸運的是，SQL Server的CLR內建特性給我們提供了一個解決這個問題的方法。 請看如下代碼：

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對象發送到用戶端。

在上面的示例中，我們隻傳回了一行資料給調用者。 那麼，如果要傳回多行呢？ 請看下面的代碼：

public static void GetMultipleCustomRows()

("CustomerID", SqlDbType.NVarChar, 50);

("CompanyName", SqlDbType.NVarChar, 50);

SqlContext.Pipe.SendResultsStart(record);

SqlContext.Pipe.SendResultsRow(record);

record.SetString(0, "ANATR");

record.SetString(1, "Ana Trujillo Emparedados y helados");

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中來測試它們。