Solved

Managing transaction in Dot net

Posted on 2003-10-23
21
313 Views
Last Modified: 2013-12-03
Dear Friends,
Hi!

I have an application written in c#. The client interface class uses a dataaccess class to interact with the database. The application runs 3 stored procedures one by one calling the functions of the dataaccess class that inserts the data in 3 tables.
Now the problem is to manage the transaction in all the 3 procedures i.e. if 3rd procedure fails then the execution of previously run 2 procedures should also be rolled back.

Can you please help me to resolve the issue.

Thanks in advance.
0
Comment
Question by:rj_khatri
[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
  • 8
  • 8
  • 2
  • +1
21 Comments
 
LVL 10

Expert Comment

by:GoodJun
ID: 9609100
use the ado.net tranction object.
transaction obj=cn.begintranction();
//create the 3 command to insert the 3 tables
cmd1.tranction=obj;
cmd2.tracntion=obj;
cmd3.tranction=obj;

try
{//code here to insert
obj.commit
}
//catch exception code here
obj.rollback
0
 

Author Comment

by:rj_khatri
ID: 9612764
Dear Friend,
Hi

Thanks for the reply.
Th eproblem is that the interface file calls a function in Dataaccess file that in turns create a connecton object and perform the data access. So for the 3 procedures it will call the function 3 times and each time a new connection object is created. So transaction is not possible to maintain.
What I did that I created a connection object and a new function that accepts the connection object. So I just create connection object once and send it as an argument to the new function three times. But still I could not be able to maintain the transaction.

Hope you get my point.
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9613004
Take note that even thought your connection create a transaction, the individual command will not execute as part of that transaction unless you tell them to. Illustrated in the code snippets below.


try
{
    IDbConnection con = {your_connection};
    con.Open( )

    IDbTransaction trans = con.BeginTransaction( );
    foo1 ( trans );
    foo2 ( trans );

    trans.Commit( );
}
catch (Exception ex1)
{
//* Log exception
    if (null != trans)
    {
        try
        {
            trans.Rollback( );
        }
        catch (Exception ex2)
        {
        //* Log exception
        }
    }
}

//------------------------------------

void foo1 ( IDbTransaction trans )
{
    IDbCommand cmd = {your_command};
    cmd.CommandType = System.Data.CommandType.StoredProcedure
// blah blah

    cmd.Transaction = trans;  //*** part of the transaction ***//
    cmd.ExecuteNonQuery( );
}
0
Independent Software Vendors: 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!

 

Author Comment

by:rj_khatri
ID: 9613174
Hi _ys_ & GoodJun

I am applying the same, but at the moment 3rd procedure fails, I am not able to rollback the rows inserted by the first 2 procedures. Is it possible to rollback those rows?

Please help.

Thanks
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9613202
>> at the moment 3rd procedure fails
Does it RAISERROR upon failure? This is translated to an Exception for your C# code to handle.
0
 
LVL 10

Expert Comment

by:ptmcomp
ID: 9613218
Did you set the IsolationLevel to ReadCommited or above? What database are you using, does it support transaction? (SQL Server and Oracle do)
0
 

Author Comment

by:rj_khatri
ID: 9613223
Yes it raises an error (code and the description).
In the catch part I write
   Transaction.RollBack()
And that is executed but the effect of first 2 procedures is not rollbacked.

??
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9613276
Are you also assigning the connection object to the Command

void foo1 ( IDbConnection con, IDbTransaction trans )
{
    IDbCommand cmd = {your_command};
    cmd.CommandType = System.Data.CommandType.StoredProcedure
// blah blah

    cmd.Connection = con;  //*** use existing connection ***//
    cmd.Transaction = trans;  //*** part of the transaction ***//
    cmd.ExecuteNonQuery( );
}
0
 

Author Comment

by:rj_khatri
ID: 9613371
I am using "ReadCommited" Isolation level. And it is the DB2 database.

And while calling foo1, I am passing the transaction object only and then in the procedure body I am writting

cmd.Connection = Transaction.Connection

Is it ok?


??
0
 
LVL 10

Expert Comment

by:ptmcomp
ID: 9613518
DB2 on what system? DB2 != DB2 ... Is it AS400? Some DB2 cannot handle that kind of transactions. What DBClient / ODBC driver are you using?
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9613819
Nothing wrong with your code.

As ptmcomp suggested, start looking at the drivers.

As a quick test, try callinf trans.Rollback( ) after the first fn call, then the second, and check the database. See if it is actually rolled back or not.

Are you using BEGIN TRANS etc. within the strored procs themselves? You're not supposed to mix .NET and SQL transactions.
0
 

Author Comment

by:rj_khatri
ID: 9613923
Hi

It is Windows XP system and I am using "DB2OLEDB" provider. I have the M/S Host Integration server installed. Does this provider support transactions?

I am using .Net transactions not the SQL transactions.

I will check the rollback just after the 1st proc call, next,  as per the suggestion of "_ys_"

Thanks
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9613993
>> Does this provider support transactions?

http://www.microsoft.com/hiserver/techinfo/faq.asp

Extract
--------
DTC-driven 2PC support for DB2 for OS/390 and AS/400 using LU6.2. In Host Integration Server 2000, OLE DB for DB2 Provider and ODBC Driver for DB2 are enhanced to support the DRDA 2 Phase Commit protocols over LU6.2. This enables COM+ applications to participate in a Distributed Transaction Coordinator (DTC)-driven two-phase commit transaction between SQL Server and DB2 databases on OS/390 and OS/400.
0
 

Author Comment

by:rj_khatri
ID: 9614186
I checked with explicitly rolling back just after the first proc but it did not.

Even I checked with creating the connection and command object within the user interface file and checked for rollback. but it did not.

Any ideas/suggetsions??
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9614282
>> Any ideas/suggetsions??
Don't rely on transactions from within you .NET code. What other options are there? Sounds like there's no support with your current drivers/systems.

Create a seperate stored procedure (or embedded SQL statement), to wrap the other three. That way, everything happens on the DB in a single step.
0
 

Author Comment

by:rj_khatri
ID: 9614354
Yes that is the ultimate option I will plan for.
Will have to play with the parameters passed to wrapper procedure.

So it is the provider diability to support transaction? Can I confirm it?

Well thanks for all your support.
0
 
LVL 9

Expert Comment

by:_ys_
ID: 9614577
>> I checked with explicitly rolling back just after the first proc but it did not.
Speaks for itself. Provided your code is as discussed.
0
 

Author Comment

by:rj_khatri
ID: 9614859
I used the following code (Directly called from client interface)

OleDbConnection objCon = new OleDbConnection(ConnectionString);
   objCon.Open();  
  // Grab the transaction Object
  OleDbTransaction objTrans = objCon.BeginTransaction();
  OleDbCommand objCmd = new OleDbCommand();

  // set the command object properties.
  objCmd.Connection = objCon;
  objCmd.CommandText = strSql;
  objCmd.Transaction = objTrans;
      
  // Execute the procedure
  objCmd.ExecuteNonQuery();

  // Explicitly roll back
  objTrans.Rollback();

 Is there any problem here?
0
 
LVL 9

Accepted Solution

by:
_ys_ earned 100 total points
ID: 9614874
Looks fine.

Blame the provider.
0

Featured Post

Independent Software Vendors: 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

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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