Hi!

Today we've upgraded our DAL to the new version of the LLBL - 1.0.2005.1. (I'm still amazed by the new stuff btw!!!) from 1.0.2004.1

All previous code works perfectly except one thing. For various reasons we have several DAL libraries - one for each separated module of our system. All was fine until we came to the point when transaction across two adapters was needed.

After some tweaking I was able to create a inherited DataAccessAdapter which took a existing IDbConnection and IDbTransaction and by overriding methods like:

• CreateNewPhysicalConnection
• CreateNewPhysicalTransaction
• StartTransaction and
• CreateUpdateDQ (to attach existing IDbTransaction to the command)

I was able to use one transaction for multiple updates using two adapters.

And that's part of the application which I cannot make work anymore.

What confuses me though is the Exception I am receiving:

 An exception was caught during the execution of an action query: The ConnectionString property has not been initialized.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception

 at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(ArrayList queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave)
   at Sinte.Scoring.WebApp.Collections.tskProcessingList.ProcessTasks(RequestDataAdapter adapterRM, Int64[] taskIds) in d:\mydocs\visual studio projects\creditscoring\processing\sinte.scoring.webapp\collections\tskprocessinglist.aspx.cs:line 346

Was there any major change in the process of saving entity? If you could point me to the right direction I would be really grateful.

Thanks for any advice you have.

Jakub

Yes, SaveEntity isnt called recursively anymore. It now pre-calculates the queues to process for insert and update and processes them in 1 go. This means that SaveEntity isn't called per entity anymore, just once by you.

OnBeforeEntitySave is the method you should override now if you want to do something with the entity prior to saving, like setting some fields.

The common way to do a cross adapter transaction is via enterprise services, though that's slower than using 2 adapters and one connection. My question is then: why don't you re-use the adapter instance instead?

Ok, to give u some idea about the task:

• we have one set of classes and corresponding adapter for processing related entities (the system is a worflow processor)
• we have one class for request related data (RequestManager) which uses its own entities and corresponding adapter

The goal is to process multiple workflow tasks (from processing DAL) over several requests (RequestManager). This is the part that should be in a transaction since processing just part of them is not an option.

The problem is : each module uses different DAL library and therefore its own adapter of different types, so if I use one adapter I cannot save entities from a different library (o can i?).

That's why I started with one adapter, started transaction and passed the connection and transaction to the second adapter. Since the database is the same a SqlTransaction works just fine.

Is there any (regular) way how to pass opened connection and transaction to a adapter?

Ok, solved the issue, I think.

Seems like the problem was not actually in the DataAdapter itself, rather in one of our classes which was closing connection every time.

Anyway, I was able to simplify my previous code and ended with a new adapter with following overrides:

protected override System.Data.IDbConnection CreateNewPhysicalConnection(string connectionString)
{
    //is connection external?
    if (connection != null) {
        return connection;
    }
    else
        return base.CreateNewPhysicalConnection(connectionString);
}

protected override System.Data.IDbTransaction CreateNewPhysicalTransaction()
{
    //is transaction external?
    if (transaction != null) 
    {
        return transaction;
    }
    else
        return base.CreateNewPhysicalTransaction();
}

public override void CloseConnection()
{
    if (connection == null) 
    {
        //close internal connection only!
        base.CloseConnection();
    }
}

connection and transaction are objects passed to the adapter after starting transaction on the first adapter. After that I call StartTransaction method on the second adapter (see above) with exactly same parameters and thus setting all internal properties (like IsTransactionInProgress) without actually creating new connection and transaction.

Glad it's solved!

Koubessus wrote:

Ok, solved the issue, I think.

Seems like the problem was not actually in the DataAdapter itself, rather in one of our classes which was closing connection every time.

Anyway, I was able to simplify my previous code and ended with a new adapter with following overrides:

protected override System.Data.IDbConnection CreateNewPhysicalConnection(string connectionString)
{
    //is connection external?
    if (connection != null) {
        return connection;
    }
    else
        return base.CreateNewPhysicalConnection(connectionString);
}

protected override System.Data.IDbTransaction CreateNewPhysicalTransaction()
{
    //is transaction external?
    if (transaction != null) 
    {
        return transaction;
    }
    else
        return base.CreateNewPhysicalTransaction();
}

public override void CloseConnection()
{
    if (connection == null) 
    {
        //close internal connection only!
        base.CloseConnection();
    }
}

connection and transaction are objects passed to the adapter after starting transaction on the first adapter. After that I call StartTransaction method on the second adapter (see above) with exactly same parameters and thus setting all internal properties (like IsTransactionInProgress) without actually creating new connection and transaction.

Hi guys,

