The Provider Factory Pattern

At our weekly tech lunch we watched Jean-Paul Boodhoo on Demystifying Design Patterns Part 1 on dnrtv.com and he showed us how to use the "The Provider Factory Pattern" which is basically using a factory to give a data provider that is completely abstracted away from the database request methods that use it.

This is some very cool stuff basically you have a factory that provides a methods for getting a connection

public IDbConnection GetConnection()
{
     IDbConnection connection =  _frameworkDBProviderFactory.CreateConnection();
     connection.ConnectionString = _authenticationSettings.ConnectionString;            
     return connection;
}      

though an interface so you can call any type of Database who's connection object implements the IDbConnection interface (SQLServer, MySQL, Oracle, etc.) (see full source)

To use this you create a request method

public bool AuthenticateUser(string userName, string password)
{
    bool success = false;
    DataTable requestTable = new DataTable();
    using (IDbConnection conn = UserDataProvider.GetConnection())
    {
        conn.Open();
        IDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select UserName, Password from users where UserName = @Username and Password = @Password";
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@UserName",userName));
        cmd.Parameters.Add(UserDataProvider.AddParameterWithValue("@Password", password));
        IDataReader reader = cmd.ExecuteReader();

        using (reader)
        {
            requestTable.Load(reader);                    
        }
    }
    if (requestTable.Rows.Count==1 )
    {
        success = true;
    }
    requestTable.Dispose();
    return success;
    }

The request method can open this connection, pass in your ANSI SQL query, parameters, etc. then execute it. (see full source)

The Method doesn't know or care what kind of data base it's talking to, additionally starting in the 2.0 framework you can pull the type of Database from the connection string configuration so all you have to do is have the database client lib included in your project and your good to go.