Why does temporary tables not work in nhibernate?

Question

I have been trying to use temporary tables along with nhibernate.

The following piece of code doesn't work

query = @"CREATE TABLE [#Dataset_x]
          ([Name] [nvarchar](max) NULL,
           [Value] [nvarchar](max) NULL )";
Session.CreateSQLQuery(query).SetTimeout(uploadExecQueryTimeout).ExecuteUpdate();

query = @"INSERT INTO [#Dataset_x] ([Name],[Value]) VALUES('Dataset','MyDataset')";
Session.CreateSQLQuery(query).SetTimeout(uploadExecQueryTimeout).ExecuteUpdate();

And I get the object invalid error when I try to run the second query (because the temporary table that was created expired before I called in the second query).

However if I add the above piece of code under a transaction as shown below it works perfectly fine.

using (var transaction = Session.BeginTransaction())
{
    query = @"CREATE TABLE [#Dataset_x]
              ([Name] [nvarchar](max) NULL,
               [Value] [nvarchar](max) NULL )";

    Session.CreateSQLQuery(query).SetTimeout(uploadExecQueryTimeout).ExecuteUpdate();

    query = @"INSERT INTO [#Dataset_x] ([Name],[Value]) VALUES('Dataset','MyDataset')";
    Session.CreateSQLQuery(query).SetTimeout(uploadExecQueryTimeout).ExecuteUpdate();
    transaction.Commit();
}

Can anyone suggest me why transactions are required to use temporary tables in nhibernate?

Note: The "current_session_context_class" value which I used in my config is call

hival hival · Accepted Answer · 2012-02-20T10:06:01

By default in NHibernate a database connection is opened and closed for each transaction. In the first code snippet each statement is executed in a separate implicit transaction so NHibernate closes connection after each statement. In the second code snippet connection is closed only when you end transaction so your temporary table is not dropped till the end of transaction. By the way use of implicit transactions in NH is discouraged.

Why does temporary tables not work in nhibernate?

1 Answers