OracleConnection.ClientId?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hudgeo
Posts: 10
Joined: Mon 01 Feb 2010 19:09

OracleConnection.ClientId?

Post by hudgeo » Tue 16 Feb 2010 16:39

My entity framework ObjectContext partial looks like below. I have a need that for every new oracle connection DevArt makes to get the ClientId property set (different than User Id which is our app account). We use an app user account and we need to continue passing in the actual user as client id so we can extract it on the DB side in our triggers for CreatedBy / EditedBy audit fields to get the real end user.

The problem is Devart.Data.Oracle.OracleConnection does not expose a ClientId property like Oracle.DataAccess.Client.OracleConnection does. How can I accomplish this with Devart? This is essential for our auditing.

Code: Select all

// This file is intended to be edited manually

using System.Data;
using System.Data.EntityClient;
using System.Diagnostics;
using System.Security.Principal;
using Devart.Data.Oracle;

namespace TWData
{
    partial class TWDataEntities : ITWDataEntities
    {

        // Place your implementation of partial extension methods here

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

            this.Connection.StateChange += Connection_StateChange;
        }

        private EntityConnection EntityConnection
        {
            [DebuggerStepThrough]
            get { return this.Connection as EntityConnection; }
        }

        private OracleConnection OracleConnection
        {
            [DebuggerStepThrough]
            get { return this.EntityConnection.StoreConnection as OracleConnection; }
        }

        private void Connection_StateChange(object sender, StateChangeEventArgs e)
        {
Debug.WriteLine(e.CurrentState.ToString());
            //if (e.CurrentState != ConnectionState.Connecting) return;
            if (e.CurrentState != ConnectionState.Open) return;
            
            Debug.WriteLine(this.Connection.ConnectionString);

            var oracleConn = this.OracleConnection;
            if (null == oracleConn) return;

            //var user = new WindowsPrincipal(WindowsIdentity.GetCurrent());
            //var clientId = user.Identity.Name;

            //oracleConn.ConnectionString += ""
            
            //oracleConn.ConnectionString
            // there is no ClientId on OracleConnection b/c it is Devart.OracleConnection not OracleConnection
            //oracleConn.ClientId
            return;
        }

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

            base.Dispose(disposing);
        }
    }
}

hudgeo
Posts: 10
Joined: Mon 01 Feb 2010 19:09

Workaround for missing ClientId

Post by hudgeo » Tue 16 Feb 2010 20:44

I was able to get around this issue by directly invoking oracle's DBMS_SESSION.SET_IDENTIFIER. However it would be nice if a ClientId property were added to mimic the real OracleConnection and to prevent having to dive down under the hood to do this manually.


Code: Select all

// This file is intended to be edited manually

using System.Data;
using System.Data.EntityClient;
using System.Diagnostics;
using Devart.Data.Oracle;

namespace TWData
{
    partial class TWDataEntities : ITWDataEntities
    {
        // Place your implementation of partial extension methods here

        public static string ClientId { get; set; }

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

            this.Connection.StateChange += Connection_StateChange;
        }

        private EntityConnection EntityConnection
        {
            [DebuggerStepThrough]
            get { return this.Connection as EntityConnection; }
        }

        private OracleConnection OracleConnection
        {
            [DebuggerStepThrough]
            get { return this.EntityConnection.StoreConnection as OracleConnection; }
        }

        private void Connection_StateChange(object sender, StateChangeEventArgs e)
        {
            Debug.WriteLine(e.CurrentState.ToString());
            //if (e.CurrentState != ConnectionState.Connecting) return;
            if (e.CurrentState != ConnectionState.Open) return;
            
            Debug.WriteLine(this.Connection.ConnectionString);

            var oracleConn = this.OracleConnection;
            if (null == oracleConn) return;

            // there is no ClientId on OracleConnection b/c it is Devart.OracleConnection not Oracle.DataAccess.OracleConnection
            var cmd = oracleConn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "DBMS_SESSION.SET_IDENTIFIER";
            cmd.Parameters.Add(new OracleParameter {ParameterName = "client_id", Value = TWDataEntities.ClientId});
            cmd.ExecuteNonQuery();
            
            return;
        }

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

            base.Dispose(disposing);
        }
    }
}

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 17 Feb 2010 12:02

We will investigate the possibility to add this property in one of the nearest builds.
I will post here when it is avalable.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 27 Apr 2010 13:08

We have implemented the ClientId connection string parameter.

gustavp
Posts: 10
Joined: Wed 13 Jul 2011 08:44

Post by gustavp » Thu 18 Aug 2011 13:13

The only problem is, that OracleConnection.ClientId actually closes the connection again, so the connectionstate is Closed/Invalid when the context is created.
The solution remains to set the identifier manually.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 23 Aug 2011 16:57

Could you please describe the problems you encounter with the connection being closed after changing ClientId? For example, the ObjectContext connection is closed by default (i.e., just after creating an ObjectContext instance) and between executions of queries/update commands, thus you should be able to change ClientId before executing a new set of commands without redundant reopening of the connection.

gustavp
Posts: 10
Joined: Wed 13 Jul 2011 08:44

Post by gustavp » Thu 25 Aug 2011 08:29

My implementation, very much a copy pastejob of the above solution.

I can't set clientid if the connection is closed, so I have to do it when the state is changed to open.

If I use the builtin clientid = "something" the connection on the context unexpectedly changes to closed...(?)

Code: Select all

namespace MyProject.Models
{
    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 );
        }
    }
}


Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 31 Aug 2011 12:04

Changing any parameters of OracleConnection (e.g., ClientId), which affect the session state at the server, leads to closing connection.
As a solution, create a context with a constructor which accepts a connection string. The connection string can be constructed dynamically: add ";ClientId=xxx" to its constant part.

Post Reply