Fluent NHibernate sessions closes the database connection

Question

I'm using Fluent NHibernate to write to an Oracle 11g database. I'm not sure if it's a problem, but the NHibernate drivers have only configuration settings for 9 and 10g databases. Anyways, when I had instantiated only one NHibernate SessionFactory and only one NHibernate session (a used both a regular session and a IStatelessSession). Anytime I performed a read or write to the database, the Oracle sys.aud$ table would have a log of the transactions being performed. Our DBA said it was because the connection was logging in and then logging off after each read or write transaction. With a large amount of queries, we would end up killing the audit table. We're going to create a second database user with tweaked auditing for that account, but is it the default nature for NHibernate to close and open the connection for each transaction? Is there a way to prevent the connection from logging in and logging off?

Here's the SessionFactory configuration

public static ISessionFactory getSessionFactory() {
        var cfg = FluentNHibernate.Cfg.Db.OracleClientConfiguration.Oracle10;
        cfg.ConnectionString(c => {
            c.Instance(...);
            c.Username(...);
            c.Password(...);
            c.Server(...);
        });


        return Fluently.Configure().Database(cfg).Mappings(m => {
            m.FluentMappings.Add<DummyDataMap>();
        }).BuildSessionFactory();
    }

and here's the Main method for the tests I wrote

 static void Main(string[] args) {
        try {
            var sessionFactory = getSessionFactory();

            /*using (var statelessSession = sessionFactory.OpenStatelessSession()) {
                for (int i = 0; i < 1000; i++) {
                    var dd = new DummyData();
                    dd.FIRST_COLUMN = i;
                    using (var t = statelessSession.BeginTransaction()) {
                        statelessSession.Insert(dd);
                        t.Commit();
                    }
                }
            }*/
            /*using (var statefulSession = sessionFactory.OpenSession()) {
                for (int i = 0; i < 1000; i++) {
                    var dd = new DummyData();
                    dd.FIRST_COLUMN = i;
                    using (var t = statefulSession.BeginTransaction()) {
                        statefulSession.Save(dd);
                        t.Commit();
                    }
                }
            }*/

            using (var statefulSession = sessionFactory.OpenSession()) { 
                for (int i = 0; i < 1000; i++) {
                    statefulSession.Query<DummyData>().Where(dd => dd.FIRST_COLUMN == i).ForEach(dd =>
                        Console.Out.WriteLine(dd.FIRST_COLUMN));
                }
            }
        } catch (Exception ex) {
            Console.Out.WriteLine(ex.Message);
        }

Firo Firo · Accepted Answer · 2013-08-01T08:14:05

opening and closing the connection for each use case is the recommended way in ADO.NET

Using Connections

High performance applications keep connections to the data source in use for a minimal amount of time, as well as take advantage of performance enhancing technology such as connection pooling.

Double check that connectionpooling is enabled and supported by the ADO.Net driver you are using.

If you really need to have one global connection then implement IConnectionProvider which opens a connection on first CreateConnection and hands out the created each time, however you have to make sure that no 2 databaseoperations are performed at the same time because this is not supported by the connection.

Fluent NHibernate sessions closes the database connection

1 Answers