How to get a DataRow out the current row of a DataReader?

Question

Ok, I would like to extract a DataRow out a DataReader. I have been looking around for quite some time and it doesn't look like there is a simple way to do this.

I understand a DataReader is more a collection of rows but it only read one row at the time.

So my question: is there any way to extract a DataRow out the current row of a DataReader?

_"A DataReader is more a collection of rows"_ no, it's more like a view on a single record. A `DataReader` is a forward-only stream to records in the database. You can only look at the current record. — Tim Schmelter, Feb 20 '15 at 16:24

Tim Schmelter · Accepted Answer · 2014-11-07T06:35:34.030

Is there any way to extract a DataRow out the current row of a DataReader ?

No, at least no simple way. Every DataRow belongs to one Table. You cannot leave that property empty, you cannot even change the table(without using ImportRow).

But if you need DataRows, why don't you fill a DataTable in the first place?

DataTable table = new DataTable();
using(var con = new SqlConnection("...."))
using(var da = new SqlDataAdapter("SELECT ... WHERE...", con))
    da.Fill(table);
// now you have the row(s)

If you really need to get the row(s) from the DataReader you can use reader.GetSchemaTable to get all informations about the columns:

if (reader.HasRows)
{
    DataTable schemaTable = reader.GetSchemaTable();
    DataTable data = new DataTable();
    foreach (DataRow row in schemaTable.Rows)
    {
        string colName = row.Field<string>("ColumnName");
        Type t = row.Field<Type>("DataType");
        data.Columns.Add(colName, t);
    }
    while (reader.Read())
    {
        var newRow = data.Rows.Add();
        foreach (DataColumn col in data.Columns)
        {
            newRow[col.ColumnName] = reader[col.ColumnName];
        }
    }
}

But that is not really efficient.

I will only get one row at time. So this is why I tought a `DataReader` was better than filling a `DataTable` then merging it with other rows in another `DataTable` — Rémi, Aug 29 '13 at 13:06
Your edit bring in a nice point of view. How ever it doesn't really look better than filling a `DataTable`with a single row and than merging afterward. I will mark as answer event if it doesn't exactly answer my question. — Rémi, Aug 29 '13 at 13:16
@im_a_noob: note that i have tested the code now and edited the second piece. — Tim Schmelter, Aug 29 '13 at 13:22

score 3 · Answer 2 · answered Mar 30 '18 at 05:12

You're probably asking because you have DataReader code that looks something like this:

static void ViaDataReader(IDataReader rdr)
{
    while (rdr.Read())
        Console.WriteLine("{0,-27} {1,-46} {2,-25} {3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}

/// ...

ViaDataReader(DbProviderFactories.GetFactoryClasses().CreateDataReader());

But as Tim pointed out, if you know in advance that you're going to want the DataRow at each iteration, you should use DataTable instead, and then just iterate on its Rows property (following output is identical to above):

static void ViaDataTable(IDataReader rdr)
{
    var table = new DataTable();
    table.Load(rdr);
    foreach (DataRow row in table.Rows)
        Console.WriteLine("{0,-27} {1,-46} {2,-25} {3}", row[0], row[1], row[2], row[3]);
}

/// ...

ViaDataTable(DbProviderFactories.GetFactoryClasses().CreateDataReader());

If you absolutely must continue using the DataReader for some reason, I suppose you could add a loop index integer to that first example, and then grab each row from the (fully-pre-loaded DataTable) on each iteration. But since the DataTable is richer all-around, there's really no reason not to abandon the DataReader after doing the DataTable.Load().

I'm not so quick to suggest using DataTable. DataReader has a huge advantage over DataTable/DataRow, in that the latter hold the entire result in memory, while the former only holds one row. — Joel Coehoorn, Dec 10 '20 at 20:22

How to get a DataRow out the current row of a DataReader?

2 Answers2

Linked