CODEWITHSUNDEEP
c#
Oliver
Oliver

Reputation: 11597

foreach with SqlDataReader?

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?

Upvotes: 14

Views: 54510

Answers (3)

Ciro Corvino
Ciro Corvino

Reputation: 2128

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"]);
          }
    }
}

Upvotes: 2

to StackOverflow
to StackOverflow

Reputation: 124696

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

Upvotes: 25

Adam Houldsworth
Adam Houldsworth

Reputation: 64467

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.

Upvotes: 17

Related Questions