型指定された DataSet の TableAdapters に新しいストアド プロシージャを作成する (VB)
前のチュートリアルでは、コードに SQL ステートメントを作成し、実行するデータベースにこのステートメントを渡しました。 別の方法として、データベースで SQL ステートメントを事前定義するストアド プロシージャを使用します。 このチュートリアルでは、TableAdapter ウィザードで新しいストアド プロシージャを生成する方法について学習します。
はじめに
このチュートリアル群のデータ アクセス層 (DAL) では、型指定された DataSet を使用します。 「データ アクセス層を作成する」チュートリアルで説明されているように、型指定された DataSet は厳密に型指定された DataTable と TableAdapter で構成されます。 DataTable がシステム内の論理エンティティを表すのに対し、TableAdapter は基になるデータベースとのインターフェイスとしてデータ アクセス作業を実行します。 これには、DataTable へのデータの追加、スカラー データを返すクエリの実行、データベースのレコードの挿入、更新、削除が含まれます。
TableAdapter によって実行される SQL コマンドとしては、アドホック SQL ステートメント (SELECT columnList FROM TableName
など) またはストアド プロシージャを使用できます。 このアーキテクチャの TableAdapter では、アドホック SQL ステートメントが使用されます。 ただし、多くの開発者やデータベース管理者は、セキュリティ、保守容易性、更新可能性などの理由から、アドホック SQL ステートメントよりもストアド プロシージャの方を好みます。 その柔軟性のためにアドホック SQL ステートメントを熱心に支持する人たちもいます。 私自身の仕事では、アドホック SQL ステートメントよりもストアド プロシージャをよく使用しますが、前のチュートリアルを単純化するために、アドホック SQL ステートメントを使用することを選択しました。
TableAdapter を定義したり、新しいメソッドを追加したりする場合に、TableAdapter ウィザードを使用すると、アドホック SQL ステートメントを使用するのと同じくらいに、新しいストアド プロシージャの作成や既存のストアド プロシージャの使用が容易になります。 このチュートリアルでは、TableAdapter ウィザードでストアド プロシージャを自動生成する方法について説明します。 次のチュートリアルでは、既存のストアド プロシージャや手動で作成されたストアド プロシージャを使用するために、TableAdapter のメソッドを構成する方法について説明します。
Note
ストアド プロシージャとアドホック SQL の長所と短所に関する活発な議論については、Rob Howard のブログ記事「ストアド プロシージャをまだ使わないのですか?」と Frans Bouma のブログ記事「ストアド プロシージャは良くないって?」をご覧ください。
ストアド プロシージャの基本
関数は、すべてのプログラミング言語に共通する構成概念です。 関数は、関数が呼び出されたときに実行されるステートメントのコレクションです。 関数は入力パラメーターを受け取り、任意に値を返すことができます。 "ストアド プロシージャ" は、プログラミング言語における関数と多くの類似点を共有するデータベースの構成概念です。 ストアド プロシージャは、ストアド プロシージャが呼び出されたときに実行される一連の T-SQL ステートメントで構成されています。 ストアド プロシージャは、0 個以上の入力パラメーターを受け取り、スカラー値、出力パラメーター、または最も一般的には、SELECT
クエリの結果セットを返すことができます。
Note
ストアド プロシージャは、SPROC や SP と呼ばれることがよくあります。
ストアド プロシージャは、CREATE PROCEDURE
T-SQL ステートメントを使用して作成されます。 たとえば、次の T-SQL スクリプトは、@CategoryID
という名前のパラメーターを 1 つ受け取り、CategoryID
と一致する値を持つ Products
テーブルの列の ProductID
、ProductName
、UnitPrice
、Discontinued
フィールドを返す、GetProductsByCategoryID
という名前のストアド プロシージャを作成します。
CREATE PROCEDURE GetProductsByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
このストアド プロシージャが作成されたら、次の構文を使用して呼び出すことができます。
EXEC GetProductsByCategory categoryID
Note
次のチュートリアルでは、Visual Studio IDE を使用したストアド プロシージャの作成について取り上げます。 ただし、このチュートリアルでは、TableAdapter ウィザードでストアド プロシージャを自動的に生成できるようにします。
ストアド プロシージャは、単にデータを返すだけでなく、多くの場合、1 つのトランザクションのスコープ内で複数のデータベース コマンドを実行するために使用されます。 たとえば、DeleteCategory
という名前のストアド プロシージャは、@CategoryID
パラメーターを取り込み、2 つの DELETE
ステートメントを実行します。1 つ 目は関連製品を削除し、2 つ 目は指定したカテゴリを削除します。 ストアド プロシージャ内の複数のステートメントは、トランザクション内に自動的にラップされるわけではありません。 ストアド プロシージャの複数のコマンドをアトミック操作として扱うようにするためには、追加の T-SQL コマンドを発行する必要があります。 トランザクションのスコープ内でストアド プロシージャの複数のコマンドをラップする方法については、後続のチュートリアルで説明します。
アーキテクチャ内でストアド プロシージャが使用される場合、データ アクセス層のメソッドは、アドホック SQL ステートメントを発行するよりも、特定のストアド プロシージャを呼び出します。 これにより、(データベース上で) 実行される SQL ステートメントの場所はアプリケーションのアーキテクチャ内で定義されるのではなく、1 か所に集められます。 この一元化により、クエリの検索、分析、チューニングが確実に容易になり、データベースが使用されている場所と方法について、はるかに明確に把握できるようになると考えられます。
ストアド プロシージャの基礎についてのさらに詳細な情報は、このチュートリアルの最後にある「関連項目」セクションのリソースを参照してください。
ステップ 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
図 1: 「高度なデータ アクセス層のシナリオ」チュートリアル用の ASP.NET ページを追加する
他のフォルダーと同様に、AdvancedDAL
フォルダーの 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 シナリオ」チュートリアル用の項目が追加されました。
図 3: サイト マップに、「高度な DAL シナリオ」チュートリアル用のエントリが追加された
ステップ 2: TableAdapter を構成して新しいストアド プロシージャを作成する
アドホック SQL ステートメントの代わりにストアド プロシージャを使用したデータ アクセス層の作成について具体的に説明するために、~/App_Code/DAL
フォルダーに NorthwindWithSprocs.xsd
という名前の新しい型指定された DataSet を作成します。 このプロセスについては前のチュートリアルで詳しく説明したので、ここのステップの説明はすばやく進めます。 型指定された DataSet の作成と構成に関してうまくいかず、詳細な手順が必要な場合は、「データ アクセス層の作成」に関するチュートリアルを再度、参照してください。
DAL
フォルダーを右クリックし、[新しい項目の追加] を選択し、図 4 に示す DataSet テンプレートを選択して、新しいデータセットをプロジェクトに追加します。
図 4: NorthwindWithSprocs.xsd
という名前の新しい型指定された DataSet をプロジェクトに追加する (クリックするとフルサイズの画像が表示されます)
これにより、新しい型指定された DataSet が作成されます。そのデザイナーを開き、新しい TableAdapter を作成し、TableAdapter 構成ウィザードを立ち上げます。 TableAdapter 構成ウィザードの最初のステップでは、使用するデータベースを選択するように求められます。 Northwind データベースへの接続文字列がドロップダウン リストに表示されます。 これを選択し、[次へ] をクリックします。
この次の画面で、TableAdapter のデータベースにアクセスする方法を選択できます。 前のチュートリアルでは、最初のオプションである [SQL ステートメントを使用する] を選択しました。 このチュートリアルでは、2 番目のオプションである [新しいストアド プロシージャを作成する] を選択し、[次へ] をクリックします。
図 5: TableAdapter に新しいストアド プロシージャの作成を指示する (クリックするとフルサイズの画像が表示されます)
アドホック SQL ステートメントを使用する場合と同様に、次の手順では TableAdapter のメイン クエリの SELECT
ステートメントを指定するように求められます。 ただし、ここで入力した SELECT
ステートメントを使用して、直接アドホック クエリを実行する代わりに、TableAdapter ウィザードによって、この SELECT
クエリを含むストアド プロシージャを作成します。
この TableAdapter には、次の SELECT
クエリを使用します。
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
図 6: SELECT
クエリを入力する (クリックするとフルサイズの画像が表示されます)
Note
上記のクエリは、Northwind
型指定された DataSet の ProductsTableAdapter
のメイン クエリとは若干異なります。 Northwind
型指定された DataSet の ProductsTableAdapter
には、各製品のカテゴリとサプライヤーに相当するカテゴリ名と会社名を取得するための 2 つの相関サブクエリが含まれていることを思い出してください。 今後の「TableAdapter を更新してJOIN を使用する」チュートリアルで、この関連データをこの TableAdapter に追加することについて取り上げます。
[詳細オプション] ボタンをクリックします。 ここでは、ウィザードで TableAdapter の INSERT、UPDATE、DELETE の各ステートメントの生成も行うかどうか、オプティミスティック同時実行制御を使用するかどうか、挿入および更新後にデータ テーブルを更新するかどうかを指定することができます。 [INSERT、UPDATE、DELETE ステートメントを生成する] オプションは、デフォルトでオンになってます。 これはオンのままにしておきます。 このチュートリアルでは、[オプティミスティック同時実行制御を使用する] オプションはオフのままにします。
TableAdapter ウィザードによってストアド プロシージャを自動的に作成する場合、[データ テーブルを更新する] オプションは無視されます。 このチェックボックスのチェックの有無に関わらず、結果として得られる挿入および更新のストアド プロシージャは、ステップ 3 で示すように、just-inserted または just-updated のレコードを取得します。
図 7: [INSERT、UPDATE、DELETE ステートメントを生成する] オプションをオンのままにする
Note
[オプティミスティック同時実行制御を使用する] オプションをオンにすると、ウィザードは追加の条件を WHERE
句に追加して、他のフィールドに変更があった場合にデータが更新されないようにします。 TableAdapter の組み込みのオプティミスティック同時実行制御機能の使用の詳細については、「オプティミスティック同時実行制御を実装する」チュートリアルを参照してください。
SELECT
クエリを入力し、[INSERT、UPDATE、DELETE ステートメントを生成する] オプションがオンになっていることを確認したら、[次へ] をクリックします。 図 8 に示す次の画面では、ウィザードによって、データの選択、挿入、更新、削除用に作成される各ストアド プロシージャの名前の入力を求められます。 これらのストアド プロシージャの名前を Products_Select
、Products_Insert
、Products_Update
、Products_Delete
に変更します。
図 8: ストアド プロシージャの名前を変更する (クリックするとフルサイズの画像が表示されます)
TableAdapter ウィザードで 4 つのストアド プロシージャの作成に使用される T-SQL を表示するには、[SQL スクリプトのプレビュー] ボタンをクリックします。 [SQL スクリプトのプレビュー] ダイアログ ボックスで、スクリプトのファイルへの保存やクリップボードへのコピーを行えます。
図 9: ストアド プロシージャの生成に使用される SQL スクリプトのプレビュー
ストアド プロシージャに名前を付けた後、[次へ] をクリックして TableAdapter の対応するメソッドに名前を付けます。 アドホック SQL ステートメントを使用する場合と同様に、既存の DataTable に格納したり、新しい DataTable を返したりするメソッドを作成できます。 また、TableAdapter に、レコードを挿入、更新、削除するための DB-Direct パターンを含めるべきかどうかを指定することもできます。 3 つのチェックボックスはすべてオンのままにしますが、[DataTable を返す] メソッドの名前を GetProducts
に変更します (図 10 に示すように)。
図 10: メソッドに Fill
と GetProducts
という名前を付ける (クリックするとフルサイズの画像が表示されます)
[次へ] をクリックすると、ウィザードで実行されるステップの概要が表示されます。 [完了] ボタンをクリックしてウィザードを完了します。 ウィザードが完了すると、DataSet のデザイナーに戻ります。デザイナーには ProductsDataTable
が追加されています。
図 11: DataSet のデザイナーには、新たに追加された ProductsDataTable
が表示されている (クリックするとフルサイズの画像が表示されます)
ステップ 3: 新しく作成されたストアド プロシージャを調べる
ステップ 2 で使用した TableAdapter ウィザードによって、データの選択、挿入、更新、削除のためのストアド プロシージャが自動的に作成されました。 これらのストアド プロシージャは、サーバー エクスプローラーに移動し、データベースの [ストアド プロシージャ] フォルダーをドリルダウン表示することで、Visual Studio で表示や変更を行えます。 図 12 に示すように、Northwind データベースには、Products_Delete
、Products_Insert
、Products_Select
、Products_Update
の 4 つの新しいストアド プロシージャが含まれています。
図 12: ステップ 2 で作成された 4 つのストアド プロシージャが、データベースのストアド プロシージャ フォルダーにあるのがわかる
Note
サーバー エクスプローラーが表示されない場合は、[表示] メニューに移動し、[サーバー エクスプローラー] オプションを選択します。 ステップ 2 で追加された製品関連のストアド プロシージャが表示されない場合は、[ストアド プロシージャ] フォルダーを右クリックし、[更新する] を選択してみてください。
ストアド プロシージャを表示または変更するには、サーバー エクスプローラーでその名前をダブルクリックするか、そのストアド プロシージャを右クリックして [開く] を選択します。 図 13 は、開かれた Products_Delete
ストアド プロシージャを示しています。
図 13: Visual Studio 内でストアド プロシージャを開き、変更することができる (クリックするとフルサイズの画像が表示されます)
Products_Delete
ストアド プロシージャと Products_Select
ストアド プロシージャの内容はどちらも非常に単純です。 一方、Products_Insert
ストアド プロシージャと Products_Update
ストアド プロシージャは、INSERT
と UPDATE
のステートメントの後に SELECT
ステートメントを実行するため、より詳細に調べる必要があります。 たとえば、次の 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
を含む) を返します。 この更新機能は、バッチ更新パターンを使用して新しいレコードを追加する場合に便利です。新しく追加された ProductRow
インスタンスの ProductID
プロパティは、データベースによって割り当てられた自動インクリメント値で自動的に更新されます。
次のコードはこの機能を示す例です。 これには、NorthwindWithSprocs
型指定された DataSet に対して作成された ProductsTableAdapter
と ProductsDataTable
が含まれています。 ProductsRow
インスタンスが作成され、その値を指定し、TableAdapter の Update
メソッドを呼び出して、ProductsDataTable
を渡すことで、新しい製品がデータベースに追加されます。 内部では、TableAdapter の Update
メソッドが、渡された DataTable 内の ProductsRow
インスタンスを列挙し (この例では、追加したインスタンスは 1 つだけ)、挿入、更新、削除のいずれかの適切なコマンドを実行します。 この例では、Products_Insert
ストアド プロシージャが実行され、新しいレコードが Products
テーブルに追加され、新しく追加されたレコードの詳細が返されます。 その後、ProductsRow
インスタンスの ProductID
値が更新されます。 Update
メソッドが完了したら、ProductsRow
の ProductID
プロパティを使用して、新しく追加されたレコードの ProductID
値にアクセスできます。
' 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
ストアド プロシージャの場合も同様に、UPDATE
ステートメントの後に SELECT
ステートメントが含まれます。
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
に @Original_ProductID
と @ProductID
の 2 つの入力パラメーターが含まれていることに注意してください。 この機能により、主キーが変更される場合のシナリオが可能になります。 たとえば、従業員データベースでは、従業員の社会保障番号を各従業員レコードの主キーとして使用する場合があります。 既存の従業員の社会保障番号を変更するには、新しい社会保障番号と元の社会保障番号の両方を指定する必要があります。 Products
テーブルの場合、ProductID
列は IDENTITY
列であり、変更されることがないため、このような機能は必要ありません。 実際、Products_Update
ストアド プロシージャの UPDATE
ステートメントには、列リストに ProductID
列は含まれていません。 したがって、@Original_ProductID
は UPDATE
ステートメントの WHERE
句では使用されますが、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 デザイナーで TableAdapter を選択し、プロパティ ウィンドウに移動して、UpdateCommand
の Parameters
コレクションの省略記号をクリックします。 これにより、図 14 に示す [パラメーター コレクション エディター] ダイアログ ボックスが表示されます。
図 14: パラメーター コレクション エディターに、Products_Update
ストアド プロシージャに渡されるパラメーターの一覧が表示されている
ここで、メンバーの一覧から @Original_ProductID
パラメーターを選択し、[削除] ボタンをクリックするだけで、このパラメーターを削除できます。
または、デザイナーで TableAdapter を右クリックし、[構成] を選択して、すべてのメソッドで使用されるパラメーターを更新することもできます。 これにより、TableAdapter 構成ウィザードが表示され、ストアド プロシージャが受け取るパラメーターと共に、選択、挿入、更新、削除に使用されるストアド プロシージャが一覧表示されます。 [更新] のドロップダウン リストをクリックすると、Products_Update
ストアド プロシージャの入力パラメーターが表示されます。このパラメーターには、もう @Original_ProductID
は含まれていません (図 15 を参照)。 [完了] をクリックすれば、TableAdapter で使用されるパラメーター コレクションが自動的に更新されます。
図 15: TableAdapter の構成ウィザードを使用して、メソッドのパラメーター コレクションを更新することもできる (クリックするとフルサイズの画像が表示されます)
ステップ 5: その他の TableAdapter メソッドを追加する
ステップ 2 で説明したとおり、新しい TableAdapter を作成するときに、対応するストアド プロシージャを自動生成することは容易です。 これは TableAdapter にメソッドを追加する場合も同様です。 この説明のために、ステップ 2 で作成した ProductsTableAdapter
に GetProductByProductID(productID)
メソッドを追加します。 このメソッドでは、入力として ProductID
値を受け取り、指定された製品に関する詳細情報を返します。
まず、TableAdapter を右クリックし、コンテキスト メニューから [クエリの追加] を選択します。
図 16: TableAdapter に新しいクエリを追加する
これにより、TableAdapter クエリ構成ウィザードが起動します。このウィザードでは、最初に TableAdapter のデータベースにアクセスする方法を尋ねるプロンプトが表示されます。 新しいストアド プロシージャを作成するには、[新しいストアド プロシージャを作成する] オプションを選択し、[次へ] をクリックします。
図 17: [新しいストアド プロシージャを作成する] オプションを選択する (クリックするとフルサイズの画像が表示されます)
次の画面では、行のセットまたは単一のスカラー値を返すか、または UPDATE
、INSERT
、DELETE
のステートメントを実行するかという、実行するクエリの種類を決定するよう求められます。 GetProductByProductID(productID)
メソッドは行を返すので、[行を返す SELECT] オプションを選択したままにして、[次へ] をクリックします。
図 18: [行を返す SELECT] オプションを選択する (クリックするとフルサイズの画像が表示されます)
次の画面には、TableAdapter の メイン クエリが表示されます。このクエリには、ストアド プロシージャの名前 (dbo.Products_Select
) だけが表示されています。 このストアド プロシージャ名を次の SELECT
ステートメントに置き換えます。このステートメントは、指定した製品のすべての製品フィールドを返します。
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
図 19: ストアド プロシージャ名を SELECT
クエリに置き換える (クリックするとフルサイズの画像が表示されます)
次の画面では、作成するストアド プロシージャの名前を指定するように求められます。 名前の Products_SelectByProductID
を入力して、[次へ] をクリックします。
図 20: 新しいストアド プロシージャに Products_SelectByProductID
という名前を付ける (クリックするとフルサイズの画像が表示されます)
ウィザードの最後のステップでは、生成されたメソッド名の変更や、[DataTable にデータを格納する] パターン または [DataTable を返す] パターンを使用するか、あるいはその両方を使用するかの指定を行えます。 このメソッドでは、両方のオプションをオンのままにしますが、メソッドの名前を FillByProductID
と GetProductByProductID
に変更します。 [次へ] をクリックしてウィザードが実行するステップの概要を表示してから、[完了] をクリックしてウィザードを完了します。
図 21: TableAdapter のメソッドの名前を FillByProductID
と GetProductByProductID
に変更する (クリックするとフルサイズの画像が表示されます)
ウィザードが完了すると、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 クラスを作成する必要があります。
~/App_Code/BLL
フォルダーに ProductsBLLWithSprocs.vb
という名前の新しいクラス ファイルを 作成し、次のコードを追加します。
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
クラス セマンティクスを模倣しますが、NorthwindWithSprocs
DataSet の ProductsTableAdapter
オブジェクトと ProductsDataTable
オブジェクトを使用します。 たとえば、ProductsBLL
のように、クラス ファイルの先頭に Imports NorthwindTableAdapters
ステートメントを含めるのではなく、ProductsBLLWithSprocs
クラスでは Imports NorthwindWithSprocsTableAdapters
が使用されます。 同様に、このクラスで使用される ProductsDataTable
オブジェクトと ProductsRow
オブジェクトには、NorthwindWithSprocs
名前空間のプレフィックスが付けられます。 この ProductsBLLWithSprocs
クラスには、GetProducts
と GetProductByProductID
の 2 つのデータ アクセス メソッドと、 1 つの製品インスタンスを追加、更新、削除するための各メソッドが用意されています。
ステップ 7: プレゼンテーション層から NorthwindWithSprocs
DataSet を操作する
この時点で、ストアド プロシージャを使用して基になるデータベース データにアクセスして変更する DAL を作成しています。 さらに、すべての製品または特定の製品を取得するメソッドと、製品を追加、更新、削除するためのメソッドを備えた基本的な BLL も構築されています。 このチュートリアルの締めくくりとして、レコードの表示、更新、削除に BLL の ProductsBLLWithSprocs
クラスを使用する ASP.NET ページを作成しましょう。
AdvancedDAL
フォルダー内の NewSprocs.aspx
ページを開き、ツールボックスからデザイナーに GridView をドラッグし、Products
と名前を付けます。 GridView のスマート タグで、ProductsDataSource
という名前の新しい ObjectDataSource にバインドすることを選択します。 図 22 に示すように、この ObjectDataSource を ProductsBLLWithSprocs
クラスを使用するように構成します。
図 8: ProductsBLLWithSprocs
クラスを使用するように ObjectDataSource を構成する (クリックするとフルサイズの画像が表示されます)
[SELECT] タブのドロップダウン リストには、GetProducts
と GetProductByProductID
の 2 つのオプションがあります。 GridView にすべての製品を表示したいので、GetProducts
メソッドを選択します。 [UPDATE]、[INSERT]、[DELETE] タブのドロップダウン リストには、それぞれ 1 つのメソッドのみが含まれています。 これらの各ドロップダウン リストで適切なメソッドが選択されていることを確認し、[完了] をクリックします。
ObjectDataSource ウィザードが完了すると、Visual Studio では、製品データ フィールドの GridView に BoundField と CheckBoxField が追加されます。 スマート タグにある [編集を有効にする] と [削除を有効にする] のオプションをオンにして、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
列を DropDownList としてレンダリングしたりなどを行って、GridView を整理することができます。 また、[削除] ボタンにクライアント側の確認を追加することもできます。時間を割いて、このような機能強化を実装することをお勧めします。 ただし、こういったトピックは前のチュートリアルで取り上げたので、ここで改めて取り上げることはしません。
GridView を強化するかどうかに関わらず、ブラウザーでページのコア機能をテストします。 図 24 に示すように、ページでは、GridView で製品が一覧表示され、行ごとに編集と削除の機能が設けられています。
図 24: 製品を GridView から表示、編集、削除できる (クリックするとフルサイズの画像が表示されます)
まとめ
型指定された DataSet の TableAdapter は、アドホック SQL ステートメントまたはストアド プロシージャを使用して、データベースのデータにアクセスできます。 ストアド プロシージャを使用する場合は、既存のストアド プロシージャを使用することも、SELECT
クエリに基づいて新しいストアド プロシージャを作成するように TableAdapter ウィザードに指示することもできます。 このチュートリアルでは、ストアド プロシージャを自動的に作成する方法について説明しました。
ストアド プロシージャを自動生成すると時間の節約になりますが、ウィザードによって作成されたストアド プロシージャは、独自に作成したものとは合わない場合があります。 1 つの例として Products_Update
ストアド プロシージャがあります。このストアド プロシージャでは、@Original_ProductID
パラメーターが必要なくても、@Original_ProductID
と @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見つけることができます。
特別な感謝
このチュートリアル シリーズは、多くの役に立つ校閲者によってレビューされました。 このチュートリアルのリード レビュー担当者は、Hilton Geisenow 氏でした。 今後の MSDN の記事を確認することに関心がありますか? その場合は、 にmitchell@4GuysFromRolla.com行をドロップしてください。