Hi Guys,

I wonder if you can give me some advice. I've been tasked with creating an application framework for my organisation to help standardise our development efforts.

So far, i've come up with a base template that includes:-

• Skinning
• Exception Management
• Config Management
• Security Management

These all work great, I have Provider Interfaces for Exception, Security, and Config Management, and I have implementations of these Providers specific for my project. e.g. I have made a standard one for each. Because its a provider, if other developers have a need to handle it differently they can create a new provider.

Now I know the benefits of an OR Mapper having used it on a few applications recently, and how much time it actually does save me.

My concern is that how can I get something similar to the provider sense for data access? Especially when the O/R Mapper generates all your business objects. I am concerned by locking ourselves into a tool the framework will become unusable if we have to implement an application using another database not supported by LLBLGen.

Being part of the core team for dotnetnuke we recently upgraded our DAL layer to support Access/Sql Server using a provider model, this worked quite well, but again we were writing copious amounts of Sql. This is something I do not wish to go back to, since we lose so much productivity by not using an OR mapper, but, I think we also lose a little bit of flexibility in our framework.

So my other question is, how would you package an application for LLBLgen that supports 2 databases (e.g. oracle & sql server), just have 2 installation packages?

smcculloch-home wrote:

I wonder if you can give me some advice. I've been tasked with creating an application framework for my organisation to help standardise our development efforts. ... Now I know the benefits of an OR Mapper having used it on a few applications recently, and how much time it actually does save me. My concern is that how can I get something similar to the provider sense for data access? Especially when the O/R Mapper generates all your business objects. I am concerned by locking ourselves into a tool the framework will become unusable if we have to implement an application using another database not supported by LLBLGen.

First of all: the plan is to support every database that has a .NET provider. Oracle is up Monday November 3rd, and after the SDK and the new template set I've planned to add support for interbase/firebird, mysql and postgresql (in that order), plus when DB2's .NET provider is done, that one is added too. But there will always be databases not supported or not at the moment you want.

Now, to implement a full database generic framework, you have to define the framework first, then the intermediate layer that connects your framework with several databases. I don't see the objects generated by LLBLGen as 'business objects' per se. The classes consuming the classes generated are the business objects for me. Making these objects generic requires OR a data layer that is always the same, OR a transformation layer between the dal and the BL objects. So if you generate the code with LLBLGen Pro, and you want to use a different product in the future, you can still do so, as long as that product provides the same API. This is not impossible, as other products will use templates too. It is however very hard to write code that is true database generic, so you most of the time keep the code that can be generic as a generic code base and all code below it is changed per database, there is not a lot of choice...

Being part of the core team for dotnetnuke we recently upgraded our DAL layer to support Access/Sql Server using a provider model, this worked quite well, but again we were writing copious amounts of Sql. This is something I do not wish to go back to, since we lose so much productivity by not using an OR mapper, but, I think we also lose a little bit of flexibility in our framework.

Sql delimits your flexibility a lot, since f.e. oracle and sqlserver are day and night when it comes to SQL. So to get database generic code, you have to work on a higher abstract level, otherwise the code will not be portable. LLBLGen's code for example is not portable code, but the code calling/consuming it is (thus your code). THe templatesets for oracle and Sqlserver differ in 5 places, for the rest they are identical. Most work is inside the DQE's.

