マネージド コードでストアド プロシージャとユーザー定義関数を作成する (C#)
Microsoft SQL Server 2005 は .NET 共通言語ランタイムと統合され、開発者はマネージド コードを使用してデータベース オブジェクトを作成できます。 このチュートリアルでは、Visual Basic または C# コードを使用して、マネージド ストアド プロシージャとマネージド ユーザー定義関数を作成する方法について説明します。 また、これらのエディションの Visual Studio を使用して、このようなマネージド データベース オブジェクトをデバッグする方法についても説明します。
はじめに
Microsoft の SQL Server 2005 などのデータベースでは、データの挿入、変更、および取得に Transact-構造化照会言語 (T-SQL) が使用されます。 ほとんどのデータベース システムには、1 つの再利用可能な単位として実行可能な一連の SQL ステートメントをグループ化するためのコンストラクトが含まれています。 ストアド プロシージャは 1 つの例です。 もう 1 つは、手順 9 で詳しく説明するコンストラクトであるユーザー定義関数 (UDF) です。
その中核では、SQL はデータ のセットを操作するように設計されています。 SELECT
、UPDATE
、および DELETE
ステートメントは、本質的に対応するテーブル内のすべてのレコードに適用され、WHERE
句によってのみ制限されます。 しかし、一度に 1 つのレコードを操作する、または、スカラー データを操作するために設計された言語機能が多数あります。 CURSOR
を使用すると、一連のレコードを一度に 1 つずつループできます。 LEFT
、CHARINDEX
、PATINDEX
などの文字列操作関数は、スカラー データを操作します。 SQL には、IF
や WHILE
などの制御フロー ステートメントも含まれています。
Microsoft SQL Server 2005 より前では、ストアド プロシージャと UDF は T-SQL ステートメントのコレクションとしてのみ定義できました。 しかし、SQL Server 2005 は、すべての .NET アセンブリで使用されるランタイムである共通言語ランタイム (CLR) との統合を提供するように設計されていました。 そのため、SQL Server 2005 データベースのストアド プロシージャと UDF は、マネージド コードを使用して作成できます。 つまり、ストアド プロシージャまたは UDF を C# クラスのメソッドとして作成できます。 これにより、これらのストアド プロシージャと UDF では、.NET Framework および独自のカスタム クラスの機能を利用できます。
このチュートリアルでは、マネージド ストアド プロシージャとユーザー定義関数を作成する方法と、それらを Northwind データベースに統合する方法について説明します。 では、始めましょう。
Note
マネージド データベース オブジェクトは、対応する SQL オブジェクトよりもいくつかの利点を提供します。 言語の豊富さと親しみやすさ、既存のコードとロジックを再利用できることがメインの利点です。 ただし、多くの手続き型ロジックを含まないデータ セットを操作する場合、マネージド データベース オブジェクトは効率が低い可能性があります。 マネージド コードと T-SQL を使用する利点の比較の詳細については、「マネージド コードを使用してデータベース オブジェクトを作成する利点」を参照してください。
手順 1: Northwind データベースをApp_Dataから移動する
ここまでのすべてのチュートリアルでは、Web アプリケーションの App_Data
フォルダーににある Microsoft SQL Server 2005 Express Edition データベース ファイルを使用してきました。 データベースを App_Data
に配置すると、すべてのファイルが 1 つのディレクトリ内に配置され、チュートリアルをテストするのに追加の構成手順が不要になるため、これらのチュートリアルの配布と実行が簡略化されます。
ただし、このチュートリアルでは、Northwind データベースを App_Data
の外に移動し、SQL Server 2005 Express Edition データベース インスタンスに明示的に登録します。 このチュートリアルの手順は、App_Data
フォルダー内のデータベースで実行できますが、SQL Server 2005 Express Edition データベース インスタンスにデータベースを明示的に登録することで、いくつかの手順がはるかに簡単になります。
このチュートリアルのダウンロードには、NORTHWND.MDF
と NORTHWND_log.LDF
の 2 つのデータベース ファイルが、DataFiles
という名前のフォルダーに配置されています。 自分独自の実装を使ってチュートリアルを学習している場合は、Visual Studio を閉じて、NORTHWND.MDF
ファイルと NORTHWND_log.LDF
ファイルを Web サイトの App_Data
フォルダーから Web サイトの外部のフォルダーに移動します。 データベース ファイルが別のフォルダーに移動されたら、Northwind データベースを SQL Server 2005 Express Edition データベース インスタンスに登録する必要があります。 これは、SQL Server Management Studio から行うことができます。 コンピューターに SQL Server 2005 の Express 以外のエディションがインストールされている場合は、既に Management Studio がインストールされている可能性があります。 コンピューターに SQL Server 2005 Express Edition しかない場合は、Microsoft SQL Server Management Studio をダウンロードしてインストールします。
SQL Server Management Studio を起動します。 図 1 に示すように、Management Studio はまず、接続先のサーバーを確認します。 サーバー名として「localhost\SQLExpress」と入力し、[認証] ドロップダウン リストで [Windows 認証] を選択し、[接続] をクリックします。
図 1: 適切なデータベース インスタンスに接続する
接続すると、オブジェクト エクスプローラー ウィンドウに、SQL Server 2005 Express Edition データベース インスタンスに関する情報 (データベース、セキュリティ情報、管理オプションなど) が一覧表示されます。
DataFiles
フォルダー (または任意の移動先) にある Northwind データベースを SQL Server 2005 Express Edition データベース インスタンスにアタッチする必要があります。 [Databases] フォルダーを右クリックし、コンテキスト メニューから [アタッチ] オプションを選択します。 すると、[データベースのアタッチ] ダイアログ ボックスが表示されます。 [追加] ボタンをクリックし、適切な NORTHWND.MDF
ファイルにドリルダウンして、[OK] をクリックします。 この時点で、画面は図 2 のようになります。
図 2: 適切なデータベース インスタンスに接続します (クリックするとフルサイズの画像が表示されます)
Note
Management Studio を使用して SQL Server 2005 Express Edition インスタンスに接続する場合、[データベースのアタッチ] ダイアログ ボックスでは、マイ ドキュメントなどのユーザー プロファイル ディレクトリにドリルダウンすることはできません。 そのため、NORTHWND.MDF
ファイルと NORTHWND_log.LDF
ファイルは、必ずユーザー プロファイル以外のディレクトリに配置してください。
[OK] ボタンをクリックしてデータベースをアタッチします。 [データベースのアタッチ] ダイアログ ボックスが閉じ、オブジェクト エクスプローラーにアタッチされたデータベースが一覧表示されます。 Northwind データベースに 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF
のような名前が付いている可能性があります。 データベースを右クリックし、[名前の変更] を選択して、データベースの名前を Northwind に変更します。
図 3: データベースの名前を Northwind に変更する
手順 2: Visual Studio で新しいソリューションと SQL Server プロジェクトを作成する
SQL Server 2005 でマネージド ストアド プロシージャまたは UDF を作成するには、ストアド プロシージャと UDF ロジックをクラス内の C# コードとして記述します。 コードが記述されたら、このクラスをアセンブリ (.dll
ファイル) にコンパイルし、アセンブリを SQL Server データベースに登録してから、アセンブリ内の対応するメソッドを指すストアド プロシージャまたは UDF オブジェクトをデータベースに作成する必要があります。 これらの手順はすべて手動で実行できます。 このコードは、任意のテキスト エディターで作成し、C# コンパイラ (csc.exe
) を使用してコマンド ラインからコンパイルし、コマンドまたは Management Studio を使用して CREATE ASSEMBLY
データベースに登録します。また、同様の方法でストアド プロシージャまたは UDF オブジェクトを追加できます。 さいわい、Visual Studio の Professional および Team Systems バージョンには、これらのタスクを自動化する SQL Server プロジェクト タイプが含まれています。 このチュートリアルでは、SQL Server プロジェクト タイプを使用して、マネージド ストアド プロシージャと UDF を作成する方法について説明します。
Note
Visual Web Developer または Visual Studio の Standard エディションを使用している場合は、代わりに手動によるアプローチを使用する必要があります。 手順 13 では、これらの手順を手動で実行するための詳細な手順を示します。 手順 13 を読む前に手順 2 ~ 12 をお読みください。これらの手順には、使用している Visual Studio のバージョンに関わらず適用する必要がある重要な SQL Server 構成手順が含まれているからです。
まず Visual Studio を開きます。 [ファイル] メニューの [新しいプロジェクト] を選択して、[新しいプロジェクト] ダイアログ ボックスを表示します (図 4 を参照)。 [データベース] プロジェクト タイプにドリルダウンし、右側に一覧表示されている [テンプレート] から、新しい SQL Server プロジェクトを作成することを選択します。 このプロジェクトに ManagedDatabaseConstructs
という名前を付け、Tutorial75
という名前のソリューション内に配置しました。
図 4: 新しい SQL Server プロジェクトを作成します (クリックするとフルサイズの画像が表示されます)
[新しいプロジェクト] ダイアログ ボックスの [OK] ボタンをクリックして、ソリューションと SQL Server プロジェクトを作成します。
SQL Server プロジェクトは、特定のデータベースに関連付けられています。 そのため、新しい SQL Server プロジェクトを作成した後に、すぐにこの情報を指定するように求められます。 図 5 は、手順 1 で SQL Server 2005 Express Edition データベース インスタンスに登録した Northwind データベースを指すように入力された [新しいデータベース参照] ダイアログ ボックスを示しています。
図 5: SQL Server プロジェクトを Northwind データベースに関連付ける
このプロジェクト内で作成するマネージド ストアド プロシージャと UDF をデバッグするには、接続に対する SQL/CLR デバッグのサポートを有効にする必要があります。 (図 5 で行なったように) SQL Server プロジェクトを新しいデータベースに関連付けるたびに、接続で SQL/CLR デバッグを有効にするかどうかを確認するメッセージが Visual Studio により表示されます (図 6 を参照)。 [はい] をクリックします。
図 6: SQL/CLR デバッグを有効にする
この時点で、新しい SQL Server プロジェクトがソリューションに追加されました。 これには、Test Scripts
という名前のフォルダーと Test.sql
という名前のファイルが含まれています。このフォルダーは、プロジェクトで作成されたマネージド データベース オブジェクトのデバッグに使用されます。 デバッグについては手順 12 で見ていきます。
これで、このプロジェクトに新しいマネージド ストアド プロシージャと UDF を追加できるようになりましたが、その前に、ソリューションに既存の Web アプリケーションをまず追加します。 [ファイル] メニューから [追加] オプションを選択し、[既存の Web サイト] を選択します。 適切な Web サイトのフォルダーを参照し、[OK] をクリックします。 図 7 に示すように、これによりソリューションが更新され、Web サイトと ManagedDatabaseConstructs
SQL Server プロジェクトの 2 つのプロジェクトが追加されました。
図 7: ソリューション エクスプローラーに 2 つのプロジェクトが追加された
現在、Web.config
の NORTHWNDConnectionString
値は、App_Data
フォルダー内の NORTHWND.MDF
ファイルを参照しています。 このデータベースを App_Data
から削除して、SQL Server 2005 Express Edition データベース インスタンスに明示的に登録したので、それに対応するように NORTHWNDConnectionString
値を更新する必要があります。 Web サイトで Web.config
ファイルを開き、接続文字列が Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True
となるように NORTHWNDConnectionString
値を変更します。 この変更後、Web.config
の <connectionStrings>
セクションは次のようになります。
<connectionStrings>
<add name="NORTHWNDConnectionString" connectionString=
"Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
Integrated Security=True;Pooling=false"
providerName="System.Data.SqlClient" />
</connectionStrings>
Note
前のチュートリアルで説明したように、ASP.NET Web サイトなどのクライアント アプリケーションから SQL Server オブジェクトをデバッグする場合は、接続プールを無効にする必要があります。 上記の接続文字列では、接続プールが無効になります ( Pooling=false
)。 ASP.NET Web サイトからマネージド ストアド プロシージャと UDF をデバッグする予定がない場合は、接続プールを有効にします。
手順 3: マネージド ストアド プロシージャの作成
Northwind データベースにマネージド ストアド プロシージャを追加するには、まず SQL Server プロジェクトのメソッドとしてストアド プロシージャを作成する必要があります。 ソリューション エクスプローラーで、ManagedDatabaseConstructs
プロジェクト名を右クリックし、新しい項目を追加することを選択します。 すると、[新しい項目の追加] ダイアログ ボックスが表示され、プロジェクトに追加できるマネージド データベース オブジェクトの種類が一覧表示されます。 図 8 に示すように、これにはストアド プロシージャやユーザー定義関数などが含まれます。
まず、廃止されたすべての製品を返すストアド プロシージャを追加します。 新しいストアド プロシージャ ファイルに GetDiscontinuedProducts.cs
という名前を付けます。
図 8: GetDiscontinuedProducts.cs
という名前の新しいストアド プロシージャを追加します (クリックするとフルサイズの画像が表示されます)
これにより、次の内容を含む新しい C# クラス ファイルが作成されます。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetDiscontinuedProducts()
{
// Put your code here
}
};
ストアド プロシージャは、StoredProcedures
という名前の partial
クラス ファイル内で static
メソッドとして実装されることに注意してください。 さらに、GetDiscontinuedProducts
メソッドは、メソッドをストアド プロシージャとしてマークする SqlProcedure attribute
で修飾されます。
次のコードでは、SqlCommand
オブジェクトを作成し、Products
テーブルから Discontinued
フィールドが 1 である製品に対応する列をすべて返す SELECT
クエリにその CommandText
を設定します。 次に、コマンドを実行し、結果をクライアント アプリケーションに送信します。 GetDiscontinuedProducts
メソッドにこのコードを追加します。
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
すべてのマネージド データベース オブジェクトは、呼び出し元のコンテキストを表す SqlContext
オブジェクトにアクセスできます。 SqlContext
は、Pipe
プロパティを介して SqlPipe
オブジェクトへのアクセスを提供します。 この SqlPipe
オブジェクトは、SQL Server データベースと呼び出し元アプリケーションの間で情報を渡すために使用されます。 その名前が示すように、ExecuteAndSend
メソッドは渡された SqlCommand
オブジェクトを実行し、クライアント アプリケーションに結果を返します。
Note
マネージド データベース オブジェクトは、セット ベースのロジックではなく手続き型ロジックを使用するストアド プロシージャと UDF に最適です。 手続き型ロジックでは、行ごとにデータのセットを操作したり、スカラー データを操作したりします。 ただし、先ほど作成した GetDiscontinuedProducts
メソッドでは、手続き型ロジックは扱いません。 そのため、これは T-SQL ストアド プロシージャとして実装するのが理想的です。 マネージド ストアド プロシージャとして実装しているのは、マネージド ストアド プロシージャの作成と配置に必要な手順を示すためです。
手順 4: マネージド ストアド プロシージャを配置する
このコードが完了したので、Northwind データベースに配置する準備ができました。 SQL Server プロジェクトを配置すると、コードはアセンブリにコンパイルされ、そのアセンブリがデータベースに登録され、そして、対応するオブジェクトがデータベースに作成され、アセンブリ内の適切なメソッドにリンクされます。 配置オプションによって実行される正確なタスク セットは、手順 13 で詳しく示されています。 ソリューション エクスプローラーで ManagedDatabaseConstructs
プロジェクト名を右クリックし、[配置] オプションを選択します。 ただし、配置は、「'EXTERNAL' の近くの構文が正しくありません」というエラーで失敗します。 現在のデータベースの互換性レベルを高い値に設定し、この機能を有効にする必要があります。 ストアド プロシージャ sp_dbcmptlevel
のヘルプを参照してください。
このエラー メッセージは、アセンブリを Northwind データベースに登録しようとしたときに発生します。 アセンブリを SQL Server 2005 データベースに登録するには、データベースの互換性レベルを 90 に設定する必要があります。 既定では、新しい SQL Server 2005 データベースの互換性レベルは 90 です。 ただし、Microsoft SQL Server 2000 を使用して作成されたデータベースの既定の互換性レベルは 80 です。 Northwind データベースは最初は Microsoft SQL Server 2000 データベースであったため、互換性レベルは現在 80 に設定されています。そのため、マネージド データベース オブジェクトを登録するには 90 へと引き上げる必要があります。
データベースの互換性レベルを更新するには、Management Studio で [新しいクエリ] ウィンドウを開き、次のように入力します。
exec sp_dbcmptlevel 'Northwind', 90
ツール バーの [実行] アイコンをクリックして、上記のクエリを実行します。
図 9: Northwind データベースの互換性レベルを更新します (クリックしてフルサイズの画像が表示されます)
互換性レベルを更新した後、SQL Server プロジェクトを再配置します。 今回は、エラーなしで配置が完了するはずです。
SQL Server Management Studio に戻り、オブジェクト エクスプローラーで Northwind データベースを右クリックし、[更新] を選択します。 次に、[Programmability] フォルダーにドリルダウンし、[Assemblies] フォルダーを展開します。 図 10 に示すように、Northwind データベースには、ManagedDatabaseConstructs
プロジェクトによって生成されたアセンブリが含まれるようになりました。
図 10: ManagedDatabaseConstructs
アセンブリが Northwind データベースに登録されました
[Stored Procedures] フォルダーも展開します。 GetDiscontinuedProducts
という名前のストアド プロシージャが表示されます。 このストアド プロシージャは、配置プロセスによって作成され、ManagedDatabaseConstructs
アセンブリ内の GetDiscontinuedProducts
メソッドを指します。 GetDiscontinuedProducts
ストアド プロシージャは、実行されると GetDiscontinuedProducts
メソッドを実行します。 これはマネージド ストアド プロシージャであるため、Management Studio では編集できません (そのため、ストアド プロシージャ名の横にはロック アイコンが表示されています)。
図 11: GetDiscontinuedProducts
ストアド プロシージャが [Stored Procedures] フォルダーに一覧表示されている
マネージド ストアド プロシージャを呼び出せるようになる前に克服しなければならないハードルがもう 1 つあります。データベースは、マネージド コードの実行を防ぐために構成されています。 これを確認するには、新しいクエリ ウィンドウを開き、GetDiscontinuedProducts
ストアド プロシージャを実行します。 「.NET Framework でのユーザー コードの実行が無効になっています」というエラー メッセージが表示されます。 "clr enabled" 構成オプションを有効にしてください。
Northwind データベースの構成情報を調べるには、クエリ ウィンドウでコマンド exec sp_configure
を入力して実行します。 これは、clr enabled 設定が現在 0 に設定されていることを示しています。
図 12: clr enabled 設定が現在 0 に設定されています (クリックするとフルサイズの画像が表示されます)
図 12 の各構成設定には、最小値と最大値、構成値と実行値の 4 つの値が一覧表示されています。 clr enabled 設定の構成値を更新するには、次のコマンドを実行します。
exec sp_configure 'clr enabled', 1
exec sp_configure
を再実行すると、上記のステートメントで clr enabled 設定の構成値が 1 に更新されましたが、実行値は引き続き 0 に設定されていることがわかります。 この構成変更を反映させるには、RECONFIGURE
コマンドを実行する必要があります。これにより、実行値が現在の構成値に設定されます。 クエリ ウィンドウに「RECONFIGURE
」と入力し、ツール バーの [実行] アイコンをクリックするだけです。 ここで exec sp_configure
を実行すると、clr enabled 設定の構成と実行値の値が 1 になります。
clr enabled の構成が完了したので、マネージド GetDiscontinuedProducts
ストアド プロシージャを実行する準備ができました。 クエリ ウィンドウで、コマンド exec
GetDiscontinuedProducts
を入力して実行します。 ストアド プロシージャを呼び出すと、GetDiscontinuedProducts
メソッド内の対応するマネージド コードが実行されます。 このコードは、廃止されたすべての製品を返す SELECT
クエリを発し、このデータを呼び出し元のアプリケーション (このインスタンスの SQL Server Management Studio) に返します。 Management Studio はこれらの結果を受け取り、[結果] ウィンドウに表示します。
図 13: GetDiscontinuedProducts
ストアド プロシージャは、すべての廃止された製品を返します (クリックするとフルサイズの画像が表示されます)
手順 5: 入力パラメーターを受け入れるマネージド ストアド プロシージャを作成する
これらのチュートリアル全体を通じて作成したクエリとストアド プロシージャの多くは、"パラメーター" を使用しています。 たとえば、「型指定されたデータセット の TableAdapters 用の新しいストアド プロシージャの作成」チュートリアルでは、@CategoryID
という名前の入力パラメーターを受け取る GetProductsByCategoryID
という名前のストアド プロシージャを作成しました。 そして、このストアド プロシージャは、CategoryID
フィールドが指定された @CategoryID
パラメーターの値と一致するすべての製品を返しました。
入力パラメーターを受け取るマネージド ストアド プロシージャを作成するには、メソッドの定義でこれらのパラメーターを指定するだけです。 これを説明するために、GetProductsWithPriceLessThan
という名前の ManagedDatabaseConstructs
プロジェクトに別のマネージド ストアド プロシージャを追加してみましょう。 このマネージド ストアド プロシージャは、価格を指定する入力パラメーターを受け取り、UnitPrice
フィールドがパラメーターの値より小さいすべての製品を返します。
新しいストアド プロシージャをプロジェクトに追加するには、ManagedDatabaseConstructs
プロジェクト名を右クリックし、新しいストアド プロシージャを追加することを選択します。 そのファイルに GetProductsWithPriceLessThan.cs
という名前を付けます。 手順 3 で説明したように、partial
クラス StoredProcedures
内に配置された GetProductsWithPriceLessThan
という名前のメソッドを含む新しい C# クラス ファイルが作成されます。
GetProductsWithPriceLessThan
メソッドの定義を更新して、price
という名前の SqlMoney
入力パラメーターを受け取るようにし、クエリを実行し結果を返すようにコードを記述します。
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice < @MaxPrice";
myCommand.Parameters.AddWithValue("@MaxPrice", price);
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}
GetProductsWithPriceLessThan
メソッドの定義とコードは、手順 3 で作成した GetDiscontinuedProducts
メソッドの定義とコードによく似ています。 唯一の違いは、GetProductsWithPriceLessThan
メソッドが入力パラメーター (price
) として受け取り、SqlCommand
のクエリにパラメーター (@MaxPrice
) が含まれており、パラメーターが SqlCommand
の Parameters
コレクションに追加され、price
変数の値が割り当てられている点です。
このコードを追加した後に、SQL Server プロジェクトを再配置します。 次に、SQL Server Management Studio に戻り、[Stored Procedures] フォルダーを更新します。 新しいエントリ GetProductsWithPriceLessThan
が表示されます。 クエリ ウィンドウでコマンド exec GetProductsWithPriceLessThan 25
を入力して実行します。このコマンドを実行すると、図 14 に示すように、25 ドル未満のすべての製品が一覧表示されます。
図 14: 25 ドル未満の製品が表示されます (クリックするとフルサイズの画像が表示されます)
手順 6: データ アクセス層からマネージド ストアド プロシージャを呼び出す
この時点で、ManagedDatabaseConstructs
プロジェクトに GetDiscontinuedProducts
および GetProductsWithPriceLessThan
マネージド ストアド プロシージャが追加され、Northwind SQL Server データベースに登録されました。 また、SQL Server Management Studio からこれらのマネージド ストアド プロシージャも呼び出しました (図 13 および 図 14 を参照)。 ただし、ASP.NET アプリケーションでこれらのマネージド ストアド プロシージャを使用するには、アーキテクチャのデータ アクセス層とビジネス ロジック層に追加する必要があります。 この手順では、NorthwindWithSprocs
型指定されたデータセットの ProductsTableAdapter
に 2 つの新しいメソッドを追加します。このメソッドは、最初は「型指定されたデータセットの TableAdapters 用の新しいストアド プロシージャの作成」チュートリアルで作成しました。 手順 7 では、対応するメソッドを BLL に追加します。
Visual Studio で NorthwindWithSprocs
型指定されたデータセットを開き、最初に GetDiscontinuedProducts
という名前の ProductsTableAdapter
に新しいメソッドを追加します。 TableAdapter に新しいメソッドを追加するには、デザイナーで TableAdapter の名前を右クリックし、コンテキスト メニューから [クエリの追加] オプションを選択します。
Note
Northwind データベースを App_Data
フォルダーから SQL Server 2005 Express Edition データベース インスタンスに移動したので、この変更を反映するように Web.config 内の対応する接続文字列を更新する必要があります。 手順 2 では、Web.config
の NORTHWNDConnectionString
値の更新について説明しました。 この更新を忘れた場合は、次のようなエラー メッセージが表示されます。 クエリを追加できませんでした。TableAdapter に新しいメソッドを追加しようとすると、ダイアログ ボックスでオブジェクト Web.config
の接続 NORTHWNDConnectionString
が見つかりません。」 このエラーを解決するには、[OK] をクリックし、Web.config
に移動し、手順 2 で説明したように NORTHWNDConnectionString
値を更新します。 次に、TableAdapter にメソッドを再追加してみてください。 今回はエラーなしで動作するはずです。
新しいメソッドを追加すると、TableAdapter クエリ構成ウィザードが起動します。このウィザードは、過去のチュートリアルで何度も使用してきました。 最初の手順では、TableAdapter がデータベースにアクセスする方法を指定するように求められます。アドホック SQL ステートメントを使用するか、新規または既存のストアド プロシージャを使用します。 GetDiscontinuedProducts
マネージド ストアド プロシージャを既に作成してデータベースに登録してあるので、[既存のストアド プロシージャを使用する] オプションを選択し、[次へ] をクリックします。
図 15: [既存のストアド プロシージャを使用する] オプションを選択します (クリックするとフルサイズの画像が表示されます)
次の画面では、メソッドが呼び出すストアド プロシージャの入力を求められます。 ドロップダウン リストから GetDiscontinuedProducts
マネージド ストアド プロシージャを選択し、[次へ] をクリックします。
図 16: GetDiscontinuedProducts
マネージド ストアド プロシージャを選択します (クリックするとフルサイズの画像が表示されます)
その後、ストアド プロシージャが行を返すが、1 つの値を返すか、または何も返さないかのいずれかを指定するように求められます。 GetDiscontinuedProducts
は廃止された製品行のセットを返すので、最初のオプション (表形式データ) を選択し、[次へ] をクリックします。
図 17: 表形式データ オプションを選択します (クリックするとフルサイズの画像が表示されます)
最後のウィザード画面では、使用するデータ アクセス パターンと、結果のメソッドの名前を指定できます。 両方のチェックボックスをオンのままにし、メソッドに FillByDiscontinued
と GetDiscontinuedProducts
という名前を付けます。 [完了] をクリックして、ウィザードを完了します。
図 18: メソッドの FillByDiscontinued
と GetDiscontinuedProducts
に名前を付けます (クリックするとフルサイズの画像が表示されます)
これらの手順を繰り返して、GetProductsWithPriceLessThan
マネージド ストアド プロシージャ用の ProductsTableAdapter
に FillByPriceLessThan
および GetProductsWithPriceLessThan
という名前のメソッドを作成します。
図 19 は、GetDiscontinuedProducts
および GetProductsWithPriceLessThan
マネージド ストアド プロシージャの ProductsTableAdapter
にメソッドを追加した後の DataSet デザイナーのスクリーンショットを示しています。
図 19: ProductsTableAdapter
には、この手順で追加された新しいメソッドが含まれています (クリックするとフルサイズの画像が表示されます)
手順 7: ビジネス ロジック層に対応するメソッドを追加する
ステップ 4 と 5 で追加されたマネージド ストアド プロシージャを呼び出すためのメソッドを含むようにデータ アクセス層を更新したので、対応するメソッドをビジネス ロジック層に追加する必要があります。 次の 2 つのメソッドを ProductsBLLWithSprocs
クラスに追加します。
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
(System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable
GetProductsWithPriceLessThan(decimal priceLessThan)
{
return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}
どちらのメソッドも、対応する DAL メソッドを呼び出して、ProductsDataTable
インスタンスを返すだけです。 各メソッドの上にある DataObjectMethodAttribute
マークアップにより、ObjectDataSource の [データ ソースの構成] ウィザードの [SELECT] タブのドロップダウン リストにこれらのメソッドが含まれます。
手順 8: プレゼンテーション 層からマネージド ストアド プロシージャを呼び出す
ビジネス ロジック層とデータ アクセス層が拡張され、GetDiscontinuedProducts
と GetProductsWithPriceLessThan
のマネージド ストアド プロシージャの呼び出しのサポートが含まれるようになったため、これらのストアド プロシージャの結果を ASP.NET ページで表示できるようになりました。
AdvancedDAL
フォルダーの ManagedFunctionsAndSprocs.aspx
ページを開き、[ツールボックス] から GridView をデザイナーにドラッグします。 GridView の ID
プロパティを DiscontinuedProducts
に設定し、スマート タグから DiscontinuedProductsDataSource
という名前の新しい ObjectDataSource にバインドします。 ProductsBLLWithSprocs
クラスの GetDiscontinuedProducts
メソッドからデータをプルするように ObjectDataSource を構成します。
図 20: ProductsBLLWithSprocs
クラスを使用するように ObjectDataSource を構成します (クリックするとフルサイズの画像が表示されます)
図 21: [SELECT] タブのドロップダウン リストから GetDiscontinuedProducts
メソッドを選択します (クリックするとフルサイズの画像が表示されます)
このグリッドは製品情報の表示のみに使用されるため、[UPDATE]、[INSERT]、[DELETE] タブのドロップダウン リストを [なし] に設定し、[完了] をクリックします。
ウィザードが完了すると、Visual Studio によって、ProductsDataTable
内の各データ フィールドに対して BoundField または CheckBoxField が自動的に追加されます。 ProductName
と Discontinued
以外のこれらのフィールドをすべて削除します。その時点で、GridView と ObjectDataSource の宣言型マークアップは次のようになります。
<asp:GridView ID="DiscontinuedProducts" runat="server"
AutoGenerateColumns="False" DataKeyNames="ProductID"
DataSourceID="DiscontinuedProductsDataSource">
<Columns>
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:CheckBoxField DataField="Discontinued"
HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>
少し時間を取り、ブラウザーでこのページを表示してみてください。 ページにアクセスすると、ObjectDataSource は ProductsBLLWithSprocs
クラスの GetDiscontinuedProducts
メソッドを呼び出します。 手順 7 で説明したように、このメソッドは DAL の ProductsDataTable
クラスの GetDiscontinuedProducts
メソッドを呼び出し、GetDiscontinuedProducts
ストアド プロシージャを呼び出します。 このストアド プロシージャはマネージド ストアド プロシージャであり、手順 3 で作成したコードを実行して、廃止された製品を返します。
マネージド ストアド プロシージャによって返される結果は、DAL によって ProductsDataTable
にパッケージ化され、BLL に返されます。この結果は、プレゼンテーション層に返され、GridView にバインドされて表示されます。 予想どおり、グリッドには、廃止された製品が一覧表示されました。
図 22: 廃止された製品が一覧表示されます (クリックするとフルサイズの画像が表示されます)
さらなる練習として、TextBox ともう 1 つの GridView をページに追加します。 この GridView では、ProductsBLLWithSprocs
クラスの GetProductsWithPriceLessThan
メソッドを呼び出して、TextBox に入力された量より小さい製品を表示します。
手順 9: T-SQL UDF の作成と呼び出し
ユーザー定義関数 (UDF) は、プログラミング言語の関数のセマンティクスを厳密に模倣するデータベース オブジェクトです。 C# の関数と同様に、UDF には可変数の入力パラメーターを含め、特定の型の値を返すことができます。 UDF は、スカラー データ (文字列、整数など) または表形式データを返すことができます。 スカラー データ型を返す UDF から順に、両方の種類の UDF を簡単に見てみましょう。
次の UDF は、特定の製品の在庫の見積もり値を計算します。 これは、特定の製品の UnitPrice
、UnitsInStock
、および Discontinued
値の 3 つの入力パラメーターを受け取り、money
型の値を返します。 UnitPrice
に UnitsInStock
を乗算して、インベントリの推定値を計算します。 廃止されたアイテムの場合、この値は半分になります。
CREATE FUNCTION udf_ComputeInventoryValue
(
@UnitPrice money,
@UnitsInStock smallint,
@Discontinued bit
)
RETURNS money
AS
BEGIN
DECLARE @Value decimal
SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
IF @Discontinued = 1
SET @Value = @Value * 0.5
RETURN @Value
END
この UDF がデータベースに追加されると、[Programmability] フォルダー、さらに、[Functions]、[Scalar-value Functions] の順に展開することで、Management Studio から見つけることができます。 これは、次のようなクエリで SELECT
使用できます。
SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
(UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
UDF を udf_ComputeInventoryValue
Northwind データベースに追加しました。図 23 は、Management Studio で表示した場合の上記 SELECT
のクエリの出力を示しています。 また、UDF は、オブジェクト エクスプローラーの [Scalar-value Functions] フォルダーの下に一覧表示されることにも注意してください。
図 23: 各製品の在庫値が一覧表示されます (クリックするとフルサイズの画像が表示されます)
UDF は表形式のデータを返すこともできます。 たとえば、特定のカテゴリに属する製品を返す UDF を作成できます。
CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(
@CategoryID int
)
RETURNS TABLE
AS
RETURN
(
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
)
udf_GetProductsByCategoryID
UDF は、@CategoryID
入力パラメーターを受け取り、指定した SELECT
クエリの結果を返します。 作成後、この UDF は、SELECT
クエリの FROM
(または JOIN
) 句で参照できます。 次の例では、各飲料の ProductID
、ProductName
、および CategoryID
値を返します。
SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)
Northwind データベースに udf_GetProductsByCategoryID
UDF を追加しました。図 24 は、Management Studio で表示した場合の上記の SELECT
クエリの出力を示しています。 表形式のデータを返す UDF は、オブジェクト エクスプローラーの [Table-value Functions] フォルダーにあります。
図 24: 各飲料の ProductID
、ProductName
、および CategoryID
が一覧表示されます (クリックするとフルサイズの画像が表示されます)
Note
UDF の作成と使用の詳細については、「ユーザー定義関数の概要」を参照してください。 また、「ユーザー定義関数の利点と欠点」も参照してください。
手順 10: マネージド UDF の作成
上記の例で作成した udf_ComputeInventoryValue
UDF および udf_GetProductsByCategoryID
UDF は、T-SQL データベース オブジェクトです。 SQL Server 2005 では、マネージド UDF もサポートされています。これは、手順 3 と手順 5 のマネージド ストアド プロシージャと同様に、ManagedDatabaseConstructs
プロジェクトに追加できます。 この手順では、マネージド コードに udf_ComputeInventoryValue
UDF を実装します。
マネージド UDF を ManagedDatabaseConstructs
プロジェクトに追加するには、ソリューション エクスプローラーでプロジェクト名を右クリックし、[新しい項目の追加] を選択します。 [新しい項目の追加] ダイアログ ボックスからユーザー定義テンプレートを選択し、新しい UDF ファイルに udf_ComputeInventoryValue_Managed.cs
という名前を付けます。
図 25: ManagedDatabaseConstructs
プロジェクトに新しいマネージド UDF を追加します (クリックするとフルサイズの画像が表示されます)
ユーザー定義関数テンプレートは、UserDefinedFunctions
という名前の partial
クラスを作成します。これには、クラス ファイルの名前と同じ名前 (この場合は udf_ComputeInventoryValue_Managed
) のメソッドが含まれます。 このメソッドは、マネージド UDF としてメソッドにフラグを設定する SqlFunction
属性を使用して修飾されます。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString udf_ComputeInventoryValue_Managed()
{
// Put your code here
return new SqlString("Hello");
}
};
この udf_ComputeInventoryValue
メソッドは現在、SqlString
オブジェクトを返し、入力パラメーターを受け取りません。 メソッド定義を更新して、UnitPrice
、UnitsInStock
、Discontinued
の 3 つの入力パラメーターを受け入れ、SqlMoney
オブジェクトを返すようにする必要があります。 インベントリ値を計算するロジックは、T-SQL udf_ComputeInventoryValue
UDF のロジックと同じです。
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
(SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
SqlMoney inventoryValue = 0;
if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
{
inventoryValue = UnitPrice * UnitsInStock;
if (Discontinued == true)
inventoryValue = inventoryValue * new SqlMoney(0.5);
}
return inventoryValue;
}
UDF メソッドの入力パラメーターは、対応する SQL 型であることに注意してください。UnitPrice
フィールドには SqlMoney
、UnitsInStock
には SqlInt16
、Discontinued
には SqlBoolean
です。 これらのデータ型は、Products
テーブルで定義されている型を反映します。UnitPrice
列は money
型、UnitsInStock
列は smallint
型、Discontinued
列は bit
型です。
このコードは、値 0 が割り当てられている inventoryValue
という名前の SqlMoney
インスタンスを作成することから始めます。 Products
テーブルでは、UnitsInPrice
列と UnitsInStock
列のデータベース NULL
値を使用できます。 したがって、まず、これらの値に NULL
が含まれているかどうかを確認する必要があります。これは、SqlMoney
オブジェクトの IsNull
プロパティで行います。 UnitPrice
と UnitsInStock
の両方に NULL
以外の値が含まれている場合は、inventoryValue
を 2 つの積として計算します。 次に、Discontinued
が true の場合は、値を半分に分割します。
Note
SqlMoney
オブジェクトでは、2 つの SqlMoney
インスタンスを乗算することのみが許可されます。 SqlMoney
インスタンスにリテラル浮動小数点数を乗算することはできません。 したがって、inventoryValue
半分に、値が 0.5 の新しい SqlMoney
インスタンスで乗算します。
手順 11: マネージド UDF の配置
マネージド UDF が作成されたので、Northwind データベースに配置する準備ができました。 手順 4 で説明したように、SQL Server プロジェクトのマネージド オブジェクトは、ソリューション エクスプローラーのプロジェクト名を右クリックし、コンテキスト メニューから [配置] オプションを選択することで配置されます。
プロジェクトを配置したら、SQL Server Management Studio に戻り、[Scalar-value Functions] フォルダーを更新します。 次の 2 つのエントリが表示され流はずです。
dbo.udf_ComputeInventoryValue
- 手順 9 で作成した T-SQL UDF。および、dbo.udf ComputeInventoryValue_Managed
- 先ほど配置した、手順 10 で作成されたマネージド UDF。
このマネージド UDF をテストするには、Management Studio 内から次のクエリを実行します。
SELECT ProductID, ProductName,
dbo.udf_ComputeInventoryValue_Managed(
UnitPrice,
UnitsInStock,
Discontinued
) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
このコマンドは、T-SQL udf_ComputeInventoryValue
UDF の代わりにマネージド udf ComputeInventoryValue_Managed
UDF を使用しますが、出力は同じです。 図 23 を再度参照して、UDF の出力のスクリーンショットを確認してください。
手順 12: マネージド データベース オブジェクトのデバッグ
「ストアド プロシージャのデバッグ」チュートリアルでは、Visual Studio を使用して SQL Server をデバッグするための 3 つのオプションについて説明しました。データベースの直接デバッグ、アプリケーション デバッグ、および SQL Server プロジェクトからのデバッグです。 マネージド データベース オブジェクトは、ダイレクト データベース デバッグを使用してデバッグすることはできませんが、クライアント アプリケーションから、または直接 SQL Server プロジェクトからデバッグできます。 ただし、デバッグを機能させるには、SQL Server 2005 データベースで SQL/CLR デバッグを許可する必要があります。 Visual Studio で最初に ManagedDatabaseConstructs
プロジェクトを作成したときに、SQL/CLR デバッグを有効にするかどうかを尋ねられたことを思い出してください (手順 2 の図 6 を参照)。 この設定を変更するには、[サーバー エクスプローラー] ウィンドウからデータベースを右クリックします。
図 26: データベースで SQL/CLR デバッグが許可されていることを確認する
GetProductsWithPriceLessThan
マネージド ストアド プロシージャをデバッグしたいとします。 まず、GetProductsWithPriceLessThan
メソッドのコード内にブレークポイントを設定します。
図 27: GetProductsWithPriceLessThan
メソッドにブレークポイントを設定します (クリックするとフルサイズの画像が表示されます)
まず、SQL Server プロジェクトからのマネージド データベース オブジェクトのデバッグを見てみましょう。 ソリューションには 2 つのプロジェクト (ManagedDatabaseConstructs
SQL Server プロジェクトと Web サイト) が含まれるため、SQL Server プロジェクトからデバッグするには、デバッグを開始するときに ManagedDatabaseConstructs
SQL Server プロジェクトを起動するように Visual Studio に指示する必要があります。 ソリューション エクスプローラーで ManagedDatabaseConstructs
プロジェクトを右クリックし、コンテキスト メニューから [スタートアップ プロジェクトとして設定] オプションを選択します。
ManagedDatabaseConstructs
プロジェクトがデバッガーから起動されると、Test Scripts
フォルダーにある Test.sql
ファイル内の SQL ステートメントが実行されます。 たとえば、GetProductsWithPriceLessThan
マネージド ストアド プロシージャをテストするには、既存の Test.sql
ファイルの内容を次のステートメントに置き換えます。このステートメントは、 @CategoryID
値 14.95 を渡す GetProductsWithPriceLessThan
マネージド ストアド プロシージャを呼び出します。
exec GetProductsWithPriceLessThan 14.95
上記のスクリプトを Test.sql
に入力したら、[デバッグ] メニューに移動し、[デバッグの開始] を選択するか、ツール バーの F5 キーまたは緑色の再生アイコンを押してデバッグを開始します。 これにより、ソリューション内にプロジェクトがビルドされ、マネージド データベース オブジェクトが Northwind データベースにデプロイされ、Test.sql
スクリプトが実行されます。 この時点でブレークポイントがヒットし、GetProductsWithPriceLessThan
メソッドのステップ 実行、入力パラメーターの値の確認などを行うことができます。
図 28: GetProductsWithPriceLessThan
メソッドのブレークポイントがヒットしました (クリックするとフルサイズの画像が表示されます)
クライアント アプリケーションを使用して SQL データベース オブジェクトをデバッグするには、アプリケーションのデバッグをサポートするようにデータベースを構成する必要があります。 サーバー エクスプローラーでデータベースを右クリックし、[アプリケーション デバッグ] オプションがチェックされていることを確認します。 さらに、SQL デバッガーと統合し、接続プールを無効にするように ASP.NET アプリケーションを構成する必要があります。 これらの手順については、「ストアド プロシージャのデバッグ」チュートリアルの手順 2 で詳しく説明しました。
ASP.NET アプリケーションとデータベースを構成したら、ASP.NET Web サイトをスタートアップ プロジェクトとして設定し、デバッグを開始します。 ブレークポイントを持つマネージド オブジェクトのいずれかを呼び出すページにアクセスすると、アプリケーションが停止し、制御がデバッガーに引き継がれ、図 28 に示すようにコードをステップ実行できます。
手順 13: マネージド データベース オブジェクトの手動コンパイルと配置
SQL Server プロジェクトを使用すると、マネージド データベース オブジェクトを簡単に作成、コンパイル、および配置できます。 残念ながら、SQL Server プロジェクトは、Visual Studio の Professional および Team Systems エディションでのみ使用できます。 Visual Web Developer または Visual Studio の Standard Edition を使用していて、マネージド データベース オブジェクトを使用する場合は、手動で作成して配置する必要があります。 これには、次の 4 つの手順が含まれます。
- マネージド データベース オブジェクトのソース コードを含むファイルを作成します。
- オブジェクトをアセンブリにコンパイルします。
- アセンブリを SQL Server 2005 データベースに登録します。
- アセンブリ内の適切なメソッドを指すデータベース オブジェクトを SQL Server に作成します。
これらのタスクを説明するために、UnitPrice
が指定された値より大きい製品を返す新しいマネージド ストアド プロシージャを作成してみましょう。 コンピューター上に GetProductsWithPriceGreaterThan.cs
という名前の新しいファイルを作成し、次のコードをそのファイルに入力します (これは、Visual Studio、メモ帳、または任意のテキスト エディターを使用して行うことができます)。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceGreaterThan(SqlMoney price)
{
// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice > @MinPrice";
myCommand.Parameters.AddWithValue("@MinPrice", price);
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}
};
このコードは、手順 5 で作成した GetProductsWithPriceLessThan
メソッドとほぼ同じです。 唯一の違いは、メソッド名、WHERE
句、およびクエリで使用されるパラメーター名です。 GetProductsWithPriceLessThan
メソッドでは、WHERE
句は次のようになっていました: WHERE UnitPrice < @MaxPrice
。 ここでは、GetProductsWithPriceGreaterThan
で WHERE UnitPrice > @MinPrice
を使用します。
次に、このクラスをアセンブリにコンパイルする必要があります。 コマンド ラインから、GetProductsWithPriceGreaterThan.cs
ファイルを保存したディレクトリに移動し、C# コンパイラ (csc.exe
) を使用してクラス ファイルをアセンブリにコンパイルします。
csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
csc.exe
を含むフォルダーがシステムの PATH
にない場合は、次のように、そのパス %WINDOWS%\Microsoft.NET\Framework\version\
を完全に参照する必要があります。
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs
図 29: アセンブリに GetProductsWithPriceGreaterThan.cs
をコンパイルします (クリックするとフルサイズの画像が表示されます)
/t
フラグは、C# クラス ファイルを (実行可能ファイルではなく) DLL にコンパイルすることを指定します。 /out
フラグは、結果のアセンブリの名前を指定します。
Note
コマンド ラインから GetProductsWithPriceGreaterThan.cs
クラス ファイルをコンパイルするのではなく、Visual C# Express Edition を使用するか、Visual Studio Standard Edition で別のクラス ライブラリ プロジェクトを作成することもできます。 S ren Jacob Lauritsen は、GetProductsWithPriceGreaterThan
ストアド プロシージャのためのコードと、手順 3、5、および 10 で作成された 2 つのマネージド ストアド プロシージャと UDF をを持つこのような Visual C# Express Edition プロジェクトを提供してくれました。 S ren のプロジェクトには、対応するデータベース オブジェクトを追加するために必要な T-SQL コマンドも含まれています。
コードをアセンブリにコンパイルしたら、SQL Server 2005 データベース内にアセンブリを登録する準備が整いました。 これは、T-SQL、コマンド CREATE ASSEMBLY
、または SQL Server Management Studio を使用して実行できます。 Management Studio の使用に焦点を当ててみましょう。
Management Studio から、Northwind データベースの [Programmability] フォルダーを展開します。 サブフォルダーの 1 つは [Assemblies] です。 新しいアセンブリをデータベースに手動で追加するには、[Assemblies] フォルダーを右クリックし、コンテキスト メニューから [新しいアセンブリ] を選択します。 [新しいアセンブリ] ダイアログ ボックスが表示されます (図 30 を参照)。 [参照] ボタンをクリックし、コンパイルした ManuallyCreatedDBObjects.dll
アセンブリを選択し、[OK] をクリックしてアセンブリをデータベースに追加します。 オブジェクト エクスプローラーに ManuallyCreatedDBObjects.dll
アセンブリは表示されないはずです。
図 30: ManuallyCreatedDBObjects.dll
アセンブリをデータベースに追加します (クリックするとフルサイズの画像が表示されます)
図 31: オブジェクト エクスプローラーに ManuallyCreatedDBObjects.dll
が表示される
Northwind データベースにアセンブリを追加しましたが、ストアド プロシージャをアセンブリ内の GetProductsWithPriceGreaterThan
メソッドにはまだ関連付けていません。 これを行うには、新しいクエリ ウィンドウを開き、次のスクリプトを実行します。
CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan]
(
@price money
)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan]
GO
これで、GetProductsWithPriceGreaterThan
という名前の Northwind データベースに新しいストアド プロシージャが作成され、それがマネージド メソッド GetProductsWithPriceGreaterThan
(アセンブリ ManuallyCreatedDBObjects
内のクラス StoredProcedures
) に関連付けられました。
上記のスクリプトを実行した後、オブジェクト エクスプローラーの [Stored Procedures] フォルダーを更新します。 新しいストアド プロシージャ エントリ (GetProductsWithPriceGreaterThan
) が表示され、その横にロック アイコンが表示されています。 このストアド プロシージャをテストするには、クエリ ウィンドウで次のスクリプトを入力して実行します。
exec GetProductsWithPriceGreaterThan 24.95
図 32 に示すように、上記のコマンドは、24.95 ドルを超える UnitPrice
を持つ製品の情報を表示します。
図 32: オブジェクト エクスプローラーに ManuallyCreatedDBObjects.dll
が表示されます (クリックするとフルサイズの画像が表示されます)
まとめ
Microsoft SQL Server 2005 は、共通言語ランタイム (CLR) と統合されています。これにより、マネージド コードを使用してデータベース オブジェクトを作成できます。 以前は、これらのデータベース オブジェクトは T-SQL を使用してのみ作成できましたが、今は C# のような .NET プログラミング言語を使用してこれらのオブジェクトを作成できるようになりました。 このチュートリアルでは、2 つのマネージド ストアド プロシージャとマネージド ユーザー定義関数を作成しました。
Visual Studio の SQL Server プロジェクト タイプにより、マネージド データベース オブジェクトの作成、コンパイル、および配置が容易になります。 さらに、豊富なデバッグサポートも提供します。 ただし、SQL Server プロジェクト タイプは、Visual Studio の Professional および Team Systems エディションでのみ使用できます。 Visual Web Developer または Visual Studio の Standard Edition を使用する場合は、手順 13 で説明したように、作成、コンパイル、配置の手順を手動で実行する必要があります。
プログラミングに満足!
もっと読む
この記事で説明したトピックの詳細については、次のリソースを参照してください。
- ユーザー定義関数の利点と欠点
- マネージ コードでの SQL Server 2005 オブジェクトの作成
- 方法: CLR SQL Server ストアド プロシージャを作成して実行する
- 方法: CLR SQL Server ユーザー定義関数を作成して実行する
- 方法:
Test.sql
スクリプトを編集して SQL オブジェクトを実行する - ユーザー定義関数の概要
- マネージド コードと SQL Server 2005 (ビデオ)
- Transact-SQL リファレンス
- チュートリアル: マネージド コードでのストアド プロシージャの作成
著者について
7 冊の ASP/ASP.NET 書籍の著者であり、4GuysFromRolla.com の創設者である Scott Mitchell は、1998 年から Microsoft Web テクノロジに取り組んでいます。 Scott は、独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の本は サムズは24時間で2.0 ASP.NET 自分自身を教えています。 にアクセスするか、ブログを使用して にアクセスmitchell@4GuysFromRolla.comできます。これは でhttp://ScottOnWriting.NET見つけることができます。
特別な感謝
このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は S ren Jacob Lauritsen でした。 この記事のレビューだけでなく、S ren は、マネージド データベース オブジェクトを手動でコンパイルするために、この記事のダウンロードに含まれる Visual C# Express Edition プロジェクトも作成しました。 今後の MSDN の記事を確認することに関心がありますか? その場合は、 にmitchell@4GuysFromRolla.com行をドロップしてください。