Universal DAL

"

I will blog about it as well as post on the PDNUG...
Here is a quick synopsis of what this is about (I removed a lot of comments and compiler directives)...

public class DBHelper
{
#region public enum DBLibrary

public enum DBLibrary
{
SqlClient,
OleDb,
Odbc,
OracleClient,
None
}

#endregion

#region public static void SetDBLibrary(DBLibrary dbLibrary)

/// <summary>
/// Holds the local client library to use
/// This is your default library to use
/// </summary>

private static DBLibrary dbLibrary = DBLibrary.SqlClient;
public static void SetDBLibrary(DBLibrary dbLibrary)
{
if (!Enum.IsDefined(typeof(DBLibrary), dbLibrary))
    throw new ArgumentOutOfRangeException("dbLibrary");
if(dbLibrary == DBLibrary.None)
    throw new ArgumentOutOfRangeException(
"dbLibrary"
, "The value \"None\" means no provider, which is not allowed.");

DBHelper.dbLibrary = dbLibrary;
}

#endregion

#region GetDbConnection

public static IDbConnection
GetDbConnection(System.String connectionString)
{
IDbConnection conn = null;
switch(dbLibrary)
{
case DBLibrary.SqlClient :
conn = new SqlConnection(connectionString);
break;
case DBLibrary.OleDb :
conn = new OleDbConnection(connectionString);
break;
case DBLibrary.Odbc :
conn = new OdbcConnection(connectionString);
break;
case DBLibrary.OracleClient :
conn = new OracleConnection(connectionString);
break;
default :
break;
}
return conn;
}

#endregion

}

 
Then in your code you do this:

IDbConnection conn = DBHelper.GetDbConnection(connString);

You would probably not even use this particular method unless you were going to use transactions. But, there are a number of other functions, namely ExecuteDataSet/ExecuteDataTable/ExecuteDataRow/ExecuteScalar/ExecuteNonQuery (among others)

This does make it very easy to add support for other providers (that follow the interfacing rules in System.Data).

"

 From David L. Penton, Microsoft MVP