型指定された DataSet の TableAdapters に新しいストアド プロシージャを作成する (VB)

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

以前のチュートリアルでは、コードに SQL ステートメントを作成し、実行するデータベースにステートメントを渡しました。 別の方法として、SQL ステートメントがデータベースで事前に定義されているストアド プロシージャを使用します。 このチュートリアルでは、TableAdapter ウィザードで新しいストアド プロシージャを生成する方法について説明します。

はじめに

これらのチュートリアルのデータ アクセス層 (DAL) では、型指定されたデータセットを使用します。 データ アクセス層の作成チュートリアルで説明したように、型指定された DataSet は厳密に型指定された DataTable と TableAdapters で構成されます。 DataTables はシステム内の論理エンティティを表し、TableAdapters は基になるデータベースとインターフェイスしてデータ アクセスを実行します。 これには、DataTable にデータの設定、スカラー データを返すクエリの実行、データベースからのレコードの挿入、更新、削除が含まれます。

TableAdapters によって実行される SQL コマンドには、 などの SELECT columnList FROM TableNameアドホック SQL ステートメントまたはストアド プロシージャのいずれかを指定できます。 このアーキテクチャの TableAdapters では、アドホック SQL ステートメントを使用します。 ただし、多くの開発者とデータベース管理者は、セキュリティ、保守容易性、および更新容易性の理由から、アドホック SQL ステートメントよりもストアド プロシージャを優先します。 柔軟性のためにアドホック SQL ステートメントを熱心に好む人もいます。 私の仕事では、アドホック SQL ステートメントよりもストアド プロシージャを優先していますが、前のチュートリアルを簡略化するためにアドホック SQL ステートメントを使用することを選択しました。

TableAdapter を定義したり、新しいメソッドを追加したりする場合、TableAdapter ウィザードを使用すると、アドホック SQL ステートメントを使用する場合と同じように、新しいストアド プロシージャの作成や既存のストアド プロシージャの使用が簡単になります。 このチュートリアルでは、TableAdapter ウィザードでストアド プロシージャを自動生成する方法について説明します。 次のチュートリアルでは、既存のストアド プロシージャまたは手動で作成されたストアド プロシージャを使用するように TableAdapter のメソッドを構成する方法について説明します。

注意

ストアド プロシージャとアドホック SQL の長所と短所に関する活発な議論については、Rob Howard のブログ エントリ「 ストアド プロシージャをまだ使用しないか? 」および 「Frans Bouma のブログ エントリ ストアド プロシージャは Bad、M Kay?」 を参照してください。

ストアド プロシージャの基本

関数は、すべてのプログラミング言語に共通のコンストラクトです。 関数は、関数が呼び出されたときに実行されるステートメントのコレクションです。 関数は入力パラメーターを受け取ることができ、必要に応じて値を返すことができます。 ストアド プロシージャ は、プログラミング言語の関数と多くの類似点を共有するデータベース コンストラクトです。 ストアド プロシージャは、ストアド プロシージャが呼び出されたときに実行される一連の T-SQL ステートメントで構成されます。 ストアド プロシージャでは、0 から多くの入力パラメーターを受け取ることができ、スカラー値、出力パラメーター、またはクエリから SELECT 結果セットを返すことができます。

注意

ストアド プロシージャは、多くの場合、sprocs または SP と呼ばれます。

ストアド プロシージャは、T-SQL ステートメントを CREATE PROCEDURE 使用して作成されます。 たとえば、次の T-SQL スクリプトは、 という名前GetProductsByCategoryIDの 1 つのパラメーターを受け取り、一致するCategoryID値を持つテーブル内Productsの列の 、ProductName、UnitPrice、および Discontinued フィールドを返す ProductIDという名前@CategoryIDのストアド プロシージャを作成します。

