2

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
  • 1,421,763
  • 867
  • 9,128
  • 9,194
sth
  • 146
  • 8

1 Answers1

6

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Thanks Jon. Yes this gets called from multiple users at a time. Will the above code prevent the `Timeout` exception? – sth Aug 04 '15 at 09:42
  • 1
    @sth: No, the code snippet I've provided is functionally equivalent, but simpler than your current code. Depending on how many concurrent users you have and how long the stored procedure takes to run, you may just need to increase the size of the connection pool - but we don't know enough context to say. – Jon Skeet Aug 04 '15 at 09:43
  • I hope there will not be any side effects of increasing the connection pool size. – sth Aug 04 '15 at 10:17
  • @sth: Well your database will have more to do - but basically we don't know whether that's an issue or not, as we don't have the context. – Jon Skeet Aug 04 '15 at 10:18