Transaction handling in CLSA.NET

gsitaramireddy posted on Wednesday, April 30, 2008

Hi I am new to CSLA.net, I want to learn more about it.

When I am trying to delete a record from DB2 database, I am getting the below error and inner exception 

at the line  "connEmp.Open();" in my code sample.

Error:  "ERROR [58005] [IBM][DB2/6000] SQL0998N Error occurred during transaction or heuristic processing. Reason Code = "16". Subcode = "2-8004D026". SQLSTATE=58005"
Inner Exception: "_COMPlusExceptioncode -532459699"

Below are two ways I tried.

[Transactional(TransactionalTypes.TransactionScope)]
        private void DataPortal_Delete(Criteria criteria)
        {
            using (DB2Connection connEmp = new DB2Connection(Databases.EmpDBConnection))
            {
                connEmp.Open();

                using (DB2Command cmdEmp = connEmp.CreateCommand())
                {
                    string strSQL = "SP_DELETE_Employee";
                    cmdEmp.CommandType = CommandType.StoredProcedure;
                    cmdEmp.CommandText = strSQL;
                    cmdEmp.Parameters.Add("EmpID",criteria.EmpId);
                    cmdEmp.ExecuteNonQuery();
                }
            }
        }

and

[Transactional(TransactionalTypes.TransactionScope)]
private void DataPortal_Delete(Criteria criteria)
{
       Database dbEmp = DatabaseFactory.CreateDatabase();
       System.Data.Common.DbCommand dbCommand = dbEmp.GetStoredProcCommand("SP_DELETE_Employee");
       dbEmp.AddInParameter(dbCommand, "EmpID", DbType.Int16, criteria.empId);
       dbEmp.ExecuteNonQuery(dbCommand);
}

Distributed transaction coordinator service also running on my machine.

I added below code in web.config to change the default transaction time out

<system.transactions>

    <defaultSettings timeout="00:010:00" />

  </system.transactions>

I am using below softwares list

Visual Studio 2005,ASP.NET 2.0,CSLA.NET 3.0.4,UDB DB2 9.1.2 database,C#.NET and Microsoft Enterprise Library 3.1 and Windows XP with SP2.

Is it mandatory to run the MDT service to work with CSLA Transactions, if not can you please advise me the best solution to resolve this problem.

If I removed transaction attribute ([Transactional(TransactionalTypes.TransactionScope)]) it was working fine in both the cases.

Can some one please help me to work with transactions in CSLA.net.

RockfordLhotka replied on Friday, May 02, 2008

I don't think DB2 provides support for System.Transactions.TransactionScope?

I think you need to use either manual ADO.NET transactions or Enterprise Services with DB2.

So use

[Transactional(TransactionalTypes.EnterpriseServices)]

and you should be good (assuming your machine is configured to allow the DTC to work).

gsitaramireddy replied on Monday, May 05, 2008

Thank you very much for support, but I am getting "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) " error.

When I use [Transactional(TransactionalTypes.EnterpriseServices)]

Sitha Ram

RockfordLhotka replied on Monday, May 05, 2008

gsitaramireddy replied on Tuesday, May 06, 2008

Hi Rocky,

Currently the 'Distributed Transaction Coordinator' Service status is started on my Machine.

Sitha Ram

RockfordLhotka replied on Tuesday, May 06, 2008

gsitaramireddy replied on Monday, May 12, 2008

I will try that, thanks for the information.

Sitha Ram

dblwizard replied on Thursday, May 24, 2012

Rocky,

Whats the difference between Manual and EnterpriseServices?  I'm getting the same error if I use EnterpriseServices but not if I use manual.  I'm working with IBM to solve this as well but Id like to understand the difference?

From what I understand transactions in db2 are implied.  Meaning that there is a transaction wrapped from one Commit/Rollback to the other. 

So I'm just trying to understand what changing out that attribute actually effects.

Thanks

dbl

RockfordLhotka replied on Thursday, May 24, 2012

For a comprehensive answer look at the 2008 book or the 'Using CSLA 4' book.

In summary:

• Manual means you and/or your database must manage the transaction. CSLA does not put your code into any transaction context.
• TransactionScope means CSLA puts your code into a TransactionScope context before calling your root DataPortal_XYZ method.
• EnterpriseServices means CSLA puts your code into a COM+ transactional context (which uses the DTC) before calling your DataPortal_XYZ method.