Generic Repository with Data Access Layer

Question

I am creating a new project using business objects (Employee, Product). Due to constraints, I am not using LINQ to SQL or any ORM Mapper.

I have to hand code the Data Access Layer(s). I am interested in using the 'Repository Pattern'.

According to what I understand, I have to create a generic repository IRepository which is implemented by all repositories ProductRepository, EmployeeRepository.

What confuses me is that different business objects have different requirements. For example:

ProductRepository

 GetAllProducts ();
 GetProductById (int id);
 GetProductByMaxPrice (double price);
 GetProductByNamePrice (string name, double Price);
 Get... (...);

EmployeeRepository

 GetEmployeeByAge ();
 GetEmployeeByJob (string description);
 GetEmployeeBySalary (double salary);
 Get... (...); //and so on

How can I create a generic repository which meets different data access requirements of different objects?

I have read a lot of theory regarding Repository Pattern but would really appreciate a working example.

Additionally, if I can create all repositories using a generic repository, using the factory pattern becomes easy aswell. For example:

interface IRepository
{
    ....
}

ProductRepository : IRepository
{
    ....
}

EmployeeRepository : IRepository
{
    ....
}

Then we can use the factory pattern effectively as:

IRepository repository;
repository = new ProductRepository ();
repository.Call_Product_Methods ();

repository = new EmployeeRepository ();
repository.Call_Employee_Methods ();

Solution 1

The repository Pattern is a great pattern to use, but if it is not done correctly, instead of making your life easier, it will be a huge pain!

So, the best possible way to do this (since you dont want to use EF or other ORM) is by creating a generic interface, and then a base abstract implementation. This way you dont need to code each repository, you can just instantiate them by the type!

And after this, if you have any particular method specific to some of your entities, you can all inherit from the Repository and override or add methods and properties as nedded.

If you want to use the Repository Pattern, i also suggest that you use the IUnitOfWork pattern, and keep it separated from the repository.

Both interfaces should look something like this:

The very simple IUnitOfWork:

Public interface IUnitOfWork
{
    bool Save();
}

And them, the Repository interface, using generic:

public interface IRepository<TEntity> : IDisposable where TEntity : class

    IUnitOfWork Session { get;}

    IList<TEntity> GetAll();
    IList<TEntity> GetAll(string[] include);
    IList<TEntity> GetAll(Expression<Func<TEntity, bool>> predicate);

    bool Add(TEntity entity);
    bool Delete(TEntity entity);
    bool Update(TEntity entity);
    bool IsValid(TEntity entity);
}

The methods .Add(), .Delete() should not send anything to the database, but they should always send the changes to the IUnitOfWork (that you can implement in you DAL class), and only when you call the .Save() method of the IUnitOfWork you will save things to the Database.

I have implemented my Repository class using EntityFramework, and that makes things easier, but you can do it any way you want.

The code you will use will be somethin like this:

