Introduction
This is my first article, so please bear with me. Recently at work, I've been asked to redesign a VB database layer that utilizes the Microsoft Enterprise Library. Now, the Enterprise Library is great, but it's more than what I need. In this new layer, we need to be able to easily switch our software from using one database to another. What better thing to do than use C# Generics to create an abstract database class that can be used with ODBC, OLEDB, and SQL?
Our Abstract Class
Why abstract? I'm not really sure why, but I felt like building it that way. If you have got a problem with it, let me know. First, we'll start with defining the class.
public abstract class DataBase<TParameter,TDataReader,TConnection,
TTransaction,TDataAdapter,TCommand> : IDisposable
where TParameter : DbParameter,IDataParameter
where TDataReader : DbDataReader,IDataReader
where TConnection : DbConnection,IDbConnection,new()
where TTransaction : DbTransaction,IDbTransaction
where TDataAdapter : DbDataAdapter,IDataAdapter,new()
where TCommand : DbCommand, IDbCommand, new()
{
The "where TConnection : DbConnection,IDbConnection,new()" requires that the type passed in for TConnection be or inherit from DbConnection and implement IDbConnection. Why did I add the interface on top of DbConnection when DbConnection implements IDbConnection? Just to show you that you can require the type to implement many of the interfaces. The "new()" means that the type being passed must have a parameterless constructor. I want that so that later in my code, I can instantiate the TConnection object that is passed in. Otherwise, you'll get a compilation error because the compiler doesn't know if the type you passed in can be instantiated.
Let's move on to actually instantiating the connection object.
protected TConnection _conn;
protected TTransaction _trans;
public DataBase(string ConnectionString)
{
_conn = new TConnection();
_conn.ConnectionString = ConnectionString;
}
First, we declare our connection variable of type TConnection and do the same for our transaction object. In our constructor, we can simply instantiate our object like normal because we used the new() keyword in defining our type.
Now, let's create our ExecuteDataset(), ExecuteScalar<T>(), ExecuteNonQuery(), and ExecuteDataReader() methods.
ExecuteDataSet
protected DataSet ExecuteDataSet(string StoreProcName, List<tparameter /> Params)
{
bool internalOpen = false;
DataSet ds = null;
TDataAdapter da;
TCommand cmd;
try
{
ds = new DataSet();
da = new TDataAdapter();
cmd = new TCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName;
if (_trans != default(TTransaction))
cmd.Transaction = _trans;
else
cmd.Connection = (TConnection)_conn;
if (Params != null || Params.Count > 0)
{
foreach (DbParameter param in Params)
cmd.Parameters.Add(param);
}
da.SelectCommand = cmd;
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
internalOpen = true;
}
da.Fill(ds);
return ds;
}
catch (DbException DbEx)
{
throw DbEx;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (internalOpen)
_conn.Close();
}
}
Need an explanation?? It is very simple actually. Since we defined our Adapter and Command types with a new(), we can also instantiate those objects. Wondering what's with the internalOpen? Well, that's just so we don't close the connection if the connection was opened outside of this method. Especially if there is a transaction in place. We want all the transaction handling outside of this class. What's the default (TTransaction)? Basically, this means if _trans is null (null is the default for a DbTransaction object), then don't use it.
ExecuteScalar
protected T ExecuteScalar<t />(string StoreProcName, List<tparameter /> Params)
{
bool internalOpen = false;
TCommand cmd;
try
{
cmd = new TCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName;
if (_trans != default(TTransaction))
cmd.Transaction = _trans;
else
cmd.Connection = _conn;
if (Params != null || Params.Count > 0)
{
foreach (DbParameter param in Params)
cmd.Parameters.Add(param);
}
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
internalOpen = true;
}
object retVal = cmd.ExecuteScalar();
if (retVal is T)
return (T)retVal;
else if (retVal == DBNull.Value)
return default(T);
else
throw new Exception("Object returned was of the wrong type.");
}
catch (DbException DbEx)
{
throw DbEx;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (internalOpen)
_conn.Close();
}
}
One of the greatest pieces of Generics is that we can define methods that return a specific type. Here, we've told our code to return whatever type we want, and if what is returned is DBNull, the return that types default. If it isn't DBNull or of the correct type, then some database developer has screwed up and needs to be scolded. :P
ExecuteNonQuery
protected int ExecuteNonQuery(string StoreProcName, List<tparameter /> Params)
{
bool internalOpen = false;
TCommand cmd;
try
{
cmd = new TCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName;
if (_trans != default(TTransaction))
cmd.Transaction = _trans;
else
cmd.Connection = _conn;
if (Params != null || Params.Count > 0)
{
foreach (DbParameter param in Params)
cmd.Parameters.Add(param);
}
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
internalOpen = true;
}
return cmd.ExecuteNonQuery();
}
catch (DbException DbEx)
{
throw DbEx;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (internalOpen)
_conn.Close();
}
}
Easily understood.
ExecuteReader
protected TDataReader ExecuteReader(string StoreProcName, List<tparameter /> Params)
{
bool internalOpen = false;
TCommand cmd;
try
{
cmd = new TCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName;
if (_trans != default(TTransaction))
cmd.Transaction = _trans;
else
cmd.Connection = _conn;
if (Params != null || Params.Count > 0)
{
foreach (DbParameter param in Params)
cmd.Parameters.Add(param);
}
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
internalOpen = true;
}
return (TDataReader)cmd.ExecuteReader();
}
catch (DbException DbEx)
{
throw DbEx;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (internalOpen)
_conn.Close();
}
}
In this method, our DataReader returned is of the type that was defined with our class.
Transaction Logic
public bool BeginTransaction()
{
if (_conn != null && _conn.State == ConnectionState.Closed && _trans == null)
{
_conn.Open();
_trans = (TTransaction)_conn.BeginTransaction();
return true;
}
return false;
}
public bool RollBackTransaction()
{
if (_conn != null && _conn.State == ConnectionState.Open && _trans != null)
{
_trans.Rollback();
_conn.Close();
_trans.Dispose();
_trans = default(TTransaction);
return true;
}
return false;
}
public bool CommitTransaction()
{
if (_conn != null && _conn.State == ConnectionState.Open && _trans != null)
{
_trans.Commit();
_conn.Close();
_trans.Dispose();
_trans = default(TTransaction);
return true;
}
return false;
}
public abstract bool RollBackTransaction(string SavePointName);
public abstract bool SaveTransactionPoint(string SavePointName);
Here we've added our transaction logic!!! Easy!! But I haven't tested it yet so I'm not sure if it works. Ha ha. I just wrote all of this this morning and just had to get it out as soon as possible to you all. We have abstract methods because OLEDB and ODBC don't support SavePoints as of now. We'll only use these methods in our SQL database.
Inheriting our DataBase Class
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
namespace Global.Data
{
public class MyDataBaseLayer<TParameter, TDataReader, TConnection,
TTransaction, TDataAdapter, TCommand> : DataBase<TParameter,
TDataReader, TConnection, TTransaction, TDataAdapter, TCommand>
where TParameter : DbParameter, IDataParameter,new()
where TDataReader : DbDataReader, IDataReader
where TConnection : DbConnection, IDbConnection, new()
where TTransaction : DbTransaction, IDbTransaction
where TDataAdapter : DbDataAdapter, IDataAdapter, new()
where TCommand : DbCommand, IDbCommand, new()
{
protected List<TParameter> _params;
public MyDataBaseLayer(string ConnectionString):base(ConnectionString)
{}
public Guid InsertNeMember(string MemberName)
{
TParameter paramMemberName = new TParameter();
paramMemberName.ParameterName = "@memberName";
paramMemberName.Value = MemberName;
paramMemberName.DbType = System.Data.DbType.VarNumeric;
_params = new List<TParameter>();
_params.Add(paramMemberName);
return ExecuteScalar<Guid>("proc_Member_Insert",_params);
}
public override bool RollBackTransaction(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
public override bool SaveTransactionPoint(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Global.Data
{
public class SqlDataBase : DataBase<SqlParameter,SqlDataReader,
SqlConnection,SqlTransaction,SqlDataAdapter,SqlCommand>
{
public SqlDataBase(string ConnectionString)
: base(ConnectionString)
{
}
public override bool RollBackTransaction(string SavePointName)
{
if (_conn != null && _conn.State == ConnectionState.Open && _trans != null)
{
_trans.Rollback(SavePointName);
return true;
}
return false;
}
public override bool SaveTransactionPoint(string SavePointName)
{
if (_conn != null && _conn.State == ConnectionState.Open && _trans != null)
{
_trans.Save(SavePointName);
return true;
}
return false;
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
namespace Global.Data
{
public class OdbcDataBase : DataBase<OdbcParameter,OdbcDataReader,
OdbcConnection,OdbcTransaction,OdbcDataAdapter,OdbcCommand>
{
public OdbcDataBase(string ConnectionString)
: base(ConnectionString)
{
}
public override bool RollBackTransaction(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
public override bool SaveTransactionPoint(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace Global.Data
{
class OleDbDataBase : DataBase<OleDbParameter,OleDbDataReader,
OleDbConnection,OleDbTransaction,OleDbDataAdapter,OleDbCommand>
{
public OleDbDataBase(string ConnectionString)
: base(ConnectionString)
{
}
public override bool RollBackTransaction(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
public override bool SaveTransactionPoint(string SavePointName)
{
throw new Exception("The method or operation is not implemented.");
}
}
}
Conclusion
If you don't find this a good resolution, don't just give me a bad vote, leave me a comment and an explanation. Let me know what you think. I like to know others' views.
