SQL Server 2008 のテーブル値パラメータ (ADO.NET)

  • [アーティクル]

更新 : November 2007

テーブル値パラメータを使用すると、ラウンド トリップを何度も繰り返したり、サーバー側にデータを処理するための特殊なロジックを組み込んだりすることなく、複数行のデータをクライアント アプリケーションから SQL Server へと簡単にマーシャリングできます。テーブル値パラメータを使用すると、クライアント アプリケーションのデータ行をカプセル化して単一のパラメータ化コマンドでサーバーに送ることができます。受信データ行はテーブル変数に格納され、Transact-SQL によって操作できるようになります。

テーブル値パラメータの列値には、Transact-SQL の標準的な SELECT ステートメントを使ってアクセスできます。テーブル値パラメータは厳密に型指定されており、その構造は自動的に検証されます。テーブル値パラメータのサイズは、サーバーのメモリによってのみ制限されます。

Bb675163.alert_note(ja-jp,VS.90).gifメモ :

テーブル値パラメータでデータを返すことはできません。テーブル値パラメータは入力専用です。OUTPUT キーワードはサポートされません。

テーブル値パラメータの詳細については、次のリソースを参照してください。

リソース

説明

テーブル値パラメータ (データベース エンジン) (SQL Server オンライン ブック)

テーブル値パラメータの作成方法および使用方法について説明します。

ユーザー定義テーブル型 (SQL Server オンライン ブック)

テーブル値パラメータを宣言する際に使用するユーザー定義テーブル型について説明します。

CodePlex の「Microsoft SQL Server Database Engine」セクション

SQL Server の機能の使用方法を示すサンプルがあります。

旧バージョンの SQL Server での複数行の受け渡し

SQL Server 2008 でテーブル値パラメータが導入されるまでは、複数行データをストアド プロシージャまたはパラメータ化 SQL コマンドに渡す方法は限られていました。複数行をサーバーに渡す方法には、次のオプションがありました。

  • 複数のデータ列およびデータ行の値を表す一連の個別パラメータを使用する。この方法で渡すことのできるデータの量は、使用可能なパラメータの数によって制限されます。SQL Server プロシージャが持つことのできるパラメータ数は最大 2,100 です。これらの個々の値をテーブル変数または一時テーブルにまとめて処理するには、サーバー側のロジックが必要です。

  • 複数のデータを区切られた文字列または XML ドキュメントとしてまとめ、そのテキスト値をプロシージャまたはステートメントに渡す。これには、データ構造を検証して値を処理するためのロジックをプロシージャまたはステートメントに含める必要があります。

  • 複数の行に影響を与えるデータ変更のための一連の SQL ステートメントを作成する。たとえば、SqlDataAdapter の Update メソッドを呼び出すことによって作成できます。変更はサーバーに個別に送ることもグループにまとめて送ることもできます。ただし、複数のステートメントを含むバッチを送信しても、サーバーでは個々のステートメントが別々に実行されます。

  • bcp ユーティリティ プログラムまたは SqlBulkCopy オブジェクトを使用して、多数行のデータをテーブルに読み込む。この方法は効率的ですが、データが一時テーブルまたはテーブル変数に読み込まれなければ、サーバー側での処理がサポートされません。

テーブル値パラメータ型の作成

テーブル値パラメータは、Transact-SQL の CREATE TYPE ステートメントを使用して定義された厳密に型指定されたテーブルの構造に基づいています。クライアント アプリケーションでテーブル値パラメータを使用するには、まず SQL Server でテーブル型を作成し、その構造を定義する必要があります。テーブル型の作成の詳細については、SQL Server 2008 オンライン ブックの「ユーザー定義テーブル型」を参照してください。

次のステートメントは、CategoryID と CategoryName 列から成る CategoryTableType というテーブル型を作成します。

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

テーブル型を作成したら、その型に基づいてテーブル値パラメータを宣言できます。次の Transact-SQL フラグメントは、ストアド プロシージャ定義の中でテーブル値パラメータを宣言する方法を示しています。テーブル値パラメータの宣言には READONLY キーワードが必要であることに注意してください。

