在 SQL Server 2008 中引入表值参数之前，用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。 开发人员可以选择使用以下选项，将多个行传递给服务器：
1. 使用一系列单个参数表示多个数据列和行中的值。 使用此方法传递的数据量受所允许的参数数量的限制。 SQL Server 过程最多可以有 2100 个参数。 必须使用服务器端逻辑才能将这些单个值组合到表变量或临时表中以进行处理。
2. 将多个数据值捆绑到分隔字符串或 XML 文档中，然后将这些文本值传递给过程或语句。 此过程要求相应的过程或语句包括验证数据结构和取消捆绑值所需的逻辑。
3. 针对影响多个行的数据修改创建一系列的单个 SQL 语句，例如通过调用 SqlDataAdapter 的 Update 方法创建的内容。 可将更改单独提交给服务器，也可以将其作为组进行批处理。 不过，即使是以包含多个语句的批处理形式提交的，每个语句在服务器上还是会单独执行。
4. 使用 bcp 实用工具程序或 SqlBulkCopy 对象将很多行数据加载到表中。 尽管这项技术非常有效，但不支持服务器端处理，除非将数据加载到临时表或表变量中。
实际代码上， 我们采用的最多的是第2种， 第1种也有用到，其它两种比较少见。
表值参数为强类型，其结构会自动进行验证。 表值参数的大小仅受服务器内存的限制。
优点
表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。表值参数具有以下优势：
-
首次从客户端填充数据时，不获取锁。
-
提供简单的编程模型。
-
允许在单个例程中包括复杂的业务逻辑。
-
减少到服务器的往返。
-
可以具有不同基数的表结构。
-
是强类型。
-
使客户端可以指定排序顺序和唯一键。
限制
表值参数有下面的限制：
-
SQL Server 不维护表值参数列的统计信息。
-
表值参数必须作为输入 READONLY 参数传递到 Transact-SQL 例程。不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。
-
不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。表值参数可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存储过程中。
| 数据源 | 服务器逻辑 | 行数 | 最佳技术 |
|---|---|---|---|
| 服务器上带格式的数据文件 | 直接插入 | < 1000 | BULK INSERT |
| 服务器上带格式的数据文件 | 直接插入 | > 1000 | BULK INSERT |
| 服务器上带格式的数据文件 | 复杂 | < 1000 | 表值参数 |
| 服务器上带格式的数据文件 | 复杂 | > 1000 | BULK INSERT |
| 远程客户端进程 | 直接插入 | < 1000 | 表值参数 |
| 远程客户端进程 | 直接插入 | > 1000 | BULK INSERT |
| 远程客户端进程 | 复杂 | < 1000 | 表值参数 |
| 远程客户端进程 | 复杂 | > 1000 | 表值参数 |
下面我们来关注一下表值参数的应用及与C#的交互：
一、预备SQL:
--1. 创建测试表
IF OBJECT_ID('Categories') IS NULL
BEGIN
CREATE TABLE Categories(
CategoryID INT PRIMARY KEY,
CategoryName nvarchar(50)
)
INSERT INTO Categories(CategoryID,CategoryName) VALUES(1,N'计算机')
INSERT INTO Categories(CategoryID,CategoryName) VALUES(2,N'哲学')
END
GO
--2. 创建表值参数类型
IF NOT EXISTS (SELECT * FROM sys.types AS t WHERE t.name='type_CategoryTable')
BEGIN
CREATE TYPE dbo.type_CategoryTable AS TABLE(
CategoryID INT,
CategoryName nvarchar(50)
)
END
GO
--3. 创建测试存储过程
IF EXISTS(SELECT * FROM sys.procedures AS p WHERE p.[object_id]=OBJECT_ID('dbo.Proc_UpdateCategories'))
BEGIN
DROP PROC dbo.Proc_UpdateCategories
END
GO
CREATE PROCEDURE dbo.Proc_UpdateCategories (@tvpEditedCategories dbo.type_CategoryTable READONLY)
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.Categories
SET Categories.CategoryName = ec.CategoryName
FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
ON dbo.Categories.CategoryID = ec.CategoryID;
END
GO
二、C#测试代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.SqlServer.Server;
namespace TableParametersTest
{
class Program
{
//连接串
//注：MultipleActiveResultSets=True => 允许一个连接多个 SqlDataReader （不推荐，仅为实现 SqlDataReader 作表值参数）
static readonly string CONNSTRING = "Data Source=(local),2014;Initial Catalog=AdventureWorks2014;Integrated Security=True;MultipleActiveResultSets=True";
static void Main(string[] args)
{
//1. 以 DataTable 作为表值参数
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("CategoryID", typeof(int)));
dt.Columns.Add(new DataColumn("CategoryName", typeof(string)));
for (int i = 1; i <= 2; i++)
{
DataRow dr = dt.NewRow();
dr["CategoryID"] = i;
dr["CategoryName"] = "计算机1"+i.ToString();
dt.Rows.Add(dr);
}
SqlParameter sp = new SqlParameter("@tvpEditedCategories", dt);
sp.SqlDbType = SqlDbType.Structured;
sp.TypeName = "dbo.type_CategoryTable";
ExecuteNonQueryByProc("Proc_UpdateCategories", sp);
//2. 以 SqlDataReader 作为表值参数 （不推荐）
string sql = "select CategoryID,N'计算机2'+CAST(CategoryID AS nvarchar(30)) AS CategoryName from dbo.Categories";
using (SqlConnection conn = new SqlConnection(CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
SqlCommand cmd2 = new SqlCommand("Proc_UpdateCategories", conn);
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = cmd2.Parameters.AddWithValue("@tvpEditedCategories", sdr);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.type_CategoryTable";
cmd2.ExecuteNonQuery();
}
//3. 以 IList<SqlDataRecord> 作为表值参数
IList<SqlDataRecord> list = new List<SqlDataRecord>();
for (int i = 1; i <= 2; i++)
{
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData[] {
new SqlMetaData("CategoryID", SqlDbType.Int),
new SqlMetaData("CategoryName", SqlDbType.NVarChar,50)
});
record.SetInt32(0, i);
record.SetSqlString(1, "计算机3"+i.ToString());
list.Add(record);
}
SqlParameter sp3 = new SqlParameter("@tvpEditedCategories", list);
sp3.SqlDbType = SqlDbType.Structured;
sp3.TypeName = "dbo.type_CategoryTable";
ExecuteNonQueryByProc("Proc_UpdateCategories", sp3);
Console.WriteLine("End");
Console.Read();
}
static void ExecuteNonQueryByProc(string proc, params SqlParameter[] spArr)
{
using (SqlConnection conn = new SqlConnection(CONNSTRING))
{
conn.Open();
SqlCommand cmd = new SqlCommand(proc, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (spArr!=null && spArr.Length > 0)
{
cmd.Parameters.AddRange(spArr);
}
cmd.ExecuteNonQuery();
}
}
}
}C#运行时SQL Profiler跟踪到的脚本：
--方法1.
declare @p1 dbo.type_CategoryTable
insert into @p1 values(1,N'计算机11')
insert into @p1 values(2,N'计算机12')
exec Proc_UpdateCategories @tvpEditedCategories=@p1
--方法2.
select CategoryID,N'计算机2'+CAST(CategoryID AS nvarchar(30)) AS CategoryName from dbo.Categories
select CategoryID,N'计算机2'+CAST(CategoryID AS nvarchar(30)) AS CategoryName from dbo.Categories
declare @p1 dbo.type_CategoryTable
insert into @p1 values(1,N'计算机21')
insert into @p1 values(2,N'计算机22')
exec Proc_UpdateCategories @tvpEditedCategories=@p1
--方法3.
declare @p1 dbo.type_CategoryTable
insert into @p1 values(1,N'计算机31')
insert into @p1 values(2,N'计算机32')
exec Proc_UpdateCategories @tvpEditedCategories=@p1由上可见：在C#中无论采用哪种方式使用表值参数， 都是将值逐行插入到表值参数（其实相当于表变量，只是比表变量多了作为参数的功能）中，然后再传给存储过程。
扫一扫
1959
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
