Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C#
Tip/Trick

Data Access using ServiceStack

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
6 Feb 2015CPOL4 min read 31.2K   439   9   5
Using ServiceStack's ORMLite framework as part of your MVC project's database access

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:

C#
//
// 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:

C#
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:

C#
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.

C#
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:

C#
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.

C#
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:

C#
OrmLiteConfig.DialectProvider = SqlServerDialect.Provider;

And change for example, to:

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer swit
Mexico Mexico
Graduated from Hermosillo Institute of Technology in 2001. I started working as help desk and lost practice in programming. In march 2002 I had an excellent opportunity of going to Japan for training.

In Japan I had my first contact with .net with Visual Studio .net 2002 Professional. I really thank Onodera sensei for that opportunity.

When I returned in november 2002, I started working with PHP / MySQL, and 5 months later I returned to the roots of .net. I started working with Visual Studio .net 2003 and so far .net has been my focus.

Comments and Discussions

 
Questionservicestack ormlite with .NET MVC Pin
Mahesh Waghmare6-May-16 3:11
Mahesh Waghmare6-May-16 3:11 
AnswerRe: servicestack ormlite with .NET MVC Pin
Daniel Santillanes6-May-16 6:30
professionalDaniel Santillanes6-May-16 6:30 
QuestionClass Changes Pin
vbjay.net27-Mar-15 8:44
vbjay.net27-Mar-15 8:44 
AnswerRe: Class Changes Pin
Daniel Santillanes27-Mar-15 9:47
professionalDaniel Santillanes27-Mar-15 9:47 
I'm afraid that's far from OrmLite's reach, and you'd need to implement your own database schema versioning of sorts, so that you know where the schema is, and how it needs to be updated. I've seen solutions like that implement it with liquibase (Java). which shouldn't be a problem to use since it should be executed before you app.

I hope this information helps somehow.
daniero

SuggestionRe: Class Changes Pin
Matt Cowan6-Oct-15 17:41
Matt Cowan6-Oct-15 17:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.