Why are temporary tables not working in nhibernate?

Question

I am trying to use temporary tables with nhibernate.

The following code snippet does not 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 an invalid object error when I try to run the second query (because the temporary table that was created expired before I called the second query).

However, if I add the above code snippet to the transaction, as shown below, it works 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 someone suggest me why transactions are needed to use temporary tables in nhibernate?

Note. The current_session_context_class value that I used in my configuration causes a call

Answer 1

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 the connection after each statement. In the second connection, the code fragment is closed only at the end of the transaction, so the temporary table is not discarded until the end of the transaction. By the way, the use of implicit transactions in NH discouraged .