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.

Gameness

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).

Whack

Is it good to open an IDbConnection only once per request and disposed after use?I thought I should open the connection at the begin of each method and close it when method is over. – Gameness

It's fine, if you're using a connection pool (i.e. the default behaviour for SqlServer) the connection isn't really closed anyway, it's just released back into the pool. But yeah either way it's fine to use Request Scope since it's single-threaded per-request so a connection instance can safely be shared with other dependencies in the same request scope. – Whack

If one request is holding the connection for a long time,but it only need to access DB just a monent(like select some data for DB then process it),I think the performance of DB will be bad...But it's not easy to handle the connection manually... – Gameness

You have no valid reason to think this. What are you going to do if the long running connection needs db access multiple times? Re-open a db connection multiple times within the same request? How is opening a new connection better than re-using an existing one? Seems you don't understand connection pooling or multi-threading, don't over think this, the answer is to use Transient or RequestScope. But if your paranoid, feel free to explicitly open and close the connection each time as you're doing in your example. – Whack

I think maybe I misunderstanding the relationship between IDbTransaction IDbCommand and IDbConnection...I agree with what your say... – Gameness

#14974148 I redefine the question at here.I think this is an good example of using ServiceStack.ORMLite. – Gameness

@Whack in this case i guess you would register IDbConnectionFactory as a Singleton and Repositories as Transient/RequestScope correct? What happens if you add a Service Layer as well, lets say in an Mvc Project, how would you register the services that have the repositories as dependencies? – Jinnyjinrikisha

@AndreasPilavakis if your Service uses transient dependencies it also needs to be transitive, which ServiceStack Services are by default. – Whack

This answer is incomplete because it doesn't actually show using the transaction object. This is important because the way it is shown here you'll just get a exception with […] requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. – Afterdamp

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

Phlebosclerosis