Design pattern for Data Access Layer

Question

You might feel this is homework, for that I am sorry. I have searched but couldn't find a proper answer.

So my question is:

I have several classes and each class has a method to save. So I created a separate class for database handling.

namespace HospitalMgt.Data
{
    public static class DBConnection
    {
        public static string constr = "Data Source=ABD;Initial Catalog=HospitalMgt;User Id=sa;Password=123";
        public static SqlConnection con;
      //  public static SqlCommand com;

        public static SqlConnection OpenConnection()
        {
            con= new SqlConnection(constr);
            con.Open();
            return con;
        }

    }
}

However, I don't think it's suitable to implement all the classes with a DBConnection class.

My question :

1. What design pattern is suited to overcome this problem?
2. Is it good practise to create DBConnection as class? (Or should it be an Interface)

I found a few articles about DA layers using the Factory method, but according to my knowledge, that pattern does not suit my situation.

Answer 1

Its too old but just came around this question and could not resist to post my thoughts.

I found Repository with UnitOfWork with some descent ORM is good approach. This minimizes most of the issues.

The UoW mentioned in above link can be injected in Repository. That increases the flexibility of usage. Also, all DB Communication code is centralized at one place. The example is not complete but a startup point.

Repository pattern mentioned in above link is actually a generic base class. You can create new class for each of your concrete Repository that derives from it.

Generic repository is considered an anti pattern; there are lot many articles on internet that explains it.

Why generic repository is anti-pattern?

1. A repository is a part of the domain being modeled, and that domain is not generic.
   • Not every entity can be deleted.
   • Not every entity can be added
   • Not every entity has a repository.
   • Queries vary wildly; the repository API becomes as unique as the entity itself.
   • For GetById(), identifier types may be different.
   • Updating specific fields (DML) not possible.
2. Generic query mechanism is the responsibility of an ORM.
   • Most of the ORMs expose an implementation that closely resemble with Generic Repository.
   • Repositories should be implementing the SPECIFIC queries for entities by using the generic query mechanism exposed by ORM.
3. Working with composite keys is not possible.
4. It leaks DAL logic in Services anyway.
   • Predicate criteria if you accept as parameter needs to be provided from Service layer. If this is ORM specific class, it leaks ORM into Services.

I suggest you read these (1, 2, 3, 4, 5) articles explaining why generic repository is an anit-pattern.

Solution:

1. Write an abstract Generic Repository that is wrapped by a concrete repository. That way you can control the public interface but still have the advantage of code-reuse that comes from generic repository.
2. Use a Generic Repository but do use composition instead of inheritance and do not expose it to the domain as a contract.

In any case, do not expose Generic Repository to calling code. Also, do not expose IQueryable from concrete repositories.

Answer 2

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.

Answer 3

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:

1. Active record pattern (wiki, Detailed info).
2. Repository pattern (Detailed info).

Answer 4

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.

Answer 5

I suggest you to use a RepositoryBase for all this common operations. If you decide to use an ORM for data access it's good to think in a implementation of repositories based on a Generic Type repository.

Here is a good article about it:

http://lostechies.com/jimmybogard/2009/09/03/ddd-repository-implementation-patterns/