Solved

SQL Transaction Statement

Posted on 2008-06-25
4
642 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 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