Click here to Skip to main content
15,891,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I did some inserts and deletes in my routine and if they are all successful, I want to commit the transactions, else rollback.
How do I do it from my code - I mean if I use a Data Adapter, it has SelectCommand/ InsertCommand/DeleteCommand/UpdateCommand.
Should it be UpdateCommand, or I need to use something else?
Thanks,

Ekjon
Posted

This[^] from MSDN should give you enough to work out what you should do.

Although it beats me why my search results should be any better than your own. You did search before asking, didn't you?
 
Share this answer
 
Comments
Ekjon 18-Mar-11 17:29pm    
Thanks Henry.
Have a look at System.Transactions Namespace[^]. Especially TransactionScope Class[^]. I believe that this approach would nicely wrap your operations into a single unit-of-work.
 
Share this answer
 
Comments
Ekjon 18-Mar-11 17:29pm    
Thanks Mika.
Wendelius 18-Mar-11 17:37pm    
No problem :)
Ekjon 18-Mar-11 18:32pm    
I found an example like this:
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = new TimeSpan(0, 2, 0);
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (SqlConnection connA = new SqlConnection(connStringA))
{
using (SqlCommand cmdA = new SqlCommand(sqlStmtA, connA))
{
int rowsAffectedA = cmdA.ExecuteNonQuery();
if (rowsAffectedA > 0)
{
using (SqlConnection connB = new SqlConnection(connStringB))
{
using (SqlCommand cmdB = new SqlCommand(sqlStmtB, connB))
{
int rowsAffectedB = cmdB.ExecuteNonQuery();
if (rowsAffectedB > 0)
{
transactionScope.Complete();
}
} // Dispose the second command object.
} // Dispose (close) the second connection.
}
} // Dispose the first command object.
} // Dispose (close) the first connection.
} // Dispose TransactionScope object, to commit or rollback transaction.

My question is: do I have to use the If(rowsAffectedA > 0) to go for the next transaction, or I can directly go for transactions 1,2,3,4 and If one fails, at the end - the scope object will automatically roll back all? If it does, should I call the Complete() method anyway?

Thanks.
Wendelius 19-Mar-11 6:07am    
Basically you should wrap the whole thing inside a try catch block. An axception is the way to roll back the thing. Sa everything inside your transaction is purely "business logic". In your example it seems that the business logic defines that the first query must modify rows in order execute the next statements. Hopefully this helps.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900