Batch SQL inserts in .NET

Question

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!

Answer 1

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

Answer 2

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...