how to make my appliaction works on multiple database types?

Question

hello everybody..
im working on a windows aplication project using vb & visual studio2005.net, & i want to buil it to work on multiple database (oracle & sqlserver), but i dont know the best way to do that, can anyone help me plz....

Answer 1

Use the microsoft paterns & practices data access application block as a gateway to access your data. The code can be authored fairly generically for both databases. Its an abstract factory class that will generate db calls based off of configuration settings.



http://msdn2.microsoft.com/en-us/library/aa480453.aspx



Any technology that enables this capability, such as the db providers shipped with visual studio 2005, nhibernate, or the p&p DABB will slow your code down. They will not execute optomized sql code, but rather lowest common denominator calls. Performance and cross platform compatibility are at odds. They're not terrible, but it does make a bit of a difference.

Answer 2

You are getting into very shaky ground trying to make it work with any database, very few major software houses attempt more than one, because there are so many differences in the sql, and particularly in the way tables are defined, you will have real problems with this. For serious database work you are better off using a commonly available data source (mysql maybe) and web base the front end. This will run on any platform.

Answer 3

Microsoft recommends that today projects should be written using the Database Provider Model.



This basically means that you write your ADO as a distinct layer that supports pluggable code that automatically, simply and immediately does a good deal of the plumbing connecting to the specified database. If you wish to connect to a different database engine, you simply configure a different Database Provider in your project's config file (or embedded in your main code).



This works almost 100% when migrating between different versions of the same engine (eg from SQL2K to SQL2K5). Pointing to an altogether different engine (eg from MS SQL to Oracle) requires you to also re-write and support parallel code for each engine.



One vendor's description of their Oracle Provider

http://www.ftponline.com/vsm/2004_11/magazine/departments/firstlooks/



The MSDN reference

http://msdn2.microsoft.com/en-us/library/aa905797.aspx

Answer 4

Hi,



We have done just that with our application. It can be done, but it is hard work. You need to think about a lot of stuff.



One alternative may be to look at nHibernate, which is an ORM package. (Object Relational Mapper). It does much more that just abstract your database, but it is a nice side effect that this layer takes care of database specifics.



Good luck

Reddy

Answer 5

encapsulate all database functionality within classes that have a common interface, you can change databases easily this way



and ado.net packaged with vb2005 supports both sql server and oracle databases