Designing a Generic Database Layer
Some people like to automatically generate their database tier. Personally, I had enough bad experiences with ORM tools that I try to avoid them. After all, data tier code can be knocked out pretty quickly and it's always nice to know you didn't take any shortcuts. It's been a while since I've seen data tiers discussed, so I figured I'd pass along a modern approach that I have found works quite well and results in really clean data tiers.
Very early in your programming days, you learn that when separating an application into logical tiers, it is important that lower tiers do not couple themselves with higher tiers. As coupling between the tiers is reduced, your code gains flexibility. The entire data tier should be able stand on its own. If later you need to completely revamp your domain model or business layer, the data tier should be able to remain untouched.
First, consider a simple create operation inside a database class.
One option is to simply pass a data row to your database class and let it handle the update. There are a few problems with this approach. First, DataSets aren't exactly a high performance solution and have a lot of overhead. Additionally, a simple DataRow doesn't have a defined set of columns. As a result, you can't use refactoring operations should the column name change, nor can you use the compiler to check argument types or counts.
Another option is to pass an entity object directly to your database class. This is slightly better, as it ensures type safety and supports IDE features such as refactoring and auto-complete. However, this approach has a few flaws of its own. You can't tell by looking at the method which properties are required to create a customer. You can't tell if the data tier assigns the customer an ID, or if the ID must the ID be set before you pass the customer in. Additionally, now your data tier is being coded with explicit knowledge about the layout of your domain model. Since the domain model lives above the data tier, this should not happen.
A third approach is to have a method which takes all the parameters required to create a record in the database and return information about the results of the database call, such as an ID from an identity column. By going this route, you keep the coupling between the two tiers low, support compile time checking, and explicitly encode information about inputs and outputs of your method in its signature
| Bad |
public class CustomerDB
{
public void CreateCustomer(DataRow row) { … }
} |
| Better |
public class CustomerDB
{
public void CreateCustomer(Customer c) { … }
} |
| Best |
public class CustomerDB
{
public int CreateCustomer(string firstName, string lastName, string company, string phoneNumber) { … }
} |
Create, update, and delete are easy to handle in this fashion. But how do we handle query operations?
Again, one option is to work with DataSets or DataTables. While this gets the job done, you again incur a needless performance penalty by loading things into temporary tables before the data is loaded into entity objects. Another option is to pass back entity objects from your data tier. Again, however, this couples your data tier to your object model and is bad for the same reasons as it was bad in your create and update methods.
An alternate approach, which utilizes delegates and generics, eliminates the coupling between the tiers as well as the performance problems associated with datasets. Because this approach is new, I have included more complete code, illustrating how the different layers can work together.
|
Bad |
public class CustomerDB
{
public Customer GetCustomer(int id) { … }
public Collection<Customer> GetCustomers(string company) { … }
}
|
| Better |
public class CustomerDB
{
public DataRow GetCustomer(int id) { … }
public DataTable GetCustomers(string company) { … }
}
public class Customer
{
public static Customer CreateFrom(DataRow row)
{
...
}
}
|
| Best |
public delegate T CreateEntity<T>(IDataReader reader);public delegate void AddToList<T>(T item); public class CustomerDB<TCustomer>
{
public TCustomer GetCustomer(int id, CreateEntity<TCustomer> create)
{
using(SqlDataReader reader = ExecuteStoredProc(“GetCustomer”, id))
{
if(reader.Read()) return create(reader);
else return default(TCustomer)
}
}
public void GetCustomers(string company, CreateEntity<TCustomer> create, AddToList< TCustomer> addToList)
{
using(SqlDataReader reader = ExecuteStoredProc(“GetCustomersByCompany”, company);
{
while(reader.Read())
{
addToList(create(reader));
}
}
}
} public class Customer
{
public static Customer CreateFrom(IDataReader reader)
{
…
}
}
public class CustomerManager
{
CustomerDB<Customer> _db = new CustomerDB<Customer>(); public Customer GetCustomer(int id)
{
return _db.GetCustomer(id, Customer.CreateFrom);
}
public Collection<Customer> GetCustomers(string company)
{
Collection<Customer> customers = new Collection<Customer>();
_db.GetCustomers(company, Customer.CreateFrom, customers.Add);
}
} |