• Download demo - 8.9 KB

Introduction

I've been working on a Unit of work-ish / repository solution using ServiceStack's ORMLite data access solution through several projects, and so far it's worked wonders for me, and I thought it would be a good idea to share it.

I'll consider you have basic knowledge on how to use ORMLite, For more information, please do read the above link for further information, I'll focus on the repository implementation.

Now this solution really doesn't comply with the Unit of work / repository design pattern, since it doesn't control access through transactions. Instead, it works with information "on the fly".

Background

The Entity framework solution is great and all, and there are great articles that describe just how to use it, but sometimes I feel kind of frustrated when using it, since it tries to accomplish so much, it kind of gets in the way.

For this reason, I looked up other options to use instead. I tried Dapper, and it's great but it still needs a lot of work from you to get things done. NHibernate and I... have never really been friends, I haven't given it a try in a long time, and later I stumbled on ServiceStack's ORMLite, and I like just how easy it is to use, considering its limitations, of course.

Disclaimer

This is my own (independent) work and it's not related in any way to ServiceStack's website or anything. It's simply a solution I've come up with, and thought it's a good enough of an idea that I could share it.

Using the Code

First of all, we will define an Interface for the repository:

//
// Repository interface
//
    public interface IRepository<T> where T : class
    {
        /// <summary>
        /// Get all elements of type T
        /// </summary>
        List<T> GetAll();
        /// <summary>
        /// Get elements that comply to the specified criteria
        /// </summary>
        List<T> Get(Expression<Func<T, bool>> exp);
        /// <summary>
        /// Get an instance of T with the specified id
        /// </summary>
        T GetById(int id);
        /// <summary>
        /// Inserts or updates instance
        /// </summary>
        bool Save(T o);
        /// <summary>
        /// Deletes element with specified id
        /// </summary>
        void Delete(int id);
        /// <summary>
        /// Delete elements that comply to specified criteria
        /// </summary>
        void Delete(Expression<Func<T, bool>> exp);
    }

As you can see, with this implementation, I'm assuming that Ids in all tables will be of type int.

And now the base implementation:

public class Repository<T> : IRepository<T> where T : class
{
    protected IDbConnection db;

    public Repository(IDbConnection db)
    {
        this.db = db;
    }

    public virtual List<T> GetAll()
    {
        return db.Select<T>();
    }

    public List<T> Get(Expression<Func<T, bool>> exp)
    {
        return db.Select<T>(exp);
    }

    public virtual T GetById(int id)
    {
        var o = db.SingleById<T>(id);
        return o;
    }

    public virtual T GetById(string id)
    {
        var o = db.SingleById<T>(id);
        return o;
    }

    public bool Save(T o)
    {
        db.Save<T>(o);
        return true;
    }

    public void Delete(int id)
    {
        db.DeleteById<T>(id);
    }

    public void Delete(Expression<Func<T, bool>> exp)
    {
        db.Delete<T>(exp);
    }
}

Assuming we have a customer table... we have:

public class Customer
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [Required, StringLength(100)]
    public string Name { get; set; }
}

Now you can be as specific about attributes as I did, or you can go with the minimum, and only define AutoIncrement for Id. ServiceStack takes Id as primary key by default, and if you don't define string lengths, ORMLite will use MAX instead.

From this point forward, there are two ways you can implement a repository:

First is... inherit directly.

public class CustomerRep : Repository<Customer>
{
    public CustomerRep(IDbConnection db) : base(db) { }
}

This already gives you a basic Repository for customer table.

Now this is considered bad practice as for an article I read before... so the other option would be:

public class CustomerRep
{
    private Repository<Customer> rep;

    public CustomerRep(IDbConnection db) : base(db)
    {
        rep = new Repository<Customer>(db);
    }

    ///Further implementation
}

This second approach lets you define the specific parts that need to be accessible in the repository, and this is the recommended way of using a repository.

Now last is... Unit of work.

public class UnitOfWork : IDisposable
{
    private bool disposed;
    private IDbConnection db;

    static UnitOfWork()
    {
        OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;
    }

    public UnitOfWork(string connection)
    {
        db = connection.OpenDbConnection();
        Customers = new CustomerRep(db);
        if (!db.TableExists("Customer"))
        {
            db.CreateTableIfNotExists<Customer>();
            //Add customer to database when created for first time
            var c = new Customer() { Name = "John Doe" };
        }
    }

    public RepUsuario Customers { get; private set; }

    public void Dispose()
    {
        if (!disposed)
        {
            disposed = true;
            db.Dispose();
            db = null;
        }
    }
}

Now, let me explain what I did here... for simplicity, I can create the tables with the definitions made to the POCO class, so right before using the unit of work, I create the tables if they are not present. Of course, that means a number of permissions in the database server and so on... but this is not really mandatory if the tables already exist or are made beforehand.

What I love about this implementation is... with changing the following line of code:

OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;

And change for example, to:

OrmLiteConfig.DialectProvider = SqliteDialect.Provider;

And using an appropriate connection string, you don't need to change anything else to have your system working instantly with another DBMS. That's so cool in my opinion.

Points of Interest

Well for me, it's been gratifying to find ORMLite, since it's kind of working directly with database connections, with the best functionality of an ORM, but not so overwhelming as EF when it comes to all the resources it can take.

Don't get me wrong, EF is great and it works, but sometimes I like to have better control as if I have direct access to the queries, but not having to work with so much details, and also the flexibility to change to another DBMS if need be.

I've added a working demo project as a console app where you can see how it works using a sqlite in-memory database. The implemented repository only defines what's necessary for the demo, but more can be added as needed. I believe it's pretty straight forward how this code can be used in an MVC project, but I'll be glad to help if required.

Thanks for reading.

History

• 2015-02-05: Initial release
• 2015-03-24: Added demo project