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?

Answer 1

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