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?

Answer 1

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.

Answer 2

I would assume that it's hanging in the connectionpool

Answer 3

SqlCommand and SqlDataAdapter implement IDisposable. Use the using statement for these two objects too.

Another thought: Maybe your stored procedure needs an explicit “Close”. Close the connection object at the end of the using statement . This is recommended for all open connections. Maybe it’s necessary for your stored procedure.