チュートリアル : マネージ コードでのストアド プロシージャの作成
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 Server ストアド プロシージャを作成および実行する
方法: 共通言語ランタイム統合機能を使用して SQL Server のトリガーを作成および実行する
方法: 共通言語ランタイム統合機能を使用して SQL Server の集計を作成および実行する
方法: 共通言語ランタイム統合機能を使用して SQL Server のユーザー定義関数を作成および実行する
方法: 共通言語ランタイム統合機能を使用して SQL Server のユーザー定義型を作成および実行する
方法 : SQL CLR のストアド プロシージャをデバッグする
参照
SQL CLR データベース プロジェクトおよびデータベース オブジェクトの属性