1

In my current task, I need to take some existing legacy code (which is using raw ADO) and make it work under NHibernate. I want to keep original sql queries as is, to ensure exactly the same behavior.

This is what original code was doing:

  1. Copy everything from table A to temporary table #A (using "insert" statement)
  2. Run external BCP utility (via command line), which is copies everything from backup file (.tbl) to table A
  3. Copy everything from #A to A

(for sake of discussion, let's assume this is perfectly logical and this is what required to be done)

The problem:

I can't run 1 + 3 in single statement, because I have BCP call in the middle. Therefore, I forced to perform three separate calls. So, I calling 1, then 2, then 3... and I getting error about invalid object name #A.

What actually happens? Apparently, after each query execution, NHibernate recycles connection --> which making temporary table #A to be deleted right after 1 is executed (temporary tables live until connection is open).

I need somehow to overcome this problem. Ideally, I would like somehow to tell NHibernate to keep the same connection all the time during this process.

Thanks!

P.S. I tried to execute 1+2+3 in transaction. That causes deadlock with BCP (no matter which IsolationLevel)

Illidan
  • 4,047
  • 3
  • 39
  • 49
  • In sqlserver you can create temporary tables of global scope. (##A instead of #A). This might solve your problem. (But also might introduce concurrency issues, depending on how it's used). http://stackoverflow.com/questions/18614344/scope-of-temporary-tables-in-sql-server-2012 – Andrew Shepherd Nov 03 '14 at 21:42
  • Also, can you just access the `ISession.Connection` property, and keep the raw ADO code? – Andrew Shepherd Nov 03 '14 at 21:44

2 Answers2

0
  1. Open session
  2. Start transaction
  3. Start task 1
  4. Close transaction
  5. Start task 2
  6. Open transaction
  7. Start task 3
  8. Close transaction
  9. Close session

It is maybe needed to wrap task 2 also in a transaction.

Orlando Helmer
  • 403
  • 1
  • 4
  • 14
  • I think between "Close transaction" and "Start task 2", nHibernate will switch to a new connection, which is the actual problem. – Andrew Shepherd Nov 03 '14 at 21:50
0

I found that if I create session by providing connection into it's constructor - then session keeps that connection all the way. So, this is my "quick & dirty" solution:

ISession dummy = factory.OpenSession();

using (ISession session = factory.OpenSession(dummy.Connection))
{
// my stuff here. the connection will remain the same across multiple query executions
}

As I understand, more elegant solution is possible by providing custom IConnectionProvider - would be nice if someone could provide such solution.

Illidan
  • 4,047
  • 3
  • 39
  • 49