次の方法で共有

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

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

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 を Visual Basic クラスのメソッドとして作成できます。 これにより、これらのストアド プロシージャと 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 認証] を選択し、[接続] をクリックします。

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

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

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

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

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

図 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 に変更します。

データベースの名前を 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 プロジェクト タイプを使用して、マネージド ストアド プロシージャと UDF を作成する方法について説明します。

Note

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

まず 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 デバッグのサポートを有効にする必要があります。 (図 5 で行なったように) SQL Server プロジェクトを新しいデータベースに関連付けるたびに、接続で SQL/CLR デバッグを有効にするかどうかを確認するメッセージが Visual Studio により表示されます (図 6 を参照)。 [はい] をクリックします。

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

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

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

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

ソリューション エクスプローラーに 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.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 という名前の Partial クラス ファイル内で Shared メソッドとして実装されることに注意してください。 さらに、GetDiscontinuedProducts メソッドは、メソッドをストアド プロシージャとしてマークする SqlProcedure 属性で修飾されます。

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

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

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

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

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

互換性レベルを更新した後、SQL Server プロジェクトを再配置します。 今回は、エラーなしで配置が完了するはずです。

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

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

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

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

[ストアド プロシージャ] フォルダー内のリストに表示されている GetDiscontinuedProducts ストアド プロシージャ

図 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 はこれらの結果を受け取り、[結果] ウィンドウに表示します。

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

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

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

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

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

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

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

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

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 マネージド ストアド プロシージャを選択し、[次へ] をクリックします。

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

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

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

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

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

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

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

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

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

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

この手順で追加された新しいメソッドが 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 の [データ ソースの構成] ウィザードの [SELECT] タブのドロップダウン リストにこれらのメソッドが含まれます。

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

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

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

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

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

[SELECT] タブのドロップダウン リストから GetDiscontinuedProducts メソッドを選択する

図 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) は、プログラミング言語の関数のセマンティクスを厳密に模倣するデータベース オブジェクトです。 Visual Basic の関数と同様に、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] フォルダーにあります。

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

図 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.vb という名前を付けます。

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

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

ユーザー定義関数テンプレートは、UserDefinedFunctions という名前の Partial クラスを作成します。これには、クラス ファイルの名前と同じ名前 (この場合は udf_ComputeInventoryValue_Managed) のメソッドが含まれます。 このメソッドは、マネージド UDF としてメソッドにフラグを設定する SqlFunction 属性を使用して修飾されます。

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 オブジェクトを返し、入力パラメーターを受け取りません。 メソッド定義を更新して、UnitPrice、UnitsInStock、Discontinued の 3 つの入力パラメーターを受け入れ、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 型であることに注意してください。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 を参照)。 この設定を変更するには、[サーバー エクスプローラー] ウィンドウからデータベースを右クリックします。

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

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

GetProductsWithPriceLessThan マネージド ストアド プロシージャをデバッグしたいとします。 まず、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 メソッドのステップ 実行、入力パラメーターの値の確認などを行うことができます。

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 フラグは、結果のアセンブリの名前を指定します。

Note

コマンド ラインから GetProductsWithPriceGreaterThan.vb クラス ファイルをコンパイルするのではなく、Visual Basic Express Edition を使用するか、Visual Studio Standard Edition で別のクラス ライブラリ プロジェクトを作成することもできます。 S ren Jacob Lauritsen は、GetProductsWithPriceGreaterThan ストアド プロシージャのためのコードと、手順 3、5、および 10 で作成された 2 つのマネージド ストアド プロシージャと UDF を持つこのような Visual Basic 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 アセンブリは表示されないはずです。

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) に関連付けられました。

上記のスクリプトを実行した後、オブジェクト エクスプローラーの [Stored Procedures] フォルダーを更新します。 新しいストアド プロシージャ エントリ (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 つのマネージド ストアド プロシージャとマネージド ユーザー定義関数を作成しました。

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 は、独立したコンサルタント、トレーナー、ライターとして働いています。 彼の最新の著書は Sams Teach Yourself ASP.NET 2.0 in 24 Hoursです。 にアクセスするか、ブログを使用して にアクセスmitchell@4GuysFromRolla.comできます。これは でhttp://ScottOnWriting.NET見つけることができます。

特別な感謝

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

[戻る]