switching between different database backends

[jennyflower]

Hi, I am wondering how easy you rekon it would be to set up an application which allows a user to switch between say an SQL Server database and a MySQL database so if the user wants to use MySQL instead of SQL Server they can easily set this up.

I have tried doing it all programmatically but have been told this would take tons of coding and time if I wanted to bind textboxes etc to a data adapter/data source and as the .net providers for these 2 databases have different commands (e.g. sqlconnection and mysqlconnection) for all the different sql things you can do, there are either going to be lots of ifs or it will a case of writing multiple functions and classes which are identical but one is mysql based and one is sql server based. right?

I have also tried to do it using the drag and drop data tools in vs2005 but this seems to have the same sort of problems and doing this programmatically, i.e. the commands are different so there are still going to have to be different classes and functions to make each database work correctly depending on which one is chosen. But using the drag and drop components also seems to have to added problem that as everything is set when the application / form loads the databindingsource and table adapters need to be altered on load depending on what database backend is being used which means using ifs and possibly more functions and classes need to be written.

If any one managed to understand that (!!!) is there going to be a simple way of getting this to happen or is it going to be very complicated and time consuming to do whatever method is chosen

Thanks!

 

[ThatRickGuy]

I know of no simple (ie: drag and drop) solution for this. I do have a system that can do this, but it is a custom data abstraction layer (DAL) that was designed with that situation as a requirement. You could try nHibernate, it is a DAL of sorts (or Persistent Object Storage I think they call it) that you may be able to configure into what you are looking for.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

Yes nhibernate will do that but then you would need to program in an OO manner. Which means that you use model-view-presenter or model-view-controller and you build a service-layer to fill the model objects with the nhibernate layer.

Sun (J2EE) has a very nice pattern for this and is is called DAO. This article is for Java

http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

but it is easily transformed into a VB.Net version. I believe that the DAL rick wrote follows the same lines. This pattern makes it easy to switch databases.

Christiaan Baes
Belgium

"My old site" - Me

 

[jplockwood]

I've used NHibernate for just that. Database configuration was in an XML file and I could quickly and easily switch between different databases/SQL dialects. Only problem may be if you use GUIDs as surrogate keys. On one project I was developing locally on SQL Server and deploying to Oracle 9i. On another I was testing using SQL Server again and interfacing with a 3rd party MySql implementation.

Here's my response to the last response...
>> Addressing response by: chrissie1 (Programmer) 6 Aug 07 17:47
Just a quick response...I respectfully have to disagree and believe that NHibernate may well prove usefull.

>> Yes nhibernate will do that but then you would need to
>> program in an OO manner.
True, but you should be doing that anyway in .Net.

>> Which means that you use model-view-presenter or model-
>> view-controller and you build a service-layer to fill
>> the model objects with the nhibernate layer.
False, you don't have to implement the MVP pattern and you could even utilize NHibernate in the presentation layer (such as code behind).

>> Sun (J2EE) has a very nice pattern for this and is is
>> called DAO. This article is for Java
>>

http://java.sun.com/blueprints/corej2eepatterns

>> /Patterns/DataAccessObject.html but it is easily
>> transformed into a VB.Net version. I believe that the
>> DAL rick wrote follows the same lines. This pattern
>> makes it easy to switch databases.
True, but it's not unique to Java. Microsoft shops have been developing DAOs for some time, but the old-school DAO approaches used can often be inflexible and limiting (I think that the example in the Java article above is inferrior too). If you are using .Net 2.0+, generics can be used to create interesting DAO scaffolds. Bill McCafferty wrote an interesting article on The Code Project about using generics and NHibernate for DAO authoring. Check it out here:

http://www.codeproject.com/aspnet/NHibernateBestPractices.asp

The really kickin' thing is that you can persist a complex object and the NHibernate mapping will handle sub-object dependencies for you when transfering data to/from the datasource.

Check it out and tell me what you think. If you do use NHibernate respond and let me know. I'll try and help. Learning curve can be high on some complex operations and it takes a while to grasp what NHibernate is doing under the covers and what its strengths really are. It has proven useful to me in the past and has helped in reducing maintenance costs. Good luck!

Joshua Lockwood
Texas