[Question] Does NHibernate offer multi-connection strings?

[Bogatinov]

Dear NHibernate devs,
I'm working on a test project where I wish to reuse the SessionFactory for multiple databases, which can exist on different machines.

Motivation came from Nerdhold blog post
http://www.nerdhold.com/coder/2014/11/21/shared-session-factories-in-nhibernate/

where you can extend the DriverConnectionProvider

    internal class MyCustomConnectionProvider : DriverConnectionProvider
    {
        protected override string ConnectionString
        {
            get => Manager.Instance.GetConfiguration().DatabaseConnectionString;
        }

        public override DbConnection GetConnection()
        {
            try
            {
                return base.GetConnection();
            }
            catch (Exception ex)
            {
                var id = Manager.Instance.GetConfiguration().Name;
                throw new ArgumentException($"Can't connect to database {id}");
            }
        }

        public override async Task<DbConnection> GetConnectionAsync(CancellationToken cancellationToken)
        {
            try
            {
                return await base.GetConnectionAsync(cancellationToken);
            }
            catch (Exception ex)
            {
                var id = Manager.Instance.GetConfiguration().Name;
                throw new ArgumentException($"Can't connect to database {id}");
            }
        }
    }

Challenges

• With custom provider, I still need to pass default connection string for Configuration.BuildSessionFactory()

db.ConnectionString = settings.DefaultSqlConnection;

• Manager.Instance is not a thread-safe code, but the 4th solution from Jon Skeet is
• ConnectionString is called for each connection, so the Manager object needs to cache the Connection for some time so that GetConfiguration() is not heavy on NHibernate.

notes: Manager is loading configuration from a file right now, but I'm thinking to invoke WebAPI in the future to let users edit the configuration from anywhere.

Questions

1. Is connecting to master sufficient for a "default" string? Or is there an option for NHibernate to connect the first time via the MyCustomConnectionProvider first connection?

Server=.\SQLEXPRESS;Database=master;Trusted_Connection=Yes;

2. Are there official documentation to read more on the patterns of extending DriverConnectionProvider? I'm curious whether I extend GetConnectionAsync() properly.
3. Is the caching approach necessary? or is there a setting in NHibernate to reuse the same connection for a limited time?

[fredericDelaporte]

Having just read the start of the blog you have linked, I think that this blog is unsuitable for most cases. Switching to connection release mode "on close" is bad for connections reuse and will increase the number of connection simultaneously opened on your database, which could cause serious issues.

Your question is unclear, but it seems to me you search a solution for shardings with NHibernate without creating a session factory for each shard. Maybe Nhibernate.Shards does that, I do not know. @ggeurts, you seem to be contributor of it, may you know?

[Bogatinov]

Hi @fredericDelaporte, I do agree that the blog post is not the most suitable, seeing that it's written in 2014.

I used it as a motivation on how to extend over DriverConnectionProvider, since it was the only resource I can locate on how to use Multiple connection strings via NHibernate on runtime of the application. Maybe I went in the wrong direction related to the motivation I'm trying to achieve.

Your question is unclear

Let me clarify, if I may. The main motivation is to Allow users to connect to multiple databases, hosted on different machines, while in runtime.

To achieve this, I found the original blog post and the AspNet web blog to achieve Switching between Connection strings while in runtime.

For example: Something similar to multitenancy 😃

1. User A has database connection string Server=192.168.1.101\SQLSERVER;Databasse=RuntimeDatabaseOne
2. User B has a database connection string Server=192.168.1.125\SQLSERVER;Databasse=RuntimeDatabaseTwo
3. My app lets user A to connect to RuntimeDatabaseOne when user A logs in to the app in runtime

will increase the number of connection simultaneously opened on your database, which could cause serious issues.

On the topic of opening many connections simultaneously, will this happen with my custom class MyCustomConnectionProvider?

I can further profile this behaviour, but can you share with me some resources I can learn?

I'm not entirely sure with what you meant on why extending DriverConnectionProvider is open to serious issues, and I'm open to learn more.

Current documentations read:

• NHibernate Cookbook 4
• NHibernate blog
• NHibernate source code - however, I don't see it fully how it connects to other parts of NHibernate
• AspNet blog from Ricardo Peres

[Bogatinov]

mode "on close" is bad for connections

I've read the NHibernate docs which states on_close - says to use ConnectionReleaseMode.OnClose. This setting is left for backwards compatibility, but its use is highly discouraged.

I'm using the auto which is equal to after_transaction. So I think I will be okay with MyCustomConnectionProvider?

I will experiment in a few days with a profiler and get back with the results 😃

[gliljas]

If the idea is to represent the same schema, for different setups, you can use the built in MultiTenancy support. It can be used for purposes which has nothing to do with multitenancy.

[Bogatinov]

Interesting concept, didn't know it existed.

I can experiment further with it. Would you say that the #2108 is better to understand the plumbing, or should I see how TenantConfiguration is used in tests.

To summarize, feel free to correct me if I'm wrong

1. for Tenants using many databases in 1 server = prefer Sharding
2. for Tenants using different setups (such as servers on many machines) = prefer MultiTenancy support

[gliljas]

1. for Tenants using many databases in 1 server = prefer Sharding
2. for Tenants using different setups (such as servers on many machines) = prefer MultiTenancy support

If I understand you correctly, yes that's about it.

[ggeurts]

Hello, NHibernate.Shards reuses configuration but keeps a session factory per shard. That was sufficient for my purpose at the time. Kind Regards, Gerke.

…

On Mon, 23 May 2022, 21:06 Frédéric Delaporte, ***@***.***> wrote: Having just read the start of the blog you have linked, I think that this blog is unsuitable for most cases. Switching to connection release mode "on close" is bad for connections reuse and will increase the number of connection simultaneously opened on your database, which could cause serious issues. Your question is unclear, but it seems to me you seek a solution for shardings with NHibernate without creating a session factory for each shard. Maybe Nhibernate.Shards <https://www.nuget.org/packages/NHibernate.Shards> does that, I do not know. @ggeurts <https://github.com/ggeurts>, you seem to be contributor of it, may you know? — Reply to this email directly, view it on GitHub <#3068 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAFHG7MQJQFQQDCZSICWDATVLPJM7ANCNFSM5WWZJC7A> . You are receiving this because you were mentioned.Message ID: ***@***.*** com>