CREATE PROCEDURE GetProductsByCategoryID
(
    @CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

このストアド プロシージャを作成したら、次の構文を使用して呼び出すことができます。

EXEC GetProductsByCategory categoryID

注意

次のチュートリアルでは、Visual Studio IDE を使用してストアド プロシージャを作成する方法について説明します。 ただし、このチュートリアルでは、TableAdapter ウィザードでストアド プロシージャが自動的に生成されるようにします。

単にデータを返すだけでなく、ストアド プロシージャは多くの場合、1 つのトランザクションのスコープ内で複数のデータベース コマンドを実行するために使用されます。 たとえば、 という名前 DeleteCategoryのストアド プロシージャは、 パラメーターを @CategoryID 受け取り、2 つの DELETE ステートメントを実行できます。1 つは関連製品を削除するステートメント、もう 1 つは指定したカテゴリを削除するステートメントです。 ストアド プロシージャ内の複数のステートメントは、トランザクション内で自動的にラップ されません 。 ストアド プロシージャの複数のコマンドがアトミック操作として扱われるようにするには、追加の T-SQL コマンドを発行する必要があります。 後続のチュートリアルでは、トランザクションのスコープ内でストアド プロシージャのコマンドをラップする方法について説明します。

アーキテクチャ内でストアド プロシージャを使用する場合、データ アクセス層の メソッドは、アドホック SQL ステートメントを発行するのではなく、特定のストアド プロシージャを呼び出します。 これにより、アプリケーションのアーキテクチャ内で定義されるのではなく、(データベース上で) 実行される SQL ステートメントの場所が一元化されます。 この一元化により、クエリの検索、分析、チューニングが容易になり、データベースの使用場所と使用方法に関するより明確な画像が提供されます。

ストアド プロシージャの基礎の詳細については、このチュートリアルの最後にある「さらに読む」セクションのリソースを参照してください。

手順 1: 高度なデータ アクセス層のシナリオ Web ページの作成

ストアド プロシージャを使用した DAL の作成に関する説明を開始する前に、まず、このチュートリアルと次のいくつかのチュートリアルに必要な ASP.NET ページを Web サイト プロジェクトに作成します。 まず、 という名前 AdvancedDALの新しいフォルダーを追加します。 次に、次の ASP.NET ページをそのフォルダーに追加し、各ページをマスター ページに Site.master 関連付けます。

  • Default.aspx
  • NewSprocs.aspx
  • ExistingSprocs.aspx
  • JOINs.aspx
  • AddingColumns.aspx
  • ComputedColumns.aspx
  • EncryptingConfigSections.aspx
  • ManagedFunctionsAndSprocs.aspx

高度なデータ アクセス層のシナリオに関するチュートリアルの ASP.NET ページを追加する

図 1: 高度なデータ アクセス層のシナリオに関するチュートリアルの ASP.NET ページを追加する

他のフォルダーと同様に、 Default.aspx フォルダーには AdvancedDAL セクションにチュートリアルが一覧表示されます。 ユーザー コントロールに SectionLevelTutorialListing.ascx この機能があることを思い出してください。 したがって、このユーザー コントロールを にDefault.aspx追加するには、ソリューション エクスプローラーからページのデザイン ビューにドラッグします。

SectionLevelTutorialListing.ascx ユーザー コントロールを Default.aspx に追加する

図 2: ユーザー コントロールを SectionLevelTutorialListing.ascx に追加する Default.aspx (クリックするとフルサイズの画像が表示されます)

最後に、これらのページをエントリとしてファイルに追加します Web.sitemap 。 具体的には、バッチ データの操作の後に次のマークアップを追加します <siteMapNode>。

<siteMapNode url="~/AdvancedDAL/Default.aspx" 
    title="Advanced DAL Scenarios" 
    description="Explore a number of advanced Data Access Layer scenarios.">
    
    <siteMapNode url="~/AdvancedDAL/NewSprocs.aspx" 
        title="Creating New Stored Procedures for TableAdapters" 
        description="Learn how to have the TableAdapter wizard automatically 
            create and use stored procedures." />
    <siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx" 
        title="Using Existing Stored Procedures for TableAdapters" 
        description="See how to plug existing stored procedures into a 
            TableAdapter." />
    <siteMapNode url="~/AdvancedDAL/JOINs.aspx" 
        title="Returning Data Using JOINs" 
        description="Learn how to augment your DataTables to work with data 
            returned from multiple tables via a JOIN query." />
    <siteMapNode url="~/AdvancedDAL/AddingColumns.aspx" 
        title="Adding DataColumns to a DataTable" 
        description="Master adding new columns to an existing DataTable." />
    <siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx" 
        title="Working with Computed Columns" 
        description="Explore how to work with computed columns when using 
            Typed DataSets." />
    <siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx" 
        title="Protected Connection Strings in Web.config" 
        description="Protect your connection string information in 
            Web.config using encryption." />
    <siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx" 
        title="Creating Managed SQL Functions and Stored Procedures" 
        description="See how to create SQL functions and stored procedures 
            using managed code." />
</siteMapNode>

を更新した Web.sitemap後、ブラウザーを使用してチュートリアル Web サイトを表示します。 左側のメニューに、高度な DAL シナリオのチュートリアルの項目が含まれるようになりました。

サイト マップに、高度な DAL シナリオのチュートリアルのエントリが含まれるようになりました

図 3: サイト マップに、高度な DAL シナリオのチュートリアルのエントリが含まれるようになりました

手順 2: 新しいストアド プロシージャを作成するための TableAdapter の構成

アドホック SQL ステートメントではなくストアド プロシージャを使用するデータ アクセス層を作成する方法を示すために、 という名前NorthwindWithSprocs.xsdのフォルダーに新しい Typed DataSet を~/App_Code/DAL作成します。 このプロセスを前のチュートリアルで詳しく説明したので、ここでの手順をすばやく進めます。 型指定された DataSet の作成と構成に関する詳細な手順が必要な場合は、 データ アクセス層の作成 に関するチュートリアルを参照してください。

図 4 に示すように、フォルダーを右クリックし、[ DAL 新しい項目の追加] を選択し、DataSet テンプレートを選択して、新しい DataSet をプロジェクトに追加します。

NorthwindWithSprocs.xsd という名前のプロジェクトに新しい型指定された DataSet を追加する

図 4: 名前付きの NorthwindWithSprocs.xsd プロジェクトに新しい型指定された DataSet を追加する (フルサイズの画像を表示する をクリックします)

これにより、新しい Typed DataSet が作成され、そのDesignerが開き、新しい TableAdapter が作成され、TableAdapter 構成ウィザードが起動されます。 TableAdapter 構成ウィザードの最初の手順では、操作するデータベースを選択するように求められます。 Northwind データベースへの接続文字列がドロップダウン リストに表示されます。 これを選択し、[次へ] をクリックします。

この次の画面では、TableAdapter がデータベースにアクセスする方法を選択できます。 前のチュートリアルでは、SQL ステートメントを使用する最初のオプションを選択しました。 このチュートリアルでは、2 番目のオプション [新しいストアド プロシージャの作成] を選択し、[次へ] をクリックします。

新しいストアド プロシージャを作成するように TableAdapter に指示する

図 5: 新しいストアド プロシージャを作成するように TableAdapter に指示する (フルサイズの画像を表示する をクリックします)

アドホック SQL ステートメントの使用と同様に、次の手順では、TableAdapter の メイン クエリの ステートメントを指定SELECTするように求められます。 ただし、ここで入力したステートメントを SELECT 使用してアドホック クエリを直接実行する代わりに、TableAdapter ウィザードによって、この SELECT クエリを含むストアド プロシージャが作成されます。

この TableAdapter には、次 SELECT のクエリを使用します。

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products

SELECT クエリを入力する

図 6: クエリを入力します SELECT (クリックするとフルサイズの画像が表示されます)

注意

上記のクエリは、型指定された DataSet の Northwind メイン クエリProductsTableAdapterとは若干異なります。 ProductsTableAdapter型指定された DataSet の Northwind には、各製品のカテゴリと仕入先のカテゴリ名と会社名を取り戻すための 2 つの相関サブクエリが含まれていることを思い出してください。 今後の 「JOIN を使用するための TableAdapter の更新 」チュートリアルでは、この関連データをこの TableAdapter に追加する方法について説明します。

[詳細オプション] ボタンをクリックします。 ここから、ウィザードで TableAdapter の挿入、更新、および削除ステートメントも生成するかどうか、オプティミスティック コンカレンシーを使用するかどうか、および挿入と更新後にデータ テーブルを更新するかどうかを指定できます。 [挿入、更新、および削除ステートメントの生成] オプションは、既定でオンになっています。 オンのままにします。 このチュートリアルでは、[オプティミスティック コンカレンシーオプションを使用する] をオフのままにします。

TableAdapter ウィザードによってストアド プロシージャが自動的に作成されると、[データ テーブルの更新] オプションは無視されたように見えます。 このチェック ボックスがオンになっているかどうかに関係なく、結果の挿入と更新のストアド プロシージャは、手順 3 で示すように、just-inserted または just-updated レコードを取得します。

[挿入、更新、および削除のステートメントの生成] オプションをオンのままにします

図 7: [挿入、更新、および削除のステートメントの生成] オプションをオンのままにする

注意

[オプティミスティック コンカレンシーの使用] オプションをオンにすると、他のフィールドに変更があった場合に WHERE データが更新されないようにする条件が句に追加されます。 TableAdapter の組み込みの オプティミスティック コンカレンシー 制御機能の使用方法の詳細については、オプティミスティック コンカレンシーの実装に関するチュートリアルを参照してください。

クエリを SELECT 入力し、[挿入、更新、および削除ステートメントの生成] オプションがオンになっていることを確認したら、[次へ] をクリックします。 図 8 に示す次の画面では、ウィザードによって作成されるデータの選択、挿入、更新、削除を行うストアド プロシージャの名前の入力を求めるメッセージが表示されます。 これらのストアド プロシージャの名前を、、、Products_InsertProducts_Updateおよび Products_DeleteにProducts_Select変更します。

ストアド プロシージャの名前を変更する

図 8: ストアド プロシージャの名前を変更する (クリックするとフルサイズの画像が表示されます)

TableAdapter ウィザードが 4 つのストアド プロシージャの作成に使用する T-SQL を表示するには、[SQL スクリプトのプレビュー] ボタンをクリックします。 [SQL スクリプトのプレビュー] ダイアログ ボックスで、スクリプトをファイルに保存するか、クリップボードにコピーできます。

ストアド プロシージャの生成に使用される SQL スクリプトをプレビューする

図 9: ストアド プロシージャの生成に使用される SQL スクリプトをプレビューする

ストアド プロシージャに名前を付けたら、[次へ] をクリックして TableAdapter の対応するメソッドに名前を付けます。 アドホック SQL ステートメントを使用する場合と同様に、既存の DataTable を埋めるメソッドを作成するか、新しい DataTable を返すことができます。 TableAdapter に、レコードの挿入、更新、および削除のための DB-Direct パターンを含めるかどうかを指定することもできます。 3 つのチェック ボックスをすべてオンのままにしますが、Return a DataTable メソッドの名前を に GetProducts 変更します (図 10 を参照)。

メソッドに Fill と GetProducts という名前を付ける

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

[次へ] をクリックすると、ウィザードで実行される手順の概要が表示されます。 [完了] ボタンをクリックしてウィザードを完了します。 ウィザードが完了すると、DataSet の Designerに戻り、 が含まれるProductsDataTableようになります。

DataSet のDesignerには、新しく追加された ProductsDataTable が表示されます

図 11: DataSet のDesignerは、新しく追加されたProductsDataTableを示しています (フルサイズの画像を表示する をクリックします)。

手順 3: 新しく作成されたストアド プロシージャを調べる

手順 2 で使用される TableAdapter ウィザードでは、データの選択、挿入、更新、削除のためのストアド プロシージャが自動的に作成されます。 これらのストアド プロシージャは、サーバー エクスプローラーに移動し、データベースの [ストアド プロシージャ] フォルダーにドリルダウンすることで、Visual Studio で表示または変更できます。 図 12 に示すように、Northwind データベースには、、Products_InsertProducts_Selectおよび Products_Updateの 4 つの新しいストアド プロシージャがProducts_Delete含まれています。

手順 2 で作成した 4 つのストアド プロシージャは、データベースのストアド プロシージャ フォルダーにあります

図 12: 手順 2 で作成された 4 つのストアド プロシージャは、データベースのストアド プロシージャ フォルダーにあります

注意

[サーバー] エクスプローラーが表示されない場合は、[表示] メニューに移動し、[サーバーのエクスプローラー] オプションを選択します。 手順 2 で追加された製品関連のストアド プロシージャが表示されない場合は、[ストアド プロシージャ] フォルダーを右クリックし、[更新] を選択してみてください。

ストアド プロシージャを表示または変更するには、サーバー エクスプローラーでその名前をダブルクリックするか、またはストアド プロシージャを右クリックして [開く] を選択します。 図 13 は、開かれたストアド プロシージャを Products_Delete 示しています。

ストアド プロシージャは Visual Studio 内から開いて変更できます

図 13: Visual Studio 内からストアド プロシージャを開いて変更できる (フルサイズの画像を表示する をクリックします)

ストアド プロシージャと Products_Select ストアド プロシージャの両方Products_Deleteの内容は非常に簡単です。 一方、 および Products_Update ストアド プロシージャはProducts_Insert、 と ステートメントの後にステートメントを実行するため、より詳細なSELECT検査がINSERTUPDATE必要です。 たとえば、次の SQL はストアド プロシージャを Products_Insert 構成します。

ALTER PROCEDURE dbo.Products_Insert
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], 
    [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) 
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, 
    @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, 
    UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = SCOPE_IDENTITY())

