Database Connection not releasing following .Close

  • Thread starter Thread starter arby
  • Start date Start date
A

arby

Hi There.

I have a complicated application that I'm seeing issues with where my
various connections to my sql server are not getting released. Rather
then delve into that, I created the following very simple asp.net
application that executes the following code:

private void Button1_Click(object sender, System.EventArgs e)
{
// Build connection
SqlConnection connection = new SqlConnection();
string sConnection = "data source=localhost;" +
"initial catalog=northwind;" +
"User ID=webuser;" +
"Password=;";
connection.ConnectionString = sConnection;
connection.Open();

// Build Query
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = connection;
objCmd.CommandText = "select * from categories";

try
{
objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string test = ex.Message;
}
finally
{
connection.Close();
connection = null;
objCmd = null;
}

}

Upon execution (in debug mode) of the above .aspx page I am able to
see the connection still alive via a sp_who query within my sql
database. In addition, when I stop debugging the connection still
remains.

From everywhere I've read (including MS), the above code is
appropriate. However, the fact that the connection remains is very
concerning. However, each time I restart my application it appears
that no new connections are created, almost as though it's reusing my
old connection. Therefore, I'm wondering if this is the typical
behavior as without knowing any better I would expect the connection
to be destroyed/recreated on each application instance.

Thanks in advance,
Jason
 
Hi,

the connection is maintained in a pool so that subsequent requests to use it
may get served from the pool rather than creating it altogether. Best
practises for using ADO .NET objects says that you should release / destroy
objects after you are finished with it to let the CLR garbage collect it.

Regards
Joyjit
 
Back
Top