Build Your Own Data Access Layer: Executing Custom Queries
Posted by Davy Brion on August 28th, 2009
Note: This post is part of a series. Be sure to read the introduction here.
You’ve already seen that this DAL offers you the ability to query each entity by primary key, or to retrieve a list of all instances of an entity type. Obviously, this isn’t even sufficient for the most trivial applications so we need a way to execute custom queries. So i needed to provide something that would enable you to easily execute custom queries and get a list of entity instances, or a single result without having to muck around with transforming the results on your own.
Due to the extremely simplistic nature of this DAL, i can’t support much more than queries which either return a single value (being one entity instance, or a scalar value) or a list of entities. As soon as you need to execute queries that return joined results, this DAL won’t be able to deal with the results automatically. I’ll get back to this part later on in the post so for now, let’s focus on queries whose result can be transformed to an entity automatically.
My first version of this DAL hardly had any support for this, and when using the DAL for custom queries, you basically had to deal with SqlCommands and their results manually. Well, you could pass the SqlCommand to the EntityHydrater, but you still had to deal with a lot of ugly code. I had asked Ayende to do a private review before i started with this series, and he quickly pointed out that i needed something better for executing SQL queries and suggested something like (surprise, surprise) what NHibernate offers for SQL queries. So yes, this solution is once again heavily inspired by NHibernate 
Let’s go over the details… First of all, we have this simple IQuery interface:
public interface IQuery
{
void AddParameter(string name, object value, DbType dbType);
TResult GetSingleResult<TResult>();
IEnumerable<TResult> GetResults<TResult>();
int ExecuteNonQuery();
}
That is what you should be able to do with a query once you’ve created it. Creating a query is possible through my session API (which will be covered in the next post):
IQuery CreateQuery(string sql);
IQuery CreateQuery<TEntity>(string whereClause);
Through the regular CreateQuery method, you can provide the full SQL string and you have full control over the actual SQL. The CreateQuery<TEntity> overload only requires you to provide the WHERE clause because it will generate a SELECT clause for the given entity which automatically retrieves all of the columns for this entity. This also ensures that the result of CreateQuery<TEntity> can always be cleanly transformed to a list of entities or a single entity instance through the EntityHydrater.
This is the implementation of both CreateQuery methods:
public IQuery CreateQuery(string sql)
{
var command = GetConnection().CreateCommand();
command.Transaction = GetTransaction();
command.CommandText = sql;
return new Query(command, metaDataStore, hydrater);
}
public IQuery CreateQuery<TEntity>(string whereClause)
{
return CreateQuery(metaDataStore.GetTableInfoFor<TEntity>().GetSelectStatementForAllFields() + " " + whereClause);
}
And here’s the actual implementation of the Query class:
public class Query : IQuery
{
private readonly SqlCommand command;
private readonly MetaDataStore metaDataStore;
private readonly EntityHydrater hydrater;
public Query(SqlCommand command, MetaDataStore metaDataStore, EntityHydrater hydrater)
{
this.command = command;
this.metaDataStore = metaDataStore;
this.hydrater = hydrater;
}
public void AddParameter(string name, object value, DbType dbType)
{
command.CreateAndAddInputParameter(dbType, name, value);
}
public TResult GetSingleResult<TResult>()
{
var tableInfo = metaDataStore.GetTableInfoFor<TResult>();
if (tableInfo == null)
{
var scalar = (TResult)command.ExecuteScalar();
command.Dispose();
return scalar;
}
var result = hydrater.HydrateEntity<TResult>(command);
command.Dispose();
return result;
}
public IEnumerable<TResult> GetResults<TResult>()
{
var tableInfo = metaDataStore.GetTableInfoFor<TResult>();
if (tableInfo == null)
{
var listOfValues = GetListOfValues<TResult>();
command.Dispose();
return listOfValues;
}
var result = hydrater.HydrateEntities<TResult>(command);
command.Dispose();
return result;
}
private IEnumerable<TResult> GetListOfValues<TResult>()
{
using (var reader = command.ExecuteReader())
{
var list = new List<object>();
while (reader.Read())
{
list.Add(reader.GetValue(0));
}
return list.Cast<TResult>();
}
}
public int ExecuteNonQuery()
{
var rowsAffected = command.ExecuteNonQuery();
command.Dispose();
return rowsAffected;
}
}
Pretty simple, right? The ability to get a strong typed result is something that i find very important when using any DAL, and the Query class makes this very easy to do. If you want to return a list of single value results, you can do that easily. If you want to return a single scalar result, you can do that easily. A single entity instance? No problem, the EntityHydrater takes care of that for us. Same thing goes for a list of entities. Custome delete or update statements? No problem, the ExecuteUpdate method can be used for that and will return the typical number of affected rows as reported by the database.
And now you can do things like this pretty easily:
var query = session.CreateQuery<Customer>("where this.NumberOfPurchases > @numberOfPurchases");
query.AddParameter("@numberOfPurchases", 100, DbType.Int32);
var goodCustomers = query.GetResults<Customer>();
or
var query = session.CreateQuery("select max(NumberOfPurchases) from Customer");
int max = query.GetSingleResult<int>();
or
var query = session.CreateQuery<Customer>("where this.NumberOfPurchases = (select max(NumberOfPurchases) from Customer))");
var bestCustomer = query.GetSingleResult<Customer>();
I’m sure you get the idea by now
Note: when using the CreateQuery<TEntity> method, the SELECT clause that is generated automatically prefixes each selected column from the entity with the ‘this’ prefix. This makes it easier to refer to the entity’s properties in other clauses while you still have to ability to join on another table in the from clause, though you obviously can’t add any columns to the select clause anymore.
Now, this is all pretty good for queries where you only need to return scalar values or specific entities, but what if you want to return values from multiple tables in one query? This DAL doesn’t have any support for that, but what you could do instead, is to create a view for your query and map an ‘entity’ to that view instead. Then you could still get a typed result while querying the view, though the results wouldn’t be able to be transformed into your ‘real’ entities. But for filling grids or just to write typical overview queries, this might already be sufficient.
As you can tell, there’s not a lot of power or flexibility behind this approach. But try to think of the complexity involved with trying to deal with results from multiple tables. It makes everything a whole lot more complex. At that time, you really need to consider if it’s still worth writing your own DAL because the effort you’ll spend on getting it right will be very significant.
August 28th, 2009 at 9:36 pm
Would it make sense for the query to implement IDisposable so that you can put it in a using block? I would be a little concerned about the query failing and never getting disposed of. Another complementary idea may be to do some lifetime management from the session so that you can be sure that when the session goes away, all queries open with it do too. Am I making sense?
August 29th, 2009 at 12:03 pm
well, if a query fails the underlying SqlCommand object will eventually be garbage collected so i wouldn’t really worry about that. Failed queries shouldn’t happen frequently anyway IMO because they typically are the result of incorrect code.
August 30th, 2009 at 7:56 am
Incorrect code happens. When it happens, you may exhaust your connection pool before that stuff gets collected. I think the creator of a disposable object is responsible for disposing of it no matter what happens, not the garbage collector. So you have basically two correct strategies: handling it yourself (in a fail-safe way) or delegating that responsiblity to the calling code by making yourself disposable.
August 30th, 2009 at 10:13 am
code that causes failing queries should never even make it to your source code repository… you do write tests for queries, no?
if you deploy code which causes frequently failing queries, then you have some fundamental problems in the way you create, test and deliver software.
August 30th, 2009 at 4:53 pm
[...] Build Your Own Data Access Layer: Executing Custom Queries [...]
August 31st, 2009 at 7:41 am
@Davy: I answered on your new post.
August 31st, 2009 at 5:55 pm
[...] Building Your Own Data Access Layer: Executing Custom Queries [...]
September 1st, 2009 at 9:42 am
[...] Build Your Own Data Access Layer: Executing Custom Queries – Davy Brion continues his series of posts on building a custom data access tier from first principles, looking in this part at how to execute custom queries against the database within the framework, and Ayende follows on from Davy’s post giving the NHibernate perspective on Custom Query execution. In his next part of the series(Bringing It All Together), Davy Brion looks at bringing the whole framework together with a look at the public interface of the DAL, and in the final part, Davy shares his Conclusions on building a custom DAL. [...]