Entity framework 4 not closing connection in sql s

问题:

I'm using entity framework 4 for the first time in an ASP.net application. I wanted to make sure that DB connections are closed after leaving the using statement, however, in the SQL Server 2005 profiler, I cannot see the connection logout when leaving the using, only the login when entering it,

For example, I started with a blank asp.net page, and in the Page_Load, I tried with this simple code (and absolutly nothing else in the page) :

using (var model = new DB("name=DB"))
{
  var livre = (from l in model.books
           select l).SingleOrDefault();
}

In the profiler, I see the Audit login, but not the logout after leaving the "using". So I tried closing the connection explicitly, using the following clode:

using (var model = new DB("name=DB"))
{  
  var livre = (from l in model.books
           select l).SingleOrDefault();
  model.Connection.Close();
}

Again, I see the login, but not the logout. The strange thing is that when I inspect the ConnectionState property in model.Connection after the close, it indicates 'Closed', but not in Sql Server.

To see the actual logout in SQL Server, I have to press the Stop debugging button in VS2010, and when I restart the Web Application, only then I see the logout (following by the actual login when my page gets processed).

I'm starting to wonder if there is something in SQL Server that might keep the connection opened even if it's closed in the code.

Any idea?

Thanks

回答1:

Yes, there is something in SQL Server that will keep the connection open: connection pooling. As opening and closing database connections is an expensive operation, SQL Server manages a pool of connections that it will assign to connection requests. Roughly speaking, if the request connection string matches the connection string of a inactive connection in the pool, SQL Server will assign the connection to the request.