I have found two post on the forum about this type of features (thank's to walaa). But i still have a pb, to implement that.

The code seems to be ok, the adapter uses the good connection and the good transaction. But i still have a pb with the command object

{SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: 
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  
The Transaction property of the command has not been initialized.. 
Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(ArrayList queueToPersist, Boolean insertActions)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse)
   at Gofluent.Exceptions.BL.BusinessObjects.ErrorLogs.Save(ErrorLogsInfo info, Boolean resursive, Boolean refetchEntityAfter, IDbTransaction transaction) in C:\Documents and Settings\Michael\My Documents\LLBLGen Pro\GoFluent.Exceptions\Gofluent.Exceptions.BL\BusinessObjects\ErrorLogs.cs:line 35}

I have forgotten something ? Where can I find more information ?

Thank's a lot for your help ;o)

Mika

Almost the same adapter inherited

protected override System.Data.IDbConnection CreateNewPhysicalConnection(string connectionString)
        {
            //is connection external?
            if (_transaction != null)
            {
                return _transaction.Connection;
            }
            else
                return base.CreateNewPhysicalConnection(connectionString);
        }

        protected override System.Data.IDbTransaction CreateNewPhysicalTransaction()
        {
            //is transaction external?
            if (_transaction != null)
            {
                return _transaction;
            }
            else
                return base.CreateNewPhysicalTransaction();
        }

        public override void CloseConnection()
        {
            if (_transaction == null)
            {
                //close internal connection only!
                base.CloseConnection();
            }
        }

I was able to solve a part of my problem, but i don't understand all. In fact, I need to call the StartTransaction on the adapter when i pass an external transaction.

                    
adapter.Transaction = transaction;
if (transaction != null) adapter.StartTransaction(transaction.IsolationLevel, "trans1");
adapter.SaveEntity(info, refetchEntityAfter, resursive);

Does the startTransaction Method initialize the command object's transaction ?

Mika

Yes it wires the transaction to the command when the query is executed. So it's generated -> wired -> executed.

it wires the active transaction of the adapter.

Thank's Otis for your answer

Maybe people on this forum have said that before, but once again : llblgen is a very great tool !!!!!

thank's again

Mika

Koubessus wrote:

The problem is : each module uses different DAL library and therefore its own adapter of different types, so if I use one adapter I cannot save entities from a different library (o can i?).

This is the LLBLGen Pro's architecture point that I do not like, the DbSpecific project should not exist. I would like to use a generic SQLAdapter or OracleAdapter, etc, then the adapter should get the persistenceInfo from an interface implemented by the entity that is going to be fetched or updated.

That's why to pass adapter as a parameter (like I have seen in the forum), that's not a good idea. Then you will have pb if you try to share your BL.

It could be very useful when you're working with different DAL. Like on a Big project with several BL and DAL

That's why I needed to pass an external transaction to the adapter ...

mika

Rogelio wrote:

Koubessus wrote:

The problem is : each module uses different DAL library and therefore its own adapter of different types, so if I use one adapter I cannot save entities from a different library (o can i?).

This is the LLBLGen Pro's architecture point that I do not like, the DbSpecific project should not exist. I would like to use a generic SQLAdapter or OracleAdapter, etc, then the adapter should get the persistenceInfo from an interface implemented by the entity that is going to be fetched or updated.

A generic SQLAdapter is still a specifc adapter. The db specific code has to be placed somewhere, and it's located in the dbspecific project in the current architecture. What you propose won't work (IMHO) as the entity doesn't know anything about the database it's saved in, the adapter does. So asking the entity isn't helping.

Mika wrote:

That's why to pass adapter as a parameter (like I have seen in the forum), that's not a good idea. Then you will have pb if you try to share your BL.

It could be very useful when you're working with different DAL. Like on a Big project with several BL and DAL

That's why I needed to pass an external transaction to the adapter ...

mika

The transactions are tie to the connection that started them and the connection are started by the adapter. Then you need to pass the adapter or the connection, I am not sure if the adapter allows to assign a connection to it (this is in the case that you pass the connection). I prefer to pass the adapter between businessManagers and check if the adapter is nothing. With the actual architecture it is better to pass the connection as you are trying to do.

Otis wrote:

Rogelio wrote:

Koubessus wrote:

The problem is : each module uses different DAL library and therefore its own adapter of different types, so if I use one adapter I cannot save entities from a different library (o can i?).

This is the LLBLGen Pro's architecture point that I do not like, the DbSpecific project should not exist. I would like to use a generic SQLAdapter or OracleAdapter, etc, then the adapter should get the persistenceInfo from an interface implemented by the entity that is going to be fetched or updated.

A generic SQLAdapter is still a specifc adapter. The db specific code has to be placed somewhere, and it's located in the dbspecific project in the current architecture. What you propose won't work (IMHO) as the entity doesn't know anything about the database it's saved in, the adapter does. So asking the entity isn't helping.

OK, I went too fast when I said "the DbSpecific project should not exist". I will come back with my ideas.

Ok, I am back.

First, sorry for my words about DbSpecific project. LLBLGen Pro is the best ORM and Frans is doing a great job.

I have the following scenario:

