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
Accepted Answer
Like someone else mentioned. Connection pooling is your friend.
The opening and closing is perfectly fine.
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();
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow