Managing transaction in Dot net

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.
rj_khatriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GoodJunCommented:
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
rj_khatriAuthor Commented:
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
_ys_Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

rj_khatriAuthor Commented:
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
_ys_Commented:
>> at the moment 3rd procedure fails
Does it RAISERROR upon failure? This is translated to an Exception for your C# code to handle.
0
ptmcompCommented:
Did you set the IsolationLevel to ReadCommited or above? What database are you using, does it support transaction? (SQL Server and Oracle do)
0
rj_khatriAuthor Commented:
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
_ys_Commented:
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
rj_khatriAuthor Commented:
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
ptmcompCommented:
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
_ys_Commented:
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
rj_khatriAuthor Commented:
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
_ys_Commented:
>> 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
rj_khatriAuthor Commented:
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
_ys_Commented:
>> 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
rj_khatriAuthor Commented:
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
_ys_Commented:
>> 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
rj_khatriAuthor Commented:
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
_ys_Commented:
Looks fine.

Blame the provider.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.