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

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

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

はじめに

Microsoft s 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 は、マネージド コードを使用して作成できます。 つまり、Visual Basic クラスのメソッドとしてストアド プロシージャまたは UDF を作成できます。 これにより、これらのストアド プロシージャと UDF は、.NET Frameworkおよび独自のカスタム クラスの機能を利用できます。

このチュートリアルでは、マネージド ストアド プロシージャと User-Defined 関数を作成する方法と、それらを 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 データベース インスタンスに明示的に登録することで、いくつかの手順がはるかに簡単になります。

このチュートリアルのダウンロードには、 と という 2 つのデータベース ファイル NORTHWND.MDFNORTHWND_log.LDF が という名前 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 データベース インスタンスに関する情報が一覧表示されます。

フォルダー内DataFilesの Northwind データベース (または移動した場所) をSQL 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 ロジックを Visual Basic コードとしてクラスに記述します。 コードが記述されたら、このクラスをアセンブリ (.dllファイル) にコンパイルし、アセンブリをSQL Server データベースに登録し、アセンブリ内の対応するメソッドを指すストアド プロシージャまたは UDF オブジェクトをデータベースに作成する必要があります。 これらの手順はすべて手動で実行できます。 任意のテキスト エディターでコードを作成し、Visual Basic コンパイラ (vbc.exe) を使用してコマンド ラインからコンパイルし、コマンドまたは Management Studio からデータベース CREATE ASSEMBLY に登録し、同様の方法でストアド プロシージャまたは UDF オブジェクトを追加できます。 さいわい、Visual Studio の Professional および Team Systems のバージョンには、これらのタスクを自動化するSQL Server プロジェクトの種類が含まれています。 このチュートリアルでは、SQL Server Project の種類を使用して、マネージド ストアド プロシージャと 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 Project を新しいデータベースに関連付けるたびに (図 5 のように)、接続で SQL/CLR デバッグを有効にするかどうかが Visual Studio から確認されます (図 6 を参照)。 [はい] をクリックします。

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

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

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

GetDiscontinuedProducts.vb という名前の新しいストアド プロシージャを追加する

図 8: 名前付きの GetDiscontinuedProducts.vb 新しいストアド プロシージャを追加する (フルサイズの画像を表示する をクリックします)

これにより、次の内容を含む新しい Visual Basic クラス ファイルが作成されます。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetDiscontinuedProducts ()
        ' Add your code here
    End Sub
End Class

ストアド プロシージャは、 という名前StoredProceduresのクラス ファイル内のSharedPartialメソッドとして実装されることに注意してください。 さらに、 GetDiscontinuedProducts メソッドは 属性でSqlProcedure修飾され、メソッドはストアド プロシージャとしてマークされます。

次のコードでは、 オブジェクトをSqlCommand作成し、フィールドSELECTCommandTextが 1 の製品Discontinuedのテーブルからすべての列をProducts返すクエリに 設定します。 次に、コマンドを実行し、結果をクライアント アプリケーションに送信します。 GetDiscontinuedProducts メソッドにこのコードを追加します。

' Create the command
Dim myCommand As 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.vb という名前を付けます。 手順 3 で説明したように、 クラス 内に 配置された という名前GetProductsWithPriceLessThanのメソッドを使用して、新しい Visual Basic クラス StoredProceduresファイルがPartial作成されます。

メソッドの定義をGetProductsWithPriceLessThan更新して、 という名前priceの入力パラメーターをSqlMoney受け取り、実行するコードを記述し、クエリ結果を返すようにします。

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub GetProductsWithPriceLessThan(ByVal price As SqlMoney)
    'Create the command
    Dim myCommand As 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)
End Sub

メソッドの定義とコードは GetProductsWithPriceLessThan 、手順 3 で作成した GetDiscontinuedProducts メソッドの定義とコードによく似ています。 唯一の違いは、メソッドがGetProductsWithPriceLessThan入力パラメーター (price) SqlCommand として受け取り、s クエリにパラメーター (@MaxPrice) が含まれており、パラメーターが s Parameters コレクションにSqlCommand追加され、変数のprice値が割り当てられる点です。

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

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

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

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

この時点で、 および GetProductsWithPriceLessThan マネージド ストアド プロシージャをプロジェクトにManagedDatabaseConstructs追加GetDiscontinuedProductsし、Northwind SQL Server データベースに登録しました。 また、これらのマネージド ストアド プロシージャをSQL Server Management Studioから呼び出しました (図 13 および図 14 を参照)。 ただし、ASP.NET アプリケーションでこれらのマネージド ストアド プロシージャを使用するには、アーキテクチャのデータ アクセス層とビジネス ロジック層に追加する必要があります。 この手順では、Typed DataSet の NorthwindWithSprocsTableAdapters チュートリアルの「新しいストアド プロシージャの作成」で最初に作成した、型指定された DataSet の に 2 つの新しいメソッドProductsTableAdapterを追加します。 手順 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 選択します (フルサイズの画像を表示する場合はクリックします)

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

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

図 17: [表形式データ] オプションを選択します (フルサイズの画像を表示する場合にクリックします)

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

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

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

マネージド ストアド プロシージャの と という名前FillByPriceLessThanのメソッドを 作成するには、次のProductsTableAdapter手順をGetProductsWithPriceLessThan繰り返GetProductsWithPriceLessThanします。

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

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

図 19: この ProductsTableAdapter 手順で追加された新しいメソッドが含まれています (フルサイズの画像を表示するには、ここをクリックします)

手順 7: 対応するメソッドをビジネス ロジック 層に追加する

