ServiceStack + ORMLite + Repository Pattern

Question

I'm trying to implement the Repository pattern using ORMLite. I initially started off with:

public List<Todo> GetByIds(long[] ids)
{
    using (IDbConnection dbConn = dbFactory.OpenDbConnection())
    {
        return dbConn.Ids<Todo>(ids).ToList();
    }
}

But having this under every method in my Repository seemed a bit repetitive? So I created a data context class which all my repositories inherit from:

public class TodoRepository : DataContext

Here is that DataContext class:

public class DataContext
{
    protected OrmLiteConnectionFactory dbFactory = new 
OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["AppDb"].
ConnectionString, SqlServerOrmLiteDialectProvider.Instance);

    protected IDbConnection dbConn;

    public DataContext()
    {
        dbConn = dbFactory.OpenDbConnection();
    }
}

I then simply have to do this in my methods:

public List<Todo> GetByIds(long[] ids)
{
    return dbConn.Ids<Todo>(ids).ToList();
}

I was just curious if this is a good design pattern and what other's have come up with when using the Repository pattern for data access with ORMLite.

Taffeta

Answer 1

I wouldn't hard-code the strong-type DialectProvider and configuration for the connection in your repository. Instead I would do something that's similar to ServiceStack's base Service class, e.g:

public class Repository : IDisposable {

    public IDbConnectionFactory DbFactory { get; set; } //injected by IOC

    IDbConnection db;
    IDbConnection Db 
    { 
        get 
        {
            return db ?? db = DbFactory.Open();
        }
    }

    public List<Todo> GetByIds(long[] ids)
    {
        return Db.Ids<Todo>(ids);
    }

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

This way you can initialize the IDbConnectionFactory where it's meant to be, i.e. in the IOC registration of your Host project. In ServiceStack this is in AppHost.Configure():

container.Register<IDbConnectionFactory>(c => 
    OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["AppDb"].
        ConnectionString, SqlServerDialect.Provider);

container.RegisterAutoWired<Repository>().ReusedWithin(ReuseScope.None); 

Leduc

I guess I could further refactor this to pass in a type for my repository? Repository<T> : IDisposable where T : new() ? As my repo's will have the same functionality (CRUD operations)? – Taffeta

Sure, it's just C#, though I personally dislike forcing a repository per Table pattern - preferring to instead grow my architecture – Leduc

Well if I need additional functionality, I can just inherit from my base repository and add in any additional methods I need for more complex queries etc.. – Taffeta

Are there any example projects with this setup?I can't get the IDbConnectionFactory to be autowired/injected when not inheriting Service. – Gardener

I tried this and DBFactory was always null and so throwing errors when trying to 'Open()'. – Hashum

@user904538 you still need to register it in the IOC. – Leduc

@mythz, I'd like to use this pattern (a shared class for DB access across multiple MVC controllers) in a new app I'm about to start. Is there any merit (performance-wise or other) to making Repository a singleton for easy sharing (ie Repository.Shared.GetById(1)) instead of getting it via TryResolve each time? Would there be any threading issues across users if a singleton? – Fidelafidelas

@Fidelafidelas Note the DbFactory registered dependency is already a singleton. Also a singleton repository would need to ensure Thread Safety and would require resolving a IDbConnection from the DbFactory behind each API call making the impl more verbose and require fetching from the Pool for each Db access which is more expensive than fetching from the IOC. Basically unless there's a good reason I'd avoid singleton repositories, you can make the DbFactory available as a singleton but you're essentially just saving a Dictionary access so IMO not worth it. – Leduc

@Leduc Gotcha, thanks! Given that, I can just stick to TryResolve<Repository>(). Ha! – Fidelafidelas

@Leduc Pardon if this is a dumb question, but if its ReuseScope.None and thereby a new instance each time then what advantage does it have over something as simple as new Repository() each time it is needed? Would the DbFactory not get injected in the latter case? – Fidelafidelas

@Fidelafidelas Not following, how can the DbFactory dependency be injected if it's not resolved with the IOC? Also the DbFactory (like all connection factories) should always be registered as a singleton. If you have other questions, please open a new question with the full details of what you mean. – Leduc

Answer 2

I know this is an old question but I thought I'd chip in. My services access a IGenericRepository<T> that access a IDatabaseContext (could be anything that uses System.Data.IDbConnection, but in this case ServiceStack's OrmLite), which in turn uses ServiceStack's IDbConnectionFactory:

public class GenericRepository<T> : IGenericRepository<T>
    where T : class
{
    private readonly IDatabaseContext _databaseContext;

    public GenericRepository(IDatabaseContext databaseContext)
    {
        _databaseContext = databaseContext;
    }

    public T Get(int id)
    {
        return _databaseContext.Query(db =>
            {
                return db.SingleById<T>(id);
            });
    }

    public async Task<T> GetAsync(int id)
    {
        return await _databaseContext.QueryAsync(async db =>
        {
            return await db.SingleByIdAsync<T>(id);
        });
    }

    // other methods (All, First, etc)
}

IDbConnectionFactory is registered as

container.Register<IDbConnectionFactory>(c => 
    OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["AppDb"].
        ConnectionString, SqlServerDialect.Provider);

The IDatabaseContext for OrmLite:

public class OrmLiteDatabaseContext : IDatabaseContext
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public DatabaseContext(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public T Query<T>(Func<IDbConnection, T> query)
    {
        using (var connection = _dbConnectionFactory.OpenDbConnection())
        {
            return query(connection);
        }
    }

    public async Task<T> QueryAsync<T>(Func<IDbConnection, Task<T>> query)
    {
        using (var connection = _dbConnectionFactory.OpenDbConnection())
        {
            return await query(connection);
        }
    }
}

which works pretty well.

Subak