配置 SqlParameter 示例
System.Data.SqlClient 支持从 DataTable、DbDataReader 或 IList 对象填充表值参数。必须通过使用 SqlParameter 的 TypeName 属性指定表值参数的类型名称。TypeName 必须与以前在服务器上创建的兼容类型的名称相匹配。下面的代码段演示如何配置 SqlParameter 以插入数据。
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";
Visual Basic
复制代码
' 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 中派生的任何对象,将数据行流处理到表值参数,如本代码段所示:
C#
复制代码
// Configure the SqlCommand and table-valued parameter.
SqlCommand insertCommand = new SqlCommand(
"usp_InsertCategories", connection);
= CommandType.StoredProcedure;
SqlParameter tvpParam =
insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", dataReader);
tvpParam.SqlDbType = SqlDbType.Structured;
Visual Basic
复制代码
' Configure the SqlCommand and table-valued parameter.
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)
= CommandType.StoredProcedure
Dim tvpParam As SqlParameter = _
insertCommand.Parameters.AddWithValue("@tvpNewCategories", _
dataReader)
tvpParam.SqlDbType = SqlDbType.Structured
将表值参数传递给存储过程
此示例演示如何将表值参数数据传递给存储过程。示例代码通过使用 GetChanges 方法,将已添加的行提取到新的 DataTable 中。然后,示例代码定义一个 SqlCommand,并将 CommandType 属性设置为 StoredProcedure。示例代码通过使用 AddWithValue 方法对 SqlParameter 进行填充,并将 SqlDbType 设置为 Structured。然后,通过使用 ExecuteNonQuery 方法执行 SqlCommand。
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);
= CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
// Execute the command.
insertCommand.ExecuteNonQuery();
}
Visual Basic
复制代码
' 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)
= 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 必须与以前在服务器上创建的兼容类型的名称相匹配。此示例中的代码使用 TypeName 属性来引用 dbo.CategoryTableType 中定义的类型结构。
说明:
如果为表值参数中的标识列提供值,则必须为该会话发出 SET IDENTITY_INSERT 语句。
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();
}
Visual Basic
复制代码
' 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 结果集作为表值参数传递给存储过程。
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);
= CommandType.StoredProcedure;
SqlParameter tvpParam =
insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", oracleReader);
tvpParam.SqlDbType = SqlDbType.Structured;
// Execute the command.
insertCommand.ExecuteNonQuery();
看看对你有帮助吗