マネージド コードでストアド プロシージャとユーザー定義関数を作成する (C#)

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

Microsoft SQL Server 2005 は.NET 共通言語ランタイムと統合され、開発者はマネージド コードを使用してデータベース オブジェクトを作成できます。 このチュートリアルでは、Visual Basic または C# コードを使用して、マネージド ストアド プロシージャとマネージド ユーザー定義関数を作成する方法について説明します。 また、これらのエディションの Visual Studio を使用して、このようなマネージド データベース オブジェクトをデバッグする方法についても説明します。

はじめに

Microsoft SQL Server 2005 のようなデータベースでは、データの挿入、変更、取得に Transact-構造化照会言語 (T-SQL) が使用されます。 ほとんどのデータベース システムには、一連の SQL ステートメントをグループ化するためのコンストラクトが含まれています。その後、1 つの再利用可能なユニットとして実行できます。 ストアド プロシージャは 1 つの例です。 もう 1 つは、手順 9 で詳しく調べるコンストラクトである ユーザー定義関数 (UDF) です。

その中核となる SQL は、一連のデータを操作するように設計されています。 、UPDATE、および DELETE ステートメントはSELECT、本質的に対応するテーブル内のすべてのレコードに適用され、句WHEREによってのみ制限されます。 ただし、一度に 1 つのレコードを操作したり、スカラー データを操作したりするために設計された多くの言語機能があります。 CURSOR では 、一連のレコードを一度に 1 つずつループできます。 、CHARINDEX、 などのLEFT文字列操作関数は、PATINDEXスカラー データを操作します。 SQL には、 や WHILEなどのIF制御フロー ステートメントも含まれています。

Microsoft SQL Server 2005 より前では、ストアド プロシージャと UDF は T-SQL ステートメントのコレクションとしてのみ定義できました。 ただし、SQL Server 2005 は、すべての .NET アセンブリで使用されるランタイムである共通言語ランタイム (CLR) との統合を提供するように設計されています。 そのため、SQL Server 2005 データベース内のストアド プロシージャと UDF は、マネージド コードを使用して作成できます。 つまり、ストアド プロシージャまたは UDF を C# クラスのメソッドとして作成できます。 これにより、これらのストアド プロシージャと UDF は、.NET Frameworkおよび独自のカスタム クラスの機能を利用できます。

このチュートリアルでは、マネージド ストアド プロシージャと User-Defined Functions を作成する方法と、それらを Northwind データベースに統合する方法について説明します。 始めましょう。

注意

マネージド データベース オブジェクトには、対応する SQL に比べていくつかの利点があります。 言語の豊富さと知識、既存のコードとロジックを再利用する機能は、メインの利点です。 ただし、多くの手続き型ロジックを含まないデータ セットを操作する場合、マネージド データベース オブジェクトの効率は低下する可能性があります。 マネージド コードと T-SQL を使用する利点について詳しく説明するには、「マネージド コードを使用してデータベース オブジェクトを作成する利点」をチェック。

手順 1: Northwind データベースをApp_Dataから移動する

ここまでのすべてのチュートリアルでは、Web アプリケーションの App_Data フォルダー内の Microsoft SQL Server 2005 Express Edition データベース ファイルを使用してきました。 すべてのファイルが 1 つのディレクトリ内にあり、チュートリアルをテストするための追加の構成手順は必要なかったので App_Data 、これらのチュートリアルの配布と実行を簡略化してデータベースを配置しました。

ただし、このチュートリアルでは、Northwind データベースを からApp_Data移動し、SQL Server 2005 Express Edition データベース インスタンスに明示的に登録します。 フォルダー内App_Dataのデータベースを使用してこのチュートリアルの手順を実行できますが、SQL Server 2005 Express Edition データベース インスタンスにデータベースを明示的に登録することで、いくつかの手順がはるかに簡単になります。

このチュートリアルのダウンロードには、 と という NORTHWND.MDFNORTHWND_log.LDF 2 つのデータベース ファイルが という名前 DataFilesのフォルダーに配置されています。 チュートリアルの独自の実装に従っている場合は、Visual Studio を閉じて、 ファイルと NORTHWND_log.LDF ファイルを NORTHWND.MDF Web サイトのApp_Dataフォルダーから Web サイトの外部のフォルダーに移動します。 データベース ファイルが別のフォルダーに移動されたら、Northwind データベースを SQL Server 2005 Express Edition データベース インスタンスに登録する必要があります。 これは、SQL Server Management Studioから行うことができます。 コンピューターに SQL Server 2005 の Express Edition 以外のエディションがインストールされている場合は、既に Management Studio がインストールされている可能性があります。 お使いのコンピューターにSQL Server 2005 Express Editionがある場合は、Microsoft SQL Server Management Studioをダウンロードしてインストールしてください。

SQL Server Management Studio を起動します。 図 1 に示すように、Management Studio はまず、接続するサーバーを確認します。 サーバー名に「localhost\SQLExpress」と入力し、[認証] ドロップダウン リストで [Windows 認証] を選択し、[接続] をクリックします。

SQL Server Management Studioの [サーバーへの接続] ウィンドウを示すスクリーンショット。

図 1: 適切なデータベース インスタンスに接続する

接続すると、[オブジェクト エクスプローラー] ウィンドウに、データベース、セキュリティ情報、管理オプションなど、SQL Server 2005 Express Edition データベース インスタンスに関する情報が一覧表示されます。

Northwind データベースをフォルダー (または移動した場所) にDataFilesSQL Server 2005 Express Edition データベース インスタンスにアタッチする必要があります。 [データベース] フォルダーを右クリックし、コンテキスト メニューから [アタッチ] オプションを選択します。 これにより、[データベースのアタッチ] ダイアログ ボックスが表示されます。 [追加] ボタンをクリックし、適切な NORTHWND.MDF ファイルにドリルダウンして、[OK] をクリックします。 この時点で、画面は図 2 のようになります。

データベース MDF ファイルにアタッチする方法を示す [データベースのアタッチ] ウィンドウのスクリーンショット。

図 2: 適切なデータベース インスタンスに接続する (クリックするとフルサイズの画像が表示されます)

注意

Management Studio を使用してSQL Server 2005 Express Edition インスタンスに接続する場合、[データベースのアタッチ] ダイアログ ボックスでは、[マイ ドキュメント] などのユーザー プロファイル ディレクトリにドリルダウンできません。 そのため、 ファイルと NORTHWND_log.LDF ファイルはNORTHWND.MDF必ずユーザー以外のプロファイル ディレクトリに配置してください。

[OK] ボタンをクリックしてデータベースをアタッチします。 [データベースのアタッチ] ダイアログ ボックスが閉じ、オブジェクト エクスプローラーに接続されているデータベースが一覧表示されます。 Northwind データベースに のような 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF名前が付いている可能性があります。 データベースを右クリックし、[名前の変更] を選択して、データベースの名前を Northwind に変更します。

データベースの名前を 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 を作成する方法について説明します。

注意

Visual Web Developer または Visual Studio の Standard エディションを使用している場合は、代わりに手動のアプローチを使用する必要があります。 手順 13 では、これらの手順を手動で実行するための詳細な手順を示します。 これらの手順には、使用している Visual Studio のバージョンに関係なく適用する必要がある重要なSQL Server構成手順が含まれているので、手順 13 を読む前に手順 2 から 12 を読むことをお勧めします。

まず、Visual Studio を開きます。 [ファイル] メニューの [新しいプロジェクト] を選択して、[新しいプロジェクト] ダイアログ ボックスを表示します (図 4 を参照)。 [データベース] プロジェクトの種類にドリルダウンし、右側に一覧表示されている [テンプレート] から、新しい SQL Server プロジェクトを作成することを選択します。 私はこのプロジェクト ManagedDatabaseConstructs に名前を付け、 という名前 Tutorial75のソリューション内に配置しました。

新しいSQL Server プロジェクトを作成する

図 4: 新しいSQL Server プロジェクトを作成する (フルサイズの画像を表示する をクリックします)

[新しいプロジェクト] ダイアログ ボックスの [OK] ボタンをクリックして、ソリューションを作成し、プロジェクトをSQL Serverします。

SQL Server プロジェクトは、特定のデータベースに関連付けられています。 その結果、新しいSQL Serverプロジェクトを作成した後、すぐにこの情報を指定するように求められます。 図 5 は、手順 1 でSQL Server 2005 Express Edition データベース インスタンスに登録した Northwind データベースを指すように入力された [新しいデータベース参照] ダイアログ ボックスを示しています。

SQL Server プロジェクトを Northwind データベースに関連付ける

図 5: SQL Server プロジェクトを Northwind データベースに関連付ける

このプロジェクト内で作成するマネージド ストアド プロシージャと UDF をデバッグするには、接続に対する SQL/CLR デバッグのサポートを有効にする必要があります。 SQL Server プロジェクトを新しいデータベースに関連付けるたびに (図 5 のように)、接続で SQL/CLR デバッグを有効にするかどうかが Visual Studio から確認されます (図 6 を参照)。 [はい] をクリックします。

SQL/CLR デバッグを有効にする

図 6: SQL/CLR デバッグを有効にする

この時点で、新しいSQL Server プロジェクトがソリューションに追加されました。 これには、 という名前 Test Scripts のファイル Test.sqlを含む という名前のフォルダーが含まれています。これは、プロジェクトで作成されたマネージド データベース オブジェクトのデバッグに使用されます。 デバッグについては、手順 12 で見ていきます。

このプロジェクトに新しいマネージド ストアド プロシージャと UDF を追加できるようになりましたが、その前に、ソリューションに既存の Web アプリケーションを含めてみましょう。 [ファイル] メニューの [追加] オプションを選択し、[既存の Web サイト] を選択します。 適切な Web サイト フォルダーを参照し、[OK] をクリックします。 図 7 に示すように、これにより、Web サイトと SQL Server プロジェクトの 2 つのプロジェクトが含まれるようにソリューションがManagedDatabaseConstructs更新されます。

ソリューション エクスプローラーに 2 つのプロジェクトが含まれるようになりました

図 7: ソリューション エクスプローラーには 2 つのプロジェクトが含まれるようになりました

の値はNORTHWNDConnectionString現在、 フォルダー内のNORTHWND.MDFファイルをApp_Data参照Web.configしています。 このデータベースを からApp_Data削除し、SQL Server 2005 Express Edition データベース インスタンスに明示的に登録したので、それに対応して値を更新するNORTHWNDConnectionString必要があります。 Web サイトでファイルをWeb.config開き、接続文字列が を読み取るように値を変更NORTHWNDConnectionStringしますData Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True。 この変更後、 <connectionStrings> の Web.config セクションは次のようになります。

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

注意

前のチュートリアルで説明したように、ASP.NET Web サイトなどのクライアント アプリケーションからSQL Server オブジェクトをデバッグする場合は、接続プールを無効にする必要があります。 上記の接続文字列では、接続プール ( Pooling=false ) が無効になります。 ASP.NET Web サイトからマネージド ストアド プロシージャと UDF のデバッグを計画していない場合は、接続プールを有効にします。

手順 3: マネージド ストアド プロシージャを作成する

Northwind データベースにマネージド ストアド プロシージャを追加するには、最初にストアド プロシージャを SQL Server Project のメソッドとして作成する必要があります。 ソリューション エクスプローラーからプロジェクト名をManagedDatabaseConstructs右クリックし、新しい項目を追加することを選択します。 [新しい項目の追加] ダイアログ ボックスが表示されます。このダイアログ ボックスには、プロジェクトに追加できるマネージド データベース オブジェクトの種類が一覧表示されます。 図 8 に示すように、これにはストアド プロシージャと User-Defined 関数などが含まれます。

まず、廃止されたすべての製品を返すストアド プロシージャを追加します。 新しいストアド プロシージャ ファイルに という名前を付けます GetDiscontinuedProducts.cs。

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のクラス ファイル内のstaticpartialメソッドとして実装されることに注意してください。 さらに、 GetDiscontinuedProducts メソッドは、 で SqlProcedure attribute修飾され、メソッドがストアド プロシージャとしてマークされます。

次のコードでは、 オブジェクトをSqlCommand作成し、フィールドSELECTCommandTextが 1 の製品Discontinuedのテーブルからすべての列をProducts返すクエリに 設定します。 次に、コマンドを実行し、結果をクライアント アプリケーションに送信します。 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オブジェクトを実行し、結果をクライアント アプリケーションに返します。

注意

マネージド データベース オブジェクトは、セット ベースのロジックではなく手続き型ロジックを使用するストアド プロシージャと UDF に最適です。 手続き型ロジックには、行ごとにデータのセットを操作するか、スカラー データを操作する必要があります。 ただし、先ほど作成したメソッドには GetDiscontinuedProducts 、手続き型ロジックは必要ありません。 そのため、T-SQL ストアド プロシージャとして実装するのが理想的です。 マネージド ストアド プロシージャの作成と展開に必要な手順を示すために、マネージド ストアド プロシージャとして実装されます。

手順 4: マネージド ストアド プロシージャをデプロイする

このコードが完了したら、Northwind データベースにデプロイする準備ができました。 SQL Server Project を配置すると、コードがアセンブリにコンパイルされ、アセンブリがデータベースに登録され、対応するオブジェクトがデータベースに作成され、アセンブリ内の適切なメソッドにリンクされます。 [デプロイ] オプションによって実行されるタスクの正確なセットは、手順 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

ツール バーの [実行] アイコンをクリックして、上記のクエリを実行します。

Northwind データベースの互換性レベルを更新する

図 9: Northwind データベースの互換性レベルを更新する (フルサイズの画像を表示する をクリックします)

互換性レベルを更新した後、SQL Server Project を再デプロイします。 今回は、エラーなしでデプロイを完了する必要があります。

SQL Server Management Studioに戻り、オブジェクト エクスプローラーで Northwind データベースを右クリックし、[更新] を選択します。 次に、[プログラミング] フォルダーにドリルダウンし、[アセンブリ] フォルダーを展開します。 図 10 に示すように、Northwind データベースにプロジェクトによって生成されたアセンブリが ManagedDatabaseConstructs 含まれるようになりました。

ManagedDatabaseConstructs アセンブリが Northwind データベースに登録されました

図 10: アセンブリが ManagedDatabaseConstructs Northwind データベースに登録されました

[ストアド プロシージャ] フォルダーも展開します。 そこに という名前 GetDiscontinuedProductsのストアド プロシージャが表示されます。 このストアド プロシージャは、配置プロセスによって作成され、アセンブリ内の GetDiscontinuedProducts メソッドを ManagedDatabaseConstructs 指します。 ストアド プロシージャが GetDiscontinuedProducts 実行されると、 メソッドが GetDiscontinuedProducts 実行されます。 これはマネージド ストアド プロシージャであるため、Management Studio では編集できません (したがって、ストアド プロシージャ名の横にあるロック アイコン)。

GetDiscontinuedProducts ストアド プロシージャがストアド プロシージャ フォルダーに一覧表示されます

図 11: ストアド プロシージャが GetDiscontinuedProducts ストアド プロシージャ フォルダーに一覧表示されている

マネージド ストアド プロシージャを呼び出す前に克服しなければならないハードルがもう 1 つあります。データベースはマネージド コードの実行を防ぐよう構成されています。 これを確認するには、新しいクエリ ウィンドウを開き、ストアド プロシージャを GetDiscontinuedProducts 実行します。 次のエラー メッセージが表示されます。.NET Frameworkでのユーザー コードの実行が無効になっています。 'clr enabled 構成オプションを有効にします。

Northwind データベースの構成情報を調べるには、クエリ ウィンドウで コマンド exec sp_configure を入力して実行します。 これは、clr が有効な設定が現在 0 に設定されていることを示しています。

clr enabled 設定は現在 0 に設定されています

図 12: clr が有効な設定は現在 0 に設定されています (フルサイズの画像を表示する をクリックします)

図 12 の各構成設定には、最小値と最大値、構成値と実行値の 4 つの値が一覧表示されていることに注意してください。 clr enabled 設定の構成値を更新するには、次のコマンドを実行します。

exec sp_configure 'clr enabled', 1

を再実行 exec sp_configure すると、上記のステートメントで clr enabled 設定の構成値が 1 に更新されましたが、実行値は 0 に設定されていることがわかります。 この構成変更が影響を受けるためには、 コマンドをRECONFIGURE実行する必要があります。これにより、実行値が現在の構成値に設定されます。 クエリ ウィンドウに「」と入力 RECONFIGURE し、ツール バーの [実行] アイコンをクリックするだけです。 ここでを実行 exec sp_configure すると、clr 対応の設定の構成と実行の値に 1 の値が表示されます。

clr 対応の構成が完了したら、マネージド GetDiscontinuedProducts ストアド プロシージャを実行する準備ができました。 クエリ ウィンドウで、コマンド を入力して実行します execGetDiscontinuedProducts。 ストアド プロシージャを呼び出すと、 メソッド内の対応するマネージド コードが GetDiscontinuedProducts 実行されます。 このコードは、SELECT廃止されたすべての製品を返すクエリを発行し、このデータを呼び出し元のアプリケーションに返します。このアプリケーションは、このインスタンスでSQL Server Management Studio。 Management Studio は、これらの結果を受け取り、[結果] ウィンドウに表示します。

GetDiscontinuedProducts ストアド プロシージャは、廃止されたすべての製品を返します

図 13: ストアド プロシージャは GetDiscontinuedProducts 、廃止されたすべての製品を返します (フルサイズの画像を表示する をクリックします)

手順 5: 入力パラメーターを受け入れるマネージド ストアド プロシージャを作成する

これらのチュートリアル全体で作成したクエリとストアド プロシージャの多くは 、パラメーターを使用しています。 たとえば、Typed DataSet s TableAdapters の新しいストアド プロシージャの作成に関するチュートリアルでは、 という@CategoryID名前の入力パラメーターを受け入れる という名前GetProductsByCategoryIDのストアド プロシージャを作成しました。 その後、ストアド プロシージャは、フィールドが CategoryID 指定されたパラメーターの値と一致するすべての製品を返しました @CategoryID 。

入力パラメーターを受け取るマネージド ストアド プロシージャを作成するには、メソッドの定義でこれらのパラメーターを指定するだけです。 これを説明するために、 という名前GetProductsWithPriceLessThanのプロジェクトに別のマネージド ストアド プロシージャをManagedDatabaseConstructs追加してみましょう。 このマネージド ストアド プロシージャは、価格を指定する入力パラメーターを受け取り、フィールドがパラメーターの値より小さいすべての製品 UnitPrice を返します。

新しいストアド プロシージャをプロジェクトに追加するには、プロジェクト名を ManagedDatabaseConstructs 右クリックし、新しいストアド プロシージャを追加することを選択します。 そのファイルに GetProductsWithPriceLessThan.cs という名前を付けます。 手順 3 で説明したように、 という名前GetProductsWithPriceLessThanのメソッドが クラス 内に配置された新しい C# クラス StoredProceduresファイルがpartial作成されます。

メソッドの定義を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 として受け取り、s クエリにパラメーター (@MaxPrice) が含まれており、パラメーターが のParametersコレクションにSqlCommand追加され、変数のprice値が割り当てられる点です。

このコードを追加した後、SQL Server Project を再デプロイします。 次に、SQL Server Management Studioに戻り、ストアド プロシージャ フォルダーを更新します。 新しいエントリ GetProductsWithPriceLessThanが表示されます。 図 14 に示すように、クエリ ウィンドウで コマンド exec GetProductsWithPriceLessThan 25を入力して実行します。これにより、25 ドル未満のすべての製品が一覧表示されます。

$25 以下の製品が表示されます

図 14: $25 以下の製品が表示されます (クリックするとフルサイズの画像が表示されます)

手順 6: データ アクセス層からマネージド ストアド プロシージャを呼び出す

この時点で、 および GetProductsWithPriceLessThan マネージド ストアド プロシージャをプロジェクトにManagedDatabaseConstructs追加GetDiscontinuedProductsし、Northwind SQL Server データベースに登録しました。 また、SQL Server Management Studioからこれらのマネージド ストアド プロシージャを呼び出しました (図 13 と 14 を参照)。 ただし、ASP.NET アプリケーションでこれらのマネージド ストアド プロシージャを使用するには、アーキテクチャの Data Access と Business Logic Layer に追加する必要があります。 この手順では、型指定された DataSet の に 2 つの新しいメソッドをProductsTableAdapter追加します。これは、最初は、Typed DataSet の TableAdapters の新しいストアド プロシージャの作成に関するチュートリアルで作成されました。NorthwindWithSprocs 手順 7 では、対応するメソッドを BLL に追加します。

NorthwindWithSprocs Visual Studio で型指定された DataSet を開き、まず、 という名前GetDiscontinuedProductsの に新しいメソッドをProductsTableAdapter追加します。 TableAdapter に新しいメソッドを追加するには、Designerで TableAdapter の名前を右クリックし、コンテキスト メニューから [クエリの追加] オプションを選択します。

注意

Northwind データベースをフォルダーから App_Data SQL Server 2005 Express Edition データベース インスタンスに移動したので、この変更を反映するために、 内の対応する接続文字列 Web.config 更新する必要があります。 手順 2 では、 のWeb.config値のNORTHWNDConnectionString更新について説明しました。 この更新を忘れた場合は、クエリを追加できませんでしたというエラー メッセージが表示されます。 TableAdapter に新しいメソッドを追加しようとすると、ダイアログ ボックスでオブジェクトWeb.configの接続NORTHWNDConnectionStringが見つかりません。 このエラーを解決するには、[OK] をクリックし、手順 2 で説明されているように に Web.config 移動して値を更新 NORTHWNDConnectionString します。 次に、 メソッドを TableAdapter に再追加してみてください。 今回はエラーなしで動作するはずです。

新しいメソッドを追加すると、TableAdapter クエリ構成ウィザードが起動します。このウィザードは、過去のチュートリアルで何度も使用しました。 最初の手順では、アドホック SQL ステートメントまたは新規または既存のストアド プロシージャを使用して、TableAdapter がデータベースにアクセスする方法を指定するように求められます。 既にマネージド ストアド プロシージャを GetDiscontinuedProducts 作成してデータベースに登録しているため、[既存のストアド プロシージャを使用する] オプションを選択し、[次へ] をクリックします。

[既存のストアド プロシージャを使用する] オプションを選択する

図 15: [既存のストアド プロシージャを使用する] オプションを選択します (クリックするとフルサイズの画像が表示されます)

次の画面では、メソッドが呼び出すストアド プロシージャの入力を求められます。 GetDiscontinuedProductsドロップダウン リストからマネージド ストアド プロシージャを選択し、[次へ] をクリックします。

GetDiscontinuedProducts マネージド ストアド プロシージャを選択する

図 16: マネージド ストアド プロシージャを GetDiscontinuedProducts 選択します (クリックするとフルサイズのイメージが表示されます)

次に、ストアド プロシージャが行、1 つの値、または何も返すかどうかを指定するように求められます。 廃止された製品行のセットを返すの GetDiscontinuedProducts で、最初のオプション (表形式データ) を選択し、[次へ] をクリックします。

表形式データ オプションを選択する

図 17: 表形式データ オプションを選択します (クリックするとフルサイズの画像が表示されます)

最後のウィザード画面では、使用するデータ アクセス パターンと、結果のメソッドの名前を指定できます。 両方のチェックボックスをオンのままにし、メソッドに と という名前を FillByDiscontinued 付 GetDiscontinuedProductsけます。 [完了] をクリックしてウィザードを終了します。

メソッドに FillByDiscontinued および GetDiscontinuedProducts という名前を付けます

図 18: メソッドに名前を付け FillByDiscontinued 、 GetDiscontinuedProducts (クリックするとフルサイズの画像が表示されます)

これらの手順を繰り返して、マネージド ストアド プロシージャの と という名前FillByPriceLessThanGetProductsWithPriceLessThanのProductsTableAdapterメソッドを GetProductsWithPriceLessThan 作成します。

図 19 は、 および GetProductsWithPriceLessThan マネージド ストアド プロシージャの にメソッドProductsTableAdapterを追加した後の DataSet DesignerのGetDiscontinuedProductsスクリーンショットを示しています。

ProductsTableAdapter には、この手順で追加された新しいメソッドが含まれています

図 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 s データ ソースの構成ウィザードの [SELECT] タブのドロップダウン リストにこれらのメソッドが含まれます。

手順 8: プレゼンテーション レイヤーからマネージド ストアド プロシージャを呼び出す

ビジネス ロジックとデータ アクセスレイヤーが拡張され、ストアド プロシージャとマネージド ストアド プロシージャの呼び出しGetDiscontinuedProductsGetProductsWithPriceLessThanのサポートが含まれるようになったため、これらのストアド プロシージャの結果を ASP.NET ページで表示できるようになりました。

フォルダー内のManagedFunctionsAndSprocs.aspxページをAdvancedDAL開き、[ツールボックス] から GridView をDesignerにドラッグします。 GridView の ID プロパティを に DiscontinuedProducts 設定し、そのスマート タグから という名前 DiscontinuedProductsDataSourceの新しい ObjectDataSource にバインドします。 ObjectDataSource を構成して、クラスの GetDiscontinuedProducts メソッドからデータをProductsBLLWithSprocsプルします。

ProductsBLLWithSprocs クラスを使用するように ObjectDataSource を構成する

図 20: クラスを使用するように ObjectDataSource を構成する ProductsBLLWithSprocs (クリックするとフルサイズの画像が表示されます)

SELECT タブの Drop-Down リストから GetDiscontinuedProducts メソッドを選択します

図 21: SELECT タブの Drop-Down リストからメソッドを選択 GetDiscontinuedProducts する (フルサイズの画像を表示する をクリックします)

このグリッドは製品情報の表示のみに使用されるため、UPDATE、INSERT、DELETE タブのドロップダウン リストを (なし) に設定し、[完了] をクリックします。

ウィザードが完了すると、Visual Studio によって、 の各データ フィールドに対して BoundField または CheckBoxField が自動的に ProductsDataTable追加されます。 と Discontinuedを除くProductNameこれらのフィールドをすべて削除します。この時点で、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 は クラスの メソッドGetDiscontinuedProductsをProductsBLLWithSprocs呼び出します。 手順 7 で説明したように、このメソッドは DAL のクラス s ProductsDataTableGetDiscontinuedProducts メソッドを呼び出し、ストアド プロシージャを GetDiscontinuedProducts 呼び出します。 このストアド プロシージャはマネージド ストアド プロシージャであり、手順 3 で作成したコードを実行し、廃止された製品を返します。

マネージド ストアド プロシージャによって返される結果は、DAL によって にパッケージ化 ProductsDataTable され、BLL に返されます。この結果は、GridView にバインドされて表示されるプレゼンテーション レイヤーに返されます。 予想どおり、グリッドには、廃止された製品が一覧表示されます。

廃止された製品の一覧

図 22: 廃止された製品が一覧表示されます (クリックするとフルサイズの画像が表示されます)

さらに練習するには、TextBox と別の GridView をページに追加します。 この GridView に、クラスの GetProductsWithPriceLessThan メソッドを呼び出して TextBox に入力した量より小さい製品をProductsBLLWithSprocs表示させます。

手順 9: T-SQL UDF の作成と呼び出し

User-Defined Functions (UDF) は、プログラミング言語の関数のセマンティクスを厳密に模倣するデータベース オブジェクトです。 C# の関数と同様に、UDF には可変数の入力パラメーターを含め、特定の型の値を返すことができます。 UDF は、スカラー データ (文字列、整数など) または表形式データを返すことができます。 スカラー データ型を返す UDF から始めて、両方の種類の UDF を簡単に見てみましょう。

次の UDF は、特定の製品の在庫の見積もり値を計算します。 これは、3 つの入力パラメーター (特定の製品の UnitPrice、 UnitsInStock、および Discontinued の値) を受け取ることによって行われ、 型 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 がデータベースに追加されると、 [プログラミング] フォルダー、次に [関数] 、 [スカラー値関数] の順に展開することで、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 は、オブジェクト エクスプローラーの [スカラー値関数] フォルダーの下に表示されることにも注意してください。

各製品の在庫値が一覧表示されます

図 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 は udf_GetProductsByCategoryID 入力パラメーターを @CategoryID 受け取り、指定された SELECT クエリの結果を返します。 作成した UDF は、クエリの FROM (または JOIN) 句 SELECT で参照できます。 次の例では、各飲料の ProductID、 ProductName、および CategoryID の値を返します。

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

UDF を udf_GetProductsByCategoryID Northwind データベースに追加しました。図 24 は、Management Studio で表示した場合の上記 SELECT のクエリの出力を示しています。 表形式のデータを返す UDF は、オブジェクト エクスプローラーの Table-value Functions フォルダーにあります。

各飲料の ProductID、ProductName、CategoryID が一覧表示されます

図 24: ProductID各飲料の 、 ProductName、および CategoryID が一覧表示されている (フルサイズの画像を表示する をクリックします)

注意

UDF の作成と使用の詳細については、「User-Defined 関数の概要」をチェック。 また、User-Defined 関数の利点と欠点もチェックします。

手順 10: マネージド UDF の作成

上記の udf_ComputeInventoryValue 例で作成した UDF と udf_GetProductsByCategoryID UDF は T-SQL データベース オブジェクトです。 SQL Server 2005 ではマネージド UDF もサポートされています。これは、手順 3 と 5 のマネージド ストアド プロシージャと同様にプロジェクトに追加ManagedDatabaseConstructsできます。 この手順では、マネージド コードに UDF を udf_ComputeInventoryValue 実装してみましょう。

マネージド UDF をプロジェクトにManagedDatabaseConstructs追加するには、ソリューション エクスプローラーのプロジェクト名を右クリックし、[新しい項目の追加] を選択します。 [新しい項目の追加] ダイアログ ボックスから [User-Defined テンプレート] を選択し、新しい UDF ファイルに という名前を付けます udf_ComputeInventoryValue_Managed.cs。

ManagedDatabaseConstructs プロジェクトに新しいマネージド UDF を追加する

図 25: 新しいマネージド UDF をプロジェクトに ManagedDatabaseConstructs 追加する (クリックするとフルサイズの画像が表示されます)

User-Defined 関数テンプレートは、クラス ファイルの名前 (udf_ComputeInventoryValue_Managedこのインスタンスでは) と同じ名前の メソッドを持つ という名前UserDefinedFunctionsのクラスを作成partialします。 このメソッドは、 属性をSqlFunction使用して装飾されます。この属性は、メソッドにマネージド UDF としてフラグを設定します。

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返し、入力パラメーターを受け入れていません。 メソッド定義を更新して、3 つの入力パラメーター UnitPrice(、 UnitsInStock、および Discontinued ) を受け入れ、オブジェクトを 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 型SqlMoneyであることに注意してください。フィールドの場合UnitPriceは 、、 SqlBooleanSqlInt16UnitsInStockのDiscontinued場合は です。 これらのデータ型は、テーブルUnitPriceで定義されている型をProducts反映しています。列は 型、UnitsInStock型moneyのsmallint列、および 型bitのDiscontinued列です。

このコードは、値 0 が割り当てられた という名前inventoryValueのインスタンスを作成SqlMoneyすることから始まります。 テーブルではProducts、 列と UnitsInStock 列のデータベースNULL値をUnitsInPrice使用できます。 したがって、最初にチェックして、これらの値に が含まれているNULLかどうかを確認する必要があります。これは、オブジェクトの IsNull プロパティをSqlMoney介して行います。 と のUnitsInStock両方UnitPriceに非NULL値が含まれている場合は、 を 2 つの積として計算inventoryValueします。 次に、 が true の場合 Discontinued は、値を半分にします。

注意

オブジェクトは SqlMoney 、2 つの SqlMoney インスタンスを乗算することのみを許可します。 インスタンスに SqlMoney リテラル浮動小数点数を乗算することはできません。 したがって、半分 inventoryValue に値 0.5 を持つ新しい SqlMoney インスタンスを乗算します。

手順 11: マネージド UDF のデプロイ

マネージド UDF が作成されたので、Northwind データベースにデプロイする準備ができました。 手順 4 で説明したように、SQL Server プロジェクトのマネージド オブジェクトは、ソリューション エクスプローラーのプロジェクト名を右クリックし、コンテキスト メニューから [配置] オプションを選択して配置されます。

プロジェクトを配置したら、SQL Server Management Studioに戻り、スカラー値 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 つのオプションについて説明しました。Direct Database Debugging、Application Debugging、Debugging from a SQL Server Project。 マネージド データベース オブジェクトは、ダイレクト データベース デバッグを使用してデバッグすることはできませんが、クライアント アプリケーションから直接、SQL Server Project からデバッグできます。 ただし、デバッグを機能させるには、SQL Server 2005 データベースで SQL/CLR デバッグを許可する必要があります。 Visual Studio プロジェクトを ManagedDatabaseConstructs 初めて作成したときに、SQL/CLR デバッグを有効にするかどうかを確認したことを思い出してください (手順 2 の図 6 を参照)。 この設定は、[サーバー エクスプローラー] ウィンドウからデータベースを右クリックすることで変更できます。

データベースで SQL/CLR デバッグが許可されていることを確認する

図 26: データベースで SQL/CLR デバッグが許可されていることを確認する

マネージド ストアド プロシージャをデバッグ GetProductsWithPriceLessThan したいとします。 まず、 メソッドのコード内にブレークポイントを GetProductsWithPriceLessThan 設定します。

GetProductsWithPriceLessThan メソッドでブレークポイントを設定する

図 27: メソッドでブレークポイントを設定する GetProductsWithPriceLessThan (クリックするとフルサイズの画像が表示されます)

まず、SQL Server Project からマネージド データベース オブジェクトをデバッグする方法を見てみましょう。 ソリューションには、SQL Server プロジェクトと Web サイトの 2 つのプロジェクトManagedDatabaseConstructsが含まれているので、SQL Server プロジェクトからデバッグするには、デバッグを開始するときにSQL Server プロジェクトを起動ManagedDatabaseConstructsするように Visual Studio に指示する必要があります。 ソリューション エクスプローラーでプロジェクトをManagedDatabaseConstructs右クリックし、コンテキスト メニューから [スタートアップ プロジェクトとして設定] オプションを選択します。

プロジェクトが ManagedDatabaseConstructs デバッガーから起動されると、 フォルダーにあるファイル内の Test.sql SQL ステートメントが Test Scripts 実行されます。 たとえば、マネージド ストアド プロシージャをGetProductsWithPriceLessThanテストするには、既存Test.sqlのファイルコンテンツを次のステートメントに置き換えます。このステートメントでは、14.95 の値を渡すマネージド ストアド プロシージャが@CategoryID呼び出GetProductsWithPriceLessThanされます。

exec GetProductsWithPriceLessThan 14.95

に上記のスクリプト Test.sqlを入力したら、[デバッグ] メニューに移動して [デバッグの開始] を選択するか、ツールバーの F5 または緑色の再生アイコンを押してデバッグを開始します。 これにより、ソリューション内にプロジェクトがビルドされ、マネージド データベース オブジェクトが Northwind データベースにデプロイされ、スクリプトが実行されます Test.sql 。 この時点でブレークポイントがヒットし、 メソッドを GetProductsWithPriceLessThan ステップ実行したり、入力パラメーターの値を調べたりすることができます。

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 つの手順が含まれます。

  1. マネージド データベース オブジェクトのソース コードを含むファイルを作成します。
  2. オブジェクトをアセンブリにコンパイルします。
  3. アセンブリを SQL Server 2005 データベースに登録し、
  4. アセンブリ内の適切なメソッドを指すデータベース オブジェクトを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

GetProductsWithPriceGreaterThan.csをアセンブリにコンパイルする

図 29: アセンブリにコンパイル GetProductsWithPriceGreaterThan.cs する (クリックするとフルサイズの画像が表示されます)

フラグは /t 、C# クラス ファイルを (実行可能ファイルではなく) DLL にコンパイルすることを指定します。 フラグは /out 、結果のアセンブリの名前を指定します。

注意

コマンド ラインからクラス ファイルを GetProductsWithPriceGreaterThan.cs コンパイルするのではなく、 Visual C# Express Edition を使用するか、Visual Studio Standard Edition で別のクラス ライブラリ プロジェクトを作成することもできます。 S ren Jacob Lauritsen は、このような Visual C# Express Edition プロジェクトにストアド プロシージャの GetProductsWithPriceGreaterThan コードと、手順 3、5、10 で作成された 2 つのマネージド ストアド プロシージャと UDF を提供しました。 S ren s プロジェクトには、対応するデータベース オブジェクトを追加するために必要な T-SQL コマンドも含まれています。

コードをアセンブリにコンパイルすると、SQL Server 2005 データベース内にアセンブリを登録する準備が整いました。 これは、T-SQL、コマンドCREATE ASSEMBLY、またはSQL Server Management Studioを使用して実行できます。 Management Studio の使用に焦点を当ててみましょう。

Management Studio から、Northwind データベースの [プログラミング] フォルダーを展開します。 サブフォルダーの 1 つがアセンブリです。 新しいアセンブリをデータベースに手動で追加するには、[アセンブリ] フォルダーを右クリックし、コンテキスト メニューから [新しいアセンブリ] を選択します。 [新しいアセンブリ] ダイアログ ボックスが表示されます (図 30 を参照)。 [参照] ボタンをクリックし、コンパイルしたアセンブリを ManuallyCreatedDBObjects.dll 選択し、[OK] をクリックしてアセンブリをデータベースに追加します。 オブジェクト エクスプローラーにManuallyCreatedDBObjects.dllアセンブリが表示されません。

ManuallyCreatedDBObjects.dll アセンブリをデータベースに追加する

図 30: アセンブリを ManuallyCreatedDBObjects.dll データベースに追加する (クリックするとフルサイズの画像が表示されます)

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内にあります) に関連付けられます。

