The Open Session In View Pattern

The purpose of the Open Session In View (OSIV) Pattern is to handle a database session (connection and transaction) within the scope of a single web request cycle.

A database session is created at the beginning of each web request, this is then used by all the different parts of the web application and then the session is committed as the web request ends or rolled back if the web request fails for some reason.


using System;
using System.Configuration;
using System.Net;
using System.Web;

namespace OpenSessionInView
{
    // Implements the Open-Session-In-View pattern using DbSessionManager.
    // Assumes that each HTTP request is given a single transaction for the entire page-lifecycle.
    public class DbHttpModule : IHttpModule
    {
        private string ConnectionString
        {
            get
            {
                if (ConfigurationManager.ConnectionStrings["ConnectionString"] == null)
                    throw new Exception("ConnectionString 'ConnectionString' is Null");

                if (string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                    throw new Exception("ConnectionString 'ConnectionString' value is Empty");

                return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            }
        }

        #region IHttpModule Members

        public virtual void Init(HttpApplication httpApp)
        {
            httpApp.BeginRequest += OnBeginRequest;
            httpApp.EndRequest += OnEndRequest;
        }

        public virtual void Dispose()
        {
        }

        #endregion

        private void OnBeginRequest(object sender, EventArgs a)
        {
            DbSessionManager.Instance.Initialise(ConnectionString);

            DbSessionManager.Instance.BeginTransaction();
        }


        private void OnEndRequest(object sender, EventArgs a)
        {
            try
            {
                HttpApplication httpApp = (HttpApplication) sender;

                switch ((HttpStatusCode) httpApp.Response.StatusCode)
                {
                    case HttpStatusCode.OK:
                    case HttpStatusCode.Redirect:
                    case HttpStatusCode.MovedPermanently:

                        DbSessionManager.Instance.CommitTransaction();

                        break;
                    default:
                        // error during session - rollback (exception already handled)
                        DbSessionManager.Instance.RollbackTransaction();
                        break;
                }
            }
            finally
            {
                DbSessionManager.Instance.CloseSession();
            }
        }
    }
}

A HttpModule is used to create and commit the transaction.


using System;
using System.Data;
using System.Data.SqlClient;

namespace OpenSessionInView
{
    public class DbSessionManager
    {
        private static readonly ThreadLocal Session = new ThreadLocal();

        private readonly bool _useTransactions;
        [ThreadStatic] private SqlConnection _sqlConnection;
        private string _sqlConnectionString;

        [ThreadStatic] private SqlTransaction _sqlTransaction;

        private DbSessionManager()
        {
            _useTransactions = true;
        }

        public DbSessionManager(string sqlConnectionString, bool useTransactions)
        {
            _sqlConnectionString = sqlConnectionString;
            _useTransactions = useTransactions;
        }

        public static DbSessionManager Instance
        {
            get
            {
                DbSessionManager instance = (DbSessionManager) Session.Value;

                if (instance == null)
                {
                    instance = new DbSessionManager();

                    Session.Value = instance;
                }

                return instance;
            }
        }

        public SqlConnection SqlConnection
        {
            get
            {
                //  Lazily create the SqlConnection object
                if ((_sqlConnection == null))
                {
                    _sqlConnection = new SqlConnection(_sqlConnectionString);

                    try
                    {
                        _sqlConnection.Open();
                    }
                    catch (SqlException)
                    {
                        // Something went wrong with the connection, so reset the property.
                        _sqlConnection = null;

                        throw;
                    }
                }

                return _sqlConnection;
            }
        }

        public SqlTransaction SqlTransaction
        {
            get
            {
                BeginTransaction();

                return _sqlTransaction;
            }
        }

        public void BeginTransaction()
        {
            //  Allocate the Tx lazily
            if ((_sqlTransaction == null) && (_sqlConnection != null) && _useTransactions)
            {
                _sqlTransaction = SqlConnection.BeginTransaction();
            }
        }

        public bool IsTransactionOpen()
        {
            return (_sqlTransaction != null);
        }

        public void CommitTransaction()
        {
            if (IsTransactionOpen())
            {
                _sqlTransaction.Commit();
                _sqlTransaction = null;
            }
        }

        public void RollbackTransaction()
        {
            if (IsTransactionOpen())
            {
                _sqlTransaction.Rollback();
                _sqlTransaction = null;
            }
        }

        public void CloseSession()
        {
            if ((_sqlConnection != null) && (_sqlConnection.State != ConnectionState.Closed))
            {
                _sqlConnection.Close();
            }

            _sqlConnection = null;
            _sqlTransaction = null;
            _sqlConnectionString = null;
        }

        public void Initialise(string sqlConnectionString)
        {
            //  Create the SqlConnection lazily, so just store the connection string now
            _sqlConnectionString = sqlConnectionString;
        }

        public SqlCommand CreateCommand(string sql)
        {
            return new SqlCommand(sql, SqlConnection, SqlTransaction);
        }
    }
}

The singleton DbSessionManager is used to access and managed the database session.


using System;
using System.Threading;

namespace OpenSessionInView
{
    public class ThreadLocal
    {
        private readonly string _slotName;

        public ThreadLocal()
        {
            // Name for Thread Local Storage (TLS).  TLS will allow us to have static access to
            // our database connection instance in a way that remains local to the current thread
            // (i.e. HTTP request)
            _slotName = Guid.NewGuid().ToString();
        }

        public object Value
        {
            get
            {
                lock (this)
                {
                    LocalDataStoreSlot slot = Thread.GetNamedDataSlot(_slotName);

                    if (slot == null)
                    {
                        return null;
                    }

                    return Thread.GetData(slot);
                }
            }

            set
            {
                lock (this)
                {
                    LocalDataStoreSlot slot = Thread.GetNamedDataSlot(_slotName);

                    // Allocate a new slot if necessary
                    if (slot == null)
                    {
                        Thread.AllocateNamedDataSlot(_slotName);
                        slot = Thread.GetNamedDataSlot(_slotName);
                    }

                    slot = Thread.GetNamedDataSlot(_slotName);

                    Thread.SetData(slot, value);
                }
            }
        }
    }
}

ThreadLocal is used to store the database session in a Thread

You will also need to store your connection string



    
        
    


You will need to configure the web.config

This is for IIS 7



    
      
    


This is for IIS 6



  
    
  


You can download the Open Session in View Pattern project here



Comments

No comments yet.

Add Yours

  • Author Avatar

    YOU


Comment Arrow




About Author

Robert

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning hands down.