Data Access Application Block and Transactions

Topics: Data Access Application Block
Nov 13, 2008 at 4:43 PM
Edited Nov 14, 2008 at 1:16 PM
I'm working on converting our application from VS 2k3 / 1.1 / DAL 2.0 to VS2k8 / 3.5 / Enterprise Library 4.0 and have encountered a problem that I cannot seem to resolve.

In the existing working application the system starts a transaction, inserts a record into a "parent" table then inserts "children" into a different table and then does a commit or rollback. The Child (ren) tables have FK references back to the parent.

The problem I am experiencing in the new converted application is that when the Child inserts are attempted I am receiving FK reference errors.

I've run the whole process in SSMS inside of a transaction and all is well so my assumption is that I am not using the Enterprise Library appropriately.

Here's a simple sample of what I am trying to accomplish in my application:

OnButtonClick
    Get database
    Get connection from database object
    Begin Transaction on connection
    IF INSERT ParentRecord
        IF INSERT ChildRecord
            Commit Transaction
        Else
            RollBack Transaction
        EndIf
    Else
        RollBack Transaction
    End If



Thanks for any and all assistance!
Nov 14, 2008 at 6:53 AM
From what transaction are you calling the Commit method?  Ideally, it should be from the DbTransaction created from the call to dbConnection.BeginTransaction().
Check out the documentation on DAAB, Walkthrough: Performing Multiple Updates Within a Transaction, it provides a good example.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Nov 14, 2008 at 1:18 PM
Thanks for your reply!

I see your point. I didn't indicate in my small sample where I was creating the transaction. I've updated the sample.

I've read that specific walkthrough, the problem is that it's updating two records that already exist, not inserting one record followed by another record where there is a FK referential integrity check.

Scotty
Nov 14, 2008 at 1:48 PM
I tried using insert statements and everything worked fine.  Here's my sample code, I just hard-coded the values.  (The number 1 is the foreign key to the Address table)

Database db = DatabaseFactory.CreateDatabase("SampleDB");
DbCommand masterCommand = db.GetSqlStringCommand("INSERT INTO Employee VALUES('Sarah')");
DbCommand detailCommand = db.GetSqlStringCommand("INSERT INTO Address VALUES(1, 'Address1')");

 

 

using (DbConnection connection = db.CreateConnection())
{
    connection.Open();
    
DbTransaction transaction = connection.BeginTransaction();
    
    
try
    
{
        db.ExecuteNonQuery(masterCommand);
        db.ExecuteNonQuery(detailCommand);
        transaction.Commit();
    }
   
catch
  
{
         transaction.Rollback();
   }

 

    connection.Close();
}


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com

 

 

Nov 14, 2008 at 2:48 PM
Thanks for replying!

I was finally able to make it work. The old DAL we were using operated differently and I had assumed that it would operate the same way. Some restructuring was required but it's good now!

Scotty
Nov 14, 2008 at 3:08 PM
Could you share what was the issue? What version were you using? Just in case other people encounter the same issue.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Nov 14, 2008 at 3:33 PM
We are trying to get away from DAL version 2.0. I think it's origins are from the P&P from way back when.

The old method was create a transaction with the dal and execute all queries sending the transaction as a parameter.

The new method is very similar but all executions must be in one connection.

Our system is not currently doing everying in the connection so I'm not sure how it was working before. I've found a lot of those things in this process.
Nov 14, 2008 at 3:41 PM
Ok, thanks for the info!