Get the content of a row from sqlDataReader as a list

Question

I have a SqlDataReader reader, where I don't know the data structure in advance, i.e. the number of columns is unkown. I'd like to put the table in csv file. I retrieve the columns from

System.Data.DataTable dt = reader.GetSchemaTable();

So I have them in dt.Columns() after it. Then, I want to do something like this:

while (reader.Read())
{
    writer.WriteLine(string.Join(";",ListWithRowContent))
}

However, I have it hard to put the content of a row into ListWithRowContent list with something like linq .Select , but "reader" object can't be queried.
Question: how to do it (please no for loop!)?

using it I can query a DataTable object (dt), but I need to query a SqlDataReader object (reader) or something inside it. — Max Li
– Max Li, Commented Aug 8, 2012 at 9:41
DataReader has nothing inside it. That's why it is used in a loop. — Nikhil Agrawal
– Nikhil Agrawal, Commented Aug 8, 2012 at 9:42

Dennis · Accepted Answer · 2012-08-08 09:44:10Z

6

Assuming, that reader is positioned on any record:

var values = new Object[reader.FieldCount];

reader.GetValues(values);

writer.WriteLine(string.Join(";", values.Select(v => v.ToString())));

or more convenient way (FW 4.0 or higher):

var values = new Object[reader.FieldCount];

reader.GetValues(values);

writer.WriteLine(string.Join(";", values));

answered Aug 8, 2012 at 9:44

Dennis

37.9k11 gold badges87 silver badges158 bronze badges

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

3 Comments

Max Li Over a year ago

thanks you! just few remarks, I think "var values = ..." should be called before the reader is positioned on any record, i.e. before "while(reader.Read())". Besides, I found out that string.Join requires an array and not a list (it's my fault), so it must be additionally .ToArray() at the end , i.e. "values.Select(v => v.ToString()).ToArray()"

Dennis Over a year ago

@MaxLi, if you're using FW 3.5 or lower, then, yes - .ToArray() is a requirement.

Max Li Over a year ago

Nice to know, I'm using FW 3.5. I thought that it's strange that it works only with arrays and not iterables

Collectives™ on Stack Overflow

Get the content of a row from sqlDataReader as a list

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related