Design pattern for Data Access Layer
First of all, I would like to recommend you the article Design Patterns for Data Persistence by Jeremy Miller.
There are some data access layer patterns:
- Active record pattern (wiki, Detailed info).
- Repository pattern (Detailed info).
Normally, if I can't use any existing framework, I use both the Repository and Active patterns.
For simplicity, you could use only the Repository pattern. I normally define it like this:
public interface IEntity<T> { }
// Define a generic repository interface
public interface IRepository<TKey, TEntity>
where TEntity : IEntity<TKey>
{
void Add(TEntity entity);
void AddRange(IEnumerable<TEntity> entities);
IEntity<TKey> Get(TKey key);
IEnumerable<TEntity> GetRange(IEnumerable<TKey> keys);
IEnumerable<TEntity> GetAll();
// ..., Update, Delete methods
}
// Create an abstract class that will encapsulate the generic code
public abstract class Repository<TKey, TEntity> : IRepository<TKey, TEntity>
where TEntity : IEntity<TKey>
{
protected Repository(/*parameter you may need to implement the generic methods, like a ConnectionFactory, table name, entity type for casts, etc */) { }
public override void Insert(IEntity<TKey> entity)
{
// do the insert, treat exceptions accordingly and encapsulate them in your own and more concise Exceptions, etc
}
// ...
}
// Create the entities classes, one for each table, that will represent a row of that table
public class Car : IEntity<string> {/* Properties */}
// Create a specific repository for each table
// If the table have a composed key, just create a class representing it
public class CarRepository : Repository<string, Car>
{
public CarRepository() {/* pass the base parameters */}
// offer here your specific operations to this table entity
public IEnumerable<Car> GetByOwner(PersonKey ownerKey)
{
// do stuff
}
}
Obviously, when doing your own implementations, you must take into account thread safety making good using of transactions, specially across diferent entity repositories.
// simple example
ITransaction t = TransactionFactory.GetNewTransaction();
t.begin();
try{
// create person entity
personRepository.Add(person, t);
// create cars assigned to person
carRepository.AddRange(cars, t);
t.commit();
}catch(Exception){
t.rollback();
}
Just be sure that you really want to create your own DAL since it can end beeing extremelly complex, specially trying to develop the most generic solution.
I suggest using an ORM, Entity Framework or NHibernate will do nicely. Then you do not have to worry about a db context or create SQL statements.