Oi Ricardo, tudo bem... Yes, I also tried using the session.CreateSQLQuery(string) method and I preffer it because it is part of NHibernate but I got the same problem.
I was thinking about the CommandBehaviour but I did not specify it in any part of the code. I have a similar method that returns a generic collection (List<object[]>) and in this method I use the command.ExecuteReader() to take the reader and create a list. I did not specify the CommandBehaviour. I tried make the Command.Behaviour.Default explicity but I got the same error. I tried to call the session.Reconnect() but I got another exception! I was looking inside the NHibernate objects, I can see that the ISession is not closed but the Connection (SqlConnection) is closed and empty. Look the image: Any sugestion what could we try? We would like to keep it inside the same transaction of NHibernate because if the customer provide a code with a possible errors, the transaction will rollback everything. Obrigado Ricardo :) On Thu, Jun 11, 2015 at 7:11 AM, Ricardo Peres <[email protected]> wrote: > Oi, Felipe, > > ManagedWebContext was deprecated and removed in NHibernate 4, so you > should be using WebContext instead. > So, you are "playing" with the connection directly... may I ask why don't > you use the CreateSQLQuery method instead? > It is not visible in your code sample, but there's an overload to the > ExecuteReader method of DbCommand that takes a CommandBehavior, and one of > its possible values is CloseConnection. Are you sure it isn't being used? > > RP > > > On Wednesday, June 10, 2015 at 10:06:36 PM UTC+1, Felipe Oriani wrote: >> >> Hi Ricardo, I will give you more detail about my problem. >> >> In the Configuration to generate ISessionFactory (with FluentNH) we set >> the CurrentSessionContextClass to managed_web. We get the session using the >> sessionFactory.GetCurrentSession(). The session we bind using the >> ManagedWebSessionContext class, for sample, in the begin_request: >> >> ManagedWebSessionContext.Bind(httpContext, SessionFactory.OpenSession()); >> >> It is the classical session per request (with httpContext). >> >> About the exception, this is a pluggable part of the project, and we are >> using the codeCom to do it. We want to expose a method to the customer's >> code execute sql queries in the same database of the project and get >> results. To do this, we pass the sessionFactory to the method in the >> CodeDom and take the session with GetCurrentSession method to execute >> user's sql statements. To execute the query, we try this (in the codeDom): >> >> private object ExecuteScalar(string sql) >> { >> object result = null; >> >> var session = _sessionFactory.GetCurrentSession(); >> var connection = session.Connection; >> >> if (connection.State == System.Data.ConnectionState.Closed) >> connection.Open(); >> >> var command = connection.CreateCommand(); >> >> command.Connection = session.Connection; >> >> if (session.Transaction.IsActive) >> session.Transaction.Enlist(command); >> >> command.CommandText = sql; >> command.CommandType = System.Data.CommandType.Text; >> >> result = command.ExecuteScalar(); >> >> return result; >> } >> >> This code, executes a query fine, and the ISession is open, but we >> inspected the ISession instance and we realize that the ISession.Connection >> is closed and if we try to execute any other statement using NHibernate >> resources, we get the following exception: >> >> An exception of type 'System.InvalidOperationException' occurred in >> NHibernate.dll but was not handled in user code >> >> Additional information: ExecuteReader requires an open and available >> Connection. The connection's current state is closed. >> >> >> Thank you >> >> On Wed, Jun 10, 2015 at 5:32 PM, Ricardo Peres <[email protected]> wrote: >> >>> How are you obtaining the ISession in the first place, and how are you >>> binding it to the ISessionFactory? >>> >>> RP >>> >>> >>> On Wednesday, June 10, 2015 at 8:37:24 PM UTC+1, Felipe Oriani wrote: >>> >>>> Hi, >>>> >>>> I am working in a project, where the ISession is being binding in the >>>> ISessionFactory, so, I can access it using the >>>> sessionFactory.GetCurrentSession() method. In my scenario, the customer >>>> will config a sql query to be execute to get some value from the database. >>>> I use this value to persist another entity. >>>> >>>> var session = _sessionFactory.GetCurrentSession(); >>>> >>>> var value = _session.CreateSQLQuery(sqlCustomer).List(); >>>> /* query execute fine and I get the result to persist another object */ >>>> >>>> _session.Save(entity); >>>> >>>> The problem is when I try to Save another object, I get an exception >>>> saying that the ExecuteReader cannot be executed because the connection is >>>> closed. >>>> >>>> If I remove the call of CreateSQLQuery, it works fine. Does anybody >>>> knows, how can I fix it to keep the connection open? >>>> >>>> Thank you. >>>> >>>> >>>> -- >>>> ______________________________________ >>>> Felipe B Oriani >>>> [email protected] >>>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "nhusers" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/nhusers. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> >> >> -- >> ______________________________________ >> Felipe B Oriani >> [email protected] >> > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/nhusers. > For more options, visit https://groups.google.com/d/optout. > -- ______________________________________ Felipe B Oriani [email protected] -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
