Agnostic Connection Handlers in .NET

Question

Hello all,

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

You could use the IDbConnection interface, and create a factory to create the concrete classes. From that interface you can then create IDbCommands and attach the appropriate parameters and run them to get DataReaders or DataSets. I've done this before to great effect.

Answer 2

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.

Answer 3

You also could to use OleDb data provider, as both databases have OLEDB providers. If not, you always will have ODBC, for good or worse.