In short, this is an easy way to simplify certain aspects of data access.
Let's look at the following interfaces:
public interface IQuery
{
string Text { get; }
} public interface IDbGateway
{
DataRow Get(IQuery query);
DataTable Find(IQuery query);
void Execute(IQuery query);
}
In this case, we wrap each of our data access commands (sql, hql, etc) inside a simple object, implementing our IQuery interface.
public class AllCustomersQuery : IQuery
{
public string Text
{
get { return "exec up_Customers_GetAll"; }
}
}
public class GetCustomerQuery : IQuery
{
public GetCustomerQuery(int id)
{
_id = id;
}
private int _id;
public string Text
{
get { return string.Format("exec up_Customers_GetById @Id = {0}", _id); }
}
}
These objects are great candidates for code generation (especially if you are already generating stored procedures). Unlike in this simple example, make sure you are filtering your sql queries properly (ie..for sql injection attacks, etc).
We can group our queries in a utlity class for easy access.
public static class CustomerQueries
{
public static IQuery GetAll()
{
return new AllCustomersQuery();
}
public static IQuery GetById(int id)
{
return new GetCustomerQuery(id);
}
}
The gateway class itself is simple to implement, as it only has four methods. I'll leave that as an exercise for the reader, but here's a preview of what your code could look like after you've put it all together.
int customerId = 1;
DataRow customerInfo = gateway.Get(CustomerQueries.GetById(customerId));
By separating our queries from our gateway (which actually executes them against the database server), we have simplified a lot of other concerns. Looking at database caching, for example, we can see that now, writing a caching layer for our application has become simpler.
public class CachingDbGateway : IDbGateway
{
public CachingDbGateway(IDbGateway gateway)
{
_gateway = gateway;
_tableCache = new Dictionary<string, DataTable>();
_rowCache = new Dictionary<string, DataRow>();
}
private IDictionary<string, DataTable> _tableCache;
private IDictionary<string, DataRow> _rowCache;
private IDbGateway _gateway;
public DataRow Get(IQuery query)
{
string queryText = query.Text;
if (_rowCache.ContainsKey(queryText))
{
//cache hit
return _rowCache[queryText];
}
else
{
//cache miss
DataRow row = _gateway.Get(query);
_rowCache.Add(queryText,row);
return row;
}
}
public DataTable Find(IQuery query)
{
string queryText = query.Text;
if (_tableCache.ContainsKey(queryText))
{
//cache hit
return _tableCache[queryText];
}
else
{
//cache miss
DataTable table = _gateway.Find(query);
_tableCache.Add(queryText,table);
return table;
}
}
public void Execute(IQuery query)
{
_gateway.Execute(query);
}
}
In this example, we built a simple object which implements our gateway interface. It takes the real gateway in as an argument to the constructor and adds a layer of functionality on top. In fact, there's no reason why this caching gateway needs to take the real gateway either. If you have other concerns you want implemented between your data layer and your business layer, you are free to stack these decorators on top of each other (ie.. a LoggingDbGateway). Note that you will want this particular caching implementation to run as a singleton (as the dictionaries get initialized in the constructor).
Since we probably don't want to cache all the queries, we might also extend our IQuery interface to support this.
public interface IQuery
{
string Text { get; }
bool Cacheable { get; }
}
We can modify our CachingDbGateway to respect each query's caching expectations.
In summary, while not a production-ready example, this gives us a good base to build a data access layer on top of. By splitting the query from the object which executes the queries, we can simplify our code. Separating the responsibility of executing the query from the query itself also gives us the ability to chain different responsibilities on top of the actual execution mechanism.