Why is this class member leaving a SQL connection open?

Question

Further to my recent questions, I've now closed most of the connections that our web application was leaving open. However, the connection created by the function below remains open. Can anyone identify why it is not closing?

public DataTable GetSubDepartment(int deptId)
{   
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(Defaults.ConnStr))
    {
        SqlCommand cmd = new SqlCommand("proc_getDepartmentChild", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@dptParent", deptId));

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(dt);
    }
    return dt;
}

* EDIT * Following @HenkHolterman's comment: I'm using SQL Server Management Studio Activity log to view the open connections. This one is listed as sleeping. SO what you say makes sense. Is there any way I can tell that this is a pooled connection rather than an open one?

Are you sure? Both the using and Fill() should be closing it. You're probably looking at connections in the Pool, nothing to do with this code. — Henk Holterman
– Henk Holterman, Commented Jun 30, 2010 at 9:22
You can tell when filling the next Table does not add an extra connection. — Henk Holterman
– Henk Holterman, Commented Jun 30, 2010 at 9:32

Stefan Steinegger · Accepted Answer · 2010-06-30 09:20:45Z

2

Most probably because it went back to the connection pool.

Call

SqlConnection.ClearAllPools();

to clear the pool, then it should disappear. This could sometimes be useful, but is usually not needed.

answered Jun 30, 2010 at 9:20

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cyberzed · Accepted Answer · 2010-06-30 09:22:14Z

1

I would assume that it's hanging in the connectionpool

answered Jun 30, 2010 at 9:22

cyberzed

2,0761 gold badge16 silver badges26 bronze badges

Collectives™ on Stack Overflow

Why is this class member leaving a SQL connection open?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related