locked
close reader or connection in USING block RRS feed

  • Question

  • User-1189289792 posted

    Hi,

    We are getting below errors for some sql queries since last few days on server:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    We have already increased the timeout for command object to 3 mins but still getting these errors. In fact these queries were taking just few mili seconds in last month and all of a sudden we started getting these errors. We have used datareader in our project in below fashion:

           using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SelectEmployee", conn);
                cmd.Parameters.Add("EmployeeID", employeeID);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr != null && dr.HasRows)
                {
                    while (dr.Read())
                    {
                        employeeName = Convert.ToString(dr["EmployeeName"]);
                    }
                }
            }

    In our entire web application we are having database functions like above fashion. My question is do we need to close the connection or close the datareader or dispose command object inside "using" block ? We are getting many timeout errors or connection pool errors, please let me know if we need to make any changes in above code.

    Sunday, July 26, 2015 4:36 AM

Answers

  • User475983607 posted

    You need to explicitly close the reader.

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
    {
    	SqlCommand cmd = new SqlCommand("SelectEmployee", conn);
    	cmd.Parameters.Add("EmployeeID", employeeID);
    	cmd.CommandType = CommandType.StoredProcedure;
    	conn.Open();
    	SqlDataReader dr = cmd.ExecuteReader();
    	if (dr != null && dr.HasRows)
    	{
    		while (dr.Read())
    		{
    			employeeName = Convert.ToString(dr["EmployeeName"]);
    		}
    		dr.Close();
    	}
    }

    MSDN Docs

    https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 26, 2015 6:24 AM

All replies