Connecting to any database at runtime

In this post we will discuss how straight forward is to provide a solution for this science fiction subject! Our frameworks (XAF and XPO) along with a few smart classes are fully capable of doing all the hard job and spare our resources. At the end we will be able to apply all the XAF tools / modules in any legacy database. The module can connect the database world with powerful tools like the DX grids. In addition we can use use XAF’s main metadata storage (the model), the security system and all XAF’s modules. These tools are are so powerful that are able to produce logic by themselves! Having such great configuration flexibility it is possible to write behavior after distribution thus you enter new markets and ideas! In addition the module allows you to control the logic after generation using Xaf’s meta driven developing model.

Retrieving any database metadata

XPO is the best candidate for this. since it can transparently talk  to more than 15 different database systems!

The metadata API used from XPO to describe its supported databases is very simple as shown,

image

In a nutshell for each supported database, XPO will return a set of DBTable classes. Each DBTable will be associated with one DBPrimaryKey class and a collection of DBColumn and DBForeighKey classes. Next is the code required to get the DBTable classes out of database and  with no surprise again is very simple!

var storeSchemaExplorer = ((IDataStoreSchemaExplorer)XpoDefault.GetConnectionProvider("ConnectionString", AutoCreateOption.None));

var storageTables = storeSchemaExplorer.GetStorageTables(storeSchemaExplorer.GetStorageTablesList());

foreach (DBTable dbTable in storageTables) {

    foreach (DBColumn dbColumn in dbTable.Columns) {

        //now we are ready to do magic!

    }}

Connecting at runtime – The magic part

In order to query and create CRUD operations for a database system XPO requires a set of business classes. However, now there is a runtime factor involved. We need to create these business classes dynamically.

XAF’s modularized architecture made this task too easy for us! We already created a dynamic class generation module called WorldCreator in the past. WorldCreator maps the structure of an assembly into persistent classes.

image

What we need is for each database to create a new PersistentAssemblyInfo, then for each DBTable a new PersistentClassInfo and finally for each DBColumn a new PersistentMemberInfo. In pseudo code this will look like,

var persistentAssemblyInfo = ObjectSpace.CreateObject<PersistentAssemblyInfo>();

foreach (DBTable dbTable in storageTables) {

    var persistentClassInfo = ObjectSpace.CreateObject<PersistentClassInfo>();

    persistentAssemblyInfo.Name = dbTable.Name;

    persistentAssemblyInfo.PersistentClassInfos.Add(persistentClassInfo);

    foreach (DBColumn dbColumn in dbTable.Columns) {

        var persistentMemberInfo = ObjectSpace.CreateObject<PersistentMemberInfo>();

        persistentMemberInfo.Name = dbColumn.Name;

        persistentClassInfo.OwnMembers.Add(persistentMemberInfo);

    }

}

WorldCreator UI allows further extension of the model as simple as writing .NET code or creating new objects. More ideas and contributions are always welcome.

Up to this point everything was extremely easy for us and almost shocking in terms of productivity! I believe there is no reason to change that feeling so lets move to our final task.

WorldCreator is simply a code generation module. It generates assemblies which include XAF modules and loads them at application startup. However that assembly has no difference at all from the assemblies we create at design time. The persistent classes of this dynamic module belong and point back to main XAF database. What we want here is to redirect the generated sql statements back to the original mapped database.

Once more we have most of the job ready! eXpand already uses a proxy version of ObjectSpaceProvider to support scenarios similar to show Business Classes from several databases in a XAF application. Thus associating a WordCreator assembly (set of business classes) with a database is a rather easy job.

Connecting to any database at runtime at first sounded very complicated. However our frameworks proved once more that they can raise our productivity and make our jobs so simple that even such complex task can fit in a small post like this one.

The new module DBMapper, is already released as part of eXpand framework’s modules collection.

We would appreciate your feedback on this post. Has it been useful to you? Feel free to contact us with any further questions.

5 comment(s)
Francois Wannenburg

Hi Tolis,

Your post is definitely useful.

I have a question though.

I have an application that creates one database per client per year. Lots of information is entered into the database about the client during a two month period and the following year, a new database is created, and the process starts again. Every year there will be around 1200 to 1300 databases being created (one for each client).  I need to collate/merge all the information from all the databases into one database and / or XAF application. I would like to query the various databases from this one XAF application and run reports that I will design at run time.

Would the WorldCreator and the application of it described in your post be able to accomodate this scenario?

I am thinking of creating database class that would be used to store the location of the databases. As each new database get added to the database class, they would be added to the application and their data collated/merged with the existing databases. Properties would be: Client, Financial Year, Database location (or connection string).

Kind regards,

Francois Wannenburg

21 July, 2012
Apostolis Bekiaris (DevExpress)

WC will create same name classes for multiple databases, this means that XAF will not run. However you can script your self out of this. The non clear part is the merging, what exactly it means?

21 July, 2012
Francois Wannenburg

Hi Tolis,

Thanks for the quick response! Much appreciated.

The merging means being able to compare data captured in one database (from one year) to the data captured in other databases (from previous years) per client and across clients. This would most likely require "master" business classes populated with data from similar child business classes.

I have been thinking that harvesting the data from the various databases into a DW would be the easier route.

That way I won't have to script myself out of anything.

Thanks.

21 July, 2012
Apostolis Bekiaris (DevExpress)

WC will generate assemblies with modules at runtime. They will be load from XAF in the next cycle. The idea is to apply the powerful XAF  UI to a given database. It;s really unclear to me how WC can help you move your data in a central database since its only able to query them

21 July, 2012
Francois Wannenburg

Hi Tolis,

Thank you for your insights. I was not planning to use XAF and WC to move the data to a central database. This would be done beforehand.

Thanks again.

I can see though how I can use WC as described in your post to connect to any of the thousands of databases without having to create an XAF app for each one. This would allow the users to interrogate the data in a way they cannot currently.

Kind regards.

21 July, 2012

Please login or register to post comments.