2

Having primarily used the Enterprise Library Data Access Application Block, I am used to its ability to keep a single open connection, for a given connection string, within the scope of an ambient transaction. It does this using the classes Database, DatabaseConnectionWrapper and TransactionScopeConnections.

The Database class either gets the current connection associated with the ambient transaction, wrapped by DatabaseConnectionWrapper and increments the wrapper's reference counter (a static member) via TransactionScopeConnections.GetConnection or creates a new open connection. When disposing the ConnectionWrapper, the reference counter is decremented and the connection is not disposed until the reference count is zero. Thus, each Repository method can get an open connection without concern about creating multiple connections within a TransactionScope which would result in promotion to a distributed transaction.

Is there similar functionality within, or a preferred pattern of use with, OrmLite's OrmLiteConnectionFactory or do I need to roll my own perhaps using a combination of the connection filter and OnDisposing event of the connection factory?

An example of usage. Note two different databases are involved in the transaction.

using System;
using ServiceStack.OrmLite;
using System.Transactions;
using Dapper;
using System.Data;

public abstract class Repository
{
    public IDbConnectionFactory ConnectionFactory { get; set; }
}

public class Repository1:Repository
{
    public void UpdateSomething(int id, string value)
    {
        using (var connection = ConnectionFactory.Open("Db1"))
        {
            connection.Execute("proc1", new { id = id, value = value }, commandType: CommandType.StoredProcedure);
        };
    }
}

public class Repository2:Repository
{
    public void UpdateSomethingElse(int id, string value)
    {
        using (var connection = ConnectionFactory.Open("Db1"))
        {
            connection.Execute("proc2", new { id = id, value = value }, commandType: CommandType.StoredProcedure);
        };
    }
}

public class Repository3:Repository
{
    public void UpdateYetSomethingElse(int id, string value)
    {
        using (var connection = ConnectionFactory.Open("Db2"))
        {
            connection.Execute("proc3", new { id = id, value = value }, commandType: CommandType.StoredProcedure);
        };        
    }
}


public class BusinessObject
{
    public int Id { get; set; }
    public string Value { get; set; }

    public void Save()
    {        
        using (TransactionScope scope = new TransactionScope())
        {
            new Repository1().UpdateSomething(Id,Value);
            new Repository2().UpdateSomethingElse(Id, Value);
            new Repository3().UpdateYetSomethingElse(Id, Value);
        }
    }
}
Richard Collette
  • 5,462
  • 4
  • 53
  • 79
  • Keeping a connection open is a left-over from the pre-ORM days and a very bad practice as it consumes server resources without reason. ORMs typically keep track of the changes you make in a session/transaction and send them to the database when you commit the session. This means that they only require an open connection when you do a Session.Update/Commit or whatever each ORM calls these methods. – Panagiotis Kanavos May 30 '13 at 11:27
  • Keeping a connection open as a singleton is not ideal, but in web applications is is common to see a "session" per request pattern and have the connection open at the beginning of the request and close at the end of the request. – mosesfetters May 30 '13 at 11:33
  • Session per request doesn't refer to the connection. ORMs (e.g. NHibernate) only open a connection when reading or writing changes to the database. Connection pooling means that you don't pay any penalty for reopening the connection as the ADO.NET driver keeps a pool of connections always open for each appdomain (ie web application or service) – Panagiotis Kanavos May 30 '13 at 11:36
  • This link may help you http://stackoverflow.com/questions/14971770/transactions-in-the-repository-pattern-using-servicestack-ormlite – mosesfetters May 30 '13 at 12:35
  • I am using stored procedures. From what I have seen, the SQL is executed at the time the the execute/run/query, etc. methods are called, and not batched up to run when the transaction is committed as some sort of method of decreasing connection open time. TransactionScope also handles transactions across multiple data technologies (mq, services) as well as promoting transactions to distributed transactions. For the use cases mentioned, the question is still valid. – Richard Collette May 30 '13 at 13:15
  • Why not keep the transaction, by default, open for the lifetime of the repository (of course having the repository implement IDisposable)? You could also add commit behavior if you wish to commit prior to the disposal. Isn't the purpose of a repository to encompass a unit of work with a mediating interface? You could also use a connection factory to inject a connection as needed. – wbennett Jun 09 '14 at 18:35

0 Answers0