sql server talbe valued parameters (tvp)

SQL Server 2008 中的表值參數 (ADO.NET)

表值參數提供一種將用戶端應用程式中的多行資料封送到 SQL Server 的簡單方式，而不需要多次往返或特殊伺服器端邏輯來處理資料。您可以使用表值參數來封裝用戶端應用程式中的資料行，並使用單個參數化命令將資料發送到伺服器。傳入的資料行儲存在一個表變數中，然後您可以通過使用 Transact-SQL 對該表變數進行操作。

可以使用標準的 Transact-SQL SELECT 語句來訪問表值參數中的列值。表值參數為強型別，其結構會自動進行驗證。表值參數的大小僅受伺服器記憶體的限制。

說明
無法在表值參數中返回資料。表值參數是只可輸入的參數；不支援 OUTPUT 關鍵字。

有關表值參數的更多資訊，請參見下列資源。

資源	說明
Table-Valued Parameters (Database Engine)（表值參數 [資料庫引擎]），位於 SQL Server 聯機叢書中	說明如何建立和使用表值參數。
User-Defined Table Types（使用者定義的表類型），位於 SQL Server 聯機叢書中	說明用於聲明表值參數的使用者定義的表類型。
CodePlex 的 Microsoft SQL Server Database Engine（Microsoft SQL Server 資料庫引擎）一節	包含示範如何使用 SQL Server 特性和功能的樣本。

在 SQL Server 的早期版本中傳遞多行

在 SQL Server 2008 中引入表值參數之前，用於將多行資料傳遞到預存程序或參數化 SQL 命令的選項受到限制。開發人員可以選擇使用以下選項，將多個行傳遞給伺服器：

• 使用一系列單個參數表示多個資料列和行中的值。使用此方法傳遞的資料量受所允許的參數數量的限制。SQL Server 過程最多可以有 2100 個參數。必須使用伺服器端邏輯才能將這些單個值組合到表變數或暫存資料表中以進行處理。

• 將多個資料值捆綁到分隔字串或 XML 文檔中，然後將這些文本值傳遞給過程或語句。此過程要求相應的過程或語句包括驗證資料結構和取消捆綁值所需的邏輯。

• 針對影響多個行的資料修改建立一系列的單個 SQL 陳述式，例如通過調用 SqlDataAdapter 的 Update 方法建立的內容。可將更改單獨提交給伺服器，也可以將其作為組進行批處理。不過，即使是以包含多個語句的批處理形式提交的，每個語句在伺服器上還是會單獨執行。

• 使用 bcp 工具 + 生產力程式或 SqlBulkCopy 對象將很多行資料載入到表中。儘管這項技術非常有效，但不支援伺服器端處理，除非將資料載入到暫存資料表或表變數中。

建立表值參數類型

表值參數以通過使用 Transact-SQL CREATE TYPE 語句定義的強型別表結構為基礎。您必須先在 SQL Server 中建立一個表類型並定義結構，才能在用戶端應用程式中使用表值參數。有關建立表類型的更多資訊，請參見 SQL Server 2008 聯機叢書中的 User-Defined Table Types（使用者定義的表類型）。

下面的語句可建立一個名為 CategoryTableType 的表類型，其中包括 CategoryID 和 CategoryName 列：

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";

您也可以使用從 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;

將表值參數傳遞給預存程序

此樣本示範如何將表值參數資料傳遞給預存程序。範例程式碼通過使用 GetChanges 方法，將已添加的行提取到新的 DataTable 中。然後，範例程式碼定義一個 SqlCommand，並將 CommandType 屬性設定為 StoredProcedure。範例程式碼通過使用 AddWithValue 方法對 SqlParameter 進行填充，並將 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();}

將表值參數傳遞給參數化 SQL 陳述式

下面的樣本示範如何通過使用帶有 SELECT 子查詢（具有作為資料來源的表值參數）的 INSERT 語句將資料插入 dbo.Categories 表中。將表值參數傳遞給參數化 SQL 陳述式時，必須通過使用 SqlParameter 的新 TypeName 屬性指定表值參數的類型名稱。此 TypeName 必須與以前在伺服器上建立的相容類型的名稱相匹配。此樣本中的代碼使用 TypeName 屬性來引用 dbo.CategoryTableType 中定義的類型結構。

說明
如果為表值參數中的識別欄位提供值，則必須為該會話發出 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();}

 

使用 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();

 

 參考地址：http://msdn.microsoft.com/zh-cn/library/bb675163.aspx