• All the DataBase in the same server.
• DataBaseA, DataBaseB, DataBaseC ...., all with the same structure
• The DataBase name are dynamic, the same BusinessManager and DAL to handle request for any Database. The replace of the DataBase name through the config file will not work.
• I need to update tables from different DataBase inside one transaction.
• I need to get data like: Select field1, field2 from DatabaseA.TableA where field3 IN (Select field3 from DatabaseB.TableB).

I think this situation can be solve by a little modification to the DataAccessAdapter class that is inside the DbSpecific project:

• Add a need property to the DataAccessAdapter, this new property (entityToCatalogMapping) will be a hashTable with key = "EntityName/AliasName" and value = "CatalogNameToUse". Then modify the HandleCatalogName method the case when catalognameUsage is forceName, inside this code check if the entityToCatalogMapping property is nothing. If the property is nothing then use the default catalogNameToUse that was specificed, if the entityToCatalogMapping is not nothing then look for an item with key=entityName, if it is found then use the catalogName found otherwise use the default catalogNameToUse. This solution will work only when the entities's name are different, to solve this there must be a way to assign a alias to the field's entityName at the time the predicate expression is created.

To solve the situation when I want to update tables from different database:

• Add a new overloaded constructor to the DataAccessAdapter that accept the following parameters:

(connection as IDbConnection, catalogNameUsage as ..., catalognameToUse as string)

As the connection is a parameter then the keepConnectionOpen must be set to True automatically. This also solve the situation where you have your schema splited in two or more little dbGeneric projects and you want to update entities from two or more dbGeneric projects inside one transaction.

Rogelio wrote:

Ok, I am back.

First, sorry for my words about DbSpecific project. LLBLGen Pro is the best ORM and Frans is doing a great job.

Thanks But you may critizise our work, let that be clear . If it can lead to an even better product, why not?

I have the following scenario:

• All the DataBase in the same server.
• DataBaseA, DataBaseB, DataBaseC ...., all with the same structure
• The DataBase name are dynamic, the same BusinessManager and DAL to handle request for any Database. The replace of the DataBase name through the config file will not work.
• I need to update tables from different DataBase inside one transaction.
• I need to get data like: Select field1, field2 from DatabaseA.TableA where field3 IN (Select field3 from DatabaseB.TableB).

My question then is: why do you need to query over multiple the same databases? Why is data shared among these databases in such a way that you need to query over multiple catalogs, when you have the same structure in every catalog? Often when you see querying over multiple catalogs, there's shared data in one catalog and specific data in another catalog, though that catalog with specific data doesn't have data which looks like the shared data.

I think this situation can be solve by a little modification to the DataAccessAdapter class that is inside the DbSpecific project:

• Add a need property to the DataAccessAdapter, this new property (entityToCatalogMapping) will be a hashTable with key = "EntityName/AliasName" and value = "CatalogNameToUse". Then modify the HandleCatalogName method the case when catalognameUsage is forceName, inside this code check if the entityToCatalogMapping property is nothing. If the property is nothing then use the default catalogNameToUse that was specificed, if the entityToCatalogMapping is not nothing then look for an item with key=entityName, if it is found then use the catalogName found otherwise use the default catalogNameToUse. This solution will work only when the entities's name are different, to solve this there must be a way to assign a alias to the field's entityName at the time the predicate expression is created.

To solve the situation when I want to update tables from different database:

• Add a new overloaded constructor to the DataAccessAdapter that accept the following parameters:

(connection as IDbConnection, catalogNameUsage as ..., catalognameToUse as string)

As the connection is a parameter then the keepConnectionOpen must be set to True automatically. This also solve the situation where you have your schema splited in two or more little dbGeneric projects and you want to update entities from two or more dbGeneric projects inside one transaction.

I find this very specific and IMHO it can fail in some occasions. If you really want this, you can do this today however.

Derive a class from DataAccessAdapter and override the GetFieldPersistenceInfo(s) methods. (not all of them are necessary, some call the others, in v2 I've simplified this)

Add the property you suggested to that derived class. In your GetFieldPersistenceInfo(s) overrides you first call the base class' methods. Then, you walk the data you passed in via your property and you replace the FieldPersistenceInfo objects with new instances which reflect the persistence info you want to use for that particular field. That's it.

Otis wrote:

My question then is: why do you need to query over multiple the same databases? Why is data shared among these databases in such a way that you need to query over multiple catalogs, when you have the same structure in every catalog? Often when you see querying over multiple catalogs, there's shared data in one catalog and specific data in another catalog, though that catalog with specific data doesn't have data which looks like the shared data.

This is a long history of a battle between us and our customer.

Otis wrote:

I find this very specific and IMHO it can fail in some occasions. If you really want this, you can do this today however.

Derive a class from DataAccessAdapter and override the GetFieldPersistenceInfo(s) methods. (not all of them are necessary, some call the others, in v2 I've simplified this)

Add the property you suggested to that derived class. In your GetFieldPersistenceInfo(s) overrides you first call the base class' methods. Then, you walk the data you passed in via your property and you replace the FieldPersistenceInfo objects with new instances which reflect the persistence info you want to use for that particular field. That's it.

Thanks for the tip, I will try it.