Common method in datalayer for SQL/Oracle DB's..

Question

In .NET if my DB is SQL Server then in datalayer we have statements SQLConnection, SQLCommand, SQLDataReader...

Suppose we change the DB to oracle then we need to change the statements to OracleConnection, OracleCommand, OracleDataReader and so on everywhere.

Instead how can I have some generic method so that at runtime based on DB type I will call the respective methods.

Answer 1

Here's one idea (not a run-time solution, but may work for you)...

Create a IDataAccess interface (or perhaps an abstract class) with some virtual methods like Get(), Update(), Delete(), etc. Then create some base classes like SqlDataAccess and OracleDataAccess, which implement IDataAccess. Then create your RanjiniDataAccess class that inherits from either SqlDataAccess or OracleDataAccess.

Answer 2

One way would be to use the OleDbConnection etc classes defined in the System.Data.OleDb namespace - you should then be able to point your app at either an Oracle or a SQL Server database by changing the connection string.

Bear in mind this may have a slight performance trade-off, as the Sql and Oracle objects are optimised for those DBs.

In reality though, you should ask how likely it is that your database platform will ever change to another vendor.

Answer 3

This is the reason why Microsoft implemented database agnostic interfaces in the System.Data namespace. You would be well advised to take a look at IDbConnection, IDbCommand, IDataReader, IDbDataParameter and other that encapsulate the basic functionality and yet allow you to support multiple database systems.