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?

Answer 1

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))