Devart Forums

Posted: **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);
        }
    }
}

Posted: **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);
        }
    }
}

Posted: **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.

Posted: **Tue 27 Apr 2010 13:08**

We have implemented the ClientId connection string parameter.

Posted: **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.

Posted: **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.

Posted: **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 );
        }
    }
}

Posted: **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.

Devart Forums

OracleConnection.ClientId?

OracleConnection.ClientId?

Workaround for missing ClientId