I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net. I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result. Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview.
In this post, we’ll get into what is probably the most useful class in the framework, the Data Access Object (DAO).
Where does the DAO fit in?
Let’s backtrack a bit and take a quick look at the design of core. Below we see a snapshot of Core’s folder structure. We have a Persistence folder that contains our SqlDAO (a DAO written specifically for SqlServer), as well as our DataMappers and Persisters (we’ll go into these later). The Rules folder contains business rules and validation rules for different entities / data shapes. Security has a little plumbing code for business layer authorization. Finally, the Services folder contains the service classes that we’ve focused on during the previous few posts.
The service classes are the one stop shop that our UI code uses to get and save data. A typical data access method looks like this.
public User GetUserByEmail(string email)
{
string sql = @"SELECT *
FROM [User]
WHERE [Email] = @Email";
SqlDao dao = SharedSqlDao;
SqlCommand command = dao.GetSqlCommand(sql);
command.Parameters.Add(dao.CreateParameter("@Email", email, 100));
return dao.GetSingle<User>(command);
}
You’ll notice that this is a pretty simple method. There’s no real Ado.Net code. We’re just defining a query, wrapping it in an SqlCommand, and then passing both the SqlCommand and our desired data shape <User> to our DAO. This mystery DAO object does all the heavy lifting for us and even helps us create our query parameter in very efficient, single line of code. This is a very intentional design. After writing data access code for a few years, I finally noticed that the only things I really change from one data access method to the next are: the query; the parameters; and the return data shape. Everything else is just boilerplate plumbing code that gets repeated over and over again. So, I took all that data access boilerplate code and extracted it out to a helper class called SqlDao. The main functions of the DAO are:
- The DAO is the single access point to our database. All communication with the database goes through the DAO. No code goes around it.
- The DAO encapsulates common ADO.Net tasks like getting a connection, creating query parameters, executing queries, etc.
- The DAO defines methods that take an SqlCommand as a parameter, execute it, and return the data in one of our standard formats, a single DTO, as List<DTO>, a DataPage<DTO>, or a string value.
There’s a lot of good stuff in there that makes writing data access method almost frictionless. I can’t go over everything so I included a complete listing for the SqlDao at the end of this post.
The GetSingle<T> and GetList<T> methods
Now let’s look at something a little more interesting. The SqlDao.GetSingle<T> method takes an SqlCommand as a parameter and returns the restults of that command as a single T where T is the class of our desired datashape. So, the line dao.GetSingle<User>(command); will execute command and then return an object of type User. If no User was found then the method just returns null. Here’s how it works.
// GetSingle
public T GetSingle<T>(SqlCommand command) where T : class
{
T dto = null;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
dto = (T)mapper.GetData(reader);
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
// return the DTO, it's either populated with data or null.
return dto;
}
The code is pretty simple. We declare a new object of type T called dto (Data Transfer Object) and initialize it to null. We then check the connection on our command and make sure it’s open. We call ExecuteReader on the command which gets us an SqlDataReader. If the reader has any rows then we go to our DataMapperFactory and get an IDataMapper for the type T. A cool thing that is happening behind the scenes is that the DataMapperFactory checks to see if we have an IDataMapper that has been created specifically for type T, if we don’t it returns a GenericMapper that uses reflection to map SqlDataReader fields to fields on our type T. Once we have a mapper it’s just a matter of calling the mapper.GetData(reader) method and returning the dto.
Now let’s take a quick look at GetList<T>. This method is very similar to GetSingle<T>, but it returns a generic list of objects of type T instead of a single object of type T. If no results are returned when we execute the SqlCommand passed to GetList<T>, we don’t return null, instead we return an empty list. Here’s the code.
// GetList
public List<T> GetList<T>(SqlCommand command) where T : class
{
List<T> dtoList = new List<T>();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
while (reader.Read())
{
T dto = null;
dto = (T)mapper.GetData(reader);
dtoList.Add(dto);
}
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
// We return either the populated list if there was data,
// or if there was no data we return an empty list.
return dtoList;
}
It’s really the same as GetSingle<T>. We just have a different return type List<T>, and a while loop that get’s our individual DTOs and adds them to our return dtoList. The main logic for getting an IDataMapper from the DataMapperFactory and calling mapper.GetData(reader) to parse the reader results into an object of type T remains the same.
Conclusion
At this point you may be thinking, “That’s it???, you didn’t answer anything! All you did is get some mapper object and call the GetData method on it.” That’s true to some extent. Our GetSingle<T> and GetList<T> methods just contain the logic that is always going to be common to those tasks regardless of the data shape passed to them. Anything that is data shape specific, things like how do I get data from this field in my SqlDataReader and put it in this field in my returned data shape, are delegated to the IDataMapper. This separation of logic means that it’s very easy to maintain our code. We can add DataMapper logic for new data shapes and never touch any of the logic for GetSingle, GetList, and GetPage. In fact, our logic for a specific data shape is encapsulated in a single IDataMapper class that touches nothing else in the application. That means it’s easy to change and maintenance friendly. Plus, don’t forget that we have the reflection-based GenericMapper, so we never have to create a new IDataMapper if we don’t want to.
Next time we’ll take a look at a data shape specific IDataMapper and the DataMapperFactory. In the meantime, the full source for SqlDao is included below. Have a look at the implementation for GetDataPage<T> and see if it makes sense now that we’ve gone over GetSingle<T> and GetList<T>. The CreateParameter methods are worth a look too. I went with a design where I just overload the method CreateParameter for each type of parameter that I need to create, so the usage syntax for CreateParameter(Guid value) and CreateParameter(int value) are almost identical. Each CreateParameter method was designed with the target of enabling that single line syntax that I use: command.Parameters.Add(dao.CreateParameter("@Email", email, 100)); so each method contains automatic null value checking. You’ll also see some methods that weren’t in my original spec. While using this framework on different projects I’ve found it convenient to have methods like GetSingleInt32(command) and GetStringList(command).
Finally you can download a small, one page sample app that uses aapl at http://aapl.codeplex.com/.
using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using hf.Common;
using hf.Common.DataShapes;
using hf.Core.Persistence.DataMappers;
namespace hf.Core.Persistence
{
public class SqlDao
{
#region "Database Helper Methods"
// Connection
private SqlConnection _sharedConnection;
public SqlConnection SharedConnection
{
get
{
if (_sharedConnection == null)
{
_sharedConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["hf"].ConnectionString);
}
return _sharedConnection;
}
set
{
_sharedConnection = value;
}
}
// Constructors
public SqlDao() { }
public SqlDao(SqlConnection connection)
{
this.SharedConnection = connection;
}
// GetDbSqlCommand
public SqlCommand GetSqlCommand(string sqlQuery)
{
SqlCommand command = new SqlCommand();
command.Connection = SharedConnection;
command.CommandType = CommandType.Text;
command.CommandText = sqlQuery;
return command;
}
// GetDbSprocCommand
public SqlCommand GetSprocCommand(string sprocName)
{
SqlCommand command = new SqlCommand(sprocName);
command.Connection = SharedConnection;
command.CommandType = CommandType.StoredProcedure;
return command;
}
// CreateNullParameter
public SqlParameter CreateNullParameter(string name, SqlDbType paramType)
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = paramType;
parameter.ParameterName = name;
parameter.Value = DBNull.Value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
// CreateNullParameter - with size for nvarchars
public SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = paramType;
parameter.ParameterName = name;
parameter.Size = size;
parameter.Value = DBNull.Value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
// CreateOutputParameter
public SqlParameter CreateOutputParameter(string name, SqlDbType paramType)
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = paramType;
parameter.ParameterName = name;
parameter.Direction = ParameterDirection.Output;
return parameter;
}
// CreateOuputParameter - with size for nvarchars
public SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = paramType;
parameter.Size = size;
parameter.ParameterName = name;
parameter.Direction = ParameterDirection.Output;
return parameter;
}
// CreateParameter - uniqueidentifier
public SqlParameter CreateParameter(string name, Guid value)
{
if (value.Equals(NullValues.NullGuid))
{
// If value is null then create a null parameter
return CreateNullParameter(name, SqlDbType.UniqueIdentifier);
}
else
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.UniqueIdentifier;
parameter.ParameterName = name;
parameter.Value = value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
}
// CreateParameter - int
public SqlParameter CreateParameter(string name, int value)
{
if (value == NullValues.NullInt)
{
// If value is null then create a null parameter
return CreateNullParameter(name, SqlDbType.Int);
}
else
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Int;
parameter.ParameterName = name;
parameter.Value = value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
}
// CreateParameter - bool
public SqlParameter CreateParameter(string name, bool value)
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Bit;
parameter.ParameterName = name;
parameter.Value = value ? 1 : 0;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
// CreateParameter - datetime
public SqlParameter CreateParameter(string name, DateTime value)
{
if (value == NullValues.NullDateTime)
{
// If value is null then create a null parameter
return CreateNullParameter(name, SqlDbType.DateTime);
}
else
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.DateTime;
parameter.ParameterName = name;
parameter.Value = value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
}
// CreateParameter - nvarchar
public SqlParameter CreateParameter(string name, string value, int size)
{
if (String.IsNullOrEmpty(value))
{
// If value is null then create a null parameter
return CreateNullParameter(name, SqlDbType.NVarChar);
}
else
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Size = size;
parameter.ParameterName = name;
parameter.Value = value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
}
// CreateTextParameter
public SqlParameter CreateTextParameter(string name, string value)
{
if (String.IsNullOrEmpty(value))
{
// If value is null then create a null parameter
return CreateNullParameter(name, SqlDbType.Text);
}
else
{
SqlParameter parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Text;
parameter.ParameterName = name;
parameter.Value = value;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
}
#endregion
#region "Data Projection Methods"
// ExecuteNonQuery
public void ExecuteNonQuery(SqlCommand command)
{
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("Error executing query", e);
}
finally
{
command.Connection.Close();
}
}
// ExecuteScalar
public Object ExecuteScalar(SqlCommand command)
{
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
return command.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception("Error executing query", e);
}
finally
{
command.Connection.Close();
}
}
// GetSingleValue
public T GetSingleValue<T>(SqlCommand command)
{
T returnValue = default(T);
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return returnValue;
}
// GetSingleInt32
public Int32 GetSingleInt32(SqlCommand command)
{
Int32 returnValue = default(int);
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return returnValue;
}
// GetSingleString
public string GetSingleString(SqlCommand command)
{
string returnValue=null;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return returnValue;
}
// GetGuidList
public List<Guid> GetGuidList(SqlCommand command)
{
List<Guid> returnList = new List<Guid>();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
returnList = new List<Guid>();
while (reader.Read())
{
if (!reader.IsDBNull(0)) { returnList.Add(reader.GetGuid(0)); }
}
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return returnList;
}
// GetStringList
public List<string> GetStringList(SqlCommand command)
{
List<string> returnList = new List<string>();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
returnList = new List<string>();
while (reader.Read())
{
if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }
}
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return returnList;
}
// GetSingle
public T GetSingle<T>(SqlCommand command) where T : class
{
T dto = null;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
dto = (T)mapper.GetData(reader);
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
// return the DTO, it's either populated with data or null.
return dto;
}
// GetList
public List<T> GetList<T>(SqlCommand command) where T : class
{
List<T> dtoList = new List<T>();
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
while (reader.Read())
{
T dto = null;
dto = (T)mapper.GetData(reader);
dtoList.Add(dto);
}
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
// We return either the populated list if there was data,
// or if there was no data we return an empty list.
return dtoList;
}
// GetDataPage
public DataPage<T> GetDataPage<T>(SqlCommand command, int pageIndex, int pageSize) where T : class
{
DataPage<T> page = new DataPage<T>();
page.PageIndex = pageIndex;
page.PageSize = pageSize;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
while (reader.Read())
{
// get the data for this row
T dto = null;
dto = (T)mapper.GetData(reader);
page.Data.Add(dto);
// If we haven't set the RecordCount yet then set it
if (page.RecordCount == 0) { page.RecordCount = mapper.GetRecordCount(reader); }
}
reader.Close();
}
}
catch (Exception e)
{
throw new Exception("Error populating data", e);
}
finally
{
command.Connection.Close();
}
return page;
}
#endregion
}
}
