Rem Rem - 1 month ago 15
C# Question

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

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

Answer

Like someone else mentioned. Connection pooling is your friend.

The opening and closing is perfectly fine.

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