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.
