SpringExtension: Extends DbProvider to Provide Hook to Connection Open and Close

This is inspired by the need of populating ODP.Net's OracleConnection.ClientId property with current thread identity when Spring.Net's ADO Support is used in the data access layer.

The major difference between Java’s DataSource and its Spring.Net’s counterpart, DbProvider, is that the connection returned from DbProvider is not open. With Java’s  DataSource, you can easily make a wrapper to set up the connection before it’s used by the application code. But things become difficult with DbProvider, you just cannot do this. If you open the connection in the wrapper, the subsequent call to open the connection by the Spring.Net framework will fail (you wish it will check the status before calling open? nope, that’s not the case).

But there is a workaround. We can use an event handler to setup the connection at the moment Spring.Net framework opens the connection. Although IDbConnection doesn't provide any mean to let you react when a database connection is opened and/or closed, but the System.Data.Common.DbConnection object, which is the base class of most ADO implementation, has an event called StateChange. This gives us the hook to extend the DbProvider so that application code can react on connection open and/or close event. We can do many things with this, for example call a stored procedure to setup the connection options, tell database the current thread’s identity name, even setup the in-memory database for unit testing and etc.

The good new is we have implemented the hook in SpringExtension, so you don’t have to re-do the work. Using SpringExtension, you can easily achieve what I have described in my earlier post about setting oracle CLIENT_IDENTIFIER database session information, by just below configuration settings. No c# program needs to be written.

  <object id="DbProvider" type="Spring.Data.Common.ExtendedDbProvider, Spring.Data.Extension">

    <property name="TargetDBProvider" ref="TargetDbProvider"/>

    <property name="ConnectionStateListener">

      <object type="Spring.Data.Support.OdpNetClientIdentifierSetter,  Spring.Data.Extension"/>

    </property>

  </object>

  <db:provider id="TargetDbProvider" provider="OracleODP-2.0" connectionString="${DB.IMM.ConnectionString}"/>

You can further customize the action by replacing the OdpNetClientIdentifierSetter with any implementation of IDbConnectionStateListener, defined in SpringExtension as:

    /// <summary>

    /// Interface to receive the database connection state change.

    /// </summary>

    /// <author>Kenneth Xu</author>

    public interface IDbConnectionStateListener

    {

        /// <summary>

        /// To be called when the <paramref name="connection"/> state changed

        /// from <paramref name="original"/> to <paramref name="current"/>.

        /// </summary>

        /// <param name="connection">

        /// The database connection that its state has changed. 

        /// </param>

        /// <param name="original">

        /// The original state of the connection before change.

        /// </param>

        /// <param name="current">

        /// Current state of the connection.

        /// </param>

        void AfterStateChange(IDbConnection connection, ConnectionState original, ConnectionState current);

    }

Or if all you care is open and/or close event, you can extend DbConnectionStateListenerBase by override any of the below two virtual methods:

        /// <summary>

        /// This method is called after database is opened.

        /// </summary>

        /// <param name="connection">

        /// The connection that was just opened.

        /// </param>

        public virtual void AfterConnectionOpen(IDbConnection connection)

        {

        }

        /// <summary>

        /// This method is called after database is closed.

        /// </summary>

        /// <param name="connection">

        /// The connection that was just closed.

        /// </param>

        public virtual void AfterConnectionClose(IDbConnection connection)

        {

        }

Welcome comments and suggestions