Creating DBHelper that can support various types of database...

Yes I know there's a framework (EF, NH and whatever name that I've never heard before) that can solve the problem. Unfortunately, EF only works on new technologies (at least with .NET framework 3.5). What if we're stucked with an old Microsoft technologies (NH is another option, but I still couldn't figure out on how and where to begin with - ~ I ain't a .NET superstar ~)? Is it possible to do it then? Yes!!! back to basic stuff!!!

I've try it on .NET framework 2.0. IDBConnection, IDBTransaction and IDBDataParameter are available in System.Data namespace since .NET framework 1.1 however, DBDataReader in System.Data.Common was available not until .NET framework 2.0 (DBDataReader was derived by SqlDataReader, OracleDataReader and OleDbDataReader). Those are the important interfaces and class needed.

Basically we need to create an abstract layer to our db helper. Let's just call it IDBHelper. Define all basic methods that can support various databases. It's important not to have a method that is specific to any data provider such as ExecuteXmlReader.

    ...
    using System.Data;
    using System.Data.Common;

    public interface IDbHelper
    {
        IDbConnection CreateConnectionInstance(string connectionString); 
        
        ...
        
        DbDataReader ExecuteReader(IDbConnection connection, string query, CommandType cmdType, params IDbDataParameter[] commandParameters);
        
        ...
        
        IDbDataParameter CreateParam(string paramName, object paramValue);
    }

For concrete implementation. Create a class that implement IDBHelper interface. I named the class as SqlClientHelper.

    ...
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.Common;

    public class SqlClientHelper : IDbHelper
    {
        public IDbConnection CreateConnectionInstance(string connectionString)
        {
            return new SqlConnection(connectionString);
        }
        
        ...
        
        public DbDataReader ExecuteReader(IDbConnection connection, string query, CommandType cmdType, params IDbDataParameter[] commandParameters)
        {
            ...
        }
        
        ...
        
        public IDbDataParameter CreateParam(string paramName, object paramValue)
        {
            SqlParameter param = new SqlParameter(paramName, paramValue);
            ...
        }

        ...
    }

Then, the code for OracleClientHelper.

    ...
    using System;
    using System.Data;
    using System.Data.OracleClient;
    using System.Data.Common;

    public class OracleClientHelper : IDbHelper
    {
        public IDbConnection CreateConnectionInstance(string connectionString)
        {
            return new OracleConnection(connectionString);
        }
        
        ...
        
        public DbDataReader ExecuteReader(IDbConnection connection, string query, CommandType cmdType, params IDbDataParameter[] commandParameters)
        {
            ...
        }
        
        ...
        
        public IDbDataParameter CreateParam(string paramName, object paramValue)
        {
            OracleParameter param = new OracleParameter(paramName, paramValue);
            ...
        }

        ...
    }

Noticed that the instantiation of connection and parameter object has been done in the class itself. Ideally, if we don't use any specific data provider namespaces in DAL layer, it should be flexible enough to use any database without the need to change the code logic. The concern is more on the abstract layer method signature.

Create an object factory that'll return the helper instance. This is the only place that we need to change if the project owner aka our client suddenly decided to use different data provider in future (reality is cruel).

    ...
    public static class SomeObjectFactory
    {
        public static IDBHelper GetHelperInstance()
        {
            return new SqlClientHelper();

            // uncomment the code below for oracle client
            // return new OracleClientHelper();
        }
    }

The code at DAL

    ...
    using (IDbConnection connection = SomeObjectFactory.GetHelperInstance().CreateConnectionInstance(connectionString))
    {
        connection.Open();

        string query = "SELECT * FROM tblSomething WHERE Id = @Id";
        IDbDataParameter param = MyObjectFactory.GetDBHelperInstance().CreateParam("@Id", id);

        DBDataReader reader = SomeObjectFactory.GetHelperInstance().ExecuteReader(connection, query, CommandType.Text, param);

        ...
    }
    ...

This way, adding System.Data and System.Data.Common namespace should be sufficient enough to our DAL.