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

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

You instantiate a new database context everytime you want to do something, and make sure it is minimized to the scope of that action. The connection pooling is abstracted away and managed by EF else where — Callum Linington, Jul 01 '16 at 14:06

score 3 · Accepted Answer · edited May 23 '17 at 11:48

3

Like someone else mentioned. Connection pooling is your friend.

The opening and closing is perfectly fine.

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

edited May 23 '17 at 11:48

Community

1
1

answered Jul 01 '16 at 14:06

Joakim Hansson

544
3
15

How can I turn on connection pooling in ef-core? – Roman Kolesnikov Jul 01 '16 at 14:09
@Rem. If you already use the 'using' statement it will dispose everything for you and you don't have to worry about anything! :) – Joakim Hansson Jul 01 '16 at 14:14
I thought 'using' deals with disposing of connection. And pooling is about reusing them – Roman Kolesnikov Jul 01 '16 at 14:15
@Rem https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx Have a look at that article and spefically under "Adding connections". You will find the statement: "Connections are released back into the pool when they are closed or disposed". I personally use the 'using' statement as it's easier to forget .Open and .Close – Joakim Hansson Jul 01 '16 at 14:19
So connection pooling is always on? PS: I do not use connections per se, I use LINQ expressions and LINQ deals with connections – Roman Kolesnikov Jul 01 '16 at 14:30
1

@Rem It depends on the provider. npgsql has pooling on by default. http://www.npgsql.org/doc/connection-string-parameters.html. Check the parameter "pooling" :) If you would like to turn this off. You would set that in your connection string. – Joakim Hansson Jul 01 '16 at 14:32

score 1 · Answer 2 · edited May 23 '17 at 12:16

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();
 }

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

2 Answers2