So in theory, you can write the bits that are missing for a database that is not supported by hand and have your code be ported to another database. Most of the code is inside the DQE and the stored procedure templates that is different. (and the dbutils*.cs/vb and entityfield factory templates. The rest is pretty identical and the dao templates only differ by a using/imports statement (for the dqe).

It's a topic that I hope to address with the SDK and the template set that should be released later in november. The SDK opens up a lot of code (including the database drivers and template parsers/emitters) and I hope to illustrate clearly how to modify that code to make your own additions, so you can for example code database specific elements by hand to port your generated code base to a database LLBLGen Pro doesn't support. If others have ideas that are even more efficient, let me know

So my other question is, how would you package an application for LLBLgen that supports 2 databases (e.g. oracle & sql server), just have 2 installation packages?

The current template set requires 2 builds: one for database A and one for database B. You can't pass an entity of database A to a DAO of teh code of database B. In theory, this is good, because an application for SqlServer for example can require more tables / entities than a version on Oracle, or the Oracle version is chopped up in more than 1 schema. You can now address this by simply generate 2 projects. However, this can also be cumbersome when you have a schema that is identical on both databases (I don't think that's possible with the type differences, but you can get very far in this).

This is one of the main problems I want to solve with the new template set in November. That one has to have separate DAO objects which accept a connection string (or read it themselves) and for example an entity and get the order to persist it/save it. This will give more flexibility to application developers who target more than 1 database vendor. It is however a little complex, because the native types of the database fields are stored with the fields so the DQE knows how to construct the queries.

To illustrate how complex this is: numeric fields in Oracle are most of the time stored as 'Numeric' and then a size and precision. A Numeric field is however a decimal. But to make it efficient, it is wise to migrate the numeric type using the precision and scale to a different type, like Int16, float or int64. SqlServer has all these types and then some more...

The problem arises when you pass an entity read from SqlServer and generated using SqlServer to a DAO object that knows how to deal with Oracle, to save it in an oracle database. The DAO object has to translate the sqlserver type info to oracle. I don;t think that is possible in all cases.

However the good news is that the type stuff is stored in the entityfield factory classes. So it should be possible to have database generic code (which is interchangeable between target databases) and database specific code separated in for example 2 assemblies. A project with oracle and sqlserver as target databases should then ship with 3 assemblies: 1 with the database generic code and 2 with for each database the database specific code. But this is something for the template set of november.

Bottom line: I've tried and will try even harder to make the tool as flexible as possible and will open up anything (except the designer and the core logic below it) to make that possible, or provide documentation how to utilize what is made generic. This should result in code that is usable or modifyable to meet your needs. However as I said: writing database generic code is very very hard, and most of the time you end up with simply rewriting stuff for another database just because they do stuff upside down in that one or lack a key feature...

First of all, thanks for your detailed response Frans, your customer support is always excellant!

First of all: the plan is to support every database that has a .NET provider. Oracle is up Monday November 3rd, and after the SDK and the new template set I've planned to add support for interbase/firebird, mysql and postgresql (in that order), plus when DB2's .NET provider is done, that one is added too. But there will always be databases not supported or not at the moment you want.

Terrific

Now, to implement a full database generic framework, you have to define the framework first, then the intermediate layer that connects your framework with several databases.

Yes I understand what you are saying... and you are correct that the difficulty is in the differing implementations of the databases. So you would have to define a database independant object model, and have that map to your O/R persistance layer if you wanted to support multiple databases in a generic framework.

I don't see the objects generated by LLBLGen as 'business objects' per se. The classes consuming the classes generated are the business objects for me.

Ahh yes, sorry I was a little tired when I typed this, and didn't really think. So the business objects would be the helper/manager classes, and the codebehind classes that use the entities right?

Sql delimits your flexibility a lot, since f.e. oracle and sqlserver are day and night when it comes to SQL. So to get database generic code, you have to work on a higher abstract level, otherwise the code will not be portable. LLBLGen's code for example is not portable code, but the code calling/consuming it is (thus your code). THe templatesets for oracle and Sqlserver differ in 5 places, for the rest they are identical. Most work is inside the DQE's.

Yep I agree, I probably didn't phrase what we did right, we actually created two different providers (access & sql) - they both implemented the same provider interface for the portal. The access version contained sql and the OleDb Provider, and for the Sql Server one, we used SPs, and the application block. Needless to say, it took a while.

So using two generated layers (e.g. Oracle + Sql Server), you would dynamically invoke the selected instance in some configuration file? This of course, is if the schemas match and everything is OK.

The current template set requires 2 builds: one for database A and one for database B. You can't pass an entity of database A to a DAO of teh code of database B. In theory, this is good, because an application for SqlServer for example can require more tables / entities than a version on Oracle, or the Oracle version is chopped up in more than 1 schema. You can now address this by simply generate 2 projects. However, this can also be cumbersome when you have a schema that is identical on both databases (I don't think that's possible with the type differences, but you can get very far in this).

This is one of the main problems I want to solve with the new template set in November. That one has to have separate DAO objects which accept a connection string (or read it themselves) and for example an entity and get the order to persist it/save it. This will give more flexibility to application developers who target more than 1 database vendor. It is however a little complex, because the native types of the database fields are stored with the fields so the DQE knows how to construct the queries.

Sounds Great!

Bottom line: I've tried and will try even harder to make the tool as flexible as possible and will open up anything (except the designer and the core logic below it) to make that possible, or provide documentation how to utilize what is made generic. This should result in code that is usable or modifyable to meet your needs. However as I said: writing database generic code is very very hard, and most of the time you end up with simply rewriting stuff for another database just because they do stuff upside down in that one or lack a key feature...

Yep you are doing a fantastic job too!

Hey Otis,

Having worked on software that supports multiple databases (SQL/DB2/Pervasive) I appreciate what you are doing and saying here.

I appreciate your efforts and prompt response in supporting your product.