Agnostic Connection Handlers in .NET

Question

I'm developing a simple database acessor for a system, but i need to support 2 databases, PostgreSQL and Oracle (XXg);

A example would be like this (already working for pgsql)

... bunch of properties, constructors and fields

private String BuildConnectionString();

public Int32 ExecuteNonQuery(NpgsqlCommand sqlCommand);

public NpgsqlDataReader ExecuteQueryReader(NpgsqlCommand sqlCommand);

public Object ExecuteScalar(NpgsqlCommand sqlCommand);

I was wondering if it is possible to build a class to handle these two databases using only System.Data interfaces, like IDataReader, IDbConnection, etc. I think it is, but i'm kinda stuck when i need to instance the connection. Example:

    public PgSqlConnectionHandler(String username, String password, 
               String database, String server, Int32 port)
    {
        this._username = username;
        this._password = password;
        this._database = database;

        this._server = server;
        this._port = port;

        this._connection = new NpgsqlConnection(BuildConnectionString());
    }

In the other case, i would have (_connection is typeof(IDbConnection):

public AgnosticConnectionHandler(String userName, String password, 
               String database, String server, Int32 port)
    {
        this._userName = userName;
        this._password = password;
        this._database = database;
        this._server = server;
        this._port = port;

        this._connection = ????
    }

Is there a quick way to do this?

These are simple database requests, I don't need anything fancy, it's a small system.

Thanks for your attention!

Answer 1

Use the Factory Pattern to create your database connection.

I.e., you have a static method that creates a connection where you specify what type it is and all of the relevant information, and it returns the Interface (IDbConnection) (but correctly typed in its construction).

The core of this method would likely be an if statement that returned either the Oracle or Postgre connection.