データアダプタコマンドのパラメータ

[アーティクル]
09/17/2008

更新 : 2007 年 11 月

多くの場合、SelectCommand、InsertCommand、UpdateCommand、および DeleteCommand オブジェクトの CommandText プロパティに定義されるデータアダプタのコマンドには、パラメータがあります。実行時に、パラメータを使用して、コマンドが表す SQL ステートメントやストアドプロシージャに値を渡します。

メモ :
以前のバージョンの Visual Studio では、アプリケーションとデータベースの通信にデータアダプタが使用されていました。データアダプタは現在も .NET Framework データプロバイダ (ADO.NET) の主要なコンポーネントですが、TableAdapter はデザイナで生成されるコンポーネントで、アプリケーションとデータベースの間でデータを移動する処理を簡略化します。TableAdapter の操作方法の詳細については、「TableAdapter の概要」を参照してください。

以前のバージョンの Visual Studio では、アプリケーションとデータベースの通信にデータアダプタが使用されていました。データアダプタは現在も .NET Framework データプロバイダ (ADO.NET) の主要なコンポーネントですが、TableAdapter はデザイナで生成されるコンポーネントで、アプリケーションとデータベースの間でデータを移動する処理を簡略化します。TableAdapter の操作方法の詳細については、「TableAdapter の概要」を参照してください。

パラメータは 2 つのコンテキストで使用されます。

選択パラメータ - 実際のアプリケーションでは、データベース内のデータのサブセットだけをフェッチすることが頻繁にあります。そのためには、実行時に取得する選択基準のパラメータを持つ WHERE 句を含む SQL ステートメントまたはストアドプロシージャを使用します。また、レコードを更新または削除するときには、変更対象のレコードを指定する WHERE 句を使用します。WHERE 句で使用される値は、通常は実行時に取得されます。

更新パラメータ - 既存のレコードを更新する場合、または新しいレコードを挿入する場合に、変更するレコードまたは新しいレコードの列の値を実行時に確立します。また、オプティミスティック同時実行制御のチェック中に使用される値は、パラメータを使用して確立されます。

メモ :
Oracle では、SQL ステートメントやストアドプロシージャで名前付きパラメータを使用する場合、パラメータ名の前にコロン (:) を付ける必要があります。ただし、コード内の他の場所にある名前付きパラメータを参照する場合 (Add を呼び出す場合など) には、名前付きパラメータの前にコロン (:) を付けないでください。データプロバイダが自動的にコロンを追加します。詳細については、「OracleParameter クラス」を参照してください。

Oracle では、SQL ステートメントやストアドプロシージャで名前付きパラメータを使用する場合、パラメータ名の前にコロン (:) を付ける必要があります。ただし、コード内の他の場所にある名前付きパラメータを参照する場合 (Add を呼び出す場合など) には、名前付きパラメータの前にコロン (:) を付けないでください。データプロバイダが自動的にコロンを追加します。詳細については、「OracleParameter クラス」を参照してください。

選択パラメータ

データセットに格納するレコードを選択するときは、多くの場合、WHERE 句にパラメータを含めて、フェッチするレコードを実行時に指定できるようにします。たとえば、ユーザーが特定のタイトルキーワードを Web ページに入力して本のデータベースを検索するとします。それを可能にするには、次に示すような SQL ステートメントを SelectCommand の CommandText プロパティとして指定します。パラメータはプレースホルダ (疑問符 (?)) または名前付きパラメータ変数によって示されます。OleDbCommand オブジェクトおよび OdbcCommand オブジェクトを使用したクエリのパラメータには、疑問符を使用します。SqlCommand オブジェクトを使用したクエリには、@ 記号で始まる名前付きパラメータを使用します。OracleCommand オブジェクトの場合は、コロン (:) で始まる名前付きパラメータを使用します。

プレースホルダを使用したクエリの例を次に示します。

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

SqlCommand 名前付きパラメータを使用するクエリの例を次に示します。

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

OracleCommand 名前付きパラメータを使用するクエリの例を次に示します。

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

アプリケーションで、ユーザーにタイトルキーワードの入力を要求します。そして、パラメータの値を設定してコマンドを実行します。

メモ :
ルックアップテーブルを設定する場合など、データベーステーブル全体の内容を取得する必要がある場合もあります。しかし一般には、アプリケーションの効率を高めるために、必要なデータだけをフェッチします。

Visual Studio では、クエリビルダを使用してパラメータを持つ SQL ステートメントを作成できます。サーバーエクスプローラから要素をドラッグすると、可能な部分については、Visual Studio によって自動的にパラメータが設定されます。設定は手動で完成させる必要があります。

更新パラメータ

