Npgsql C# throwing Operation is not valid due to the current state of the object suddenly

Question

I have a C# winform application and everything was working fine, but after prolonged use of the app I started getting this error

Operation is not valid due to the current state of the object

The error is from a function that executes every 5 seconds to get a list of names from the database

NpgsqlCommand cmd = new NpgsqlCommand(
            String.Format("select pid,name from queue order by id"), conn);
NpgsqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    queue[(int)reader["pid"]] = (string)reader["name"];
}

This list contains names in a queue and needs to be updated in as short time as possible

From what I have read, it seems like a new limitation from .net framework..

Any better way to do this or a workaround to avoid this error?

Edit: and btw, I dont understand the limitation! I added a function that enters more than 100000 entries into the database and I didn't get this error!

Answer 1

Do you dispose reader and cmd after use? This could be memory leak-related where the postgres-provider ends up running out of an internal resource after some time.

You should follow a using-pattern like described on their homepage: http://www.npgsql.org/doc/

using (NpgsqlCommand cmd = new NpgsqlCommand(
        String.Format("select pid,name from queue order by id"), conn))
{
    using (NpgsqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            queue[(int)reader["pid"]] = (string)reader["name"];
        }
    }
}