型指定された DataSet の TableAdapters に既存のストアド プロシージャを使用する (C#)

作成者: Scott Mitchell

PDF のダウンロード

前のチュートリアルでは、TableAdapter ウィザードを使用して新しいストアド プロシージャを生成する方法について学習しました。 このチュートリアルでは、同じ TableAdapter ウィザードで既存のストアド プロシージャを操作する方法について学習します。 新しいストアド プロシージャをデータベースに手動で追加する方法についても学習します。

はじめに

前のチュートリアルでは、アドホック SQL ステートメントではなく、ストアド プロシージャを使用してデータにアクセスするように、型指定されたデータセットの TableAdapters を構成する方法について見ました。 具体的には、TableAdapter ウィザードでこれらのストアド プロシージャを自動的に作成する方法について説明しました。 レガシ アプリケーションを ASP.NET 2.0 に移植する場合、または既存のデータ モデルを中心に ASP.NET 2.0 Web サイトを構築する場合、必要なストアド プロシージャがデータベースに既に含まれている可能性があります。 または、ストアド プロシージャを手動で作成するか、ストアド プロシージャを自動生成する TableAdapter ウィザード以外のツールを使用して作成することもできます。

このチュートリアルでは、既存のストアド プロシージャを使用するように TableAdapter を構成する方法について見ていきます。 Northwind データベースには少数の組み込みストアド プロシージャしかないため、Visual Studio 環境を使用して新しいストアド プロシージャをデータベースに手動で追加するために必要な手順についても見ていきます。 では、始めましょう。

ステップ 1: Northwind データベースにストアド プロシージャを追加する

Visual Studio を使用すると、新しいストアド プロシージャをデータベースに簡単に追加できます。 特定の CategoryID 値を持つものについて、Products テーブルからすべての列を返す新しいストアド プロシージャを Northwind データベースに追加しましょう。 [サーバー エクスプローラー] ウィンドウで Northwind データベースを展開し、そのフォルダー (データベース ダイアグラム、テーブル、ビューなど) が表示されるようにします。 前のチュートリアルで見たように、[ストアド プロシージャ] フォルダーにはデータベースの既存のストアド プロシージャが含まれています。 新しいストアド プロシージャを追加するには、[ストアド プロシージャ] フォルダーを右クリックし、コンテキスト メニューから [新しいストアド プロシージャの追加] オプションを選択します。

図 1: [ストアド プロシージャ] フォルダーを右クリックして新しいストアド プロシージャを追加する (クリックするとフルサイズの画像が表示されます)

図 1 からわかるように、[新しいストアド プロシージャの追加] オプションを選択すると Visual Studio でスクリプト ウィンドウが開き、ストアド プロシージャの作成に必要な SQL スクリプトのアウトラインが表示されます。 このスクリプトを具体化して実行し、その時点でストアド プロシージャがデータベースに追加されるようにするのが今回の作業です。

次のスクリプトを入力します。

CREATE PROCEDURE dbo.Products_SelectByCategoryID 
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

このスクリプトを実行すると、新しいストアド プロシージャが Products_SelectByCategoryID という名前の Northwind データベースに追加されます。 このストアド プロシージャは 1 つの入力パラメーター (@CategoryID、型 int) を受け取り、一致する CategoryID 値がある製品のすべてのフィールドを返します。

この CREATE PROCEDURE スクリプトを実行してデータベースにストアド プロシージャを追加するには、ツール バーの [保存] アイコンをクリックするか、Ctrl + S キーを押します。 この操作を実行すると [ストアド プロシージャ] フォルダーが更新され、新しく作成されたストアド プロシージャが表示されます。 また、ウィンドウ内のスクリプトは、 CREATE PROCEDURE dbo.Products_SelectProductByCategoryID から ALTER PROCEDURE dbo.Products_SelectProductByCategoryIDに微妙に変化します。 CREATE PROCEDURE ではデータベースに新しいストアド プロシージャが追加されるのに対して、ALTER PROCEDURE では既存のストアド プロシージャが更新されます。 スクリプトの開始が ALTER PROCEDURE に変更されたため、ストアド プロシージャの入力パラメーターまたは SQL ステートメントを変更し、[保存] アイコンをクリックすると、これらの変更がストアド プロシージャに反映されます。

図 2 は Products_SelectByCategoryID ストアド プロシージャが保存された後の Visual Studio を示したものです。

図 2: ストアド プロシージャ Products_SelectByCategoryID がデータベースに追加されている (クリックするとフルサイズの画像が表示されます)

ステップ 2: 既存のストアド プロシージャを使用するように TableAdapter を構成する

Products_SelectByCategoryID ストアド プロシージャがデータベースに追加されたので、いずれかのメソッドが呼び出されたときにこのストアド プロシージャを使用するようにデータ アクセス層を構成できます。 具体的には、先ほど作成した Products_SelectByCategoryID ストアド プロシージャを呼び出す型指定されたデータセット NorthwindWithSprocs 内の ProductsTableAdapter に GetProductsByCategoryID(categoryID) メソッドを追加します。

まず、NorthwindWithSprocs データセットを開きます。 ProductsTableAdapter を右クリックし、[クエリの追加] を選択して TableAdapter クエリ構成ウィザードを起動します。 前のチュートリアルでは、TableAdapter で新しいストアド プロシージャを作成することにしました。 ただし、このチュートリアルでは、新しい TableAdapter メソッドを既存の Products_SelectByCategoryID ストアド プロシージャに接続します。 そのため、ウィザードの最初のステップで [既存のストアド プロシージャを使用] オプションを選択して [次へ] をクリックします。

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

次の画面には、データベースのストアド プロシージャが反映されたドロップダウン リストが表示されます。 ストアド プロシージャを選択すると、左側にその入力パラメーターが一覧表示され、右側に返されるデータ フィールド (存在する場合) が一覧表示されます。 リストから Products_SelectByCategoryID ストアド プロシージャを選択して [次へ] をクリックします。

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

次の画面では、ストアド プロシージャによって返されるデータの種類を尋ねられます。ここでの回答に応じて、TableAdapter メソッドによって返される型が決まります。 たとえば、表形式データが返されるよう指定した場合、メソッドはストアド プロシージャによって返されるレコードが反映された ProductsDataTable インスタンスを返します。 一方、このストアド プロシージャが単一の値を返すよう指定した場合、TableAdapter はストアド プロシージャによって返される最初のレコードの最初の列の値が割り当てられた object を返します。

Products_SelectByCategoryID ストアド プロシージャは特定のカテゴリに属するすべての製品を返すため、最初の回答 (表形式データ) を選択して [次へ] をクリックします。

図 5: ストアド プロシージャが表形式データを返すよう指定する (クリックするとフルサイズの画像が表示されます)

あとは、使用するメソッド パターンの後にこれらのメソッドの名前を指定することだけです。 [DataTable にデータを格納する] オプションと [DataTable を返す] オプションは両方ともチェックボックスをオンにしたままにしますが、メソッドの名前は FillByCategoryID から GetProductsByCategoryID に変更します。 次に、[次へ] をクリックしてウィザードが実行するタスクの概要を確認します。 問題がなければ [完了] をクリックします。

図 6: メソッドの名前を FillByCategoryID と GetProductsByCategoryID に設定する (クリックするとフルサイズの画像が表示されます)

ステップ 3: GetProductsByCategoryID(categoryID) メソッドを BLL に追加する

GetProductsByCategoryID DAL メソッドが完了したら、次はビジネス ロジック層でこのメソッドへのアクセスを提供します。 ProductsBLLWithSprocs クラス ファイルを開いて次のメソッドを追加します。

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByCategoryID(int categoryID)
{
    return Adapter.GetProductsByCategoryID(categoryID);
}

この BLL メソッドは ProductsTableAdapter の GetProductsByCategoryID メソッドから返された ProductsDataTable を返すだけです。 DataObjectMethodAttribute 属性は、ObjectDataSource のデータ ソースの構成ウィザードで使用されるメタデータを提供します。 具体的には、このメソッドは [選択] タブのドロップダウン リストに表示されます。

ステップ 4: カテゴリ別に製品を表示する

新しく追加された Products_SelectByCategoryID ストアド プロシージャと、対応する DAL メソッドと BLL メソッドをテストするには、DropDownList と GridView を含む ASP.NET ページを作成します。 DropDownList にはデータベース内のすべてのカテゴリが一覧表示されるのに対して、GridView には選択したカテゴリに属する製品が表示されます。

AdvancedDAL フォルダー内の ExistingSprocs.aspx ページを開き、ツールボックスからデザイナーに DropDownList をドラッグします。 DropDownList の ID プロパティを Categories に、その AutoPostBack プロパティを true に設定します。 次に、そのスマート タグから、CategoriesDataSource という名前の新しい ObjectDataSource に DropDownList をバインドします。 データを CategoriesBLL クラスの GetCategories メソッドから取得するように ObjectDataSource を構成します。 [UPDATE]、[INSERT]、[DELETE] の各タブのドロップダウン リストを (None) に設定します。

図 7: CategoriesBLL クラスの GetCategories メソッドからデータを取得する (クリックするとフルサイズの画像が表示されます)

図 8: [UPDATE]、[INSERT]、[DELETE] の各タブのドロップダウン リストを [(None)] に設定する (クリックするとフルサイズの画像が表示されます)

ObjectDataSource ウィザードが完了したら、CategoryName データ フィールドを表示し、ListItem フィールドごとに Value として CategoryID フィールドを使用するように DropDownList を構成します。

この時点で、DropDownList と ObjectDataSource の宣言型マークアップは次のようになります。

<asp:DropDownList ID="Categories" runat="server" AutoPostBack="True" 
    DataSourceID="CategoriesDataSource" DataTextField="CategoryName" 
    DataValueField="CategoryID">
</asp:DropDownList>
<asp:ObjectDataSource ID="CategoriesDataSource" runat="server"
    OldValuesParameterFormatString="original_{0}" 
    SelectMethod="GetCategories" TypeName="CategoriesBLL">
</asp:ObjectDataSource>

次に、GridView をデザイナーにドラッグし、DropDownList の下に配置します。 GridView の ID を ProductsByCategory に設定し、スマート タグから ProductsByCategoryDataSource という名前の新しい ObjectDataSource にバインドします。 クラス ProductsBLLWithSprocs を使用するように ProductsByCategoryDataSource ObjectDataSource を構成し、GetProductsByCategoryID(categoryID) メソッドを使用してデータを 取得するようにします。 この GridView はデータの表示にのみ使用されるため、[UPDATE]、[INSERT]、[DELETE] の各タブのドロップダウン リストを [(None)] に設定して [次へ] をクリックします。

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

図 10: GetProductsByCategoryID(categoryID) メソッドからデータを取得する (クリックするとフルサイズの画像が表示されます)

[選択] タブで選択したメソッドにはパラメーターが必要であるため、ウィザードの最後の手順ではパラメーターのソースの入力を求められます。 [パラメーター ソース] ドロップダウン リストを [コントロール] に設定し、[ControlID] ドロップダウン リストから Categories コントロールを選択します。 [完了] をクリックして、ウィザードを完了します。

図 11: categoryID パラメーターのソースとして Categories DropDownList を使用する (クリックするとフルサイズの画像が表示されます)

ObjectDataSource ウィザードが完了すると、Visual Studio によって、各製品データ フィールドの BoundFields と CheckBoxField が追加されます。 必要に応じて、これらのフィールドを自由にカスタマイズできます。

ブラウザーからページにアクセスします。 ページにアクセスすると [Beverages] カテゴリが選択されており、対応する製品がグリッドに一覧表示されています。 図 12 からわかるとおり、ドロップダウン リストを別のカテゴリに変更するとポストバックが発生し、新しく選択されたカテゴリの製品と共にグリッドが再度読み込まれます。

図 12: [Produce] カテゴリの製品が表示されている (クリックするとフルサイズの画像が表示されます)

ステップ 5: トランザクションのスコープ内でストアド プロシージャのステートメントをラップする

チュートリアル「トランザクション内のデータベース変更をラップする」では、トランザクションのスコープ内で一連のデータベース変更ステートメントを実行する手法について説明しました。 トランザクションの傘下で実行された変更はすべて成功または失敗し、原子性が保証されることを思い出してください。 トランザクションを使用する手法は次のとおりです。

• System.Transactions 名前空間内のクラスを使用する
• データ アクセス層で SqlTransaction のような ADO.NET クラスを使用する
• ストアド プロシージャ内に直接 T-SQL トランザクション コマンドを追加する

チュートリアル「トランザクション内のデータベース変更をラップする」では、DAL の ADO.NET クラスを使用しました。 このチュートリアルの後半では、ストアド プロシージャ内から T-SQL コマンドを使用してトランザクションを管理する方法について説明します。

トランザクションを手動で開始、コミット、ロールバックするための 3 つの主要な SQL コマンドはそれぞれ BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION です。 ADO.NET アプローチと同様、ストアド プロシージャ内からトランザクションを使用する場合は次のパターンを適用する必要があります。

1. トランザクションの開始点を示します。
2. トランザクションを構成する SQL ステートメントを実行します。
3. ステップ 2 のステートメントのいずれかでエラーが発生した場合は、トランザクションをロールバックします
4. ステップ 2 のすべてのステートメントがエラーなしで完了した場合は、トランザクションをコミットします。

このパターンは、次のテンプレートを使用して T-SQL 構文で実装できます。

BEGIN TRY
  BEGIN TRANSACTION -- Start the transaction
  ... Perform the SQL statements that makeup the transaction ...
  -- If we reach here, success!
  COMMIT TRANSACTION
END TRY
BEGIN CATCH 
  -- Whoops, there was an error
  ROLLBACK TRANSACTION
  -- Raise an error with the 
  -- details of the exception   
  DECLARE @ErrMsg nvarchar(4000),
          @ErrSeverity int 
  SELECT @ErrMsg = ERROR_MESSAGE(), 
         @ErrSeverity = ERROR_SEVERITY() 
 
  RAISERROR(@ErrMsg, @ErrSeverity, 1) 
END CATCH

テンプレートでは、最初に SQL Server 2005 の新しいコンストラクトである TRY...CATCH ブロックを定義します。 C# の try...catch ブロックと同様、SQL TRY...CATCH ブロックは TRY ブロック内のステートメントを実行します。 いずれかのステートメントでエラーが発生した場合、コントロールはすぐに CATCH ブロックに転送されます。

トランザクションを構成する SQL ステートメントの実行でエラーがない場合、COMMIT TRANSACTION ステートメントは変更をコミットしてトランザクションを完了します。 ただし、ステートメントの 1 つでエラーが発生した場合、 CATCH ブロック内の ROLLBACK TRANSACTION はトランザクションの開始前の状態にデータベースを戻します。 また、このストアド プロシージャでは RAISERROR コマンドを使用してエラーが発生します。そのため、アプリケーションで SqlException が発生します。

具体的な例を見てみましょう。 Categories と Products の各テーブルの間には外部キー制約が存在するため、Products テーブル内の各 CategoryID フィールドは Categories テーブル内の CategoryID 値にマップする必要があります。 関連製品があるカテゴリの削除など、この制約に違反するアクションを実行した場合、外部キー制約違反になります。 これを確認するには、「バイナリ データの操作」セクション (~/BinaryData/UpdatingAndDeleting.aspx) の「既存のバイナリ データの更新と削除」の例を見直します。 このページには、システム内の各カテゴリが [編集] ボタンおよび [削除] ボタンと共に一覧表示されますが (図 13 を参照)、関連製品 ([Beverages] など) があるカテゴリを削除しようとすると、外部キー制約違反が原因で削除に失敗します (図 14 を参照)。

図 13: 各カテゴリが [編集] ボタンおよび [削除] ボタンとともに GridView に表示されている (クリックするとフルサイズの画像が表示されます)

図 14: 既存の製品があるカテゴリは削除できない (クリックするとフルサイズの画像が表示されます)

ただし、カテゴリに関連製品があるかどうかに関係なく、カテゴリを削除できるようにしたいと考えているとします。 製品があるカテゴリを削除する場合に、既存の製品も削除するとしましょう (ただし、その製品 CategoryID の値を NULL に設定するだけというもう 1 つの選択肢もあります)。 この機能は、外部キー制約のカスケード ルールを使用して実装できます。 @CategoryID 入力パラメーターを受け付けるストアド プロシージャを作成し、呼び出した際にすべての関連製品と指定したカテゴリを明示的に削除することもできます。

このようなストアド プロシージャでの最初の試行は次のようになります。

CREATE PROCEDURE dbo.Categories_Delete
(
    @CategoryID int
)
AS
-- First, delete the associated products...
DELETE FROM Products
WHERE CategoryID = @CategoryID
-- Now delete the category
DELETE FROM Categories
WHERE CategoryID = @CategoryID

これによって間違いなく関連製品とカテゴリが削除されますが、トランザクション傘下では削除されません。 特定の @CategoryID 値の削除を禁止する他の外部キー制約が Categories にあるとします。 このような場合、カテゴリを削除しようとする前にすべての製品が削除されることが問題となります。 最終的には、このようなカテゴリの場合、他の一部のテーブルに関連レコードが残っているため、このストアド プロシージャによって、カテゴリが残った状態ですべての製品が削除されます。

ただし、ストアド プロシージャがトランザクションのスコープ内でラップされていた場合、Categories で削除に失敗した場合でも Products テーブルに対する削除はロールバックされます。 次のストアド プロシージャ スクリプトでは、トランザクションを使用して 2 つの DELETE ステートメント間の原子性を確保しています。

CREATE PROCEDURE dbo.Categories_Delete
(
    @CategoryID int
)
AS
BEGIN TRY
  BEGIN TRANSACTION -- Start the transaction
  -- First, delete the associated products...
  DELETE FROM Products
  WHERE CategoryID = @CategoryID
  -- Now delete the category
  DELETE FROM Categories
  WHERE CategoryID = @CategoryID
  -- If we reach here, success!
  COMMIT TRANSACTION
END TRY
BEGIN CATCH 
  -- Whoops, there was an error
  ROLLBACK TRANSACTION
  -- Raise an error with the 
  -- details of the exception   
  DECLARE @ErrMsg nvarchar(4000),
          @ErrSeverity int 
  SELECT @ErrMsg = ERROR_MESSAGE(), 
         @ErrSeverity = ERROR_SEVERITY() 
 
  RAISERROR(@ErrMsg, @ErrSeverity, 1) 
END CATCH

少し時間を取って、Categories_Delete ストアド プロシージャを Northwind データベースに追加します。 データベースにストアド プロシージャを追加する手順については、ステップ 1 に戻って確認してください。

ステップ 6: CategoriesTableAdapter を更新する

データベースに Categories_Delete ストアド プロシージャを追加しましたが、DAL は現在、アドホック SQL ステートメントを使用して削除を実行するように構成されています。 CategoriesTableAdapter を更新し、代わりに Categories_Delete ストアド プロシージャを使用するように指示する必要があります。

Northwind データセットを開き、CategoriesTableAdapter を選択してプロパティ ウィンドウに移動します。 プロパティ ウィンドウには、TableAdapter で使用する InsertCommand、UpdateCommand、DeleteCommand、SelectCommand に加え、その名前と接続情報が一覧表示されます。 DeleteCommand プロパティを展開して詳細を表示します。 図 15 からわかるとおり、DeleteCommand の CommandType プロパティは [Text] に設定されており、CommandText プロパティ内のテキストをアドホック SQL クエリとして送信するように指示しています。

図 15: デザイナーで CategoriesTableAdapter を選択してプロパティ ウィンドウにそのプロパティを表示する

これらの設定を変更するには、プロパティ ウィンドウで [(DeleteCommand)] テキストを選択し、ドロップダウン リストから [(New)] を選択します。 これにより、CommandText、CommandType、Parameters の各プロパティの設定がクリアされます。 次に、CommandType プロパティを StoredProcedure に設定し、CommandText (dbo.Categories_Delete) のストアド プロシージャの名前を入力します。 プロパティをこの順序 (CommandType、CommandText の順番) で入力すると、Visual Studio によってパラメーター コレクションが自動的に設定されます。 これらのプロパティをこの順序で入力しなかった場合は、パラメーター コレクション エディターを使用してパラメーターを手動で追加する必要があります。 どちらの場合でも、[パラメーター] プロパティの省略記号をクリックしてパラメーター コレクション エディターを表示し、正しいパラメーター設定の変更が行われていることを確認することをおすすめします (図 16 を参照)。 ダイアログ ボックスにパラメーターが一切表示されない場合は、@CategoryID パラメーターを手動で追加します (@RETURN_VALUE パラメーターを追加する必要はありません)。

図 16: パラメーター設定が正しいことを確認する

DAL が更新されると、カテゴリを削除すると関連製品がすべて自動的に削除され、トランザクションの傘下でも削除されます。 これを確認するには、[既存のバイナリ データの更新と削除] ページに戻り、いずれかのカテゴリの [削除] ボタンをクリックします。 マウスを 1 回クリックするだけで、カテゴリとその関連製品がすべて削除されます。

まとめ

TableAdapter のウィザードではストアド プロシージャが自動的に生成されますが、このようなストアド プロシージャが既に作成されている場合や、手動で作成したり、代わりに他のツールを使用して作成したりする場合もあります。 このようなシナリオに対応するために、既存のストアド プロシージャを指すように TableAdapter を構成することもできます。 このチュートリアルでは、Visual Studio 環境を使用してデータベースにストアド プロシージャを手動で追加する方法と、TableAdapter のメソッドをこれらのストアド プロシージャに接続する方法について説明しました。 また、ストアド プロシージャ内からトランザクションを開始、コミット、ロールバックするために使用される T-SQL コマンドとスクリプト パターンについても確認しました。

プログラミングに満足!

著者について

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

特別な感謝

このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、Hilton Geisenow、S ren Jacob Lauritsen、Teresa Murphy が務めました。 今後の MSDN の記事を確認することに関心がありますか? その場合は、 にmitchell@4GuysFromRolla.com行をドロップしてください。