Transactions in the Repository Pattern using ServiceStack.ORMLite

Question

I'm implementing Repository Pattern using ServiceStack.ORMLite like this:

public class MyRepository : IMyRepository
{
    private IDbConnectionFactory DbConnectionFactory = null;

    public MyRepository(IDbConnectionFactory dbConnectionFactory)
    {
        DbConnectionFactory = dbConnectionFactory;
    }

    public void MyMethod()
    {
        using (var connection = DbConnectionFactory.OpenDbConnection())
        using (var cmd = connection.CreateCommand())
        {
            //Do something here
        }
    }
}

But I don't know how to handle DbTransaction when I need to warp some DB operation in a DbTransaction.It looks like TransactionScope is a solution but I don't know whether is way too heavy for this.

Answer 1

ServiceStack OrmLite gives you access to ADO.NET's raw IDbConnection and IDbTransaction classes which you should use instead of TransactionScope's. You can create a transaction by using the IDbConnection.OpenTransaction() extension method, e.g:

public class MyRepository : IMyRepository, IDisposable
{
    private IDbConnectionFactory DbFactory { get; set; }

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

    public void WithTransactions()
    {
        using (var trans = Db.OpenTransaction())
        {
            //Do something here

            trans.Commit();
        }
    }

    public List<Poco> WithoutTransactions()
    {
        return Db.Select<Poco>();
    }

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

Since it requires less code I prefer property injection and to use a Lazy Db property to simplify data access patterns for my methods.

Note: Whenever any of your classes keeps a reference to an open IDbConnection (like this one), it should be registered with a None/Transient or RequestScope so the connection gets disposed after use (i.e. don't register it as a singleton).

Answer 2

I like mythz answer here but was having some trouble myself getting things to work as I would expect based on mythz feedback. I ran across this other answer that, at first, seemed not to be what I was looking for but really ended up putting me in the right direction.

Best practices of implementing unit of work and repository pattern using ServiceStack.ORMLite