Introduction
There are plenty of articles on CodeProject related to creating a Data Access Layer (DAL). Each one seems to look at solving a particular problem and doesn’t look to solve access as a whole. I’m writing this article to see if I can begin to address the issue of re-usability and flexibility utilizing the SOLID principles.
Update: Originally, I had no plan to discuss the code. My original thought was that there are enough articles out there on the SOLID principles that I didn’t need to rehash other people’s articles and show how they were applied to the code. I thought that I would create some code that was practical to show the principles in action and people could peruse on their own without much direction. I asked for comments to spark a conversation about what people liked or didn’t like about the code. Given some of the comments, it would seem my original thought was flawed and what follows is the correction.
Background
Most of the projects I’ve worked on over the years for various companies have all had their own homegrown DAL. Some are better than others but all leave much to be desired. The current project I’m on has evolved over the past decade or so and was starting with Classic ASP.
Over the years, developers have come and gone and contributed to the code using their ideas of what was acceptable or the norm at the time. Part of that legacy is thousands of lines of code in about 4 different implementations of a Data Access Layer.
Now that my kids are done with their activities for the summer and a major software release is complete, I found myself with some free time. I decided that I would take a little bit of time and see what I could accomplish in creating a DAL framework that would eventually replace our existing code.
Project
The source code attached is result of the limited time I had to work on it. As I mentioned in the introduction, I wanted to make the code flexible and re-usable; to that end I tried to follow the SOLID principles.
If you are not familiar with the SOLID principle, there are plenty of articles on the subject on the web. Here is the gist of the principle:
- S – Single Responsibility Principle – A class should do one thing and do it well
- O – Open/Close Principle – The software should be opened for extension but closed for modifications
- L – Liskov Substitution Principle – Functions should accept base classes and not know anything about the children
- I – Interface Segregation Principle – Interfaces should be used and contain only the basic functionality
- D – Dependency Inversion Principle – A container should be used to provide loose coupling between higher and lower classes.
I’ve intentionally violated the last principle Dependency Inversion. At this point I’m not going to implement a container. From past experience, implementing something like this always causes me more problems than it is worth. I’m talking about co-workers. A lot of developers out there work 9-5 and are done and put forth no effort in maintaining/updating their skills. To that end, I do what I can to modernize without creating more work for myself in the end.
Code
One comment that I received was that I should program more to the ADO.NET interface instead of creating specific classes for individual databases. With this in mind, I refactored all the code and now only have a couple of places in the DbFactory.cs file that has any knowledge of a database type. Minimum code was implemented and is there only out of necessity.
- Single Responsibility – If you open the QueryParameter.cs file, you will see that there are properties with some methods created to set those values through a fluent style of coding and a method of getting the value of the parameter. This represents the notion of Single Responsibility by only having that which is necessary to create a database parameter.
- Open/Close – One of the ways I accomplished this is with SqlParameterExtension.cs. The class allows for additional actions to be taken on the DbParameter child class SqlParameter. In the future an Oracle version could be created to accomplish similar behavior by naming the file by the convention of <className>Extension.cs and implementing the SetAdditionalParameters method. Within DbQuery is the call to the extension method if defined. The Executers folder contains additional classes which contain encapsulated functionality for communicating with the database. There currently is only 1 for using a SqlDataAdapter, but others could be created as well.
- Liskov Substitution – DbQuery once again shows this principle by taking a DbCommand object and within the subsequent code make no reference to a sub classed type. Using a switch statement is generally a good way to violate this principle.
- Interface Segregation – QueryBuilder take an input of an IDatabaseQuery object. It doesn’t really matter what the actual concrete type of the class is as long as it implements the required items. The interface itself is a composite interface and only has the minimum items required.
Walk Through The Query
Let’s take a walk through the Bill of Materials Query. This query will show off all the current functionality. We start with Main()
static void Main()
{
IDbFactory factory = new DbFactory(DatabaseType.SqlServer);
var obj = BillOfMaterialsQueries.GetByIdAndDate(factory, 893, new DateTime(2000, 06, 26));
}
We start with getting the database factory. This will allow us to create connections, create a query object and execute the query. While the actual implementation is delegated to other classes, this is the central place to request that something is done. I could have created the connection and the created a command from connection thus eliminating another place that has to know about the type of the database. I felt this was would limit being able to create a query and pass it around for whatever the reason the developer would have. The connection needs to know what it is connecting to, so I didn’t feel this was such a horrible decision for the command as well. I chose flexibility over anonymous functionality.
The factory is passed into the query to allow the query to be performed. The query encompasses everything required to get the information from the database and return a list of objects. I chose to return objects as opposed to a DataSet. This is person decision as I don’t believe that you should return anything but objects. This framework doesn’t preclude you from doing so. If you need to, then do it.
static public List<productbillofmaterials> GetByIdAndDate(IDbFactory factory, int startProductID, DateTime checkDate)
{
List<productbillofmaterials> list = null;
QueryCommand command = new QueryCommand
{
ConnectionString = connectionString,
QueryType = DbQueryType.StoredProcedure,
Command = "uspGetBillOfMaterials",
Parameters = new List<queryparameter>
{
new DbInt32("StartProductID").SetValue(startProductID),
new DbDateTime("CheckDate").SetValue(checkDate)
}
};
var result = factory.ExecuteDataSetFacade(command);
if (result.WasSuccessful)
{
FieldColumnMap map = FieldColumnMap.GetMappings<productbillofmaterials>();
var parser = new DataTableParser(((DataSet)result.Data).Tables[0], map);
list = parser.ParseList<productbillofmaterials>();
}
return list;
}
The method starts by creating a container (QueryCommand) to hold all the information that may be required to run the database statement: the database connection key name, whether it is a dynamic or stored procedure, the command itself as well as any parameters that may or not be required.
Once the container is setup, a call to execute the command is performed by ExecuteDataSetFacade on the factory. This is the other place that I have that needs to know what database it is using. As the type was stored inside the factory, the command will just switch to the appropriate implementation. At this point, people might point out that this violates the Open/Closed principle, and I would respond with the following article: An Open and Closed Case
If I have to add functionality to create a connection to the database, it isn’t farfetched that I have to implement specific database retrieval methods as well. Could this be better structured in the future? Sure, but right now I have one implementation, SQL Server, and I’m following the methodology of not “Gold Plating” and will refactor at such time that a pattern emerges.
Once the database statement is executed a structure is returned that returns the data and whether it was successful. Upon success, the object that is returned is parsed into the appropriate object. This may or may not depend on mapping fields to specific database columns. The class may define a static method (GetFieldColumnMap) for obtaining a specific mapping. If the class doesn’t define a mapping the property names will be used to get the value from the parser.
public class ProductBillOfMaterials : ICreator<productbillofmaterials>
{
static private FieldColumnMap _map;
public Product Product { get; set; }
public BillOfMaterials BillOfMaterials { get; set; }
public ProductBillOfMaterials Create(IParser parser, object record)
{
Product = new Product();
Product.Create(parser, record);
BillOfMaterials = new BillOfMaterials();
BillOfMaterials.Create(parser, record);
return this;
}
static public FieldColumnMap GetFieldColumnMap()
{
if (_map == null)
{
FieldColumnMap productMap = FieldColumnMap.GetMappings<product>();
productMap
.SetType<product>()
.UpdateMapping("Name", "ComponentDesc")
.Done();
FieldColumnMap bomMap = FieldColumnMap.GetMappings<billofmaterials>();
bomMap
.SetType<billofmaterials>()
.UpdateMapping("PerAssemblyQty", "TotalQuantity")
.Done();
_map = new FieldColumnMap()
.SetType<product>()
.AddDictionary(productMap.GetMappings())
.Done()
.SetType<billofmaterials>()
.AddDictionary(bomMap.GetMappings())
.Done();
}
return (FieldColumnMap)_map.Clone();
}
}
public class BillOfMaterials : ICreator<billofmaterials>
{
public BillOfMaterials Create(IParser parser, object record)
{
var type = GetType();
BillOfMaterialsID = parser.GetValue(type, record, BillOfMaterialsID, "BillOfMaterialsID");
ProductAssemblyID = parser.GetValue(type, record, ProductAssemblyID, "ProductAssemblyID");
ComponentID = parser.GetValue(type, record, ComponentID, "ComponentID");
StartDate = parser.GetValue(type, record, StartDate, "StartDate");
EndDate = parser.GetValue(type, record, EndDate, "EndDate");
UnitMeasureCode = parser.GetValue(type, record, UnitMeasureCode, "UnitMeasureCode");
BOMLevel = parser.GetValue(type, record, BOMLevel, "BOMLevel");
PerAssemblyQty = parser.GetValue(type, record, PerAssemblyQty, "PerAssemblyQty");
ModifiedDate = parser.GetValue(type, record, ModifiedDate, "ModifiedDate");
return this;
}
}
public class DataTableParser : IParser
{
private readonly DataTable _dataTable;
public FieldColumnMap FieldColumnMap { get; private set; }
public DataTableParser(DataTable dataTable)
: this(dataTable, null)
{
}
public DataTableParser(DataTable dataTable, FieldColumnMap fieldColumnMap)
{
_dataTable = dataTable;
FieldColumnMap = fieldColumnMap;
}
public T GetValue<t>(Type type, object record, T variable, string columnName)
{
var overrides = (FieldColumnMap != null)
? FieldColumnMap.GetMappings(type)
: new Dictionary<string, string="">();
var mappedName = (overrides.ContainsKey(columnName))
? overrides[columnName]
: columnName;
DataRow row = (DataRow)record;
return row.GetValue(variable, mappedName);
}
public List<t> ParseList<t>() where T : ICreator<t>, new()
{
List<t> list = new List<t>();
if (_dataTable == null)
return list;
foreach (DataRow row in _dataTable.Rows)
list.Add(ParseSingle<t>(row));
return list;
}
public T ParseSingle<t>(object record) where T : ICreator<t>, new()
{
if (record == null)
return default(T);
DataRow row = (DataRow)record;
var obj = new T();
obj.Create(this, row);
return obj.Create(this, row);
}
}
Walk Through The Building
The execution of the command is hidden behind a façade to adhere to the DRY principle and to aid in the simplicity of the framework.
public ExecutionResult ExecuteDataSetFacade(QueryCommand command)
{
string name = command.ConnectionString;
IQueryBuilder builder = GetQueryBuilder(command.QueryType);
IDatabaseQuery query = builder.Build(command);
using (DbConnection connection = GetConnection(name))
{
var e = GetDataSetExecuter(connection, query);
return Execute(e);
}
}
This façade is responsible for constructing the database specific query, opening the connection, executing the command and returning the requested data. This façade could implement for other connection types as well (XML, DataReader, etc).
We first start by getting the builder. This is that final place that we know about the database type and where we return the database specific command object and set the type of query to be dynamic or a stored procedure.
The builder is responsible for the actual construction of the database statement and is done through the use of interfaces which ultimately are performed by the DbQuery.
public class DbQuery : IDatabaseQuery
{
public DbCommand Command { get; protected set; }
public DbQuery(DbCommand command)
{
Command = command;
}
private MethodInfo GetExtensionClass()
{
var parm = Command.CreateParameter();
Type type = parm.GetType();
type = Type.GetType(string.Format("DalFramework.{0}Extension", type.Name));
return (type == null)
? null
: type.GetMethod("SetAdditionalParameters");
}
public virtual void AddCommand(string command)
{
Command.CommandText = command;
}
public virtual void AddParameters(List<queryparameter> parameters)
{
Command.Parameters.Clear();
if (parameters == null)
return;
var method = GetExtensionClass();
parameters.Any(q =>
{
var parm = Command.CreateParameter();
parm.Direction = q.Direction;
parm.IsNullable = q.IsNullable;
parm.ParameterName = q.ColumnName;
parm.Value = q.Value;
Command.Parameters.Add(parm);
if (method != null)
method.Invoke(parm, new object[] { parm, q });
return false;
});
}
public virtual void AddTimeout(int seconds)
{
Command.CommandTimeout = seconds;
}
}
This class uses the base class DbCommand to call existing methods and base properties to for the database statement to be created successfully. Additional functionality may be performed it the extension method exists.
Walk Through The Execution
Once the creation of the database statement is completed the connection the database is created and it is executed through the SqlDataSetExecuter. The results and status are then passed back to the caller.
public class SqlDataSetExecuter : DataSetExecuter
{
public override ExecutionResult Execute()
{
ExecutionResult result = new ExecutionResult();
try
{
SqlCommand command = (SqlCommand)Query.Command;
command.Connection = (SqlConnection)Connection;
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(DataSet, TableName);
result.Data = DataSet;
}
catch (Exception ex)
{
result.Error = ex.Message;
}
return result;
}
}
Template
There is a folder that I have added to the project which contains an executable as well as a template (CreateModel). This is outside of the scope of this article but included for anyone that is interested. The template will generate a business object model based upon the SQL coded that is requested, see lines 47-53 for examples. It does require a Stored Procedure to be added which is found in the SQL folder and is documented.
Response to Comments
- Why should you use this?
Let me tell you why I would use it, because it fills the need to replace various implementations with the least impact on an existing system while providing similar functionality and hides implementations from the developer.
You would use it for the same reason that you use any other framework, because it suits your needs. Some of the industries/companies that I have worked for will not allow any open source code. This means a roll your own solution. By starting this project, I begin to apply another principle, DRY (Don’t Repeat Yourself), as I move from project to project.
- What makes this better than another framework?
I leave that up to the reader. Everyone has their own opinion on why something is better than something else; that is why we have multiple frameworks. For me, it is a simple framework that gets the job done and doesn’t require a lot of explanation. I value your comment on what you like or don’t like, so let me know and I can expand on the code.
What's Working?
- Database
- Queries
- Dynamic
- Stored Procedures
- Ability to map a database column to a class field
- Data retrieval via
- Object creation
