How to intercept the connection object when it gets bound to the current transaction

[kennethxu]

We are using AdoTemplate as well as HibernateTemplate with hibernate transaction manager for our data access needs to Oracle database.

I would like to intercept the connection object so that I can issue an "alter session..." command to populate the client_identifier to be the current ASP.Net user name. Thus, the stored procedures and triggers would know who is the real user.

In Spring Java, I would wrapped the DataSource. How can I do the same in Spring.Net?

Thanks in advance!
Kenneth

[Mark Pollack]

Hi Kenneth,

In your case it seems like the same wrapping strategy will not work but you can use an AOP based solution to achieve your goal.

As background, Spring.NET has a base class (as in the Java version) DelegatingDbProvider, with some subclasses that allow for the user name and password to be set on a per-request basis or to pick a completely different database instance. See here for some details. These implementations essentially modify the connection string before the connection is open.

If you wrote a wrapper class that would execute the 'alter session' statements after creating the connection in an custom IDbProvider implementation. This would require opening the connection and associating and IDbCommand with a transaction. However, this would happen too soon as the IPlatformTransactionManager implementation hasn't created the connection/transaction pair yet.

The approach to use is to create an AOP interceptor that will be called right after the transaction interceptor. It can retrieve the current connection/transaction pair that is bound to thread local storage and then execute the 'alter select' command.

The Transactions QuickStart example shows how to chain interceptors where the pointcut is the [Transaction] attribute in the config file aspects-config.xml

This will essentially looks like the following

<object id="alterSessionInterceptor" type="MyNamespace.AlterSessionInterceptor">
  <property name="DbProvider" ref="dbProvider"/>
</object>

<object id="txAttributePointcut" type="Spring.Aop.Support.AttributeMatchMethodPointcut, Spring.Aop">
  <property name="Attribute" value="Spring.Transaction.Interceptor.TransactionAttribute, Spring.Data"/>
</object>

<aop:config>
    <aop:advisor order="2"
                      advice-ref="alterSessionAdvice"
                      pointcut-ref="txAttributePointcut"/>
</aop:config>

and you need to add the following order statement for declarative transaction management

  <tx:attribute-driven order="1"/>

The ordering will then be

1. Transaction interceptor
2. Alter session interceptor

The implementation of the AlterSessionInterceptor would roughly be

public sealed class AlterSelectInterceptor : AdoDaoSupport, IMethodBeforeAdvice
{
        public virtual void Before(MethodInfo method, object[] args, object target)
        {
              AdoTemplate.ExecuteNonQuery(CommandType.Text,
                             "alter session set current_schema=:username",
                             DbType.String, 0,
                             CurrentUser);
        }

        public string CurrentUser { get { //get current asp.net user } }
        
}

AdoTemplate will use the connection/transaction object just like in your DAO layer.

Let me know how it goes..

Cheers,
Mark

[adoehring]

Hi Mark,

I have quite the same problem as Kenneth. Our database logic needs a connection context. So for SQL Server before any writing data access takes place I have to execute "set context_info ..,". I followed your example and now I'm in the interceptor function:

public class InitSessionInterceptor : HibernateDaoSupport, IMethodBeforeAdvice {
public virtual void Before(MethodInfo method, object[] args, object target) {

HibernateTemplate.
//AdoTemplate.ExecuteNonQuery(CommandType.Text,
// "set context_info :userid",
// DbType.String, 0,
// "4");
}
}

So the question is now how to do this with HibernateTemplate.

Thank you very much for your help !
Andreas

[.ben]

Not sure, but can't you use a custom DriverConnectionProvider to do this with NHibernate?

[adoehring]

Hi .ben

that's it !

Thank's
Andreas

[kennethxu]

OK, I think I should update this thread as I now get it working perfectly with Oracle ODP.Net. It works well with both Ado and Hibernate; works with or without transaction.

Create a custom IDbProvider that sets the ClientId on the Oracle ODP.Net connection. You can run any SQL in the event handler. Code below is a simplified version with GetUser() logic removed.

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Spring.Data.Common;

namespace MyNamespace
{
    public class OracleClientIdDbProvider : 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 = GetUser();
            }
        }
    }
}

And here is the configuration section extracted from our spring configuration file.

  <object id="DbProvider" type="MyNamespace.OracleClientIdDbProvider">
    <property name="TargetDBProvider" ref="TargetDbProvider"/>
  </object>

  <db:provider id="TargetDbProvider" provider="OracleODP-2.0" 
               connectionString="${DB.IMM.ConnectionString}"/>

  <object id="AdoTemplate" type="Spring.Data.Generic.AdoTemplate, Spring.Data">
    <property name="DbProvider" ref="DbProvider"/>
  </object>

  <object id="SessionFactory" 
          type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider"/>
    <property name="MappingAssemblies">
      <list>
        ...
      </list>
    </property>
    <property name="HibernateProperties">
      <dictionary>
        <entry key="hibernate.dialect" value="${hibernate.dialect}"/>
        <entry key="hibernate.connection.driver_class" 
               value="${hibernate.connection.driver_class}"/>
      </dictionary>
    </property>
  </object>

  <object id="HibernateTemplate" 
          type="Spring.Data.NHibernate.Generic.HibernateTemplate, Spring.Data.NHibernate12">
    <property name="SessionFactory" ref="SessionFactory"/>
  </object>

  <object id="HibernateTransactionManager" name="transactionManager"
      type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate12">
    <property name="DbProvider" ref="DbProvider"/>
    <property name="SessionFactory" ref="SessionFactory"/>
  </object>

The AOP approach suggested by Mark should work too, but I didn't get a chance to try it out as we are not using AOP for transaction yet.

HTH