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

  • [アーティクル]

作成者: Scott Mitchell

PDF のダウンロード

前のチュートリアルでは、コードに 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

Add the ASP.NET Pages for the Advanced Data Access Layer Scenarios Tutorials

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

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

Add the SectionLevelTutorialListing.ascx User Control to 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 シナリオ」チュートリアル用の項目が追加されました。

The Site Map Now Includes Entries for the Advanced DAL Scenarios Tutorials

図 3: サイト マップに、「高度な DAL シナリオ」チュートリアル用のエントリが追加された

ステップ 2: TableAdapter を構成して新しいストアド プロシージャを作成する

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

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

Add a New Typed DataSet to the Project Named NorthwindWithSprocs.xsd

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

これにより、新しい型指定された DataSet が作成されます。そのデザイナーを開き、新しい TableAdapter を作成し、TableAdapter 構成ウィザードを立ち上げます。 TableAdapter 構成ウィザードの最初のステップでは、使用するデータベースを選択するように求められます。 Northwind データベースへの接続文字列がドロップダウン リストに表示されます。 これを選択し、[次へ] をクリックします。

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

Instruct the TableAdapter to Create New Stored Procedures

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

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

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

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

Enter the SELECT Query

図 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 のレコードを取得します。

Leave the Generate Insert, Update and Delete statements Option Checked

図 7: [INSERT、UPDATE、DELETE ステートメントを生成する] オプションをオンのままにする

Note

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

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

Rename the Stored Procedures

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

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

Preview the SQL Script Used to Generate the Stored Procedures

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

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

Name the Methods Fill and GetProducts

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

[次へ] をクリックすると、ウィザードで実行されるステップの概要が表示されます。 [完了] ボタンをクリックしてウィザードを完了します。 ウィザードが完了すると、DataSet のデザイナーに戻ります。デザイナーには ProductsDataTable が追加されています。

The DataSet s Designer Shows the Newly Added ProductsDataTable

図 11: DataSet のデザイナーには、新たに追加された ProductsDataTable が表示されている (クリックするとフルサイズの画像が表示されます)

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

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

The Four Stored Procedures Created in Step 2 Can Be Found in the Database s Stored Procedures Folder

図 12: ステップ 2 で作成された 4 つのストアド プロシージャが、データベースのストアド プロシージャ フォルダーにあるのがわかる

Note

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

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

Stored Procedures Can Be Opened and Modified From Within Visual Studio

図 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 に示す [パラメーター コレクション エディター] ダイアログ ボックスが表示されます。

The Parameters Collection Editor Lists the Parameters Used Passed to the Products_Update Stored Procedure

図 14: パラメーター コレクション エディターに、Products_Update ストアド プロシージャに渡されるパラメーターの一覧が表示されている

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

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

You Can Alternatively Use the TableAdapter s Configuration Wizard to Refresh Its Methods Parameter Collections

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

ステップ 5: その他の TableAdapter メソッドを追加する

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

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

Add a New Query to the TableAdapter

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

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

Choose the Create a new stored procedure Option

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

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

Choose the SELECT which returns row Option

図 18: [行を返す SELECT] オプションを選択する (クリックするとフルサイズの画像が表示されます)

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

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

Replace the Stored Procedure Name with a SELECT Query

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

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

Name the New Stored Procedure Products_SelectByProductID

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

ウィザードの最後のステップでは、生成されたメソッド名の変更や、[DataTable にデータを格納する] パターン または [DataTable を返す] パターンを使用するか、あるいはその両方を使用するかの指定を行えます。 このメソッドでは、両方のオプションをオンのままにしますが、メソッドの名前を FillByProductID と GetProductByProductID に変更します。 [次へ] をクリックしてウィザードが実行するステップの概要を表示してから、[完了] をクリックしてウィザードを完了します。

Rename the TableAdapter s Methods to FillByProductID and 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 クラスを使用するように構成します。

Configure the ObjectDataSource to Use the ProductsBLLWithSprocs Class

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

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

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

The Page Contains a GridView with Editing and Deleting Support Enabled

図 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 で製品が一覧表示され、行ごとに編集と削除の機能が設けられています。

The Products Can Be Viewed, Edited, and Deleted from the 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行をドロップしてください。

前へ次へ