Populate a table-valued parameter in ado.net with ONE insert statement for multiple values?

Question

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.

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

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

Have you tried the [IEnumerable](https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) approach? — Dale K, Jun 24 '19 at 21:52
And remember VALUES are used in the INSERT ... VALUES statement, there is a limit of 1000 rows. — Piotr Palka, Jun 24 '19 at 22:45

score 9 · Accepted Answer · answered Jun 24 '19 at 22:08

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.

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

Populate a table-valued parameter in ado.net with ONE insert statement for multiple values?

1 Answers1

Linked

Related