Solved

SQL Transaction Statement

Posted on 2008-06-25
4
640 Views
Last Modified: 2012-06-21
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
Comment
Question by:ziwez0
4 Comments
 
LVL 3

Accepted Solution

by:
maliger earned 250 total points
ID: 21864599
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
 
LVL 7

Assisted Solution

by:steelheart38
steelheart38 earned 250 total points
ID: 21865655
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question