アダプタの SelectCommand オブジェクトにパラメータ化されたコマンドが含まれるかどうかに関係なく、UpdateCommand、InsertCommand、DeleteCommand の各プロパティに対するコマンドには常にパラメータが含まれます。

UpdateCommand プロパティおよび InsertCommand プロパティに対するコマンドでは、データベース内で更新するすべての列に対してパラメータが必要です。また、UpdateCommand ステートメントおよび DeleteCommand ステートメントでは、多くの SelectCommand オブジェクトと同様に、更新するレコードを識別するためのパラメータ化された WHERE 句が必要です。

ユーザーが本を購入できるアプリケーションを考えます。ユーザーは買い物をする間、買い物カゴを保持しますが、これはデータテーブルとして実装されます。ShoppingCart テーブルにおいて、ユーザーは購入するそれぞれの本に対するレコードを保持します。このレコードには書籍 ID と顧客 ID が含まれ、買い物カゴレコードのキーとして使用されます。

ユーザーが買い物カゴに本を追加すると、アプリケーションは SQL INSERT ステートメントを呼び出します。アダプタでのステートメントの構文は次のようになります。

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

3 つの疑問符 (?) は、顧客 ID、書籍 ID、および数量の値を実行時に取得するパラメータのプレースホルダを示しています。名前付きパラメータを使用している場合は、同じクエリが次のようになります。

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

ユーザーが買い物カゴ内の項目を変更する場合 (たとえば、数量の変更など)、アプリケーションは SQL UPDATE ステートメントを呼び出します。このステートメントの構文は次のようになります。

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

また、名前付きパラメータを使用している場合は次のようになります。

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

このステートメントにおいて、SET 句のパラメータには変更するレコードの新しい値が入ります。WHERE 句のパラメータは、どのレコードを更新するかを識別するものであり、レコードの元の値が入ります。

ユーザーは、買い物カゴから項目を削除することもできます。その場合、パラメータのプレースホルダを使用しているときは、アプリケーションは次のような構文で SQL DELETE ステートメントを呼び出します。

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

名前付きパラメータを使用しているときは、次のようになります。

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

パラメータコレクションとパラメータオブジェクト

実行時にパラメータの値を渡すことができるように、データアダプタの 4 つのコマンドオブジェクトはそれぞれ Parameters プロパティをサポートしています。このプロパティには、ステートメント内のプレースホルダと一対一で対応する各パラメータオブジェクトのコレクションが含まれます。

各データアダプタに対応するパラメータコレクションを次の表に示します。

データアダプタ	パラメータコレクション
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

メモ :
Oracle では、SQL ステートメントやストアドプロシージャで名前付きパラメータを使用する場合、パラメータ名の前にコロン (:) を付ける必要があります。ただし、コード内の他の場所にある名前付きパラメータを参照する場合 (Add を呼び出す場合など) には、名前付きパラメータの前にコロン (:) を付けないでください。.NET Framework Oracle 用データプロバイダが自動的にコロンを追加します。

Oracle では、SQL ステートメントやストアドプロシージャで名前付きパラメータを使用する場合、パラメータ名の前にコロン (:) を付ける必要があります。ただし、コード内の他の場所にある名前付きパラメータを参照する場合 (Add を呼び出す場合など) には、名前付きパラメータの前にコロン (:) を付けないでください。.NET Framework Oracle 用データプロバイダが自動的にコロンを追加します。

パラメータコレクションを使用することで、実行時の値によって SQL コマンドの文字列を手動で構築する手間が省け、さらに、パラメータで型チェックができるという利点もあります。

データアダプタ構成ウィザードを使用してアダプタを設定する場合は、4 つのアダプタコマンドすべてに対してパラメータコレクションが自動的にセットアップされ、設定されます。サーバーエクスプローラからフォームまたはコンポーネントに要素をドラッグすると、Visual Studio は以下の設定を実行します。

デザイナにテーブルまたは列をドラッグした場合、Visual Studio はパラメータのない SelectCommand オブジェクト (つまり、SQL SELECT ステートメント) と、パラメータ化された UpdateCommand、InsertCommand、DeleteCommand の各オブジェクトを生成します。SelectCommand オブジェクトステートメントにパラメータを含める場合は、手動で設定できます。
デザイナにストアドプロシージャをドラッグした場合、Visual Studio は、ストアドプロシージャの必要に応じてパラメータを持つ SelectCommand オブジェクトを生成します。ただし、UpdateCommand、InsertCommand、DeleteCommand の各オブジェクトが必要な場合は、各オブジェクトをパラメータと共に自分で設定する必要があります。

