I have a .NET application using an Oracle client to access an Oracle database.
If someday we migrate the database from Oracle to any other relational database, I with not to be forced to change the data access code in my application.
I want to just change something, a driver or DLL perhaps, and the connection string such that the rest of code picks the data from appropriate database.
How can I do this?
Have a look into Microsoft's entity framework. This ORM should be able to connect to and communicate with most relational databases.
http://msdn.microsoft.com/en-us/data/ef.aspx
Books I recommend about it: http://shop.oreilly.com/product/9780596807252.do http://shop.oreilly.com/product/0636920022220.do
Or watch: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2009/DTL312
ADO .NET contains a set of abstract classes and factories which means you don't have to consider what type of database you're connecting to as long as there is an ADO.NET driver
You would need to use the factory methods to new up the various classes you use, so that you aren't tied to a particular provider.
More information is in this msdn article
Of course, simply being able to create connections to and run queries against multiple types of database is only half the problem. You also need to make sure the queries themselves are compatible with whatever database you intend to use.
You can use a subset of SQL which is likely to be understood by the major SQL servers, or the entity framework has a number of providers which generate database-specific SQL from Linq. This is still not foolproof though.. you'll still be able to write queries which can't be translated by providers which don't support them
What you need can be found on the System.Data.Common namespace, and consists in a series of classes designed specifically to deal with that kind of requisite.
The classes in there, use a Design Pattern called Abstract Factory, the main idea is that your connection string provides to the abstract factory the provider, i.e, SQL Client or Oracle Client, and based on that abstract factory can create a specific factory to generate connections to talk with a specific database engine. Enough talking, this is the code:
string providerName = ConfigurationManager.ConnectionStrings["DbConn"].ProviderName;
string connectionString = ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString;
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
You can read more about that here. About the abstraction you require, I recommend you to do the following, implement all your classes to access your data model first, then extract the interfaces from those classes.
Implement you Business Layer code relying on those interfaces, and use the Abstract Factory design pattern to create the concrete implementation classes behind the scenes.
There is a good answer here in SO regarding Abstract Factories, give it a look. Also bear in mind that if you use the Abstract Factories, you can have the both of the two worlds, this is, you can have a Generic Provider that talks to the most part of you databases, and you can have a specific one just for Oracle.
The generic provider is the one using DbConnection objects, this works for all databases. But sometimes you may want to take most value of a specific database, imagine you install your system in a topology where in some machines it has a dedicate oracle just for you app, and you want the code to be very efficient there. Then for that configuration you can use the provider specific for Oracle, the beautiful thing about abstract factories is that they let you use your concrete types, as long as your upper layers code just talks through interfaces.
Ultimately for the Abstract Factories you can have a setting in your web or app.config indicating what mode do you want, something like "Generic", "OracleSpecific", "SqlServerSpecific". This values are just convention you can create your own.
