Hi,

I am working on a NHibernate project and I have used the NHibernate Best Practices found at Codeproject.com http://www.codeproject.com/aspnet/NHibernateBestPractices.asp
A good article for NHibernate Newbies like myself.

My problem is when my application loose the database connection, like when the MS SQL 2005 server is restarted or the network connection to the server is lost.

In NHibernate session object it seems like the database is still running. The IsConnected property is true, the IsOpen is true.

I try to open and close my session between every call to the database. I use the NHibernateSessionManager class found in the Basic sample of the code found at the codeproject site.

I have searched for a solution to my problem, but not found any good example on the net.

Is there anyone that know of any good solution to this problem?

I still feel like a newbie to NHibernate, so if I have missunderstood something here, please tell me;)

Regards
Svein Thomas

As a quick fix, you could try in your connection string. A better way is to catch the exception from the SQL Server, call

and retry.

HTH, Tom

Thanks, but how and where is the best place to catch the SQL Server exception. Please take a look at NHibernate Best Practices Basic Example found at Codeproject.com.

I am building a service and its important that the service don't go down when the database connection is lost. I have to implement some kind of retry with a delay between.

The NHibernateSessionManager seems to do OpenSession when a session is not opened, this means that the session is opened each time i try to query the database or begins a transaction. I Close the session when I am finished with a query or a transaction. So what will happen if I loose my connection in the middle of my transaction?

What would you recommend that I do in this case?
Where would you put the retry code, and how can I watch the dbConnection State?

Regards
Svein Thomas

Hey Svein Thomas

I am working on the exactly same problem right now.

We use a Windows Service to send mails with regular intervals controlled by a timer. The program run by the Service is written in C# and we use NHibernate. The database is placed on another server. The database server is restarted two times a week because of maintenance (updates).
I have written exception handling so that the timer does not stop but I have not found out how to get NHibernate to reopen the connection.
I think that this basically is the same type of problem as you are describing.

So this is why I am asking if you found a solution to your problem.

Regards jorgen

No, i did not! :(

Hello Svein

I found a solution after reading this link:

http://forum.hibernate.org/viewtopic.ph ... connection

As you can see by reading this link Session.IsConnected will not tell you if there is an active connection to the database. But you can force it to do it in this way:

When you try to fetch data from the database with NHibernate then do it with a try-catch.
If you catch an exception use Session.Disconnect() to be sure that the session is disconnected.
Set a DateTime variable with DateTime.Now to mark the time.
Every time your service/timer runs use a TimeSpan variable where you calculate the difference between DateTime.Now and the variable set when the Exception occurred and wait to run your query until the delay you want has passed.
When you try to query again use Session.Reconnect() to open the connection again.

I have a DataAccess class where I have this property:

private ISession _session;
private ISession Session
{
set
{
_session = value;
}
get
{
if (!_session.IsConnected)
_session.Reconnect();
return _session;
}

}

In all my methods for fetching and saving data I call this property. Ex.:

public IList GetListByPropertyValue(Type type, string propertyName, object propertyValue)
{
try
{
ICriteria crit=Session.CreateCriteria(type);

crit.Add(Expression.Eq(propertyName, propertyValue));

return crit.List();
}
catch (Exception ex)
{
throw ex;
}
}

I use NHibenate in a web context and in my HHTPModule I have this method:

public static void DisconnectCurrentSession()
{
ISession _session = CurrentSession;

if (_session != null)
{
try
{
_session.Disconnect();
}
catch (Exception ex)
{
string _errmsg = ex.Message;
}

}
}

I hope this will help you.

jorgen