ストアド プロシージャは、TableAdapter ウィザードで指定されたクエリによってSELECT返された列を入力パラメーターProductsとして受け取り、これらの値は ステートメントでINSERT使用されます。 ステートメントの INSERT 後に、クエリを SELECT 使用して、新しく追加されたレコードの Products 列値 (を ProductID含む) を返します。 この更新機能は、Batch Update パターンを使用して新しいレコードを追加する場合に便利です。新しく追加 ProductRow されたインスタンス ProductID プロパティは、データベースによって割り当てられた自動インクリメント値で自動的に更新されます。

次のコードは、この機能を示しています。 これには、 ProductsTableAdapter が含まれており、 ProductsDataTable 型指定された DataSet 用に NorthwindWithSprocs 作成されます。 インスタンスを作成し、その値を ProductsRow 指定し、 を渡して TableAdapter の メソッドを呼び出すことで、新しい製品が Update データベースに ProductsDataTable追加されます。 内部的には、TableAdapter の Update メソッドは、渡された DataTable 内のインスタンスを列挙 ProductsRow し (この例では、追加したインスタンスは 1 つだけ)、適切な挿入、更新、または削除コマンドを実行します。 この場合、 Products_Insert ストアド プロシージャが実行され、テーブルに新しいレコードが Products 追加され、新しく追加されたレコードの詳細が返されます。 その後、 ProductsRow インスタンスの ProductID 値が更新されます。 メソッドがUpdate完了したら、 プロパティを使用して新しく追加されたレコードのProductID値にProductsRowProductIDアクセスできます。

