Design pattern for Data Access Layer

.net, c#, design-patterns

Solution

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.

Problem

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 : - What design pattern is suited to overcome this problem? - 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.

Original source

Related problems