I normally use LLBLGenPRO to create my Data Access Layer (DAL) for my ASP.NET Web Applications, I have a new requirement that will require the same code base to access both Oracle and SQL Server depending on where the application is running the backend may change between SQL Server and Oracle. For example if the application is running at headquarters the backend database is Oracle and if running at a regional office the backend is SQL Server. What would be the best solution to this problem? Keep in mind the data structure will remain the same on both database systems. My first idea was to put a setting in the web.config I also was thinking about checking the server name. Both of these solutions requires me to generate to data access layers for example SQLServerDAL and a OracleDAL and depending on the server name or the configuration settings switch between the two. I am looking for how others would solve this problem. Keep in mind LLBLGenPRO does not currently have a OLEDB option or nor does it have a ODBC option when generating the Data Access Layer.

I have never done this before but this is my input.

You definatly need the adapter templates. Use that and generate the code against one database. You will have two projects, the dbspecific (A) and dbgeneric. Now run the code against the other database, you again will get two projects again, dbspecific (B) and dbgeneric. The dbgeneric projectgs should be identicial. So you only will need one (You must have the same structures). Now you can use the adapter from which ever databse you want to fill the entities in dbgeneric.

So now you need a method to get the appropriate adapter in what ever case you are in

so youd have something like

SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase adapter;

if (Oracle) adapter = new Oracle.DataAccessAdapter else adapter = new SQLServer.DataAccessAdapter

return adapter.

Then in your BL you will instantiate your adapter using that method and fill your entities normally

Well all I can say is I've been where you are and I feel your pain. LLBLGenPro will go a long way to ensure you can do this, but you must use it wisely.

lethologica's post is certainly the way to begin. However there are a number of issues you will encounter. Off the top of my head I can mention the following:

• You should isolate lethologica's "factory" code in a separate assembly. This is the only assembly you can ever reference your dbspecific genpro code from. All your other assemblies should reference IDataAccessAdapter, or DataAccessAdapterBase, and rely on the specific subclass instantiated by your factory assembly.

• This means you can't have any stored procedures, as the code necessary to invoke them is on the dbspecific genpro assemblies. If you MUST have stored procedures, you will have to add a façade to your factory assembly to invoke them based on the specific db engine. However SP's are a pain to maintain between different db's so if db independence is a must you should probably aim to leave them off anyway.

• Some painful differences between Oracle and SQLServer:

  • Oracle limits your identifier length to 30 characters (that includes reference names, constraint and check names, table names, field names, etc.). SQLServer either doesn't limit identifier length, or the limit is much higher and I never met it
  • By default Oracle is case insensitive and SQLServer is case sensitive, so if you used to have names like ThisIsMyTable in SQL Server you will find it gets to be THISISMYTABLE in Oracle, unless you quote names in Oracle, which is a bit of a pain unless you always use LLBLGenPro (because otherwise you must always quote your names in every query). If starting from scratch i would recommend going for standard THIS_IS_MY_TABLE style in both db's, as ugly as it is. GenPro can convert it to ThisIsMyTableEntity and you will not find any problems, otherwise it will be a pain.
  • SQLServer has identity fields, Oracle has not. You have Sequences in Oracle which you can sistematically substitute for identity fields, however you must then select the proper sequence for every table in the LLBLGenPro designer, which although feasible is a pain to mantain and will prevent you from fully automatizing the change db-refresh designer project-regenerate code cycle. So if starting from scratch I would recommend to drop identity fields and sequences altogether. Use GUID's or your own identifier generation mechanism to achieve more portability and build process automatization.
  • Be careful as to which data types you use in your database design. Use only absolutely portable data types such as number, date, char, and not much more than that. There are pages devoted to this, but to mention a couple of differences: SQLServer has GUID, Oracle has not. SQLServer has bit, Oracle has not. Etc. etc. If you want to minimize your pain use only portable data types.

  Find a db design tool that properly allows you to maintain your db design and generate it to either db without pain. We started with Visio and frankly it blows. Then switched to PowerDesigner and it's generally better than Visio but regarding this particular feature it blows too. So if you have any tool suggestions for me please make them :-) Maybe I need more experience with PowerDesigner... but I'm beginning to hate it... and it has its own internal binary representation and internal repository with version control, which makes SCM a pain too.

  Currently I believe that the way to go is to decide which subset of the db feature universe you want to work with (i.e. which datatypes, identity fields or not, etc.), and build yourself a custom script that translates from an abstract specification of your entire db schema creation script to the necessary db specific script, making the identity/sequence bit/number(1) mappings or whatever.

  If you want to have hand-written SQL (views for example) take extreme care to keep the SQL portable, as there are a zillion tiny differences in SQLServer and Oracle SQL, such as string functions and whatnot. Luckily as of OracleX (not sure about the X, 8.2 or 9) you can write ANSI LEFT OUTER JOINS and stuff, before that it would be even more painful as Oracle had that annoying (+) syntax for them.

  Query isolation levels are another catch, Oracle only allows Serializable and ReadCommited, SQLServer allows those two but also allows ReadUncommited and RepeatableRead.

