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.

Let Oracle Know the Real User of ADO.Net application When Using Connection Pool

When using connection pooling, all connections are made with a fixed user name. In the database, it is difficult to tell who is the real user that is updating the database. While I can pass the user name to every stored procedures, it will be extremely tedious when multi-level of stored procedure call and still won't work for triggers.

The solution to this problem is to set the user name in some sort of database session state storage. For Oracle database, that is the CLIENT_IDENTIFIER variable in USERENV of the SYS_CONTEXT. You can set this variable by calling a build in package procedure dbms_session.SET_IDENTIFIER and retrieve it with SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER').

ODP.Net provide a convenient property, OracleConnection.ClientId, for this. It further reset ClientId automatically before the connection is returned back to the pool.

More information about this topic can be found here.

This same technique can be used with any database that provide some kind of database session storage, and in many databases, the temporary table can serve the same purpose.

For project that are required to run on different databases. A custom stored procedure can be used for this purpose and implementation can be vary. Actually, even with the case of Oracle, we end up used a package because

1. We can set more information then just CLIENT_IDENTIFIER. For example, the name of application, the client machine name and etc.
2. We caches those information in a package variable for fast access, package variable is 100 times faster then SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'), which is about the same speed as the build in function USER (we cache USER as well in package variable).

In my next post, I'll discuss how to set the ClientId when Spring.Net ADO support is used to manage the connections.