void SomeMethod()
{
    using (IUnitOfWork session = new YourUnitOfWorkImplementation())
    {
        using (var rep = new Repository<Client>(session))
        {
            var client1 = new Client("Bob");
            var client2 = new Cliente("John");
            rep.Add(client1);
            rep.Add(client2);
            var clientToDelete = rep.GetAll(c=> c.Name == "Frank").FirstOrDefaut();
            rep.Delete(clientToDelete);

            //Now persist the changes to the database
            session.Save();

        {
    {
}

Like i said, with EF and the DbContext, this is a lot easier, to here are a small part of my Repository class:

public class Repository : Component, IRepository
{


    protected DbContext session;
    {
        get
        {
            if (session == null)
                throw new InvalidOperationException("A session IUnitOfWork do repositório não está instanciada.");
            return (session as IUnitOfWork);
        }
    }

    public virtual DbContext Context
    {
        get
        {
            return session;
        }
    }

    public Repository()
        : base()
    {
    }

    public Repository(DbContext instance)
        : this(instance as IUnitOfWork)
    {


    #endregion


    public IList<TEntity> GetAll<TEntity>() where TEntity : class
    {
        return session.Set<TEntity>().ToList();
    }


    public bool Add<TEntity>(TEntity entity) where TEntity : class
    {
        if (!IsValid(entity))
            return false;
        try
        {
            session.Set(typeof(TEntity)).Add(entity);
            return session.Entry(entity).GetValidationResult().IsValid;
        }
        catch (Exception ex)
        {
            if (ex.InnerException != null)
                throw new Exception(ex.InnerException.Message, ex);
            throw new Exception(ex.Message, ex);
        }
    } ...

This way you dont need to build a GetEmployeeByAge, you would simply write:

IEnumerable<Employee> GetEmployee(int age)
{
 return  rep.GetAll<Employee>(e=> e.Age == age);
}

Or you could just call directly (no need to create the method)

Solution 2

In general, and in my opinion, a generic repository "base" interface, doesn't really solve that much. Some have mentioned that it could, in theory, provide a get property that takes an integer and returns a record. Yes, this is nice and convenient - and depending on your use-case, maybe even desirable.

Where I personally draw the line, is Insert, Update, and Delete methods. In all but the most simplistic of cases, we should be identifying what we are doing. Yes, creating a new Supplier might merely mean invoking an Insert operation. But most non-trivial cases, you're going to be doing other stuff do.

Therefore, when designing repositories I think it best to identify what actions you're going to want to do and having methods named exactly that:

CreateClient(); // Might well just be a single Insert.... might involve other operations
MoveClientToCompany(); // several updates right here
GetContractsForClient(); // explicitly returns contracts belonging to a client

We are now defining what we're doing with the data. Generic Insert, Update, and Delete methods do not infer usage of our repository and could potentially lead to misuse by developers who do not understand what other ancillary things need to happen when we actually go and do something.

So what is a good example of a base-repository? Well, what about a repository that implements caching? The base repository could have some kind of a cache and our derived repositories could use that cache to return stale data if one so-wished.

Even the this[int] default property has complicated issues when we need to answer what we are going to return. If it is a big object with many references, are we going to return the whole thing with all its parts, or are we going to return a very bare-bones POCO, with further querying necessary to fill in the gaps. A generic this[int] doesn't answer that question, but:

GetBareBonesClient(int id);
GetClientAndProductDetail(int id);
GetClientAndContracts(int id);

Are pretty well defined in my opinion. In these days of intellisense, a developer coding against your repository will know what he/she needs to invoke to get what they want back. How do you decide how many of these methods exist? Well, you look at the product you're actually developing. What cases do you have for getting data... who is getting the data, and why are they getting it? Most of the time, these are easy questions to answer.

One common problem, however, is when we want to allow users to "browse" data in a tabulated form. "Give me 'x' number of records, sorted by 'x' field, in a paginated fashion... oh, and I may or may not include some kind of a search, on some column". This kind of a code is something you really don't want to have to implement for each of your repositories. So there may be a case for some boiler-plate query construction in a hypothetical IRepositoryThatSupportsPagination. I am sure you can think of a better name for that.

Obviously, there may well be more cases. But I would never throw the default CRUD operations in to a base repository interface/class, because it doesn't mean anything with the exception of unimportant, trivial, cases.

Solution 3

[Edited based on input from MikeSW] My opinion (joining Moo-Juice here) is that you need to pick the implementation that best suits you. The repository pattern is a good one (Gabriel's answer describes a good implementation), however it can be a lot of work if implemented in its pure form. ORMs automate a lot of the grunt work.

Whichever approach you choose, you will need the following components:

1. Your business interface - the methods your client side programmers would need to call, such as GetAllEmployees(criteria), UpdateEmployee(Employee employee), etc. If you have client/server architecture, these would correspond to service calls with data contracts.

2. Your internal logic that creates the proper output to satisfy your contracts. This would be the layer that composes queries, or performs multiple database updates, for example UpdateEmployee might have to validate that the employee exists, that the updater has permissions to update, then update several tables, and insert audit records or records into a review queue. This would involve querying and updating, and would be one unit of work.

3. Your data access architecture, called by your internal logic. This is where the repository pattern would come in. This needs, regardless of what you use, the following:

3.1 A class to implement unit of work . In repository pattern, this has only Save() - but that requires in-memory state management. I prefer using the following interface for sql-driven implementation:

public interface ITransactionContext : IDisposable
{
    IDbTransaction BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted);

    void CommitTransaction();

    void RollbackTransaction();

    int ExecuteSqlCommand(string sql, params object[] parameters);

    IEnumerable<T> SqlQuery<T>(string sql, params object[] parameters);

    IEnumerable<T> SqlQuery<T>(string sql, object[] parameters, IDictionary<string, string> mappings);

    bool Exists(string sql, params object[] parameters);        
}

public interface ITransactionDbContext : ITransactionContext
{
    int SaveChanges();
}

I use EF but we have an old db where we need to write SQL, and this looks and operates a lot like the EF DbContext. Note the interace ITransactionDbContext, that adds the SaveChanges() - which is the only one an ORM needs. But if you don't do ORM, you need the others.

This is the implementation. Note that it is entirely interface-based. You can give your concrete database connection via the factory method.

public class TransactionContext : ITransactionContext
{
    protected IDbTransaction Transaction;
    protected IDbConnection Connection;
    protected readonly Func<IDbConnection> CreateConnection;

    public TransactionContext(Func<IDbConnection> createConnection)
    {
        this.CreateConnection = createConnection;
    }

    public virtual IDbConnection Open()
    {
        if (this.Connection == null)
        {
            this.Connection = this.CreateConnection();
        }

        if (this.Connection.State == ConnectionState.Closed)
        {
            this.Connection.Open();
        }

        return this.Connection;
    }


    public virtual IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
    {
        Open();
        return this.Transaction ?? (this.Transaction = this.Connection.BeginTransaction(isolationLevel));
    }

    public virtual void CommitTransaction()
    {
        if (this.Transaction != null)
        {
            this.Transaction.Commit();
        }
        this.Transaction = null;
    }

    public virtual void RollbackTransaction()
    {
        if (this.Transaction != null)
        {
            this.Transaction.Rollback();
        }
        this.Transaction = null;
    }

    public virtual int ExecuteSqlCommand(string sql, params object[] parameters)
    {
        Open();
        using (var cmd = CreateCommand(sql, parameters))
        {
            return cmd.ExecuteNonQuery();
        }
    }

    public virtual IEnumerable<T> SqlQuery<T>(string sql, object[] parameters ) 
    {
        return SqlQuery<T>(sql, parameters, null);
    }

    public IEnumerable<T> SqlQuery<T>(string sql, object[] parameters, IDictionary<string, string> mappings) 
    {
        var list = new List<T>();
        var converter = new DataConverter();
        Open();
        using (var cmd = CreateCommand(sql, parameters))
        {
            var reader = cmd.ExecuteReader();
            if (reader == null)
            {
                return list;
            }

            var schemaTable = reader.GetSchemaTable();
            while (reader.Read())
            {
                var values = new object[reader.FieldCount];
                reader.GetValues(values);
                var item = converter.GetObject<T>(schemaTable, values, mappings);
                list.Add(item);
            }
        }
        return list;        }

    public virtual bool Exists(string sql, params object[] parameters)
    {
        return SqlQuery<object>(sql, parameters).Any();
    }

    protected virtual IDbCommand CreateCommand(string commandText = null, params object[] parameters)
    {
        var command = this.Connection.CreateCommand();
        if (this.Transaction != null)
        {
            command.Transaction = this.Transaction;
        }

        if (!string.IsNullOrEmpty(commandText))
        {
            command.CommandText = commandText;
        }

        if (parameters != null && parameters.Any())
        {
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        return command;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected void Dispose(bool disposing)
    {

        if (this.Connection != null)
        {
            this.Connection.Dispose();
        }

        this.Connection = null;
        this.Transaction = null;
    }
}

3.2. Then you need to implement an Update based on a command. Here is mine (simplified):

public class UpdateHelper
{
    private readonly ITransactionContext transactionContext;

    public UpdateHelper(ITransactionContext transactionContext)
    {
        this.transactionContext = transactionContext;
    }

    public UpdateResponse Update(UpdateRequest request)
    {
        this.transactionContext.BeginTransaction(IsolationLevel.RepeatableRead);
        var response = new UpdateResponse();
        foreach (var command in request.Commands)
        {
            try
            {
                response = command.PerformAction(transactionContext);
                if (response.Status != UpdateStatus.Success)
                {
                    this.transactionContext.RollbackTransaction();
                    return response;
                }
            }

            catch (Exception ex)
            {
                this.transactionContext.RollbackTransaction();
                return HandleException(command, ex);
            }
        }

        this.transactionContext.CommitTransaction();
        return response;
    }

    private UpdateResponse HandleException(Command command, Exception exception)
    {
        Logger.Log(exception);
        return new UpdateResponse { Status = UpdateStatus.Error, Message = exception.Message, LastCommand = command };
    }
}

As you see, this takes a Command that will perform the action (which is the Command pattern). Basic command implementation:

public class Command
{
    private readonly UpdateCommandType type;
    private readonly object data;
    private readonly IDbMapping mapping;

    public Command(UpdateCommandType type, object data, IDbMapping mapping)
    {
        this.type = type;
        this.data = data;
        this.mapping = mapping;
    }

    public UpdateResponse PerformAction(ITransactionContext context)
    {
        var commandBuilder = new CommandBuilder(mapping);
        var result = 0;
        switch (type)
        {
            case UpdateCommandType.Insert:
                result  = context.ExecuteSqlCommand(commandBuilder.InsertSql, commandBuilder.InsertParameters(data));
                break;
            case UpdateCommandType.Update:
                result = context.ExecuteSqlCommand(commandBuilder.UpdateSql, commandBuilder.UpdateParameters(data));
                break;
            case UpdateCommandType.Delete:
                result = context.ExecuteSqlCommand(commandBuilder.DeleteSql, commandBuilder.DeleteParameters(data));
                break;

        }
        return result == 0 ? new UpdateResponse { Status = UpdateStatus.Success } : new UpdateResponse { Status = UpdateStatus.Fail };
    }
}

3.3 You need the object to database mapping. This is used by the update methods. In this example, if the mappings are ommitted, it is assumed that the EntityType's properties correspond to the database columns. You want a mapping for each table.

public interface IDbMapping
{
    string TableName { get; }
    IEnumerable<string> Keys { get; }
    Dictionary<string, string> Mappings { get; }
    Type EntityType { get; }
    bool AutoGenerateIds { get; }
}

public class EmployeeMapping : IDbMapping
{
    public string TableName { get { return "Employee"; } }
    public IEnumerable<string> Keys { get { return new []{"EmployeeID"};} }
    public Dictionary<string, string> Mappings { get { return null; } } // indicates default mapping based on entity type } }
    public Type EntityType { get { return typeof (Employee); } }
    public bool AutoGenerateIds { get { return true; } }
}

3.4. You need a query builder object. This builds your query based on user input in sql. For example, you might want to search employees by lastname, firstname, department, and join date. You could implement a query interface like this:

 public interface IEmployeeQuery {
     IEmployeeQuery ByLastName(string lastName);
     IEmployeeQuery ByFirstName(string firstName);
     IEmployeeQuery ByDepartment(string department);
     IEmployeeQuery ByJoinDate(Datetime joinDate);

 }

This can be concretely implemented by a class that either builds a sql query or a linq query. If you are going with sql, implement string Statement and object[] Parameters. Then your logic layer can write code like this:

   public IEnumerable<Employee> QueryEmployees(EmployeeCriteria criteria) {
        var query = new EmployeeQuery(); 
        query.ByLastName(criteria.LastName);
        query.ByFirstName(criteria.FirstName); 
        //etc.
        using(var dbContext = new TransactionContext()){
            return dbContext.SqlQuery<Employee>(query.Statement, query.Parameters);
        }
   }

3.5. You need a command builder for your objects. I suggest you use a common commandbuilder. You can either use the SqlCommandBuilder class, or you can write your own SQL generator. I do not suggest you write sql for every table and every update. That's the part that will be really tough to maintain. (Speaking from experience. We had one and we couldn't maintain it, eventually I wrote a SQL generator.)

Note: if you do not have many updates (i.e. your application is mostly display-oriented), you might omit this and just write your updates by hand when you need them.

Here is a generic builder (this code is NOT TESTED, you need to work it as you need it):

public interface ICommandBuilder
{
    string InsertSql { get; }
    string UpdateSql { get; }
    string DeleteSql { get; }
    Dictionary<string, object> InsertParameters(object data);
    Dictionary<string, object> UpdateParameters(object data);
    Dictionary<string, object> DeleteParameters(object data);
}

public class CommandBuilder: ICommandBuilder
{
    private readonly IDbMapping mapping;
    private readonly Dictionary<string, object> fieldParameters;
    private readonly Dictionary<string, object> keyParameters; 

    public CommandBuilder(IDbMapping mapping)
    {
        this.mapping = mapping;
        fieldParameters = new Dictionary<string, object>();
        keyParameters = new Dictionary<string, object>();
        GenerateBaseSqlAndParams();
    }

    private void GenerateBaseSqlAndParams()
    {
        var updateSb = new StringBuilder();
        var insertSb = new StringBuilder();
        var whereClause = new StringBuilder(" WHERE ");
        updateSb.Append("Update " + mapping.TableName + " SET ");
        insertSb.Append("Insert Into " + mapping.TableName + " VALUES (");
        var properties = mapping.EntityType.GetProperties(); // if you have mappings, work that in
        foreach (var propertyInfo in properties)
        {
            var paramName = propertyInfo.Name;
            if (mapping.Keys.Contains(propertyInfo.Name, StringComparer.OrdinalIgnoreCase))
            {
                keyParameters.Add(paramName, null);
                if (!mapping.AutoGenerateIds)
                {
                    insertSb.Append(paramName + ", ");
                }
                whereClause.Append(paramName + " = @" + paramName);
            }
            updateSb.Append(propertyInfo.Name + " = @" + paramName + ", ");
            fieldParameters.Add(paramName, null);
        }
        updateSb.Remove(updateSb.Length - 2, 2); // remove the last ","
        insertSb.Remove(insertSb.Length - 2, 2);
        insertSb.Append(" )");
        this.InsertSql = insertSb.ToString();
        this.UpdateSql = updateSb.ToString() + whereClause;
        this.DeleteSql = "DELETE FROM " + mapping.TableName + whereClause;

    }

    public string InsertSql { get; private set; }

    public string UpdateSql { get; private set; }

    public string DeleteSql { get; private set; }

    public Dictionary<string, object> InsertParameters(object data)
    {
        PopulateParamValues(data);
        return mapping.AutoGenerateIds ? fieldParameters : keyParameters.Union(fieldParameters).ToDictionary(pair => pair.Key, pair => pair.Value);
    }

    public Dictionary<string, object> UpdateParameters(object data)
    {
        PopulateParamValues(data);
        return fieldParameters.Union(keyParameters).ToDictionary(pair => pair.Key, pair => pair.Value);
    }

    public Dictionary<string, object> DeleteParameters(object data)
    {
        PopulateParamValues(data);
        return keyParameters;
    }

    public void PopulateParamValues(object data)
    {
        var properties = mapping.EntityType.GetProperties(); // if you have mappings, work that in
        foreach (var propertyInfo in properties)
        {
            var paramName = propertyInfo.Name;
            if (keyParameters.ContainsKey(paramName))
            {
                keyParameters[paramName] = propertyInfo.GetValue(data);
            }
            if (fieldParameters.ContainsKey(paramName))
            {
                fieldParameters[paramName] = propertyInfo.GetValue(data);
            }
        }
    }
}

Example usage of the update with the update helper and a command builder in the logic layer for update:

public class Logic
{
    private readonly Func<ITransactionContext> createContext;
    private readonly Func<ITransactionContext, UpdateHelper> createHelper; 

    public Logic(Func<ITransactionContext> createContext, 
        Func<ITransactionContext, UpdateHelper> createHelper)
    {
        this.createContext = createContext;
        this.createHelper = createHelper;
    }

    public int UpdateEmployee(Employee employeeData)
    {
        using (var context = createContext())
        {
            var request = new UpdateRequest();
            request.Commands.Add(new Command(UpdateCommandType.Update, employeeData, new EmployeeMapping()));
            var helper = createHelper(context);
            var response = helper.Update(request);
            return response.TransactionId ?? 0;
        }
    }
}

The ORM would really help you with:

• data mapping
• command building (you don't need to do it)
• query building - you can use the built-in Linq-to-Sql.

Overall, this approach uses the Unit of Work from the Repository pattern, but instead of the repository object and its Add, Update and Delete methods, it uses an UpdateHelper class to do the updates based on the command pattern. This allows writing SQL directly, without an ORM mapper.

Well, this was long, but apparently without all the detail my answer was deemed unworthy. I hope this helps.

Comments

[Code Guru]

I am not aiming to create a perfect DAL repository handling all kind of requests. I just want to see if the repository pattern can be used effectively in my case where I have to write methods to meet business object requirements. Correct me if I am wrong, the Repository Pattern is more suitable to use with LINQ to SQL or an ORM Mapper. In my case, could you suggest a better approach - if not using the repository pattern?

[gabnaim]

If you going to vote my answer down, explain it. I have written both types of DALs and we did have a repository implementation using ADO. It was royal pain to maintain and we ripped it out and switched to generic ADO helper classes using Datasets and tables. You tell me what you suggest if he doesn't want to use an ORM. By far I am the only one who gave him an alternative.

[MikeSW]

I didn't vote you down, but here's my opinion why you are a bit wrong. Repository isn't coupled to ORM, they are independent and have different purposes. If someone doesn't use an ORM it doesn't mean he has to write datasets. A micro ORM i.e a data mapper helps a lot and depending on the application needs, writing sql by hand might be the optimum solution. Personally I write sql MUCH faster than I write Linq2Sql. And the ORM entities ARE the tables after all, only posing as objects.

[gabnaim]

Thanks for your input. I guess I wasn't clear, I am going to update it - I am trying to say what Moo-Juice is saying, which is, don't design your DAL around a pattern, design it around your requirements. Pick a pattern that helps you if you wish, but don't just use a pattern that will create more work for you.

[Code Guru]

this is good insight. You mentioned "So, the best possible way to do this (since you dont want to use EF or other ORM) is by creating a generic interface, and then a base abstract implementation. This way you dont need to code each repository, you can just instantiate them by the type! And after this, if you have any particular method specific to some of your entities, you can all inherit from the Repository and override or add methods and properties as needed". If not much of a problem, can you give me a small example. Really appreciate your help.

[Gabriel Vonlanten C. Lopes]

To be really honest, if you really are willing to go with this approach be prepared to learn some new stuff!! Because for a manual implementation that does not know of the Types of your entities (this means without using any mapper) you will have to write T4 (.tt) files to generate the Stored Procedures for CRUD operations for all entities, and then your generic implementation of the class woul call those procedures based on some pattern you define, like : PR_ENTITYNAME_INSERT. But this would not be a very easy to break solution. I had done this before, then i met EF and changed my life!

[Gabriel Vonlanten C. Lopes]

Another thing, if you can use attributes for the Linq to SQL in your POCO classes, then it is a much easier approach to write the base repository. The problem with having no attributes is to how to map the entities to the database.. if you dont map then in any way, i will have to write a lot of code to convert you Expression<Func<T, bool>> to a SQL statement. If you can use attributes (like putting [DatabaseTable] and others, then you are half way there

[gabnaim]

I have added some more detail. The QueryEmployees and the UpdateEmployees functions would be the ones exposed to your business logic. Note that most of this is not tested as I wrote it from memory.

[Gabriel Vonlanten C. Lopes]

I think your answer has a lot of gaps, but it could not be different, because there is no easy way to map expressions to SQL without some kind of mapping (there are some T4 alternatives), but +1 for all your time invested on trying to make a point! Good job man