Phew, that got cathartic. Maybe there are even more catches, those I remembered easily

Salut álvaro.-

With 1.0.2005.1, going into beta on wednesday, you'll find a new very important feature: type converters. These let you keep your sqlserver code with guid's and booleans and what not, and map them onto Oracle constructs and convert the values transparently. So you can map for example a bool onto a NUMBER(1,0) field in oracle, or a GUID on a char(32) field or what have you. This is generic functionality and can be used for whatever you can think of, so it's not limited to oracle or booleans.

Far in development is also a tool which converts a project for database X to a project of database Y and vice versa. This should further help people targeting multiple databases.

Otis wrote:

With 1.0.2005.1, going into beta on wednesday, you'll find a new very important feature: type converters. These let you keep your sqlserver code with guid's and booleans and what not, and map them onto Oracle constructs and convert the values transparently. So you can map for example a bool onto a NUMBER(1,0) field in oracle, or a GUID on a char(32) field or what have you. This is generic functionality and can be used for whatever you can think of, so it's not limited to oracle or booleans.

Far in development is also a tool which converts a project for database X to a project of database Y and vice versa. This should further help people targeting multiple databases.

Yes, that will be a good addition. However to really take advantage from it in the multiple databases scenario you would need that kind of support from your db design tool. AFAIK there is no tool that permits it (however I admit to know little in this field). Besides you would have the maintainability issue, that is, keeping both designer projects in proper synch. In a large project it can get to be quite a task. And it would also defeat the automatization target.

The project converter tool would be the real holy grail here!

We did this - we supported Oracle And SQL Server. Its actually easy using LLBLGen There were a few tricks I had to master to get things running smoothly - the biggest pain was making sure that database fields in Oracle and SQL Server were compatible so that they mapped to the same .NET type, but it looks like Frans has made a big change here in the latest beta to make this a whole lot easier!

I have somewhere knocking around a tool I wrote that compares a SQL Server LLBLGen project with an oracle one and reports any problems... It was simple to create, Frans pointed me to the 3rd party utility someone had created that makes changes directly to the LLBLGen project file, which I used as the template! It also made changes to the Oracle project - to change the UPPER CASE TABLE NAMES that oracle uses to NicePascalCaseTableNames, so that they matched SQL Server ones

Other top tip - create custom templatesets that generate the DBSpecific for both the Oracle and SQL Server project, but only one DBGeneric (makes generation less confusing!).

Alvaro wrote:

Find a db design tool that properly allows you to maintain your db design and generate it to either db without pain. We started with Visio and frankly it blows. Then switched to PowerDesigner and it's generally better than Visio but regarding this particular feature it blows too. So if you have any tool suggestions for me please make them :-) Maybe I need more experience with PowerDesigner... but I'm beginning to hate it... and it has its own internal binary representation and internal repository with version control, which makes SCM a pain too

Have a look at ERwin Data Modeler i used this tool back in my Delphi days. This tools used to be very powerfull but also rather expensive - But it should do the job with ease - It allows you to design your DB via an ERD or reverse engineering and can generate to a multitude of DB's. You can find the new version here. http://www3.ca.com/Solutions/Product.asp?ID=260

I still have version 3.5.2 and that could reverse engineer and generate to and from the following SQL DBMS and Desktop DBMS. I might be wrong but i think it can even convert Stored Procs for you.

• AS/400
• DB2
• HiRDB
• INFORMIX
• Ingres
• Interbase
• Oracle
• Progress
• Rdb
• Red Brick
• SAS
• SQL Server
• SQLBase
• SYBASE
• Terdata
• WATCOM / SQL Anyware
• MS Access
• Clipper
• dBase III
• dBase IV
• FoxPro
• Paradox

It is / was really a Kick Ass Tool

This thread is a bit old, but I have a question/idea relating to the topic:

We use LLBLGen for both Oracle and SqlServer, and different catalogs within those 2 database types, different schemas for all.

If we have 2 catalogs for oracle and 2 for SqlServer, that means 4 LLBLGen Projects, and worse, 4 VS.Net projects.

It would be nice if those 4 could be rolled into 1 .Net project.

Right now the driver set/database type is tied to the LLBLGen project which is tied 1:1 to a .Net project. Could the LLBL project be abstracted up one layer so that I could have multiple catalogs / database types? Kind of a master project. Multiple LLBLGen projects rolled up into 1 .Net project.

Is there already an automated option for this that I am missing? I am guessing I could maintain the csproj file manually rather than use the generated one, but that is problematic.

Thanks,

Jason