DbDataReader closing by itself?

Question

I have the following code:

        var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
        DbConnection conn = entityConnection.StoreConnection;
        ConnectionState initialState = conn.State;

        List<Jobs> list = new List<Jobs>();

        int id = 0;
 try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();

            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                reader = cmd.ExecuteReader();

                int count = 0;

                while (reader.Read())
                {
                    id = int.Parse(reader[0].ToString());

                    list.Add(db.Jobs.Where(x => x.JobId == id).First());
                    //count++;
                    //if (count == 150) break;

                    reader.NextResult();
                }
            }
        }

and i get an error saying: "system.invalidoperationexception: invalid attempt to call NextResult when reader is closed"

but the thing is i havent closed the reader, however when i remove this line:

list.Add(db.Jobs.Where(x => x.JobId == id).First());

then all works fine! But this is useless to me as I need to populate a list object from the reader which if i can't do - then I can't read the table in! (p.s i am avoiding sqldatareader and using dbdatareader as it is associated with me using the entity framework to make the above call)

Answer 1

Have you tried removing this line

reader.NextResult();

the while (reader.Read()) line should be fine for reading the data.

That is how we do it using IDataReader.

Answer 2

You need to remove the call to NextResult. This method is used to advance to the next result set, not the next record. The reader.Read() in your while loop is enough

Answer 3

You're calling NextResult in the While Reader.Read loop - this is confusing. If the results returned by 'sql' are just one set of records, then NextResult will attempt to find a set batch of SQL to execute. If you just want to loop through a single set of records, you don't need anything more than the loop with Reader.Read at the top.

NextResult is only needed if your SQL contains multiple statements, like :

SELECT 1 AS ID;
SELECT 2 AS ID;
SELECT 3 AS ID

If it's just a basic SELECT, it's not going to work.