How do I use Oracle's ClientId property with Enterprise Library?

Question

Just stumbled across the new ClientId (aka client_identifier) variable that is available in Oracle 10g onwards, and would love to incorporate that into an app to assist with audit trails.

The app is using a generated Enterprise Library DAAB based layer (netTiers) that is wired to use the ODP.NET drivers with EntLibContrib, so CRUD functions create an OracleDatabase object and then retrieve generic DbCommand objects from it

It looks like the OracleConnection class has the ClientId property, so what is the cleanest way to get to the Connection object within that pattern? Should I be grabbing the connection out of every DbCommand I create and setting it there, or is that overkill?

Because EntLib is doing much of the connection management behind the scenes, I'm not sure whether I can set the ClientId somewhere outside of the CRUD functions and expect it to persist?

I see no reason why you can't just grab the connection and update the ClientId property from the DbCommand object. Though is there not somewhere where you can access the DbConnection object directly? Unless EntLib does things with the ClientId (which I doubt), it will persist for the remainder for your connection session. — mservidio

gustav gustav · Accepted Answer · 2011-11-08T14:47:34

If the connection is a partial class you can implement a trigger that sets the client id whenever the connection changes state to open. That's the way I implemented it.

I don't know if you can use part of this:

public partial class DataContext
{
    partial void OnContextCreated()
    {
        if ( null == this.Connection ) return;

        this.Connection.StateChange += Connection_StateChange;
    }
    private EntityConnection EntityConnection
    {
        get { return this.Connection as EntityConnection; }
    }
    private OracleConnection OracleConnection
    {
        get { return this.EntityConnection.StoreConnection as OracleConnection; }
    } 
    private void Connection_StateChange( object sender, StateChangeEventArgs e )
    {
        if ( e.CurrentState != ConnectionState.Open ) return;

        OracleConnection conn = this.OracleConnection;
        if ( null == conn ) return;

        //closes connection on DataContext (bug?), and passes closed/broken connection 
        //conn.ClientId = HttpContext.Current == null ? "Anonymous" : HttpContext.Current.Profile.UserName;

        //working solution
        string identity = HttpContext.Current == null ? "Anonymous" : HttpContext.Current.Profile.UserName;
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "DBMS_SESSION.SET_IDENTIFIER";
        cmd.Parameters.Add( new OracleParameter { ParameterName = "client_id", Value = identity } );
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        return;
    }

    protected override void Dispose( bool disposing )
    {
        if ( null != this.Connection )
            this.Connection.StateChange -= Connection_StateChange;

        base.Dispose( disposing );
    }
}

How do I use Oracle's ClientId property with Enterprise Library?

1 Answers