SQL Server connection is not getting closed in ASP.net web application

I have written web application and while testing it I found that the connections established with the server are not being closed even though I close the conne开发者_StackOverflow中文版ction in the application. Even after the web page is closed, the connection remains as it is. Here is a sample code snippet which opens a connection and closes it:

protected void OpenConnection_Click(object sender, EventArgs e)
{
    SqlConnection conn = null; 
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "MyServerName";
    builder.InitialCatalog = "Northwnd";
    builder.IntegratedSecurity = true;
    builder.ApplicationName = "My Test ASP";

    try
    {
        conn = new SqlConnection(builder.ConnectionString);
        conn.Open();
        conn.Close();
    }
    catch (SqlException ex)
    {
        ex.Message.ToString();
    }

}

In the activity monitor the connection still remains. If I execute the same code in a normal Windows application the connection is getting closed properly.

Please help me how to resolve this issue.

---

You should use using for better managing resources. There is a big flaw in your code that if your code encounters an exception, the connection will not be closed and this will cause severe problems. Rewriting your code would result in:

protected void OpenConnection_Click(object sender, EventArgs e)
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "MyServerName";
    builder.InitialCatalog = "Northwnd";
    builder.IntegratedSecurity = true;
    builder.ApplicationName = "My Test ASP";

    using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
    {
    try
    {
        conn.Open();
        // Do Some stuff with SqlConnection
    }
    catch (SqlException ex)
    {
        ex.Message.ToString();
    }
  }
}

When the using block ends, it automatically calls the dispose method of the using variable which is SqlConnection here. Note that calling dispose on SqlConnection will also call its Close() method, you can investigate it in reflector.

---

try putting the connection in a using, something like this:

protected void OpenConnection_Click(object sender, EventArgs e)
{
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = "MyServerName";
    builder.InitialCatalog = "Northwnd";
    builder.IntegratedSecurity = true;
    builder.ApplicationName = "My Test ASP";

    using(var conn = new SqlConnection(builder.ConnectionString)) 
    {
        try
        {
            conn.Open();
        }
        catch (SqlException ex)
        {
            ex.Message.ToString();
        }
    }
}

the using automatically disposes the connection for you.

---

Try this

using (SqlConnection conn = new SqlConnection(builder.ConnectionString)
    {
        conn.Open();
        conn.Close();
        conn.Dispose();
    }

---

I think con.Dispose is missing .

Alternative

using statement for sql connection instance

using(sqlconnection con = new sqlconnection())
{
   Your logic
}

By doing this the connection will automatically get disposed.

---

Verify that no exception is being thrown, otherwise the conn.Close() call may never run.