上記のスクリプトを実行した後、オブジェクト エクスプローラーのストアド プロシージャ フォルダーを更新します。 新しいストアド プロシージャ エントリが表示されます。このエントリ GetProductsWithPriceGreaterThan の横にロック アイコンが表示されます。 このストアド プロシージャをテストするには、クエリ ウィンドウで次のスクリプトを入力して実行します。

exec GetProductsWithPriceGreaterThan 24.95

図 32 に示すように、上記のコマンドでは、$24.95 を超える製品 UnitPrice の情報が表示されます。

実行された GetProductsWithPriceGreaterThan ストアド プロシージャを示す Microsoft SQL Server Management Studio ウィンドウのスクリーンショット。UnitPrice が $24.95 を超える製品が表示されています。

図 32: ManuallyCreatedDBObjects.dll が オブジェクト エクスプローラーに一覧表示されます (フルサイズの画像を表示する をクリックします)

まとめ

Microsoft SQL Server 2005 では、共通言語ランタイム (CLR) と統合されています。これにより、マネージド コードを使用してデータベース オブジェクトを作成できます。 以前は、これらのデータベース オブジェクトは T-SQL を使用してのみ作成できましたが、C# などの .NET プログラミング言語を使用してこれらのオブジェクトを作成できるようになりました。 このチュートリアルでは、2 つのマネージド ストアド プロシージャとマネージド User-Defined 関数を作成しました。