' Create the ProductsTableAdapter and ProductsDataTable
Dim productsAPI As New NorthwindWithSprocsTableAdapters.ProductsTableAdapter 
Dim products As New NorthwindWithSprocs.ProductsDataTable
' Create a new ProductsRow instance and set its properties
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = "New Product"
product.CategoryID = 1  ' Beverages
product.Discontinued = False
' Add the ProductsRow instance to the DataTable
products.AddProductsRow(product)
' Update the DataTable using the Batch Update pattern
productsAPI.Update(products)
' At this point, we can determine the value of the newly-added record's ProductID
Dim newlyAddedProductIDValue as Integer = product.ProductID

ストアド プロシージャもProducts_Update同様に、 ステートメントの後に ステートメントをSELECTUPDATE含めます。

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @Original_ProductID int,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] 
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @Original_ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

このストアド プロシージャには、 と @ProductIDの 2 つの入力パラメーターが含まれていることにProductID@Original_ProductID注意してください。 この機能により、主キーが変更される可能性があるシナリオが可能になります。 たとえば、従業員データベースでは、各従業員レコードが従業員の社会保障番号を主キーとして使用する場合があります。 既存の従業員の社会保障番号を変更するには、新しい社会保障番号と元の社会保障番号の両方を指定する必要があります。 テーブルの Products 場合、列は列であり、変更する必要がないため ProductID 、このような機能は IDENTITY 必要ありません。 実際、ストアド プロシージャの UPDATEProducts_Update ステートメントには列リストに ProductID 列が含まれていません。 したがって、 は ステートメント s WHERE 句でUPDATE使用されますが@Original_ProductID、テーブルでは余分Productsであり、 @ProductID パラメーターに置き換えることができます。 ストアド プロシージャのパラメーターを変更する場合は、そのストアド プロシージャを使用する TableAdapter メソッドも更新することが重要です。

