Solved

SQL Transaction Statement

Posted on 2008-06-25
4
633 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now