• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

SQL Transaction Statement

Hi,
Im trying to get my headaround the sqltransaction class.

I can do the simple statement like (see below).
//Execute Stage 1
SqlTransaction trans;
 using (trans = con.BeginTransaction())
            {
                try
                {
string strInsertData = " INSERT INTO BLAH BLAH BLAH WHERE BLAH =@BLAH";
                    SqlCommand cmd_ExecuteInsert = new SqlCommand();
// MORE CODE
 cmd_ExecuteInsert_RMA.Transaction = trans;
trans.Commit();
}
catch
{
 trans.Rollback();
}

//Execute Stage 2
// OK now I want to  Update another database, but if it fails I need to Roll back stage 1
// Can someone provide an example on how to do this please
0
ziwez0
Asked:
ziwez0
2 Solutions
 
maligerCommented:
So you want update 2 different databases? If so, you want non-trivial thing, SqlTranstaction is above 1 specific database. Transaction Servers (Enterprise transactions).

look into
TransactionScope
and "Implementing an Implicit Transaction using Transaction Scope" MSDN article

You can create 2nd transaction on 2nd databae _before_ you commit 1st transaction and commit/rollback both transactions together later. This is somewhat lightweighted solution, but can be usable in your case (perhaps). Try get the operations in transaction as fast as possible, since every delay makes heavy load on both databases involved.
0
 
steelheart38Commented:
Yup. Look into TransactionScope : http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

It could be as simple as:

using(TransactionScope scope = new TransactionScope())
{
    // perform transactions here using SqlConnection,SqlCommand as you normally do
    // the Transaction will be handled for you
    scope.Complete(); // commit
}

if there is an error the complete will not happen and since TransactionScope is IDisposable and used inside a using block, it will rollback. See the link given above for more info.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now