MT : Problem #3: The Generic DAL: Basic Structures and Implementations
To continue with the architectural examination of MT and get into the code, I'll examine the least theoretical - the Data Access Layer. Not going to spend a whole lot of time with a long-winded OO discussion or a review of the GOF patterns here. Instead, I'll get started by first showing the code for the basis of the entire DAL - the IDbLayer interface. Some of these methods might look relatively simple (and similar if you've used the SqlHelper class from the Data Access Building Block). The concept - known by GOF nuts as the Facade Pattern - is something used quite heavily within .NET. Namely, all of the DataAdapters do a pretty good job at the Facade pattern by hiding all of the connection, command-building, other random database processes. Just by calling the Fill() method, for instance, a connection is opened and managed, a command built, data retrieved, connection cut, and dataset populated. We'll use this pattern a bit to obstruct the internal workings of some pretty universal units of functionality, as exemplified in the interface code below.
using System;
using System.Data;
namespace GenericDal
{
public interface IDbLayer
{
/// <summary>
/// The connection string used to connect to a database.
/// </summary>
string ConnectionString { get; set; }
/// <summary>
/// Executes the provided commandText SQL query, builds a DataSet, and returns
/// the DataSet to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
DataSet ExecuteDataSet(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes the provided commandText SQL query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
IDataReader ExecuteReader(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes the provided commandText SQL query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
IDataReader ExecuteReader(IDbTransaction transaction,
string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes a SQL procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
object ExecuteScalar(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes a SQL procedure and returns nothing.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
void ExecuteNonQuery(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes a SQL procedure and returns nothing.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
void ExecuteNonQuery(IDbTransaction transaction,
string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Executes a SQL procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
object ExecuteScalar(IDbTransaction transaction,
string commandText,
CommandType commandType,
params IDbDataParameter[] parameters);
/// <summary>
/// Returns a list of parameters for a given stored procedure.
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
IDataParameterCollection GetCommandParameters(string commandText);
}
}
Not too difficult to surmise what we're doing in this section of the code. Nothing too complex to grasp, really, from a conceptual frame of reference. We've accomodated most of the widely-used access methods of getting to databases - disconnected scenarios (via the DataSet-related methods) and connected (via the DataReader-specific methods). In addition, there's a few overloads to accomodate the existence of IDbTransaction objects to make sure implementors provide some sort of support for database transactions that span multiple method calls.
Now, we'll examine an implementor of this class. To make the discussion a little simple, let's take a look at the SqlServerDal class, which obviously deals with the SqlClient-specific implementations of these methods.
using System;
using System.Data;
using System.Data.SqlClient;
namespace GenericDal
{
public class SqlServerDal : IDbLayer
{
private string cnStr = String.Empty;
public SqlServerDal()
{
}
public string ConnectionString
{
get { return cnStr; }
set { cnStr = value; }
}
/// <summary>
/// Adds parameters to a command object.
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameters"></param>
void PrepareParameters(IDbCommand cmd, params IDbDataParameter[] parameters)
{
if(parameters != null)
{
// add the parameters to the SelectCommand.
foreach(IDbDataParameter p in parameters)
{
cmd.Parameters.Add((SqlParameter)p);
}
}
}
/// <summary>
/// Executes the provided commandText SQL query, builds a DataSet, and returns
/// the DataSet to the calling code. The array of SqlParameter objects are added
/// to an internal SqlCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="command"></param>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
// instantiate a new dataadapter to fill up the dataset
SqlDataAdapter daTmp = new SqlDataAdapter(commandText,cn);
daTmp.SelectCommand.CommandType = commandType;
PrepareParameters(daTmp.SelectCommand, parameters);
// return the dataset
DataSet dsTmp = new DataSet();
daTmp.Fill(dsTmp);
return dsTmp;
}
/// <summary>
/// Executes the provided commandText SQL query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="command"></param>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
/// <summary>
/// Executes the provided commandText SQL query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteReader(IDbTransaction transaction, string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn,(SqlTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (SqlTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
/// <summary>
/// Executes a SQL procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
/// <summary>
/// Executes a SQL procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(IDbTransaction transaction,
string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn,(SqlTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (SqlTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
public void ExecuteNonQuery(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
public void ExecuteNonQuery(IDbTransaction transaction,
string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
SqlCommand cmd = new SqlCommand(commandText,cn,(SqlTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (SqlTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
public IDataParameterCollection GetCommandParameters(string commandText)
{
SqlConnection cn = new SqlConnection(this.ConnectionString);
cn.Open();
SqlCommand cmd = new SqlCommand(commandText,cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
cn.Close();
return cmd.Parameters;
}
}
}
You may opt to perform some extra logic in some of these methods. Myself, I like to keep the base implementations as simple as possible and account for things later on in more specific code. For example, if any of these methods throw an instance of a SqlException caused via a SQL Server RAISERROR() command, I'd most likely want to account for that in my client or middleware code.
At this point, you could basically cut and paste this minimal functionality into a new class named OleDbDal and change all of the appropriate places. It may not be optimized to the hills, but the point is simple - create a base structure and defer the internal workings of the methods to higher-level classes. That way, if need be later on, the same class' methods could be overridden and extended even further. The code sample below is just that - a copy-and-paste-and-find-and-replace, resulting in the most basic second implementation.
using System;
using System.Data;
using System.Data.OleDb;
namespace GenericDal
{
public class OleDbDal : IDbLayer
{
private string cnStr = String.Empty;
public OleDbDal()
{
}
public string ConnectionString
{
get { return cnStr; }
set { cnStr = value; }
}
/// <summary>
/// Adds parameters to a command object.
/// </summary>
/// <param name="cmd"></param>
/// <param name="parameters"></param>
void PrepareParameters(IDbCommand cmd, params IDbDataParameter[] parameters)
{
if(parameters != null)
{
// add the parameters to the SelectCommand.
foreach(IDbDataParameter p in parameters)
{
cmd.Parameters.Add((OleDbParameter)p);
}
}
}
/// <summary>
/// Executes the provided commandText OleDb query, builds a DataSet, and returns
/// the DataSet to the calling code. The array of OleDbParameter objects are added
/// to an internal OleDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="command"></param>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
// instantiate a new dataadapter to fill up the dataset
OleDbDataAdapter daTmp = new OleDbDataAdapter(commandText,cn);
daTmp.SelectCommand.CommandType = commandType;
PrepareParameters(daTmp.SelectCommand, parameters);
// return the dataset
DataSet dsTmp = new DataSet();
daTmp.Fill(dsTmp);
return dsTmp;
}
/// <summary>
/// Executes the provided commandText OleDb query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="command"></param>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
/// <summary>
/// Executes the provided commandText OleDb query
/// and returns a DataReader to the calling code. The array of IDbDataParameter objects are added
/// to an internal IDbCommand object's Parameters collection prior to the execution.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IDataReader ExecuteReader(IDbTransaction transaction, string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn,(OleDbTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (OleDbTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
/// <summary>
/// Executes a OleDb procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
/// <summary>
/// Executes a OleDb procedure and returns a single value.
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(IDbTransaction transaction,
string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn,(OleDbTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (OleDbTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
object o = cmd.ExecuteScalar();
cn.Close();
return o;
}
public void ExecuteNonQuery(string commandText,
CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn);
cmd.CommandType = commandType;
cmd.Connection = cn;
PrepareParameters(cmd, parameters);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
public void ExecuteNonQuery(IDbTransaction transaction,
string commandText,
System.Data.CommandType commandType,
params IDbDataParameter[] parameters)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
OleDbCommand cmd = new OleDbCommand(commandText,cn,(OleDbTransaction)transaction);
cmd.CommandType = commandType;
cmd.Connection = cn;
cmd.Transaction = (OleDbTransaction)transaction;
PrepareParameters(cmd, parameters);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
public IDataParameterCollection GetCommandParameters(string commandText)
{
OleDbConnection cn = new OleDbConnection(this.ConnectionString);
cn.Open();
OleDbCommand cmd = new OleDbCommand(commandText,cn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbCommandBuilder.DeriveParameters(cmd);
cn.Close();
return cmd.Parameters;
}
}
}
At this point, we've accounted for the databases support by the SqlClient namespace and the OleDb namespace. Not bad for a few minutes' work, eh? The next post will focus on the next step in the DAL - centralizing the logic in such a way that a config file can be used to name a series or collection of IDbLayer implementors in much the same way that the appSetting section works. Think of it this way - Microsoft Windows has a nice, high-level data layer built into it: ODBC - a method of using named “pointers” to connections lower in the OS so that your applications' data access can be abstracted somewhat. This DAL will take that idea and use it on a per-application basis.