Access multiple databases using fluent nhibernate in .netcore

Question

I'm new to nHibernate and trying to use it with .Net core for multiple sql server databases. I've gone through the docs and created Isessionfactoty and configured it in startup but when I try to access the session object in repository method, I'm getting error since it is only looking in one database i.e., DbConnectionString

my startup.cs looks like this

var sessionFactory = Fluently
                            .Configure()
                            .Database(() =>
                            {
                                return FluentNHibernate.Cfg.Db.MsSqlConfiguration
                                        .MsSql2008
                                        .ShowSql()
                                        .ConnectionString(Configuration.GetConnectionString("DbConnectionString"));
                            })
                            .Database(() =>
                            {
                                return FluentNHibernate.Cfg.Db.MsSqlConfiguration
                                        .MsSql2008
                                        .ShowSql()
                                        .ConnectionString(Configuration.GetConnectionString("AdminDbConnectionString"));
                            })
                            .BuildSessionFactory();

            services.AddSingleton<NHibernate.ISession>(factory => nHIbernateSession.OpenSession());

And this is my repository class which I'm calling from my controller

public class BusinessRepo
    {
        ISession _session;

        public BusinessRepo(ISession session)
        {
            _session = session;
        }

        //This method needs to use DbConnectionString
        public PersonGeo GetPersonById(string personId)
        {
            var value = _session.CreateCriteria<PersonGeo>()
                .Add(Restrictions.Eq("Person_ID", personId))
                .SetCacheable(true)
                .SetCacheMode(CacheMode.Normal)
                .List<PersonGeo>();
            return value.FirstOrDefault();
        }


        //This method needs to used AdminDbConnectionString
        public List<User> GetAllUsers()
        {
            //If I debug the _session and look for connection string it is taking "DbConnectionstring (which is configured first in startup)"
            var result = _session.CreateSQLQuery("SELECT UserID,UserName,Email,IsActive FROM Users").List<User>();
            return result();
        }
    }

And this is my repository class which I'm calling from my controller

  var person = _repo.GetPersonById(personId);
  var allUser = _repo.GetAllUsers();

And I'm getting object name not found "tableName" because the _session object is looking at the other database. Does anyone has idea on how to implement it?

ThejaSingireddy · Accepted Answer · 2019-08-02T18:04:34.177

Change your startup to

    var dbSessionFatory = Fluently
                        .Configure()
                        .Database(() =>
                        {
                            return FluentNHibernate.Cfg.Db.MsSqlConfiguration
                                    .MsSql2008
                                    .ShowSql()
                                    .ConnectionString(Configuration.GetConnectionString("DbConnectionString"));
                        })
                        .BuildSessionFactory();
    var adminDbSessionFactory= Fluently
                        .Configure()
                        .Database(() =>
                        {
                            return FluentNHibernate.Cfg.Db.MsSqlConfiguration
                                    .MsSql2008
                                    .ShowSql()
                                    .ConnectionString(Configuration.GetConnectionString("AdminDbConnectionString"));
                        })
                        .BuildSessionFactory();
       services.AddSingleton<NHibernate.ISession>(factory => dbSessionFatory.OpenSession());
       services.AddSingleton<NHibernate.ISession>(factory => adminDbSessionFactory.OpenSession());

then inject IEnumerable of Sessionfactory to repositoy method and use the particular sessionfactory for the database that you need.

public class BusinessRepo
    {
        private IEnumerable<ISession> _sessions;

        public BusinessRepo(IEnumerable<ISession> sessions)
        {
            _sessions = sessions;
        }

        //This method needs to use DbConnectionString
        public PersonGeo GetPersonById(string personId)
        {

            var _session = _sessions.Where(a => a.Connection.Database == "DbName").FirstOrDefault();
            var value = _session.CreateCriteria<PersonGeo>()
                .Add(Restrictions.Eq("Person_ID", personId))
                .SetCacheable(true)
                .SetCacheMode(CacheMode.Normal)
                .List<PersonGeo>();
            return value.FirstOrDefault();
        }


        //This method needs to used AdminDbConnectionString
        public List<User> GetAllUsers()
        {

            var _session = _sessions.Where(a => a.Connection.Database == "DbName").FirstOrDefault();
            //If I debug the _session and look for connection string it is taking "DbConnectionstring (which is configured first in startup)"
            var result = _session.CreateSQLQuery("SELECT UserID,UserName,Email,IsActive FROM Users").List<User>();
            return result();
        }
    }

You can still refactor it by creating another method that takes database name as parameter and returns the ISession object instead of doing it in every method

Having opened sessions configured as a singleton is not a good idea. You want to take advantage of connection pooling, opening sessions as late as possible, and closing them as soon as possible so the connection can be returned to the pool. https://nhibernate.info/blog/2011/03/02/effective-nhibernate-session-management-for-web-apps.html in this article it is explained how to manage named instances using a dependency injection container other than the built-in. You can plug in asp.net core more advanced dependency injection containers like autofact that allows you to use named instances. — Christian Quirós, Apr 23 '20 at 01:38

score -1 · Answer 2 · answered Aug 05 '19 at 13:40

I had similar problem a while ago and I created a class which is responsible for manage multiple SessionFactory

In Startup.cs

services.AddSingleton<ISessionSource, SessionSource>();

and class SessionSource

    public class SessionSource : ISessionSource
    {
        private static readonly object _lockObject = new object();
        private static Dictionary<string, ISessionFactory> _sessionFactories = new Dictionary<string, ISessionFactory>();

        public SessionSource()
        {}

        public ISession GetSession(int csName)
        {
            lock (_lockObject)
            {
                var session = GetSessionFactory(csName).OpenSession();
                return session;
            }
        }

        private FluentConfiguration BaseConfiguration()
        {
            return Fluently.Configure()
                    .Mappings(x => x.FluentMappings.AddFromAssemblyOf<MappingCandidate>())
                    .ExposeConfiguration(cfg =>
                    {
                        new SchemaExport(cfg)
                            .Execute(false, false, false);
                    });
        }

        private Configuration AssembleConfiguration(string connectionString)
        {
            return BaseConfiguration()
                    .Database(() =>
                    {
                        return FluentNHibernate.Cfg.Db.MsSqlConfiguration
                            .MsSql2012
                            .ConnectionString(connectionString);
                    })
                    .BuildConfiguration();
        }

        private ISessionFactory GetSessionFactory(int csName)
        {
            var connectionString = Configuration.GetConnectionString(csName);
            if (_sessionFactories.ContainsKey(connectionString))
            {
                return _sessionFactories[connectionString];
            }
            var sessionFactory = AssembleConfiguration(connectionString).BuildSessionFactory();
            _sessionFactories.Add(connectionString, sessionFactory);
            return sessionFactory;
        }
    }

and in e.g controller

using (var session = _sessionSource.GetSession(connectionStringName))

The idea is not bad. Just consider that you don't want to call BuildSessionFactory each time a connection is needed. BuildSessionFactory is an expensive task and must be invoked only once at start time. — Christian Quirós, Apr 23 '20 at 01:39

Access multiple databases using fluent nhibernate in .netcore

2 Answers2