How can I open database connection at runtime?

Question

I am working on ASP.Net MVC project. I am making my database transactions with;

using (ISession session = FluentNHibernateHelper.OpenSession())
        {
            var user = session.Query<User>()
                .FirstOrDefault(x => x.UserEmail == email && x.UserPassword == password); }

Instead of using this type of code block which is like open-close connection everytime, I want to open connection at runtime and I want to use that session variable everywhere. Maybe some codes in Application_Start()in Global.asax.cs?

I am open to your valuable ideas. Thank you for your help!

Don't. You want to use open/close connections everytime. Leaving a connection open for longer than your current operation can be bad, very very bad. There is a very good reason why best practices for database always show a using when dealing with connections or ORM connections. — Mark Fitzpatrick, Dec 14 '18 at 20:05

score 1 · Answer 1 · answered Dec 14 '18 at 20:36

It's poor practice to leave a connection open nor maintain the state in the ORM across multiple transactions as state issues can crop up rather quickly as you make multiple requests against the same object & connection.

However, if you must, you could inject it as a Singleton service which would live longer than a single request. This is problematic for scaling and not recommended.

services.AddSingleton<ISession>(provider =>
{
    return FluentNHibernateHelper.OpenSession()
});

More information: What is the difference between services.AddTransient, service.AddScoped and service.AddSingleton methods in ASP.NET Core?

How can I open database connection at runtime?

1 Answers1