逐步解說：使用 Managed 程式碼建立預存程序

發行項
08/12/2011

您現在可以使用 .NET Framework 語言，例如 Visual Basic 和 Visual C#，以 Managed 程式碼撰寫 Microsoft SQL Server 2005 和 SQL Server 2008 資料庫的預存程序。以 Managed 程式碼撰寫的預存程序稱為 SQL Server Common Language Runtime 預存程序或 SQL CLR 預存程序。

您可以將 [預存程序] 項目加入至 SQL CLR 資料庫專案，以建立 SQL 預存程序。部署以 Managed 程式碼所建立的預存程序後，便可如同其他預存程序般，呼叫和執行這些預存程序。

本逐步解說所述的工作包括下列各項：

建立新的 SQL CLR 資料庫專案
以 Managed 程式碼建立預存程序
將預存程序部署至 SQL Server 2008 資料庫
建立指令碼，在資料庫上測試預存程序
查詢資料庫中的資料，以確認預存程序正常執行

必要條件

若要完成這個逐步解說，您需要：

連接到執行於 SQL Server 2008 的 AdventureWorks2008 範例資料庫。如需詳細資訊，請參閱 Microsoft 網站上的 Microsoft SQL Server 產品範例網頁 (英文)。

建立專案

若要建立新的 SQL CLR 資料庫專案

從 [檔案] 功能表中，建立新專案。
選取 [SQL CLR 資料庫專案]，將專案命名為 SQLCLRStoredProcedure，再按一下 [確定]。如需詳細資訊，請參閱HOW TO：針對使用 SQL Server Common Language Run-time 整合的資料庫物件建立專案。

連接到 SQL Server 2008 資料庫

此逐步解說需要連接到執行於 SQL Server 2008 的 AdventureWorks2008 範例資料庫。如果 [伺服器總管] 中有 AdventureWorks2008 範例資料庫的連接，此連接會在加入資料庫參考對話方塊中列出。

若要連接到 AdventureWorks 範例資料庫

完成 [加入連接] 對話方塊，即可連接到 SQL Server 2008 中所包含的 AdventureWorks2008 範例資料庫。如需詳細資訊，請參閱新增/修改連接 (Microsoft SQL Server)。

-或-
在 [加入資料庫參考] 對話方塊中，選取現有的 AdventureWorks2008 範例資料庫連接。如需詳細資訊，請參閱加入資料庫參考對話方塊。

設定 SQL CLR 資料庫專案

建立 SQL CLR 資料庫專案之後，可能必須修改數個專案屬性，才能成功部署專案。

若要設定 SQL CLR 資料庫專案的屬性

在 [方案總管] 中，選取您要設定屬性的專案。
在 [專案] 功能表中，按一下 [ProjectName 屬性]，其中 ProjectName 是您提供給 SQL CLR 資料庫專案的名稱。
在 [應用程式] 索引標籤的 [組件名稱] 中，指定要提供給從 SQL CLR 資料庫專案建置之組件的名稱。

在 [應用程式] 索引標籤的 [目標 Framework] 中，按一下目標 SQL Server 版本所支援的 .NET Framework 版本。

重要事項
SQL Server 2005 和 SQL Server 2008 僅支援使用 .NET Framework 2.0 所建置的 SQL Server 專案。如果您嘗試部署SQL Server專案，SQL Server 2005或SQL Server 2008，將顯示錯誤消息：Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在進行校驗是您要部署的程式集的名稱）。

SQL Server 2005 和 SQL Server 2008 僅支援使用 .NET Framework 2.0 所建置的 SQL Server 專案。如果您嘗試部署SQL Server專案，SQL Server 2005或SQL Server 2008，將顯示錯誤消息：Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在進行校驗是您要部署的程式集的名稱）。

在 [資料庫] 索引標籤的 [組件擁有人] 中，輸入組件擁有人的使用者或角色名稱。

重要事項
這個值必須是在目前使用者為成員時的角色名稱，或者目前使用者必須擁有 IMPERSONATE 使用權限。如果您沒有指定組件擁有人，則擁有權會提供給目前的使用者。這個設定對應於 SQL Server CREATE ASSEMBLY 陳述式的 AUTHORIZATION owner_name 引數。如需詳細資訊，請參閱 Microsoft 網站上的 CREATE ASSEMBLY (Transact-SQL)。

在 [檔案] 功能表上，按一下 [全部儲存]。

建立 SQL Server 預存程序

建立 SQL CLR 資料庫專案之後，將預存程序加入此專案。

若要建立 SQL Server 預存程序

在 [專案] 功能表上按一下 [加入新項目]。
在 [加入新項目] 對話方塊中，選取 [預存程序]。
輸入 InsertCurrency 做為新預存程序的 [名稱]。
然後按一下 [加入]。

以下列程式碼取代 [程式碼編輯器] 中的程式碼：

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

部署、執行和偵錯預存程序

建立新的預存程序之後，按下 F5 即可進行建置、部署至 SQL Server 和偵錯等作業。首先，將執行和測試預存程序的程式碼，加入至專案中 TestScripts 資料夾的 Test.sql 檔案內。如需如何建立測試指令碼的詳細資訊，請參閱 HOW TO：編輯 Test.sql 指令碼以執行使用 SQL Server Common Language Run-time 整合的物件。

如需如何偵錯 SQL 的詳細資訊，請參閱Debugging SQL Database Objects。

若要部署及執行 InsertCurrency 預存程序

在 [方案總管] 中，展開 [TestScripts] 資料夾，並按兩下 Test.sql 檔案。

注意事項
您可以指定其他指令碼做為預設偵錯指令碼。如需詳細資訊，請參閱HOW TO：編輯 Test.sql 指令碼以執行使用 SQL Server Common Language Run-time 整合的物件。

使用下列程式碼取代 Test.sql 檔案中的程式碼：

'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

按下 F5，即可建置、部署和偵錯預存程序。如需如何在不偵錯的情況下進行部署的詳細資訊，請參閱 HOW TO：將 SQL CLR 資料庫專案項目部署至 SQL Server。

查看所示的結果輸出視窗，然後選擇 顯示輸出：資料庫輸出。

請參閱

Share via