1

Does Linq to Sql Close connections automatically? or I should use using?

var db = new DataContext();
// Codes

or

using (var db = new DataContext())
{
    // Codes
}
Mohamad Shiralizadeh
  • 8,329
  • 6
  • 58
  • 93

2 Answers2

2

When you are using the using statement then the connection will be managed automatically.The DataContext class implements the IDisposable interface, so what you need to is to call Dispose on the DataContext implementation whenever you are finished with it.

From the C# In Depth

There are a few reasons we implemented IDisposable:

  • If application logic needs to hold onto an entity beyond when the DataContext is expected to be used or valid you can enforce that
    contract by calling Dispose. Deferred loaders in that entity will
    still be referencing the DataContext and will try to use it if any
    code attempts to navigate the deferred properties. These attempts
    will fail. Dispose also forces the DataContext to dump its cache of
    materialized entities so that a single cached entity will not
    accidentally keep alive all entities materialized through that
    DataContext, which would otherwise cause what appears to be a memory
    leak.
  • The logic that automatically closes the DataContext connection can be tricked into leaving the connection open. The DataContext relies on the application code enumerating all results of a query since getting to the end of a resultset triggers the connection to close. If the application uses IEnumerable's MoveNext method instead of a foreach statement in C# or VB, you can exit the enumeration prematurely. If your application experiences problems with connections not closing and you suspect the automatic closing behavior is not working you can use the Dispose pattern as a work around.
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • `.Dispose()` close connection? – Mohamad Shiralizadeh May 31 '15 at 11:59
  • @Mohamadshiralizadeh:- You can refer http://csharpindepth.com/ViewNote.aspx?NoteID=89 – Rahul Tripathi May 31 '15 at 12:00
  • `Close()` actually calls `Dispose()`, but the `using` statement automatically calls dispose on the resource, so just use `using` and don't be afraid of closing or disposing. – pid May 31 '15 at 12:00
  • @pid:- Yes thats correct when we are using the using statement then we dont have to worry about the closing the connection(*which I have updated in my answer also*) – Rahul Tripathi May 31 '15 at 12:02
  • @Mohamadshiralizadeh:- When we are using the using statment then the dispose method is called automatically. The point is when we are using the DataContext then it should always be "destroyed" or Disposed, so optimally you should use the Context object inside an using statement: – Rahul Tripathi May 31 '15 at 12:03
1

The using version is the best you can do. The underlying connection is not closed and this is a good thing. Connections are kept in a pool so that they may be recycled.

Don't confuse your code closing a connection with the actual underlying physical connection.

You must close the connection and the best way to handle this (best practice) is the using statement.

pid
  • 11,472
  • 6
  • 34
  • 63
  • you mean first version doesn't close connection? – Mohamad Shiralizadeh May 31 '15 at 11:58
  • No, the first version does not close the connection until the application context is disposed of (that is, when the application terminates). The using statement is a recommended best practice, there really is no argument against it. – pid May 31 '15 at 12:01
  • is .net source i see this in QqlProvider that Linq uses `... } finally { this.conManager.ReleaseConnection(this); } return result; }` What is it? it exactly says that linq to sql close connections after any execute.. – Mohamad Shiralizadeh May 31 '15 at 12:27