tags:

views:

574

answers:

2

Is using the .NET DataAdapter's batch insert features any more efficient, as far as DB round-trips, compared to using DbCommand.ExecuteNonQuery() in a loop?

Coming from the Java world, I was hoping to find something similar to it's batch abilities where multiple SQL commands are sent to the database and executed in one operation. When monitoring the database server, I see the DataAdapter making one execute per insert.

I've read a few topics that use the SqlBulkCopy but that's only going to work for MS Sql Server.

Thanks!

A: 

What about: One statement multiple rows

mysql:
INSERT INTO table (id) VALUES (1), (2), (3)

mssql:
INSERT INTO table (id)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
the_ajp
I was worried that this (string concatting to make up the insert statements) would open me up to sql injection, so I was using the command object and parameters to clean input. Otherwise I could do several INSERT table VALUES() terminated by a ';' or as you had suggested.Thank you for the suggestion!
Eric Tuttleman
If your using this as part of a webapp then you shouldn't use it. Most of the time these batch ops. are used in db updates and stuff like that.
the_ajp
+4  A: 

The DataAdapter has a UpdateBatchSize property. Setting the UpdateBatchSize to a positive integer value causes updates to the database to be sent as batches of the specified size.

Hope this helps...

Vasu Balakrishnan
This is the only level that supports command batching. You may not batch your own DbCommands (without exposing some internal classes with Reflection).
Anthony Mastrean
I've tried this. On MS SQL Server, SQL Profiler shows each insert statement seems to be on it's own. After reviewing your comment, I viewed a TCP Dump of the conversation and do see that it is batching multiple commands together. SQL Profiler shows each insert as a "RPC Completed" event which was confusing me. Thanks for your help.
Eric Tuttleman