CODEWITHSUNDEEP
c#asp.netsql-serverforeach
Omer
Omer

Reputation: 317

Iterating over rows in a DataTableReader with foreach

Can i use foreach (DataRow row in r)?

I have this code:

    conStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + Server.MapPath("~/App_Data/Database.mdf") + ";Integrated Security=True;User Instance=True";
    string sqlStr = "Select * FROM Msgs WHERE Reciver='"+Reciver+"'";
    SqlDataAdapter daObj = new SqlDataAdapter(sqlStr, conStr);
    DataSet dsObj = new DataSet();
    daObj.Fill(dsObj);
    DataTableReader r = dsObj.Tables[0].CreateDataReader();
    foreach (DataRow row in r)

i ran debug and it stopped on the line of the foreach and gave me this error:

Unable to cast object of type 'System.Data.Common.DataRecordInternal' to type 'System.Data.DataRow

How can I fix it?

Upvotes: 3

Views: 7470

Answers (4)

user3047111
user3047111

Reputation: 1

onStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + Server.MapPath("~/App_Data/Database.mdf") + ";Integrated Security=True;User Instance=True"; string sqlStr = "Select * FROM Msgs WHERE Reciver='"+Reciver+"'"; SqlDataAdapter daObj = new SqlDataAdapter(sqlStr, conStr); DataSet dsObj = new DataSet(); daObj.Fill(dsObj); 
// DataTableReader r = dsObj.Tables[0].CreateDataReader();//



 foreach (DataRow dr in dsObj.Tables[0].rows)
{
enter code here`
/.........
}

Upvotes: -2

Szymon
Szymon

Reputation: 43023

Instead of foreach you can use:

while (r.Read())
{
     // r[0], r[1], ... or r["Name"] will give you access to fields
}

See this example from MSDN.

Upvotes: 3

Evan L
Evan L

Reputation: 3855

The foreach is trying to cast a DataRow from the DataTableReader which it cannot.

A DataTableReader reads rows, but does not have rows. It can only read over the rows in a DataTable one by one. Rafa's answer on this post goes over the best use of DataTableReader.

If you must use a DataTableReader then just put it in a while loop:

while (r.Read())
{
    //Access r with r["columnName"]
}

If you want the foreach, just loop over the rows in the DataTable itself:

foreach (DataRow row in dsObj.Tables[0].Rows)
{
    //Do something with the rows
}

In terms of performance, I would think that the foreach would be better simply because it does not require you to instantiate another object (the DataTableReader), you also get write access to the rows if you want to modify their contents.

Upvotes: 7

Rafa Paez
Rafa Paez

Reputation: 4860

DataTableReader contains DataTable objects in the form of one or more read-only, forward-only result sets. You can use DataTableReader.NextResult to navigate through their records.

Example from the previous link documentation

using (DataTableReader reader = new DataTableReader(
           new DataTable[] { customerDataTable, productDataTable }))
{
    // Print the contents of each of the result sets. 
    do
    {
        PrintColumns(reader);
    } while (reader.NextResult());
}

Upvotes: 3

Related Questions