Open/Close database in ASP.NET Core for every SQL command

asp.net-core c# entity-framework-core npgsql postgresql

Question

I write simple ASP.NET Core app where Controller injects MyService (configured as Scoped) that in turn injects MyDbContext.

In my controller's method I have 2 database queries and my debug output looks like this:

 Executing action method...
 Opening connection to database 'shell' on server 'tcp://127.0.0.1:5432'.
 ...
 Closing connection to database 'shell' on server 'tcp://127.0.0.1:5432'.
 Opening connection to database 'shell' on server 'tcp://127.0.0.1:5432'.
 ...
 Closing connection to database 'shell' on server 'tcp://127.0.0.1:5432'.
 Request finished in...

The question is: Is it correct to open a new connection on each request and even more - to open a new connection for each sql command? Can't it establish the connection to database once and reuse it. Wouldn't it be much better for performance?

PS: I use PostgreSQL with npgsql provider

1
1
7/1/2016 2:03:00 PM

Accepted Answer

Like someone else mentioned. Connection pooling is your friend.

The opening and closing is perfectly fine.

https://stackoverflow.com/a/4439434/3799142

3
5/23/2017 11:48:17 AM

Popular Answer

Always open and close the connection as already mentioned all over SO. Connection pooling deals with your performance issues.

Makes your code easier to read and you will never have to worry about, where is that connection i have to open, is it open? Isn't it closed somewhere else?

As you are asking, I assume you dont really want to. This is what I generally do. A small wrapper method which would fire your sql commands simmilar to this.

static bool FireCommand(SqlCommand command)
{
    command.Connection.Open();
    command.ExecuteQuery();
    command.Connection.Close();
 }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow