How to Populate ODP.Net ClientId When Spring.Net ADO is in Use

This post is long overdue but here we go.

One nice benefit of using Spring.Net's abstraction for ADO.Net data access is that I no longer need to write boiler plate code and never worry about connection leaking when someone forgets to close the connection object. But the problem this brought was that I could no longer set the CLIENT_IDENTIFIER in Oracle database as now Spring.Net is responsible for opening and closing the connection.

With help of Mark Pollack, I started with an wrapper of IDbProvider. Spring already provided DelegatingDbProvider (Kudos to Spring.Net team) so this indeed very easy.

    public class CurrentPrincipleToOracleClientIdDbProvider : DelegatingDbProvider
    {
        public override IDbConnection CreateConnection()
        {
            OracleConnection conn = (OracleConnection) TargetDbProvider.CreateConnection();
            conn.ClientId = Thread.CurrentPrincipal.Identity.Name;
            return conn;
        }
    }

with below configuration.

  <object id="DbProvider" type="Example.CurrentPrincipleToOracleClientIdDbProvider">
    <property name="TargetDBProvider" ref="TargetDbProvider"/>
  </object>
  
  <db:provider id="TargetDbProvider" provider="OracleODP-2.0" connectionString="${ConnectionString}"/>

Well, it didn't work, you cannot set the ClietnId when the connection is not open. Fine, let's open it.

            OracleConnection conn = (OracleConnection) TargetDbProvider.CreateConnection();
            conn.Open();
            conn.ClientId = Thread.CurrentPrincipal.Identity.Name;

Nope, doesn't work either. Although the CLIENT_IDENTIFIER was set correctly this time, but exception was thrown by the Spring.Net framework code complains that the connection is already opened when it tried to open. OK, now I know that IDbProvider.CreateConnection() works differently than Java's DataSource.getConnection().

Stuck? After digging around the members of OracleConnection class. I realized that it actually inherits from DbConnection which has an event called StateChange and Reflector tells that it actually raises events. Great, let's add an event handler.

    public class CurrentPrincipleToOracleClientIdDbProvider : DelegatingDbProvider
    {
        public override IDbConnection CreateConnection()
        {
            OracleConnection conn = (OracleConnection) TargetDbProvider.CreateConnection();
            conn.StateChange += StateChangeEventHandler;
            return conn;
        }

        private void StateChangeEventHandler(object sender, StateChangeEventArgs e)
        {
            if(e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Open)
            {
                OracleConnection conn = (OracleConnection)sender;
                conn.ClientId = Thread.CurrentPrincipal.Identity.Name;
            }
        }
    }

Now it works!

Update: If you user Spring.Net, here is any easy way out.