一般に、アダプタに対してパラメータ化されたクエリを作成する場合は、データアダプタ構成ウィザードを使用してください。ただし、必要な場合は [プロパティ] ウィンドウを使用してパラメータを手動で設定することもできます。

パラメータコレクションの構造

コマンドパラメータコレクション内の項目は、対応するコマンドオブジェクトに必要なパラメータと一対一で対応しています。コマンドオブジェクトが SQL ステートメントである場合、コレクション内の項目はステートメント内のプレースホルダ (疑問符 (?)) に対応します。次の UPDATE ステートメントには、5 つのパラメータ項目のコレクションが必要です。

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

名前付きパラメータを使用すると、同じステートメントが次のようになります。

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

コマンドオブジェクトがストアドプロシージャを参照する場合、コレクション内のパラメータ項目の数はプロシージャ自体によって決定されます。パラメータは、SQL ステートメント内のプレースホルダと厳密に対応しない場合もあります。

ストアドプロシージャでは、名前付きパラメータも使用できます。その場合、コレクション内のパラメータの位置は重要ではありません。その代わり、コレクション内の各パラメータ項目には ParameterName プロパティがあり、それを使用してストアドプロシージャ内の対応するパラメータと一致させます。

パラメータコレクションを手動で設定する場合は、ストアドプロシージャでどのパラメータが必要なのかを正確に理解している必要があります。多くのストアドプロシージャは値を返します。その場合、値はパラメータコレクション内でアプリケーションに返されるため、それを可能にしておく必要があります。さらに、ストアドプロシージャには複数の SQL ステートメントが含まれる場合があり、パラメータコレクションがプロシージャ内のすべてのステートメントに渡されるすべての値を反映する必要があります。

ストアドプロシージャでパラメータに名前が付いていない場合、コレクション内の項目はコマンドで必要なパラメータと位置的に対応します。コマンドがストアドプロシージャであり、値を返す場合、コレクション内の最初の項目 (項目 0) はこの戻り値のために確保されます。

したがって、それぞれのパラメータオブジェクトはコレクション内のインデックス位置によって参照できます。ただし、パラメータオブジェクトは、パラメータをその順序と関係なく参照するための ParameterName プロパティもサポートしています。たとえば、コレクションの 2 番目のパラメータが Title_Keyword という名前である場合、次の 2 つのステートメントは等しくなります。

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

一般に、インデックス値でパラメータを参照するよりも、パラメータ名を使用する方がプログラミング手法としては優れています。なぜなら、パラメータの数が変更されたときの必要な保守作業が減り、また、ストアドプロシージャが値を返すかどうかを覚えておく必要がないからです。パラメータを名前で参照する場合はインデックス値で参照する場合よりもわずかにオーバーヘッドが増えますが、これはプログラミングの簡単さとアプリケーションの保守性によって相殺されます。

パラメータ値の確立

パラメータの値を確立するには、次の 2 つの方法があります。

パラメータの Value プロパティを明示的に設定する。
パラメータをデータセットテーブルの列に割り当て、必要なときにデータ行から値を抽出できるようにする。

データセットに値を格納する場合やコマンドを呼び出す場合 (つまり、選択パラメータの場合)、パラメータの値を明示的に設定します。たとえば、前に示した本を検索する例において、アプリケーションはユーザーがタイトルキーワードを入力するテキストボックスを表示できます。その場合、パラメータの値をテキストボックスのテキストに明示的に設定してから、アダプタの Fill メソッドを呼び出します。これを行うコードは次のようになります。このコードは、テキストボックスの内容をパラメータとして確立してから、データセットに値を格納します。

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

割り当てられたパラメータ値は更新時に使用されます。アダプタの Update メソッドを呼び出すと、メソッドはデータセットテーブル内の各レコードに対して個別に適切な更新 (更新、挿入、削除) を行います。その場合、パラメータの値はデータセットレコード内の列として既に存在しています。たとえば、更新プロセスがデータベーステーブル内の新しいレコード (データベースで INSERT ステートメントを呼び出す必要のあるレコード) に達した場合、INSERT ステートメントの VALUE 句に対する値をレコードから直接読み取ることができます。

これらは一般的なシナリオですが、それがすべてではありません。ストアドプロシージャは、out パラメータを使用して、またはプロシージャの戻り値を使用してデータを返す場合があります。そのような場合、返された値をデータセットテーブルの列に割り当てる必要があります。

更新パラメータも明示的に設定することが可能です。アダプタは、行が更新されるたびに呼び出される RowUpdating イベントをサポートしています。このイベントのハンドラを作成して、その中でパラメータ値を設定できます。それにより、パラメータ値を精密に制御でき、パラメータ値の作成などのプロセスをデータベースレコードに書き込む前に動的に実行できます。