手順 4 と 5 で追加されたマネージド ストアド プロシージャを呼び出すためのメソッドを含むようにデータ アクセス層を更新したので、対応するメソッドをビジネス ロジック層に追加する必要があります。 クラスに次の 2 つのメソッドを ProductsBLLWithSprocs 追加します。

<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetDiscontinuedProducts() As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetDiscontinuedProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
    (System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsWithPriceLessThan(ByVal priceLessThan As Decimal) _
    As NorthwindWithSprocs.ProductsDataTable
    Return Adapter.GetProductsWithPriceLessThan(priceLessThan)
End Function

どちらのメソッドも、対応する DAL メソッドを呼び出してインスタンスを ProductsDataTable 返すだけです。 各メソッドの DataObjectMethodAttribute 上にあるマークアップにより、ObjectDataSource の [データ ソースの構成] ウィザードの [選択] タブのドロップダウン リストにこれらのメソッドが含まれます。

手順 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 タブの GetDiscontinuedProducts Drop-Down リストからメソッドを選択します (フルサイズの画像を表示する をクリックします)

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

ウィザードが完了すると、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 の ProductsDataTable クラス s GetDiscontinuedProducts メソッドを呼び出し、ストアド プロシージャを GetDiscontinuedProducts 呼び出します。 このストアド プロシージャはマネージド ストアド プロシージャであり、手順 3 で作成したコードを実行して、廃止された製品を返します。

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

廃止された製品の一覧

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

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

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

User-Defined 関数 (UDF) は、プログラミング言語の関数のセマンティクスを厳密に模倣するデータベース オブジェクトです。 Visual Basic の関数と同様に、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 は、オブジェクト エクスプローラーの 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 は 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.vb。

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

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

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

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function udf_ComputeInventoryValue_Managed() As SqlString
        ' Add your code here
        Return New SqlString("Hello")
    End Function
End Class

メソッドはudf_ComputeInventoryValue現在、 オブジェクトをSqlString返し、入力パラメーターを受け入れていません。 メソッド定義を更新して、 の 3 つの入力パラメーター UnitPriceUnitsInStockDiscontinued を受け入れ、オブジェクトをSqlMoney返すようにする必要があります。 インベントリ値を計算するためのロジックは、T-SQL udf_ComputeInventoryValue UDF のロジックと同じです。

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udf_ComputeInventoryValue_Managed _
    (UnitPrice As SqlMoney, UnitsInStock As SqlInt16, Discontinued As SqlBoolean) _
    As SqlMoney
    Dim inventoryValue As SqlMoney = 0
    If Not UnitPrice.IsNull AndAlso Not UnitsInStock.IsNull Then
        inventoryValue = UnitPrice * UnitsInStock
        If Discontinued = True Then
            inventoryValue = inventoryValue * New SqlMoney(0.5)
        End If
    End If
    Return inventoryValue
End Function

UDF メソッドの入力パラメーターは、対応する SQL 型SqlMoneyであることに注意してください。フィールドのUnitPrice場合は 、、 SqlBooleanSqlInt16UnitsInStockのDiscontinued場合は です。 これらのデータ型は、テーブルで定義されている型をProducts反映します。列は 型、UnitsInStock型moneyの列は、型smallintのDiscontinued列は bitです。UnitPrice

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

注意

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

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

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

プロジェクトを配置したら、SQL Server Management Studioに戻り、Scalar 値 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 Project からデバッグできます。 ただし、デバッグを機能させるには、SQL Server 2005 データベースで SQL/CLR デバッグを許可する必要があります。 最初にプロジェクトを ManagedDatabaseConstructs 作成したときに、Visual Studio から SQL/CLR デバッグを有効にするかどうかをたずねられたことを思い出してください (手順 2 の図 6 を参照)。 この設定は、[サーバー エクスプローラー] ウィンドウからデータベースを右クリックすることで変更できます。

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

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

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

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

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

まず、SQL Server プロジェクトからマネージド データベース オブジェクトをデバッグする方法を見てみましょう。 ソリューションには、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.vb の新しいファイルをコンピューターに作成し、次のコードをファイルに入力します (これを実現するには、Visual Studio、メモ帳、または任意のテキスト エディターを使用できます)。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetProductsWithPriceGreaterThan(ByVal price As SqlMoney)
        'Create the command
        Dim myCommand As 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)
    End Sub
End Class

このコードは、手順 5 で作成した GetProductsWithPriceLessThan メソッドとほぼ同じです。 唯一の違いは、クエリで使用されるメソッド名、 WHERE 句、およびパラメーター名です。 メソッドにGetProductsWithPriceLessThan戻ると、 句は WHERE を読み取ります。 WHERE UnitPrice < @MaxPrice ここでは、 で GetProductsWithPriceGreaterThanを使用します。 WHERE UnitPrice > @MinPrice

次に、このクラスをアセンブリにコンパイルする必要があります。 コマンド ラインから、ファイルを保存 GetProductsWithPriceGreaterThan.vb したディレクトリに移動し、C# コンパイラ (csc.exe) を使用してクラス ファイルをアセンブリにコンパイルします。

vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

に v bc.exe を含むフォルダーがシステム内 PATHにない場合は、次のように、 %WINDOWS%\Microsoft.NET\Framework\version\そのパスを完全に参照する必要があります。

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.vb

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

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

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

注意

コマンド ラインからクラス ファイルを GetProductsWithPriceGreaterThan.vb コンパイルするのではなく、 Visual Basic Express Edition を使用するか、Visual Studio Standard Edition で別のクラス ライブラリ プロジェクトを作成することもできます。 S ren Jacob Lauritsen は、このような Visual Basic 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 を使用してのみ作成できましたが、Visual Basic などの .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をドロップしてください。

[戻る]