手順 4: ストアド プロシージャのパラメーターの変更と TableAdapter の更新

@Original_ProductIDパラメーターは余分であるため、ストアド プロシージャから完全にProducts_Update削除してみましょう。 ストアド プロシージャをProducts_Update開き、 パラメーターを@Original_ProductID削除し、 ステートメントの UPDATE 句でWHERE、 から @Original_ProductID に使用されるパラメーター名を@ProductID変更します。 これらの変更を行った後、ストアド プロシージャ内の T-SQL は次のようになります。

ALTER PROCEDURE dbo.Products_Update
(
    @ProductName nvarchar(40),
    @SupplierID int,
    @CategoryID int,
    @QuantityPerUnit nvarchar(20),
    @UnitPrice money,
    @UnitsInStock smallint,
    @UnitsOnOrder smallint,
    @ReorderLevel smallint,
    @Discontinued bit,
    @ProductID int
)
AS
    SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, 
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, 
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, 
    [Discontinued] = @Discontinued 
WHERE (([ProductID] = @ProductID));
    
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued 
FROM Products 
WHERE (ProductID = @ProductID)

これらの変更をデータベースに保存するには、ツール バーの [保存] アイコンをクリックするか、Ctrl + S キーを押します。 この時点で、 Products_Update ストアド プロシージャは入力パラメーターを @Original_ProductID 想定していませんが、TableAdapter はこのようなパラメーターを渡すよう構成されています。 TableAdapter がストアド プロシージャに送信するProducts_Updateパラメーターを確認するには、DataSet Designerで TableAdapter を選択し、プロパティ ウィンドウに移動し、コレクションParameters内の省略記号をUpdateCommandクリックします。 これにより、図 14 に示す [パラメーター コレクションエディター] ダイアログ ボックスが表示されます。

Parameters コレクションエディター Lists Products_Update ストアド プロシージャに渡されるパラメーター

図 14: ストアド プロシージャに渡されるProducts_Updateパラメーターエディター Lists Parameters コレクション

メンバーの一覧からパラメーターを選択し、[削除] ボタンを @Original_ProductID クリックするだけで、このパラメーターをここから削除できます。

または、Designerで TableAdapter を右クリックし、[構成] を選択して、すべてのメソッドに使用されるパラメーターを更新することもできます。 これにより、TableAdapter 構成ウィザードが表示され、ストアド プロシージャが受け取るパラメーターと共に、選択、挿入、更新、削除に使用されるストアド プロシージャが一覧表示されます。 [更新] ドロップダウン リストをクリックすると、ストアド プロシージャに Products_Update 必要な入力パラメーターが表示されます。これは、現在は含め @Original_ProductID なくなりました (図 15 を参照)。 [完了] をクリックするだけで、TableAdapter で使用されるパラメーター コレクションが自動的に更新されます。

または、TableAdapter の構成ウィザードを使用して、そのメソッド パラメーター コレクションを更新することもできます

図 15: TableAdapter の構成ウィザードを使用してメソッド パラメーター コレクションを更新することもできます (フルサイズの画像を表示する をクリックします)。

手順 5: 追加の TableAdapter メソッドを追加する

手順 2 で説明したように、新しい TableAdapter を作成する場合、対応するストアド プロシージャを自動的に生成するのは簡単です。 TableAdapter にメソッドを追加する場合も同様です。 これを説明するために、手順 2 で作成した に メソッドをProductsTableAdapter追加GetProductByProductID(productID)します。 このメソッドは値を ProductID 入力として受け取り、指定された製品に関する詳細を返します。

まず、TableAdapter を右クリックし、コンテキスト メニューから [クエリの追加] を選択します。

TableAdapter に新しいクエリを追加する

図 16: TableAdapter に新しいクエリを追加する

これにより、TableAdapter クエリ構成ウィザードが起動します。このウィザードでは、最初に TableAdapter がデータベースにアクセスする方法を確認するプロンプトが表示されます。 新しいストアド プロシージャを作成するには、[新しいストアド プロシージャの作成] オプションを選択し、[次へ] をクリックします。

[新しいストアド プロシージャの作成] オプションを選択する

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

