CODEWITHSUNDEEP
c#sql-serverlinqentity-framework
Arcadian
Arcadian

Reputation: 4350

SQL Connection Remains Open when using DataContext

I have a simple test app that connects to a SQL DB and then execute a query in LINQ

When the command is executed I can see when the connection is opened.

But when I issue the

 db.Connection.Close();
 db.Dispose();

The connection in SQL is still there until I shut down the app.

I am the only user on the system.

Upvotes: 0

Views: 636

Answers (2)

Igor
Igor

Reputation: 15893

"The connection in SQL is still there" - you should give more information about this means. I am going to assume that you are monitoring connections from DBMS side.

With connection pooling enabled (which is default behaviour for SqlClient.SqlConnection), closing an SqlConnection instance does not immediately close underlying connection to the database. That connection remains in a "warm" state for about 8 minutes (if memory serves) and is ready to be used by another SqlConnection instance with exactly the same connection string.

With regard to you other question about whether db context should be disposed. If the context is explicitly disposed, you control the moment when the underlying (unmanaged) warm connection becomes usable by another instance of SqlConnection, which lets you keep the total number of simultaneously opened connections low.

Note: Line

db.Connection.Close();

before 

db.Dispose();

is absolutely unnecessary.

Upvotes: 2

Michael Di Felice
Michael Di Felice

Reputation: 406

Are you using connection pooling? This may return the connection back to the pool rather than closing it outright.

Here's a link describing pooling: https://msdn.microsoft.com/en-us/library/8xx3tyca%28v=vs.110%29.aspx

From MSDN (link above): "When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call."

Edit: as a test can you leave the connection idle for 4-8 minutes and see if it is closed?

Again from the same link: "The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed."

Upvotes: 4

Related Questions