LINQ - Clear Connection pool - Asked By Santosh Prajapati on 22-Jul-11 05:50 AM

Hi all,
How to clear connection pool in linq2sql. problem i am reset many times  sql server form live site.
how to manage this problem.
Please help me.
Riley K replied to Santosh Prajapati on 22-Jul-11 05:53 AM
By default, LINQ will properly clean up the resources, but there are cases where the connection can be left open, depending on the usage of your code (reusing the DataContext, creating a connection using an existing SqlConnection object, or using MARS).

http://msdn.microsoft.com/en-us/library/bb386929.aspx


Remember that the SqlConnection type in .NET is designed to be a very short-lived object due to its pooling behavior at the provider level and therefore should be opened just before you need it and closed just after you use it in the context of an ASP.NET web application.  

Do not try to cache a SqlConnection object in your ASP.NET code.  The guidance for LINQ is similar, don’t try to cache the DataContext, simply create a new DataContext using the same connection string (note:  using a string is not the same as using a SqlConnection object!)  When using LINQ to SQL, this means frequently creating and destroying the DataContext.  Do not try to reuse the DataContext in your code, simply recreate it each time you query the database.

Jitendra Faye replied to Santosh Prajapati on 22-Jul-11 05:55 AM

If you need to clear your connection pool in a .NET application, with the .NET 2.0 Whidbey framework, you will have the option of clearing the pool. 

ADO.NET 2.0 provides two static methods for doing this.

  • SqlConnection.ClearPool( SqlConnectionObject ).
  • SqlConnection.ClearAllPools().

According to what I have read, this feature will be available in the .NET 2.0 Sql Server and Oracle Clients from Microsoft.

pete rainbow replied to Santosh Prajapati on 22-Jul-11 06:13 AM
so as with all things that have unmanaged resources inside them ( which the db connection is )

then you need to control it's lifetime.

the easiest first step is to use the dispose pattern

using (MyDataContext context = new MyDataContext())
{
  ... do some Linq type thing
}
 
or perhaps..
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
  Using ( SqlCommand cmd = new SqlCommand("sproc_GetThingCount", cn) )
  {
      cmd.CommandType = CommandType.StoredProcedure;
      cn.Open();
      return (int)cmd.ExecuteScalar();
   }
}

however with LINQ you can have deferred loading going on which might cause you issues

so you might want to solve that by

using (MyDataContext context = new MyDataContext())
{
   context.DeferredLoadingEnabled = false;
   ...
}

then of course you could hang on to one context and share in someway...