foreach with SqlDataReader?

Question

I have the following code:

SqlDataReader reader = getAddressQuery.sqlReader;
while (reader.Read())
{
    foreach (Object ob in reader)
    {
        someText.InnerText = someText.InnerText + " " + ob.ToString();
    }
}

The code in the foreach loop does not execute. However, I can do this:

SqlDataReader reader = getAddressQuery.sqlReader;
while (reader.Read())
{
    someText.InnerText = reader[0].ToString();
}

Which works.

Obviously I could achieve the same result using a regular for loop rather than a foreach loop, but I think the foreach syntax is clearer, so I use it when possible.

What has gone wrong here? Are foreach loops in c# not as flexible as in more high level languages?

I don't know for sure but I assume the foreach is iterating through each field in the recordset rather than each record... — Chris
– Chris, Commented Aug 18, 2011 at 11:06

to StackOverflow · Accepted Answer · 2016-04-13 20:32:56Z

25

Something like the following. Note that IDataReader derives from IDataRecord which exposes the members used to process the current row:

IEnumerable<IDataRecord> GetFromReader(IDataReader reader)
{
    while(reader.Read()) yield return reader;
}

foreach(IDataRecord record in GetFromReader(reader))
{
    ... process it ...
}

Or even something like the following, to get an enumeration or list of strongly-typed entity objects from a reader:

IEnumerable<T> GetFromReader<T>(IDataReader reader, Func<IDataRecord, T> processRecord)
{
    while(reader.Read()) yield return processRecord(reader);
}

MyType GetMyTypeFromRecord(IDataRecord record)
{
    MyType myType = new MyType();
    myType.SomeProperty = record[0];
    ...
    return myType;
}

IList<MyType> myResult = GetFromReader(reader, GetMyTypeFromRecord).ToList();

UPDATE in response to Caleb Bell's comment.

I agree Enumerate is a better name.

In fact in my personal "common" library, I've now replaced the above by an extension method on IDataReader:

public static IEnumerable<IDataRecord> Enumerate(this IDataReader reader)
{
    while (reader.Read())
    {
        yield return reader;
    }
}

And the caller can get strongly-typed objects using:

reader.Enumerate.Select(r => GetMyTypeFromRecord(r))

edited Apr 13, 2016 at 20:32

answered Aug 18, 2011 at 10:55

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caleb Bell Over a year ago

That GetFromReader<T> method is nice and elegant, and it's a shame that something like that isn't in the framework natively! I created an extension method version of it and called it Enumerate<T>.

to StackOverflow Over a year ago

@CalebBell - I agree Enumerate is a better name, see update.

Adam Houldsworth · Accepted Answer · 2011-08-18 11:19:04Z

17

The foreach exposes an IDataRecord, which puts you in a very similar boat to the while loop:

using (SqlConnection conn = new SqlConnection(""))
using (SqlCommand comm = new SqlCommand("select * from somewhere", conn))
{
    conn.Open();

    using (var r = comm.ExecuteReader())
    {
        foreach (DbDataRecord s in r)
        {
            string val = s.GetString(0);
        }
    }
}

If you want to see something more useful, you'll need to have some of your own code that extracts the values from the record into something more custom, as the other answer has suggested.

Either way you are going to need custom code, whether you have it inline or not or use a while loop or not depends on how often it's going to be written I suppose, any more than once and you should probably stick it in a helper method somewhere.

And to answer the somewhat question: the problem is not the foreach, it is your attempted usage of what it returns for you, as your comparable use of the while loop is not actually comparable.

edited Aug 18, 2011 at 11:19

answered Aug 18, 2011 at 10:56

Adam Houldsworth

64.8k11 gold badges161 silver badges190 bronze badges

8 Comments

Oliver Over a year ago

I see. That is a bit annoying.

to StackOverflow Over a year ago

@Marc "wrong - SqlDataReader does implement IEnumerable" - yes, but this enumerates the columns of the (current) IDataRecord, not the rows.

Adam Houldsworth Over a year ago

@Joe on the SqlDataReader it appears to be returning entire records.

Adam Houldsworth Over a year ago

Even though my answer pretty much says the same as the other one, I'll leave it in for my own benefit. I never once tried to use foreach with a reader, I always break into while (reader.Read()) code, call it muscle memory in my fingers. That said, the end result is very much the same so I don't feel the need to revisit old code and refactor to satiate OCD :-)

Marc Gravell Over a year ago

@BornToCode firstly that is only true of the abstract base class (DbDataReader), not the interface (IDataReader). Secondly it involves extra allocation for the iterator. Thirdly it is not obvious what it is enumerable of. Fourthly it allows less granularity over rows / grids and when you can / can't access metadata. Fifthly, it doesn't really help you any, as you end up accessing the data-record exactly the same as you would have the reader. Basically, Read() works fine. If you prefer foreach: that's OK too.

|

Ciro Corvino · Accepted Answer · 2016-06-30 12:09:52Z

2

you may do also that...

string sql = "select * from Users";
using (SqlConnection conn = GetConnection()){ 

    conn.Open();
    using (SqlDataReader rdr = new SqlCommand(sql, conn).ExecuteReader()){

           foreach (DbDataRecord c in rdr.Cast<DbDataRecord>()){
               Console.Write("{0} {1} ({2}) - ", (string)c["Name"], (string)c["Surname"], (string)c["Mail"]);
               Console.WriteLine((string)c["LoginID"]);
          }
    }
}

answered Jun 30, 2016 at 12:09

Ciro Corvino

2,1466 gold badges23 silver badges34 bronze badges

Collectives™ on Stack Overflow

foreach with SqlDataReader?

3 Answers 3

2 Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related