Commit and Rollback on a batch

Hi!

I am using VB.NET 2005 and/or SQL Server 2005 studio manager.

I have a string of about 20 or 30 inserts updates and deletes. I want to process all or nothing. If there is an error that prevents a single transaction from completeing, i want to roll back the entire batch.

One solution i read is to test fot @.@.error after each statement. This is not desirable as i receive the batch as a single string already made. I would have to separate all the statements and insert the error testing myself.

I would prefere to simply execute the batch as an all or nothing batch.

Certainly this is a common request. But the only solutions i can find involve extensive re-working of the source batch of transactions. I may have up to 100 statements in my batch.

Any Ideas?

Thank you

Jerry Cicierega

using System.Transactions;

...

using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
// Do stuff with your cmd object here such as running the aforementioned batch statements
}
}
ts.Complete()
}

If an error occurs during the processing, the whole thing will be rolled back. Of course, fill in the stuff you need for the connection and command objects in the using statements.