How to efficiently invoke table-valued parameters from Ado.Net

Question

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

Because now we can use TVPs, I tried them out. On the client, I am running 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 don't think it is efficient. I fired up the Profiler, and I immediately saw that Ado.Net issues the following highly inefficient SQL being sent to the server:

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

As a result, a lot of network bandwidth and CPU on the server is wasted on sending, parsing, and compiling. Is there a more efficient way to use TVPs with Ado.Net?

Answer 1

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

I mentioned it as an alternative to TVP. See here and here for more interesting infos about SQLBulkCopy compared to TVP.

(copied from comments)