Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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.
0
rj_khatri
Asked:
rj_khatri
  • 8
  • 8
  • 2
  • +1
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now