次の画面では、実行するクエリの種類、一連の行または単一のスカラー値を返すかどうか、または 、INSERT、または DELETE ステートメントをUPDATE実行するかどうかを識別するように求められます。 メソッドは行を GetProductByProductID(productID) 返すので、行を返す SELECT オプションを選択したままにし、[次へ] をクリックします。

行を返す SELECT オプションを選択する

図 18: 行オプションを返す SELECT を選択します (フルサイズの画像を表示する 場合はクリックします)

次の画面には、TableAdapter の メイン クエリが表示され、ストアド プロシージャdbo.Products_Selectの名前 () が一覧表示されます。 ストアド プロシージャ名を次 SELECT のステートメントに置き換えます。このステートメントは、指定した製品のすべての製品フィールドを返します。

SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

ストアド プロシージャ名を SELECT クエリに置き換える

図 19: ストアド プロシージャ名をクエリに SELECT 置き換える (クリックするとフルサイズの画像が表示されます)

次の画面では、作成されるストアド プロシージャに名前を付けるよう求められます。 名前 Products_SelectByProductID を入力し、[次へ] をクリックします。

新しいストアド プロシージャに名前を付Products_SelectByProductID

図 20: 新しいストアド プロシージャ Products_SelectByProductID に名前を付ける (クリックするとフルサイズの画像が表示されます)

ウィザードの最後の手順では、生成されたメソッド名を変更し、Fill a DataTable パターン、Return a DataTable パターン、またはその両方を使用するかどうかを指定できます。 このメソッドでは、両方のオプションをオンのままにしますが、メソッドの名前を と GetProductByProductIDにFillByProductID変更します。 [次へ] をクリックしてウィザードが実行する手順の概要を表示し、[完了] をクリックしてウィザードを完了します。

TableAdapter s メソッドの名前を FillByProductID と GetProductByProductID に変更する

図 21: TableAdapter のメソッドの名前を と GetProductByProductID にFillByProductID変更します (フルサイズの画像を表示する をクリックします)

ウィザードが完了すると、TableAdapter には新しいメソッドが用意 GetProductByProductID(productID) されています。このメソッドが呼び出されると、作成したばかりのストアド プロシージャが実行 Products_SelectByProductID されます。 [ストアド プロシージャ] フォルダーにドリルインして開Products_SelectByProductIDいてサーバー エクスプローラーからこの新しいストアド プロシージャを表示します (表示されない場合は、[ストアド プロシージャ] フォルダーを右クリックし、[更新] を選択します)。

ストアド プロシージャは SelectByProductID 入力パラメーターとして を受け取り @ProductID 、ウィザードで入力したステートメントを実行 SELECT します。

ALTER PROCEDURE dbo.Products_SelectByProductID
(
    @ProductID int
)
AS
    SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID, 
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
       ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

手順 6: ビジネス ロジック レイヤー クラスを作成する

チュートリアル シリーズ全体を通して、プレゼンテーション層がビジネス ロジック 層 (BLL) のすべての呼び出しを行う階層構造のアーキテクチャを維持するよう努めていました。 この設計上の決定に従うには、プレゼンテーション層から製品データにアクセスする前に、まず新しい型指定された DataSet の BLL クラスを作成する必要があります。

