One thing that keeps amazing me is how many smart developers still feel the urge to write their own data access layer (DAL). They either do it all manually, or they generate parts of it, or they generate the whole thing. Whichever way you go, there are still various alternative paths you can choose from. Some people like to use stored procedures for everything, some people generate sql statements and provide a semi-OO API in front of it, some people still spend time constructing Command objects. I think i’ve seen most approaches by now, and my personal opinion is that they pretty much all suck.
These DAL’s usually have at least one big downside to them. Generated DAL’s are usually pretty good for productivity but most of the times they don’t really offer you that much control over how queries should be executed, whether or not statements should be batched, specify fetching-strategies, etc. Hand-written DAL’s are terrible for productivity but you can fine tune each action to an optimal implementation. These downsides are pretty big IMHO, and are often underestimated. Sometimes due to semi-religious stances and sometimes it’s just due to ignorance.
As you already know, i’m a fan of NHibernate. Well, consider me a fan of decent ORM’s in general. NHibernate just happens to be the one i know best and am very happy with. In this post i’d like to take a look at how you can easily offer the most common data access requirements without generating code, while still allowing high developer productivity. We’ll go over the implementation of a generic Repository class, and hopefully you’ll see how much NHibernate can improve your way of working.
We’re going to look at the implementation piece by piece, so lets just start with the declaration of the class so we can get that out of the way:
public class Repository<T> : IRepository<T>
As you can see, this is just a generic class that takes a type parameter. The type parameter represents the type of the Entity you want this repository to handle. If you have an Entity base class or interface, you probably want to restrict the type of T to inherit from Entity or implement IEntity or whatever.
This class needs to be able to access the current NHibernate session, which i discussed yesterday:
private readonly IActiveSessionManager activeSessionManager;
public Repository(IActiveSessionManager activeSessionManager)
{
this.activeSessionManager = activeSessionManager;
}
protected ISession Session
{
get { return activeSessionManager.GetActiveSession(); }
}
Right, now we can actually get to the interesting Data Access parts. Obviously, each DAL needs a way to retrieve a specific entity based on its Primary Key value:
/// <summary>
/// Retrieves the entity with the given id
/// </summary>
/// <param name="id"></param>
/// <returns>the entity or null if it doesn't exist</returns>
public T Get(object id)
{
return Session.Get<T>(id);
}
Very straightforward stuff… this simply uses the current NHibernate session to retrieve an entity of the requested type, with the given primary key value.
Another thing we need is a way to create or update entity instances:
/// <summary>
/// Saves or updates the given entity
/// </summary>
/// <param name="entity"></param>
public void SaveOrUpdate(T entity)
{
Session.SaveOrUpdate(entity);
}
This method simply uses the session’s SaveOrUpdate method, which will either perform an Insert (in case of a new entity) or an Update (in case of an existing entity). NHibernate will perform a check for a new instance depending on how you’ve configured this in your mapping files.
So far this has been really straightforward, so it’s time to get to a more interesting part. Retrieving entities based on criteria (basically a query):
/// <summary>
/// Returns each entity that matches the given criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public IEnumerable<T> FindAll(DetachedCriteria criteria)
{
return criteria.GetExecutableCriteria(Session).List<T>();
}
This probably needs a bit of explaining. The parameter is an instance of the DetachedCriteria type. A DetachedCriteria is just like a Criteria instance, except that it hasn’t been associated with a session yet. So you can create the DetachedCriteria without being connected to a session. This basically represents a query. I’ll show a concrete example of this later on. The thing to remember is that this is the only code you really need to perform any query you want. You just have to write the query using the Criteria API. This does have a bit of a learning curve, but most people pick it up pretty fast.
You usually also want a way to determine the ordering of the result of the query:
/// <summary>
/// Returns each entity that maches the given criteria, and orders the results
/// according to the given Orders
/// </summary>
/// <param name="criteria"></param>
/// <param name="orders"></param>
/// <returns></returns>
public IEnumerable<T> FindAll(DetachedCriteria criteria, params Order[] orders)
{
if (orders != null)
{
foreach (var order in orders)
{
criteria.AddOrder(order);
}
}
return FindAll(criteria);
}
Each order you provide is applied to the query and then the query is executed. Again, pretty simple stuff right?
You want to know how you can get paging working? Here it is:
/// <summary>
/// Returns each entity that matches the given criteria, with support for paging,
/// and orders the results according to the given Orders
/// </summary>
/// <param name="criteria"></param>
/// <param name="firstResult"></param>
/// <param name="numberOfResults"></param>
/// <param name="orders"></param>
/// <returns></returns>
public IEnumerable<T> FindAll(DetachedCriteria criteria, int firstResult, int numberOfResults, params Order[] orders)
{
criteria.SetFirstResult(firstResult).SetMaxResults(numberOfResults);
return FindAll(criteria, orders);
}
Keep in mind that the executed query only retrieves the results within the paging range. It does not retrieve everything to perform the paging client-side, this happens in the db where it’s supposed to happen.
What if you have a query that should only return one instance? That’s easy to do as well:
/// <summary>
/// Returns the one entity that matches the given criteria. Throws an exception if
/// more than one entity matches the criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public T FindOne(DetachedCriteria criteria)
{
return criteria.GetExecutableCriteria(Session).UniqueResult<T>();
}
What if you have a query and you just want the very first result instead of the entire resultset? Again, pretty easy to do:
/// <summary>
/// Returns the first entity to match the given criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public T FindFirst(DetachedCriteria criteria)
{
var results = criteria.SetFirstResult(0).SetMaxResults(1)
.GetExecutableCriteria(Session).List<T>();
if (results.Count > 0)
{
return results[0];
}
return default(T);
}
Again, NHibernate will issue a smart sql statement… that is, it only retrieves the first result instead of the entire resultset.
Obviously, this is also useful if you can define the order of the results to pick the first result:
/// <summary>
/// Returns the first entity to match the given criteria, ordered by the given order
/// </summary>
/// <param name="criteria"></param>
/// <param name="order"></param>
/// <returns></returns>
public T FindFirst(DetachedCriteria criteria, Order order)
{
return FindFirst(criteria.AddOrder(order));
}
How often have you seen developers execute a query in code, only to use the count of the records without actually needing the returned records? We no longer need to beat the shit out of these developers:
/// <summary>
/// Returns the total number of entities that match the given criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public long Count(DetachedCriteria criteria)
{
return Convert.ToInt64(criteria.GetExecutableCriteria(Session)
.SetProjection(Projections.RowCountInt64()).UniqueResult());
}
In this case, NHibernates issues a simple select count… query based on the criteria you’ve provided. Nice huh?
We might as well throw in this one as well:
/// <summary>
/// Returns true if at least one entity exists that matches the given criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public bool Exists(DetachedCriteria criteria)
{
return Count(criteria) > 0;
}
And last, but certainly not least, you’ll also want a way to delete entities from the database. How about this:
/// <summary>
/// Deletes the given entity
/// </summary>
/// <param name="entity"></param>
public void Delete(T entity)
{
Session.Delete(entity);
}
/// <summary>
/// Deletes every entity that matches the given criteria
/// </summary>
/// <param name="criteria"></param>
public void Delete(DetachedCriteria criteria)
{
// a simple DELETE FROM ... WHERE ... would be much better, but i haven't found
// a way to do this yet with Criteria. So now it does two roundtrips... one for
// the query, and one with all the batched delete statements (that is, if you've
// enabled CUD statement batching
foreach (T entity in FindAll(criteria))
{
Delete(entity);
}
}
The first Delete method simply deletes the given entity. The second method probably needs to be explained a bit more. This method receives a query, and it deletes all the items that the query returns. As you can see from the comment, it would be better if it would perform a DELETE FROM … WHERE instead of fetching the results of the query but i didn’t find a way to do that with the criteria API. In a more advanced scenario, this might actually be better than simply issuing a large DELETE statement because you could offer a Delete method which also receives a block of code to execute before or after each delete is executed. Which opens the door to a lot of interesting opportunities.
And that’s it basically… I really haven’t shown you that much code right? And what does this code offer us? We get create/update/delete functionality, and we also have some nice options for querying our data. And since the Criteria API of NHibernate allows you to create powerful and complex queries, you can use it to create your queries and then you simply pass these criteria to the repository to fetch the data. Just so we’re clear on this, you’re not just limited to specifying which data you want to retrieve, but you can also tell NHibernate how you want to retrieve it because you can define fetching strategies for each association. This is a tremendously powerful feature which offers you a lot of flexibility in choosing the most optimal data fetching approaches, without being limited to what your DAL supports or having to spend a lot of code on it.
Let’s wrap up this post with a small example of how you could use this repository class to execute a query you wrote yourself:
var criteria = DetachedCriteria.For<ProductCategory>()
.Add(Expression.Like("Name", "Test%"));
var categories = repository.FindAll(criteria);
As you can see, this is really easy. The only effort basically lies within building the query, so as you’re queries become more complex, this effort obviously increases.
If you combine this repository approach with query batching, you end up with an easy-to-use data layer which offers you all the flexibility you could want, while still allowing you to implement specifically tuned solutions to achieve excellent performance. Also, keep in mind that this is only a very basic repository implementation. The Rhino Commons repository implementation offers even more functionality with a couple of extra options to boost performance. I really can’t think of a single good reason not to use this approach anymore.
