Will return keyword inside using statement, leave the connection open?

We are getting Timeout expired exception on SqlConnection.Open().

Below is the code :

public int ExecuteNonQuery(SqlParameter[] param, string strSPName)
{
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
        int i = 0;
        using (SqlCommand cmd = new SqlCommand(strSPName, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            conn.Open();
            i = cmd.ExecuteNonQuery();
        }
        return i;
    }
}

Does the return keyword inside the using statement leaving the connection to opened and hence this issue?

Jon Skeet
people
quotationmark

Does the return keyword inside the using statement leaving the connection to opened and hence this issue?

No. The using statement is effectively a try/finally block statement a Dispose call in the finally part - so your connection will still be disposed at the end for the method.

I suspect that either you're just calling this from too many threads at the same time and exhausting your pool that way, or you're opening a connection elsewhere without closing it.

Note that you can make your code simpler by getting rid of the i local variable:

public int ExecuteNonQuery(SqlParameter[] param, string strSPName)
{
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
        using (SqlCommand cmd = new SqlCommand(strSPName, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}

Again, the command and connection will still be disposed appropriately.

people

See more on this question at Stackoverflow