使用一个insert语句为多个值填充ado.net中的表值参数? [英] Populate a table-valued parameter in ado.net with ONE insert statement for multiple values?

问题描述

我有一些C#代码正在填充TVP，然后在SQL Server中调用存储过程，并在该存储过程中传递该TVP.它利用标准的ADO.NET数据表和 Rows.Add 填充行.

I have some C# code that is populating a TVP and then calling a stored procedure in SQL Server where it passes in that TVP. It utilizes the standard ADO.NET DataTable and the Rows.Add to populate the rows.

运行 sqlCommand.ExecuteNonQuery()时，如果在SQL事件探查器中捕获了SQL跟踪，则将看到类似的内容，它将在TVP的每一行填充到单独的插入语句中.

When the sqlCommand.ExecuteNonQuery() runs, if you capture a SQL trace in SQL Profiler, you will see it something like this, where it populates each row of the TVP in separate insert statements.

declare @p1 dbo.BigIntTable -- This is a table type

insert into @p1 values(1)
insert into @p1 values(2)
insert into @p1 values(3)
insert into @p1 values(4)
insert into @p1 values(5)

exec dbo.MyProc @InputTVP=@p1
go

我希望它在一条语句中插入所有值，如下所示.下面的示例是有效的SQL，并且比ADO.NET生成的示例要好得多.有什么想法吗?

I want it to insert all the values in one statement, as seen below. The example below is valid SQL and performs much better than the above generated by ADO.NET. Any ideas?

declare @p1 dbo.BigIntTable -- This is a table type

insert into @p1 
values
 (1)
,(2)
,(3)
,(4)
,(5)

exec dbo.MyProc @InputTVP=@p1
go

推荐答案

探查器不会向您显示客户端实际发送的内容，而是向您显示可以执行相同操作的一种TSQL复制批处理.

Profiler shows you not what the client actually sent, but a kind of TSQL reproduction batch that you could run to do the same thing.

表值参数是

Table-Valued Parameters are a feature of the TDS network protocol, as well as the SQL Server Database Engine. And clients will send the data directly using TDS, and not in seperate INSERT statements.

如果运行Profiler，您会发现没有单独的SQL:StmtCompleted事件可用于将每一行插入TVP.

If you run Profiler, you'll see that there aren't separate SQL:StmtCompleted events for inserting each row into the TVP.

这篇关于使用一个insert语句为多个值填充ado.net中的表值参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！