CREATE PROCEDURE usp_UpdateCategories 
    (@tvpNewCategories dbo.CategoryTableType READONLY)

テーブル値パラメータによるデータの変更 (Transact-SQL)

テーブル値パラメータは、単一のステートメントを実行して複数行を操作する、セット ベースのデータ変更の中で使用できます。たとえば、テーブル値パラメータのすべての行を選択し、それらをデータベース テーブルに挿入できます。また、テーブル値パラメータを更新対象のテーブルに結合する更新ステートメントを作成することもできます。

次の Transact-SQL UPDATE ステートメントは、テーブル値パラメータを Categories テーブルに結合して使用する方法を示しています。テーブル値パラメータを FROM 句の JOIN で使用するときは、エイリアスを使用する必要があります。この例ではテーブル値パラメータに "ec" というエイリアスが使用されています。

UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;

この Transact-SQL の例は、単一のセット ベース操作で INSERT を実行するためにテーブル値パラメータから行を選択する方法を示しています。

INSERT INTO dbo.Categories (CategoryID, CategoryName)
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

テーブル値パラメータの制限

テーブル値パラメータにはいくつかの制限があります。

  • テーブル値パラメータをユーザー定義の関数に渡すことはできません。

  • テーブル値パラメータでは、UNIQUE 制約または PRIMARY KEY 制約をサポートするためにのみ、インデックスを付けることができます。SQL Server はテーブル値パラメータの統計を保持しません。

  • テーブル値パラメータは Transact-SQL コードの中では読み取り専用です。テーブル値パラメータの行内の列の値は更新できません。行を挿入することも削除することもできません。テーブル値パラメータ内のストアド プロシージャやパラメータ化ステートメントに渡すデータを変更するには、一時テーブルまたはテーブル変数にデータを挿入する必要があります。

  • ALTER TABLE ステートメントをテーブル値パラメータの設計変更に使用することはできません。

SqlParameter の構成例

System.Data.SqlClient では、テーブル値パラメータのデータを DataTable、DbDataReader、または IList オブジェクトから読み込むことができます。SqlParameter の TypeName プロパティを使用して、テーブル値パラメータの型名を指定する必要があります。TypeName は、既にサーバー上に作成されている、互換性のある型の名前と一致している必要があります。次のコード フラグメントは、データを挿入するための SqlParameter の構成方法を示しています。

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

  ' Configure the command and parameter.
  Dim insertCommand As New SqlCommand(sqlInsert, connection)
  Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
    "@tvpNewCategories", addedCategories)
  tvpParam.SqlDbType = SqlDbType.Structured
  tvpParam.TypeName = "dbo.CategoryTableType"

DbDataReader から派生した任意のオブジェクトを使用して、一連の行データをテーブル値パラメータに挿入することもできます。その方法を次のフラグメントに示します。

 // Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand(
   "usp_InsertCategories", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = 
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", dataReader);
 tvpParam.SqlDbType = SqlDbType.Structured;

' Configure the SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  dataReader)
tvpParam.SqlDbType = SqlDbType.Structured

ストアド プロシージャへのテーブル値パラメータの受け渡し

この例は、テーブル値パラメータのデータをストアド プロシージャに渡す方法を示しています。このコードは、GetChanges メソッドを使用して、追加された行を新しい DataTable に抽出します。次に SqlCommand を定義し、CommandType プロパティを StoredProcedure に設定します。SqlParameter へのデータ入力には AddWithValue メソッドが使用され、SqlDbType は Structured に設定されます。次に ExecuteNonQuery メソッドを使用して SqlCommand が実行されます。

// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}

' Assumes connection is an open SqlConnection object.
Using connection
   '  Create a DataTable with the modified rows.
   Dim addedCategories As DataTable = _
     CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Configure the SqlCommand and SqlParameter.
   Dim insertCommand As New SqlCommand( _
     "usp_InsertCategories", connection)
   insertCommand.CommandType = CommandType.StoredProcedure
   Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
     "@tvpNewCategories", addedCategories)
   tvpParam.SqlDbType = SqlDbType.Structured

   '  Execute the command.
   insertCommand.ExecuteNonQuery()