フォルダーに という名前 ProductsBLLWithSprocs.vb の新しいクラス ファイルを ~/App_Code/BLL 作成し、次のコードを追加します。

Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class ProductsBLLWithSprocs
    Private _productsAdapter As ProductsTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As ProductsTableAdapter
        Get
            If _productsAdapter Is Nothing Then
                _productsAdapter = New ProductsTableAdapter()
            End If
            Return _productsAdapter
        End Get
    End Property
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, True)> _
    Public Function GetProducts() As NorthwindWithSprocs.ProductsDataTable
        Return Adapter.GetProducts()
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
    Public Function GetProductByProductID(ByVal productID As Integer) _
        As NorthwindWithSprocs.ProductsDataTable
        Return Adapter.GetProductByProductID(productID)
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function AddProduct _
        (ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
         ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
         ByVal unitPrice As Nullable(Of Decimal), _
         ByVal unitsInStock As Nullable(Of Short), _
         ByVal unitsOnOrder As Nullable(Of Short), _
         ByVal reorderLevel As Nullable(Of Short), _
         ByVal discontinued As Boolean) _
         As Boolean
         
        ' Create a new ProductRow instance
        Dim products As New NorthwindWithSprocs.ProductsDataTable()
        Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
        product.ProductName = productName
        If Not supplierID.HasValue Then 
            product.SetSupplierIDNull() 
        Else 
            product.SupplierID = supplierID.Value 
        End If
        If Not categoryID.HasValue Then 
            product.SetCategoryIDNull() 
        Else 
            product.CategoryID = categoryID.Value 
        End If
        If quantityPerUnit Is Nothing Then 
            product.SetQuantityPerUnitNull() 
        Else 
            product.QuantityPerUnit = quantityPerUnit 
        End If
        If Not unitPrice.HasValue Then 
            product.SetUnitPriceNull() 
        Else 
            product.UnitPrice = unitPrice.Value 
        End If
        If Not unitsInStock.HasValue Then 
            product.SetUnitsInStockNull() 
        Else 
            product.UnitsInStock = unitsInStock.Value 
        End If
        If Not unitsOnOrder.HasValue Then 
            product.SetUnitsOnOrderNull() 
        Else 
            product.UnitsOnOrder = unitsOnOrder.Value 
        End If
        If Not reorderLevel.HasValue Then 
            product.SetReorderLevelNull() 
        Else 
            product.ReorderLevel = reorderLevel.Value 
        End If
        product.Discontinued = discontinued
        ' Add the new product
        products.AddProductsRow(product)
        Dim rowsAffected As Integer = Adapter.Update(products)
        ' Return true if precisely one row was inserted, otherwise false
        Return rowsAffected = 1
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Update, True)> _
    Public Function UpdateProduct
        (ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
         ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
         ByVal unitPrice As Nullable(Of Decimal), _
         ByVal unitsInStock As Nullable(Of Short), _
         ByVal unitsOnOrder As Nullable(Of Short), _
         ByVal reorderLevel As Nullable(Of Short), _
         ByVal discontinued As Boolean, ByVal productID As Integer) _
         As Boolean
         
        Dim products As NorthwindWithSprocs.ProductsDataTable = _
            Adapter.GetProductByProductID(productID)
        If products.Count = 0 Then
            ' no matching record found, return false
            Return False
        End If
        Dim product As NorthwindWithSprocs.ProductsRow = products(0)
        product.ProductName = productName
        If Not supplierID.HasValue Then 
            product.SetSupplierIDNull() 
        Else 
            product.SupplierID = supplierID.Value 
        End If
        If Not categoryID.HasValue Then 
            product.SetCategoryIDNull() 
        Else 
            product.CategoryID = categoryID.Value 
        End If
        If quantityPerUnit Is Nothing Then 
            product.SetQuantityPerUnitNull() 
        Else 
            product.QuantityPerUnit = quantityPerUnit 
        End If
        If Not unitPrice.HasValue Then 
            product.SetUnitPriceNull() 
        Else 
            product.UnitPrice = unitPrice.Value 
        End If
        If Not unitsInStock.HasValue Then 
            product.SetUnitsInStockNull() 
        Else 
            product.UnitsInStock = unitsInStock.Value 
        End If
        If Not unitsOnOrder.HasValue Then 
            product.SetUnitsOnOrderNull() 
        Else 
            product.UnitsOnOrder = unitsOnOrder.Value 
        End If
        If Not reorderLevel.HasValue Then 
            product.SetReorderLevelNull() 
        Else 
            product.ReorderLevel = reorderLevel.Value 
        End If
        product.Discontinued = discontinued
        ' Update the product record
        Dim rowsAffected As Integer = Adapter.Update(product)
        ' Return true if precisely one row was updated, otherwise false
        Return rowsAffected = 1
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Delete, True)> _
    Public Function DeleteProduct(ByVal productID As Integer) As Boolean
        Dim rowsAffected As Integer = Adapter.Delete(productID)
        ' Return true if precisely one row was deleted, otherwise false
        Return rowsAffected = 1
    End Function
End Class

このクラスは、前のチュートリアルの ProductsBLL クラス セマンティクスを模倣しますが、DataSet の ProductsTableAdapter オブジェクトと ProductsDataTable オブジェクトを NorthwindWithSprocs 使用します。 たとえば、クラス ファイルProductsBLLの先頭に ステートメントを含めるのではなくImports NorthwindTableAdapters、 クラスで ProductsBLLWithSprocs を使用Imports NorthwindWithSprocsTableAdaptersします。 同様に ProductsDataTable 、このクラスで使用される オブジェクトと ProductsRow オブジェクトには、 名前空間が NorthwindWithSprocs プレフィックスとして付けられます。 クラスにはProductsBLLWithSprocs、2 つのデータ アクセス メソッドとGetProductByProductID、 GetProducts 1 つの製品インスタンスを追加、更新、および削除するための メソッドが用意されています。

手順 7: プレゼンテーション レイヤーからNorthwindWithSprocsDataSet を操作する

この時点で、ストアド プロシージャを使用して基になるデータベース データにアクセスして変更する DAL を作成しました。 また、すべての製品または特定の製品を取得するメソッドと、製品を追加、更新、削除するためのメソッドを使用して、基本的な BLL を構築しました。 このチュートリアルを切り捨てるには、BLL s ProductsBLLWithSprocs クラスを使用してレコードの表示、更新、削除を行う ASP.NET ページを作成します。

フォルダー内のNewSprocs.aspxページをAdvancedDAL開き、ツールボックスから Designerに GridView をドラッグし、 という名前を付けるProducts。 GridView のスマート タグから、 という名前 ProductsDataSourceの新しい ObjectDataSource にバインドすることを選択します。 図 22 に示すように、 クラスを ProductsBLLWithSprocs 使用するように ObjectDataSource を構成します。

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

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

