Sql server ExecuteReader requires an open and available Connection. The connection’s current state is closed

sqlsql servertimeout

I am using a multithreaded application to extract data from a database and after a few minutes of running I start to get this exception or an sql timeout exception. My code is below:

    public static List<FlightInfo> FlightInfoAllPricesPerDayForRoute(string From, string To)
    { // Create the object array from the datareader
        List<FlightInfo> arr = new List<FlightInfo>();
        using (var conn = new SqlConnection(sConnectionString))
        {
            using (var command = new SqlCommand("FlightInfoAllPricesPerDayForRoute", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open(); command.CommandTimeout = 150;
                command.Parameters.AddWithValue("AirportFrom", From);
                command.Parameters.AddWithValue("AirportTo", To);
                // command.Parameters.AddWithValue("Date",  dt);


                //execute the stored procedure
                using (IDataReader dr = command.ExecuteReader())
                {


                    FlightInfo obj;

                    while (dr.Read())
                    {
                        obj = new FlightInfo();
                        obj.flightInfoID = (int)dr["FlightInfoID"];
                        obj.airportFrom = (string)dr["AirportFrom"];
                        obj.airportTo = (string)dr["AirportTo"];
                        obj.timeDeparture = DateTime.Parse(dr["TimeDeparture"].ToString());
                        obj.timeArrival = (DateTime)dr["TimeArrival"];
                        obj.price = (short)Int16.Parse(dr["Price"].ToString());
                        obj.dateAdded = (DateTime)dr["DateAdded"];
                        obj.carrier = (string)dr["Carrier"];

                        arr.Add(obj);
                    }

                    dr.Close(); dr.Dispose(); conn.Close(); conn.Dispose(); command.Dispose();


                }
            }
        } return arr;
    }

I have added teh following to the connection string to improve the timeout problem but am still getting errors:

   Min Pool Size=200;Max Pool Size=32767;Asynchronous Processing=true;   Connect Timeout=150;

The stored procedure is very simple so I don't think there is a problem there:

select *,  DATEADD(dd, 0, DATEDIFF(dd, 0, TimeDeparture)) as FlightDate
from FlightInfo  
where AirportFrom = @AirportFrom and AirportTo = @AirportTo
AND TimeDeparture > GETDATE() -- Only get future flights
Order by FlightDate ASC, Price asc

I've also tried running this to check the sql connections but I get a 'The user does not have permission to perform this action.' error

  SELECT * FROM sys.dm_exec_connections

Any suggestions would be appreciated!

Best Answer

Your code looks ok - I suggest you look on the database server for your problem. If the stored proc takes a long time to run and/or causes a lot of locking in the tables, you could get these problems (especially if it is called frequently). Some query optimization is probably in order.

fyi - you don't need a 'using' block around a SqlCommand object. Also, there is no need to call 'close' and 'dispose' on your reader and connection objects - the 'using' will handle that for you.

Related Topic