How to send multiple commands to SQL Server 2008 w

Question

I want to send multiple parametrized update, insert and delete commands to a sql server database in one round trip. I've looked at sqlbulkcopy but that appears to only work with one mapping/table/statement at a time. I was hoping for some thing a more flexible. I don't need to get any results from any of the queries (ie: affected row count) but I do want failures to be propagated back to the app layer and so that the transaction can be rolled back. Any ideas?

side question: I was also wondering how nHibernate does it, only because it is tried and true and appears to perform well. I attempted looking through the nhibernate source but it wasn't immediately clear how it was doing the batching. I can go back to the source but was hoping someone knew, high level, how it is being handled and could explain it to me.

Answer 1

Set CommandType to Text, and separate your commands with semicolons. You end up with one giant command, whose parameter names you may need to generate programmatically (to avoid conflicts).

Answer 2

if i am not wrong SqlCommandBuilder will help you :) . SqlCommandBuilder