Table-Valued Parameters(表值类型参数，Sqlserver中表值类型参数应用包括前台，数据库)

Table-Valued Parameters

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

在不要求多次循环和服务器端特殊的数据处理逻辑的前提下，表值类型参数提供了一种可以从客户端应用程序很容易访问sqlserver服务器端多行数据的方式，你可以使用表值类型参数封装客户端数据行，然后通过一个参数化的命令发送数据到服务器端。传入到服务器端的数据被存储在表变量中，这个表变量可以稍后通过使用Tansact-SQL进行操作。

 

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

表类型参数的列值可以通过标准Transact-SQL的SELECT 语法访问。表值类型参数是一个强类型，其结构是自动被验证的。表值类型参数的大小只是受服务器端的内存大小限制。

 

Note:

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

 

注意：

你不能在表值类型参数中返回数据，表值类型参数仅是输入的，关键字OUTPUT不被表值类型参数支持。

 

For more information about table-valued parameters, see the following resources.

关于表值类型参数更多的信息，请看下面的资源： 

Resource	Description
Table-Valued Parameters (Database Engine) in SQL Server Books Online	Describes how to create and use table-valued parameters.   描述如何创建和使用表值类型参数
User-Defined Table Types in SQL Server Books Online	Describes user-defined table types that are used to declare table-valued parameters.   描述用户自定义表类型以及用它生命表值类型参数
The Microsoft SQL Server Database Engine section of CodePlex	Contains samples that demonstrate how to use SQL Server features and functionality.   包含示例，演示如何使用SQL Server的特性和功能。

Passing Multiple Rows in Previous Versions of SQL Server（在早期版本的SQL Server 中传递多行数据）

Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. A developer could choose from the following options for passing multiple rows to the server:

• Use a series of individual parameters to represent the values in multiple columns and rows of data. The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server procedures can have, at most, 2100 parameters. Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing. 
• Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values. 
• Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. Changes can be submitted to the server individually or batched into groups. However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server. 
• Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable. 

在表值类型参数被引入SQL Server 2008之前，传递多行数据到存储过程或者参数化的SQL命令中的可供选择的选项是受限制的，一个开发人员可以从下列的选项中选择一个传递多行数据到服务器:

• 使用一系列的独立参数代表一行数据中的多个列的值。 用这种方法可传递数据量受允许参数个数限制。SQL Server存储过程最多可以有2100个参数。服务器端逻辑被要求组装这些单独的值放入表变量或者临时表中进行处理。 
• 收集多数据值放入一个分隔字符串或者XML文档中，然后传递这些文本值到存储过程或语句中。这就要求存储过程或者语句包含必要的验证逻辑来验证数据，并且解绑这些值。 
• 为影响多行的数据更改创建一系列对应的单独的SQL语句，例如，那些通过调用SqlDataAdapter的Update方法 创建的。更改可以单独的向服务器提交或者在一个组中一批的提交到服务器。然而，尽管包含多个语句更改在一批中提交到服务器，可是每个语句在服务器中也是分开执行的。 
• 使用bcp(bulk copy program)功能程序或者是SqlBulkCopy对象去加载多行数据到数据表中。尽管这种技术是非常有效的，但是它不支持服务器端处理，除非这些数据被加载到了临时表或者表变量中。 

 

Creating Table-Valued Parameter Types（创建表值参数类型）

Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

表值类型参数是一种通过使用Tansact-SQL CREATE TYPE语句定义的强类型表结构。在客户端使用表值类型参数之前，你必须在Sql Server服务器上创建表类型和定义结构。有关创建表类型的更多信息，请看在Sql Server在线书籍的 User-Defined Table Types 

 

CREATE TYPE dbo.CategoryTableType AS TABLE

    ( CategoryID int, CategoryName nvarchar(50) )

 

After you create a table type, you can declare table-valued parameters based on that type. The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. Note that the READONLY keyword is required for declaring a table-valued parameter.

在你创建了表类型后，你可以基于这个类型声明表值类型参数。下面的Tansact-SQL代码段演示了如何在存储过程定义时声明表值类型参数。注意关键字READONLY对定义一个表值类型参数是必须的。

 

CREATE PROCEDURE usp_UpdateCategories 

    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modifying Data with Table-Valued Parameters (Transact-SQL)（使用表值类型参数修改数据）

Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. For example, you can select all the rows in a table-valued parameter and insert them into a database table, or you can create an update statement by joining a table-valued parameter to the table you want to update.

表示类型参数能够被用来进行成数据集的数据修改，通过执行一个语句影响多行数据。例如，你可以查询出标志类型参数的所有行然后将他们插入一个数据库的数据表中。或者，你可以通过一个连接表值类型参数到数据表来创建一个更新语句进行更新。

 

The following Transact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it, as shown here, where the table-valued parameter is aliased as "ec":

下面的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;

This Transact-SQL example demonstrates how to select rows from a table-valued parameter to perform an INSERT in a single set-based operation.

INSERT INTO dbo.Categories (CategoryID, CategoryName)

    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;

Limitations of Table-Valued Parameters（表值类型参数的限制）

There are several limitations to table-valued parameters:

• You cannot pass table-valued parameters to CLR user-defined functions.
• Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
• Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable. 
• You cannot use ALTER TABLE statements to modify the design of table-valued parameters. 

表类型参数的几个限制：

• 你不能传递表值类型参数到CLR用户定义函数。
• 表值类型参数仅能通过支持UNIQUE（唯一）或者PRIMARY KEY(主键)约束进行索引。在表值类型参数上SQL Server不维护统计数据。
• 在Tansact-SQL 代码中表值类型参数是只读的。你不能更新表值类型参数数据行中的列值，并且也不能进行插入和删除。为了可以修改传递到存储过程中数据类型参数，你必须将这些数据插入到临时表或表变量中。 
• 你不能使用ALTER TABLE语句修改表值类型参数的设计。 

Configuring a SqlParameter Example（配置SqlParameter示例）

System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable<SqlDataRecord> (IEnumerable objects. You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. The TypeName must match the name of a compatible type previously created on the server. The following code fragment demonstrates how to configure SqlParameter to insert data.

 

C#

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

 

You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter, as shown in this fragment:

C#

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

Passing a Table-Valued Parameter to a Stored Procedure（传递一个存储过程的表值类型参数）

This example demonstrates how to pass table-valued parameter data to a stored procedure. The code extracts added rows into a new DataTable by using the GetChanges method. The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. The SqlCommand is then executed by using the ExecuteNonQuery method.

C#

// 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();

}

Passing a Table-Valued Parameter to a Parameterized SQL Statement（传递一个参数化的SQL语句的表值类型参数）

The following example demonstrates how to insert data into the dbo.Categories table by using an INSERT statement with a SELECT subquery that has a table-valued parameter as the data source. When passing a table-valued parameter to a parameterized SQL statement, you must specify a type name for the table-valued parameter by using the new TypeName property of a SqlParameter. This TypeName must match the name of a compatible type previously created on the server. The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

Note:

If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

C#

// 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();

}

Streaming Rows with a DataReader（通过DataReader传递行数据流）

You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. The SqlDbType property of the SqlParameter is set to Structured. The AddWithValue passes theOracleDataReader result set to the stored procedure as a table-valued parameter.

C#

// 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();