NHibernate and SQL Server user option NOCOUNT

[zdenotirc]

SQL Server has configuration called "default user options" and one of the config is NOCOUNT. If NOCOUNT is set ON, NHibernate doesn't work as this setting affects all users connections to all databases on single server instance.

It looks like 'SET NOCOUNT OFF' is required for NHibernate to work correctly, so I would expect some config for NHibernate Connection to make sure NHibernate works as expected.

user options
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-ver15

[fredericDelaporte]

This is a behavior specific to SQL-Server, and I do not see any suitable place in either the dialect or the driver for SQL Server to handle that. So the best option seems to me to handle it at user's side. You have a few ways for this.

• Derive from NHibernate.Connection.DriverConnectionProvider, override its DbConnection GetConnection(string connectionString) method for calling its base implementation then run set nocount off; on the connection it yields (which will be already opened). Set your new provider as the default connection provider with connection.provider setting. This solution will change the option at each connection opening, at the cost of an additional query sent to the database.
• Use an interceptor OnPrepareStatement method to prepend every statement with a set nocount off;. So every statement will set the option, but it will avoid an additional separated query for doing so.

Maybe there are other ways I am not thinking of yet, NHibernate has a lot of extension points allowing to handle many special needs.