Switch Connection in Interceptor

[coado]

I currently have 5 database instances running on Docker and I am attempting to load balance SQL requests by modifying the connection in the interceptor. However, simply replacing the connection string is not a viable solution. I assume that I need to open a new connection to a different database automatically after building the SQL query, but I am not sure how.

I have to use NHibernate for this task with interceptors. I'm curious how to implement this kind of load balancing with NHibernate.

public class Interceptor : EmptyInterceptor
{
    private readonly List<DbInstance> _dBsConnStr = Reader.DBsConnectionStrings;

    
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        
        using var session = NHibernate.Context.CurrentSessionContext.HasBind(NHibernateHelper.SessionFactory)
            ? NHibernateHelper.SessionFactory.GetCurrentSession()
            : NHibernateHelper.SessionFactory.OpenSession();
    
        session.Connection.ConnectionString = _dBsConnStr[1].ConnectionString;
        
        return sql;
        
    }
    
}