Transaction join problem with OpenSession(IDbConnection)

andyqp · **Joined:** Thu Aug 31, 2006 3:35 am **Posts:** 3

Hi,

Is there a way to open and operate a new ISession with user-provided IDbConnection that currently is in a transaction? It seems that NHibernate does not "join" this transaction (sql commands are not enlisted with it).

Example (probably not a good practice, but anyway):

Code:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransacton();
// Now I need another ISession on the same IDbConnection
ISession anotherSession = session.SessionFactory.OpenSession(session.Connection);

When NHibernate tries to execute sql commands using anotherSession, there is an error message stating that the command is not enlisted with an active transaction.

The reason I need anotherSession is that I'm trying to select some entity using Native SQL from database table that is different (but has the same structure) as the one that this entity is mapped to. (History table vs. Actual table). NHibernate confuses this selected entity with one that is already in session cache (has the same type and id, but came from different table). To make things more complicated, there is joined-sublass inheritance involved as well.

Code:

StringBuilder sql = new StringBuilder()
    .AppendLine("SELECT {m.*}")
    .AppendLine("FROM Muzeji_History m JOIN MuzejaVienibas_History m_1_ ON m_1_._AuditaIeraksts = m._AuditaIeraksts")
    .AppendLine("WHERE m._AuditaIeraksts = :id");
ISQLQuery query = session.CreateSQLQuery(sql.ToString())
    .AddEntity("m", typeof(Muzejs));
return (Muzejs)query.SetParameter("id", id, NHibernateUtil.Guid).UniqueResult();

In the mappings 'Muzejs' type is mapped to a different set of tables.

That's why I tried a solution with a temporary session for this read-only select. I have to use the same connection because of locking issues.

Any ideas?

Thanks,
Andy

sergey · **Posted:** Fri Nov 03, 2006 3:19 pm

Quote:

When NHibernate tries to execute sql commands using anotherSession, there is an error message stating that the command is not enlisted with an active transaction.

That's a problem since there is no way to determine that active transaction (there is no property on IDbCommand that would return the currently active transaction, probably because there might be more than one).

andyqp · **Joined:** Thu Aug 31, 2006 3:35 am **Posts:** 3

Thanks,

It seems that the best way for me to "solve" the underlying problem is to use SetResultTransformer (AliasToBean), that will create a transient entity from my select list, bypassing the session cache.

There would be "some" implications with references, collections and similar stuff anyway, if I would relied on mappings.

dbalzan · **Joined:** Sun Nov 05, 2006 3:51 pm **Posts:** 4

I was going to ask the same question today but then I saw this!

I find this really limiting as I would like to handle connections and transactions outside of Hibernate.

Could Hibernate be altered so it is possible to pass an IDbTransaction object into the constructor?

mabraham · **Joined:** Mon Mar 20, 2006 10:49 pm **Posts:** 59

Couldn't you do it from the other direction? That is, create a NHibernate session and start a transaction using ISession.BeginTransaction(). Then get the IDbConnection from the session using ISession.Connection, and use that connection to create/execute commands, etc.

Mike

dbalzan · **Joined:** Sun Nov 05, 2006 3:51 pm **Posts:** 4

mabraham wrote:

Couldn't you do it from the other direction? That is, create a NHibernate session and start a transaction using ISession.BeginTransaction(). Then get the IDbConnection from the session using ISession.Connection, and use that connection to create/execute commands, etc.

Unfortunately not. I need multiple sessions to execute within a single transaction. If you let Hibernate create the connection and transaction then it will destroy them when the first session closes.