Solved

SQL Transaction Statement

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

815 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

8 Experts available now in Live!

Get 1:1 Help Now