Solved

Managing transaction in Dot net

Posted on 2003-10-23
21
262 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

19 Experts available now in Live!

Get 1:1 Help Now