How to efficiently call table parameters from Ado.Net

Question

I need to efficiently send tens of thousands of numbers and dates from Ado.Net to SQl Server 2008. Back in the days before SQL 2008, I collected these numbers in an image, which was pretty fast. Erland Sommarskog was kind enough to include some of my codes in his article Arrays and Lists in SQL Server 2005

Because now we can use TVP, I tried them. On the client, I run this:

dataTable = new DataTable(); dataTable.Columns.Add("Date", typeof(DateTime)); dataTable.Columns.Add("Price", typeof(decimal)); dataTable.Rows.Add(new object[] { someDate, somePrice }); command.CommandText = "Writers.SavePrices"; command.CommandType = CommandType.StoredProcedure; var param = command.Parameters.AddWithValue("@Prices", dataTable); param.SqlDbType = SqlDbType.Structured; 

This code works, but I do not think it is efficient. I activated Profiler, and I immediately saw that Ado.Net issues the following extremely inefficient SQL query to the server:

 DECLARE @Prices TABLE(...) INSERT INTO @Prices(...)VALUES(...) EXEC Writers.SavePrices @ Prices=@Prices 

As a result, most of the network and processor bandwidth on the server is lost during sending, analysis, and compilation. Is there a more efficient way to use TVP with Ado.Net?

Answer 1

Do you know SqlBulkCopy ? It is very efficient and flexible. Here is an example.

I mentioned this as an alternative to TVP. See here and here for more interesting SQLBulkCopy information compared to TVP.

(copied from comments)