使用 NHibernate 記得要把 Section Close

最近所維護系統的使用者有越來越多，DataBase 的負擔也越來越重，也因此程式從 SQL Server 取得資料常有 Timeout 的情形，檢查 SQL Server 的 Connection List，赫然發現一支使用 NHibernate 的 UI，程式一啟動就建立了 7~8 個 Connection，狀態都是 Sleep，一直到程式關閉，Connection 才消失，就算使用 SqlConnection.ClearAllPool()，也無法強制將這些多餘的 Connection 回收，查了一下文件 NHibernate 使用 ADO.NET 來對資料庫做存取的動作，所以沒有理由下了 SqlConnection.ClearAllPool()，Connection 卻沒回收，檢查程式後發現，原來是因為 Section 未關閉，以致於 Connection 使用完卻無法有效回收

NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
cfg.SetProperty("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
cfg.SetProperty("hibernate.connection.driver_class", "NHibernate.Driver.SqlClientDriver");
cfg.SetProperty("hibernate.connection.connection_string", "Server=myserver;initial catalog=ABC;User Id=sa;Password=");
cfg.SetProperty("hibernate.dialect", "NHibernate.Dialect.MsSql2005Dialect,NHibernate");
cfg.SetProperty("hibernate.use_outer_join", "true");
cfg.SetProperty("hibernate.show_sql", "false");

NHibernate.ISessionFactory sessionFactory = cfg.BuildSessionFactory();
using (NHibernate.ISession sess1 = sessionFactory.OpenSession())
{
  ...
}

NHibernate.ISession sess2 = sessionFactory.OpenSession();
try
{
  ...
}
catch (Exception ex)
{

}
finally
{
    sess2.Close();
}

關於 Session Close
A call to ISession.Close() marks the end of a session. The main implication of Close() is that the ADO.NET connection will be relinquished by the session.

關於 Session Flush
Flushing synchronizes the persistent store with in-memory changes but not vice-versa. Note that for all NHibernate ADO.NET connections/transactions, the transaction isolation level for that connection applies to all operations executed by NHibernate.

From time to time the ISession synchronizes its persistent state with the database. Performance will be affected if this process occurs too often. You may sometimes minimize unnecessary flushing by disabling automatic flushing or even by changing the order of queries and other operations within a particular transaction.

sess.Save(cat);
sess.Flush(); //force the SQL INSERT
sess.Refresh(cat); //re-read the state (after the trigger executes)


DomesticCat cat = (DomesticCat) sess.Load( typeof(Cat), 69L );
cat.Name = "PK"
sess.Flush();  // changes to cat are automatically detected and persisted

參考資料：http://www.hibernate.org/