Handle multiple db updates from c# in SQL Server 2008

Question

I like to find a way to handle multiple updates to a sql db (with one singe db roundtrip). I read about table-valued parameters in SQL Server 2008 http://www.codeproject.com/KB/database/TableValueParameters.aspx which seems really useful. But it seems I need to create both a stored procedure and a table type to use it. Is that true? Perhaps due to security? I would like to run a text query simply like this:

var sql = "INSERT INTO Note (UserId, note) SELECT * FROM @myDataTable";
var myDataTable = ... some System.Data.DataTable ...
var cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
var param = cmd.Parameters.Add("@myDataTable", System.Data.SqlDbType.Structured);
param.Value=myDataTable;
cmd.ExecuteNonQuery();

So

A) do I have to create both a stored procedure and a table type to use TVP's? and
B) what alternative method is recommended to send multiple updates (and inserts) to SQL Server?

A) Yes. B) Alternatives: XML, [n]varchar(size) strings + parsing. My option is TVP. — Bogdan Sahlean
– Bogdan Sahlean, Commented Sep 25, 2011 at 14:13
At what point is this not worth the effort to avoid a couple of trips to the db? — JeffO
– JeffO, Commented Sep 25, 2011 at 20:20
Jeff - I'd like to find out how much it costs (another Q?). For now it's a hunch + more importantly a practice that seems to be commonly agreed upon, from MS P&P "Round trips significantly affect performance. They are subject to network latency and to downstream server latency. Many data-driven Web sites heavily access the database for every user request. While connection pooling helps, the increased network traffic and processing load on the database server can adversely affect performance. Keep round trips to an absolute minimum." msdn.microsoft.com/en-us/library/ff647768.aspx — joeriks
– joeriks, Commented Sep 26, 2011 at 1:37

bryanmac · Accepted Answer · 2011-09-25 14:33:21Z

3

Yes, you need to create the types.

Alternatives are sending a big string sql batch or passing XML to sprocs.

The downside to big sql string batches is it can blow the sql proc cache and might cause sql to recompile - especially if the batch is unique because of input data being part of that large string. By definition each batch would be unique.

XML was the main alternative before TVPs. The one downside to XML, for at least awhile, sql azure didn't support it (that might change?) so it limits your options.

TVPs seem to be the way to do this. Our project just converted to using TVPs.

Hope that helps.

answered Sep 25, 2011 at 14:33

bryanmac

39.3k10 gold badges93 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handle multiple db updates from c# in SQL Server 2008

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related