Visual Studio の SQL Server プロジェクトの種類により、マネージド データベース オブジェクトの作成、コンパイル、および配置が容易になります。 さらに、豊富なデバッグサポートを提供します。 ただし、SQL Server プロジェクトの種類は、Visual Studio の Professional および Team Systems エディションでのみ使用できます。 Visual Web Developer または Visual Studio の Standard Edition を使用する場合は、手順 13 で説明したように、作成、コンパイル、配置の手順を手動で実行する必要があります。

幸せなプログラミング!

もっと読む

このチュートリアルで説明するトピックの詳細については、次のリソースを参照してください。

著者について

7 冊の ASP/ASP.NET 書籍の著者であり、 4GuysFromRolla.com の創設者である Scott Mitchell は、1998 年から Microsoft Web テクノロジと協力しています。 Scott は独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の本は サムズ・ティーチ・自分自身 ASP.NET 24時間で2.0です。 にアクセスmitchell@4GuysFromRolla.comすることも、ブログを介して アクセスすることもできます。これは でhttp://ScottOnWriting.NET確認できます。

特別な感謝

このチュートリアル シリーズは、多くの役立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、S ren Jacob Lauritsen でした。 この記事を確認するだけでなく、S ren は、マネージド データベース オブジェクトを手動でコンパイルするために、この記事のダウンロードに含まれる Visual C# Express Edition プロジェクトも作成しました。 今後の MSDN 記事の確認に関心がありますか? その場合は、 に行mitchell@4GuysFromRolla.comをドロップしてください。

前へ次へ