[選択] タブのドロップダウン リストには、 GetProducts と の 2 つのオプションがあります GetProductByProductID。 GridView ですべての製品を表示する場合は、 メソッドを選択します GetProducts 。 UPDATE タブ、INSERT タブ、DELETE タブのドロップダウン リストには、それぞれ 1 つのメソッドしかありません。 これらの各ドロップダウン リストで適切な方法が選択されていることを確認し、[完了] をクリックします。

ObjectDataSource ウィザードが完了すると、Visual Studio によって、製品データ フィールドの GridView に BoundFields と CheckBoxField が追加されます。 GridView の組み込みの編集および削除機能を有効にするには、スマート タグに存在する [編集を有効にする] オプションと [削除を有効にする] オプションをオンにします。

このページには、編集と削除のサポートが有効になっている GridView が含まれています

図 23: ページには、編集と削除のサポートが有効になっている GridView が含まれています (フルサイズの画像を表示する をクリックします)

前のチュートリアルで説明したように、ObjectDataSource のウィザードが完了すると、Visual Studio によって プロパティが OldValuesParameterFormatString original_{0}に設定されます。 これは、BLL 内の {0} メソッドで想定されるパラメーターを指定して、データ変更機能が正しく機能するためには、 の既定値に戻す必要があります。 そのため、必ず プロパティを OldValuesParameterFormatString に {0} 設定するか、宣言構文からプロパティを完全に削除してください。

データ ソースの構成ウィザードを完了し、GridView でサポートの編集と削除を有効にし、ObjectDataSource の OldValuesParameterFormatString プロパティを既定値に戻すと、ページの宣言型マークアップは次のようになります。

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" 
    DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
            InsertVisible="False" ReadOnly="True" 
            SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" 
            SortExpression="SupplierID" />
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" 
            SortExpression="CategoryID" />
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" 
            SortExpression="QuantityPerUnit" />
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" 
            SortExpression="UnitPrice" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" 
            SortExpression="UnitsInStock" />
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" 
            SortExpression="UnitsOnOrder" />
        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" 
            SortExpression="ReorderLevel" />
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" 
    DeleteMethod="DeleteProduct" InsertMethod="AddProduct" 
    SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs" 
    UpdateMethod="UpdateProduct">
    <DeleteParameters>
        <asp:Parameter Name="productID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
        <asp:Parameter Name="productID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="productName" Type="String" />
        <asp:Parameter Name="supplierID" Type="Int32" />
        <asp:Parameter Name="categoryID" Type="Int32" />
        <asp:Parameter Name="quantityPerUnit" Type="String" />
        <asp:Parameter Name="unitPrice" Type="Decimal" />
        <asp:Parameter Name="unitsInStock" Type="Int16" />
        <asp:Parameter Name="unitsOnOrder" Type="Int16" />
        <asp:Parameter Name="reorderLevel" Type="Int16" />
        <asp:Parameter Name="discontinued" Type="Boolean" />
    </InsertParameters>
</asp:ObjectDataSource>

この時点で、検証を含むように編集インターフェイスをカスタマイズし、 CategoryID 列と SupplierID 列を DropDownLists としてレンダリングすることで、GridView を整理できました。 クライアント側の確認を [削除] ボタンに追加することもできます。これらの機能強化を実装する時間を取ることをお勧めします。 これらのトピックは以前のチュートリアルで取り上げられていますが、ここでは再び説明しません。

GridView を強化するかどうかに関係なく、ブラウザーでページのコア機能をテストします。 図 24 に示すように、ページには、行ごとの編集と削除機能を提供する GridView の製品が一覧表示されます。

GridView から製品を表示、編集、および削除できる

図 24: GridView から表示、編集、および削除できる製品 (フルサイズの画像を表示するをクリックします)

まとめ

型指定された DataSet の TableAdapters は、アドホック SQL ステートメントまたはストアド プロシージャを使用して、データベースのデータにアクセスできます。 ストアド プロシージャを操作する場合は、既存のストアド プロシージャを使用するか、TableAdapter ウィザードでクエリに基づいて SELECT 新しいストアド プロシージャを作成するように指示できます。 このチュートリアルでは、ストアド プロシージャを自動的に作成する方法について説明しました。

ストアド プロシージャを自動生成すると時間の節約に役立ちますが、ウィザードによって作成されたストアド プロシージャが、独自に作成したストアド プロシージャと一致しない場合があります。 1 つの例として、パラメーターがProducts_Update余分であっても、 と @ProductID の両方@Original_ProductIDの入力パラメーターが@Original_ProductID必要なストアド プロシージャがあります。

多くのシナリオでは、ストアド プロシージャは既に作成されている可能性があります。または、ストアド プロシージャのコマンドを細かく制御できるように手動でビルドすることもできます。 どちらの場合も、TableAdapter にメソッドに既存のストアド プロシージャを使用するように指示します。 これを行う方法については、次のチュートリアルで説明します。

プログラミングに満足!

もっと読む

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

著者について

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

特別な感謝

このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、ヒルトン ガイゼナウでした。 今後の MSDN の記事を確認することに関心がありますか? その場合は、 にmitchell@4GuysFromRolla.com行をドロップしてください。

前へ次へ