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 (http://www.springframework.net/doc-latest/reference/html/dbprovider.html#dbprovider-additional) 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 (http://www.springframework.net/doc-latest/reference/html/tx-quickstart.html) 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.TransactionAttribut e, 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


Transaction interceptor
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