End Using

パラメータ化 SQL ステートメントへのテーブル値パラメータの受け渡し

次の例は、データ ソースとしてテーブル値パラメータを持つ SELECT サブクエリ付きの INSERT ステートメントを使用して、dbo.Categories テーブルにデータを挿入する方法を示しています。テーブル値パラメータをパラメータ化 SQL ステートメントに渡すときは、SqlParameter の新しい TypeName プロパティを使用して、テーブル値パラメータの型名を指定する必要があります。この TypeName は、既にサーバー上に作成されている、互換性のある型の名前と一致している必要があります。このコード例では、dbo.CategoryTableType で定義されている型の構造を参照するために TypeName プロパティが使用されています。

Bb675163.alert_note(ja-jp,VS.90).gifメモ :

テーブル値パラメータで ID 列の値を指定する場合は、そのセッションの SET IDENTITY_INSERT ステートメントを実行する必要があります。

 
// Assumes connection is an open SqlConnection.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
    DataRowState.Added);

// Define the INSERT-SELECT statement.
string sqlInsert = 
    "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
    + " SELECT nc.CategoryID, nc.CategoryName"
    + " FROM @tvpNewCategories AS nc;"

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

// Execute the command.
insertCommand.ExecuteNonQuery();
}

' Assumes connection is an open SqlConnection.
Using connection
  ' Create a DataTable with the modified rows.
  Dim addedCategories As DataTable = _
    CategoriesDataTable.GetChanges(DataRowState.Added)

  ' Define the INSERT-SELECT statement.
  Dim sqlInsert As String = _
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _
  & " SELECT nc.CategoryID, nc.CategoryName" _
  & " FROM @tvpNewCategories AS nc;"

  ' Configure the command and parameter.
  Dim insertCommand As New SqlCommand(sqlInsert, connection)
  Dim tvpParam As SqlParameter = _
     insertCommand.Parameters.AddWithValue( _
    "@tvpNewCategories", addedCategories)
  tvpParam.SqlDbType = SqlDbType.Structured
  tvpParam.TypeName = "dbo.CategoryTableType"

  ' Execute the query
  insertCommand.ExecuteNonQuery()
End Using

DataReader による行のストリーミング

テーブル値パラメータにデータ行をストリーム出力するには、DbDataReader から派生したオブジェクトを使用します。次のコード フラグメントは、OracleCommand と OracleDataReader を使用して Oracle データベースからデータを取り出す方法を示しています。このコードは次に、単一の入力パラメータを持つストアド プロシージャを呼び出すように SqlCommand を構成します。SqlParameter の SqlDbType プロパティが Structured に設定されます。AddWithValue は OracleDataReader の結果セットをテーブル値パラメータとしてストアド プロシージャに渡します。

// Assumes connection is an open SqlConnection.
// Retrieve data from Oracle.
OracleCommand selectCommand = new OracleCommand(
   "Select CategoryID, CategoryName FROM Categories;",
   oracleConnection);
OracleDataReader oracleReader = selectCommand.ExecuteReader(
   CommandBehavior.CloseConnection);

 // Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand(
   "usp_InsertCategories", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = 
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", oracleReader);
 tvpParam.SqlDbType = SqlDbType.Structured;

 // Execute the command.
 insertCommand.ExecuteNonQuery();

' Assumes connection is an open SqlConnection.
' Retrieve data from Oracle.
Dim selectCommand As New OracleCommand( _
  "Select CategoryID, CategoryName FROM Categories;", _
  oracleConnection)
Dim oracleReader As OracleDataReader = _
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Configure SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
insertCommand.CommandType = CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
  oracleReader)
tvpParam.SqlDbType = SqlDbType.Structured

' Execute the command.
insertCommand.ExecuteNonQuery()

参照

概念

パラメータおよびパラメータのデータ型の構成 (ADO.NET)

DataAdapter パラメータ (ADO.NET)

その他の技術情報

コマンドとパラメータ (ADO.NET)

ADO.NET における SQL Server データ操作