チュートリアル : マネージコードでのストアドプロシージャの作成

[アーティクル]
08/09/2011

Visual Basic や Visual C# などの .NET Framework 言語を使用することにより、Microsoft SQL Server 2005 データベース用および SQL Server 2008 用のストアドプロシージャをマネージコードで記述できるようになりました。マネージコードで記述したストアドプロシージャは、SQL Server 共通言語ランタイムストアドプロシージャ (SQL CLR ストアドプロシージャ) とも呼ばれます。

SQL ストアドプロシージャを作成するには、[ストアドプロシージャ] 項目を SQL CLR データベースプロジェクトに追加します。マネージコードで作成したストアドプロシージャを配置すると、他のストアドプロシージャと同様に呼び出され、実行されます。

このチュートリアルでは、以下のタスクを行います。

新しい SQL CLR データベースプロジェクトを作成する
マネージコードでストアドプロシージャを作成する
SQL Server 2008 データベースへストアドプロシージャを配置する
データベースでストアドプロシージャをテストするためのスクリプトを作成する
ストアドプロシージャが正しく実行されることを確認するためにデータベースのデータを照会する

必須コンポーネント

このチュートリアルを完了するための要件は次のとおりです。

SQL Server 2008 で実行されている AdventureWorks2008 サンプルデータベースへの接続。詳細については、Microsoft Web サイトの「Microsoft SQL Server Product Samples (Microsoft SQL Server 製品サンプル)」ページを参照してください。

プロジェクトの作成

新しい SQL CLR データベースプロジェクトを作成するには

[ファイル] メニューで新しいプロジェクトを作成します。
[SQL CLR データベースプロジェクト] をクリックし、プロジェクトに「SQLCLRStoredProcedure」という名前を付けて、[OK] をクリックします。詳細については、「方法: SQL Server 共通言語ランタイム統合機能を使用するデータオブジェクトのプロジェクトを作成する」を参照してください。

SQL Server 2008 データベースとの接続

このチュートリアルは、SQL Server 2008 で実行されている AdventureWorks2008 サンプルデータベースへの接続が必要です。 サーバーエクスプローラーで、AdventureWorks 2008 サンプルデータベースへの接続が使用できる場合、[データベース参照の追加] ダイアログボックスに表示されます。

AdventureWorks サンプルデータベースに接続するには

[接続の追加] ダイアログボックスの入力を完了し、SQL Server 2008 に含まれる AdventureWorks2008 サンプルデータベースに接続します。詳細については、「接続の追加/変更 (Microsoft SQL Server)」を参照してください。

または
[データベース参照の追加] ダイアログボックスで、AdventureWorks2008 サンプルデータベースへの既存の接続を選択します。詳細については、「[データベース参照の追加] ダイアログボックス」を参照してください。

SQL CLR データベースプロジェクトの構成

SQL CLR データベースプロジェクトを作成した後は、プロジェクトを配置する前に、プロジェクトのプロパティを変更することもできます。

SQL CL データベースプロジェクトのプロパティを構成するには

ソリューションエクスプローラーで、プロパティを構成するプロジェクトを選択します。
[プロジェクト] メニューの [<プロジェクト名> のプロパティ] をクリックします。ここで、プロジェクト名は SQL CLR データベースプロジェクトの名前です。
[アプリケーション] タブの [アセンブリ] 名に、SQL CLR データベースプロジェクトからビルドしたアセンブリに割り当てる名前を指定します。
[アプリケーション] タブの [ターゲットフレームワーク] で、SQL Server のターゲットバージョンがサポートする .NET Framework のバージョンをクリックします。

重要

.NET Framework 2.0 でビルドされた SQL Server プロジェクトをサポートするのは、SQL Server 2005 および SQL Server 2008 のみです。展開すると、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(ここでAssemblyNameは、展開するアセンブリの名前です)。
[データベース] タブの [アセンブリ所有者] に、アセンブリの所有者であるユーザー名または役割を入力します。

重要

この値に現在のユーザーがメンバーとして含まれているロールの名前を指定するか、現在のユーザーに IMPERSONATE アクセス許可を指定する必要があります。アセンブリ所有者を指定しない場合、所有権は現在のユーザーに与えられます。この設定は、SQL Server CREATE ASSEMBLY ステートメントの AUTHORIZATION 所有者名引数に対応します。詳細については、Microsoft Web サイトの「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();
        }
    }
}

ストアドプロシージャの配置、実行、およびデバッグ

新しいストアドプロシージャを作成した後で、このストアドプロシージャのビルド、SQL Server への配置、およびデバッグを、F5 キーを押して実行できます。まず、プロジェクトの TestScripts フォルダーにある Test.sql ファイル内に、ストアドプロシージャの実行とテストを行うコードを追加します。テストスクリプトを作成する方法の詳細については、「方法: Test.sql スクリプトを編集して SQL Server 共通言語ランタイム統合機能を使用するオブジェクトを実行する」を参照してください。

SQL をデバッグする方法の詳細については、「SQL データベースオブジェクトのデバッグ」を参照してください。

InsertCurrency ストアドプロシージャを配置して実行するには

ソリューションエクスプローラーで、[TestScripts] フォルダーを展開し、Test.sql ファイルをダブルクリックします。

注意

既定のデバッグスクリプトに他のスクリプトを指定することもできます。詳細については、「方法: Test.sql スクリプトを編集して SQL Server 共通言語ランタイム統合機能を使用するオブジェクトを実行する」を参照してください。

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 キーを押して、ストアドプロシージャをビルド、配置、およびデバッグします。デバッグせずに配置する方法については、「方法: SQL CLR データベースプロジェクト項目を SQL Server に配置する」を参照してください。

表示されます、結果を表示、出力 ] ウィンドウと 出力元の表示：データベース出力。

参照

チュートリアル : マネージ コードでのストアド プロシージャの作成

必須コンポーネント

プロジェクトの作成

新しい SQL CLR データベース プロジェクトを作成するには

SQL Server 2008 データベースとの接続

AdventureWorks サンプル データベースに接続するには

SQL CLR データベース プロジェクトの構成

SQL CL データベース プロジェクトのプロパティを構成するには

SQL Server のストアド プロシージャの作成

SQL Server のストアド プロシージャを作成するには

ストアド プロシージャの配置、実行、およびデバッグ

InsertCurrency ストアド プロシージャを配置して実行するには

参照

処理手順

参照

概念

その他のリソース