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

Question

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 connection 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.

Even after using using (SqlConnection conn = new SqlConnection(builder.ConnectionString)){ .......... } the connection is not being closed in the server. — Nagesh
– Nagesh, Commented May 19, 2011 at 5:44

Alireza Maddah · Accepted Answer · 2011-05-19 05:34:11Z

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.

nathan gonzalez · Accepted Answer · 2011-05-19 04:50:12Z

0

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.

answered May 19, 2011 at 4:50

nathan gonzalez

12k4 gold badges43 silver badges57 bronze badges

Comments

Amit · Accepted Answer · 2011-05-19 04:50:49Z

0

Try this

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

answered May 19, 2011 at 4:50

Amit

22.2k28 gold badges76 silver badges94 bronze badges

Comments

Pankaj · Accepted Answer · 2011-05-19 05:06:47Z

0

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.

edited May 19, 2011 at 5:06

answered May 19, 2011 at 4:53

Pankaj

10.2k40 gold badges152 silver badges298 bronze badges

Comments

Vadim · Accepted Answer · 2011-05-19 19:47:20Z

0

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

edited May 19, 2011 at 19:47

Vadim

18k5 gold badges41 silver badges62 bronze badges

answered May 19, 2011 at 4:53

prasad

1

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related