Saving the result of Select statement from SqlDataReader to list at once

Question

As the questions says, I'm trying to save the result of a select statement from a SqlDataReader to a list at once, rather than one by one giving the column name.

Before that I've tried using reader.read() and then giving the column name one by one and saving the data something like this,

while (reader.Read())
{
    label.text = (string) reader["myColumn"];
    //..... and so on repeating for all the columns in the DB.
}

Yet I've tried using IDataRecord to put all the row values in the list but it gives me NULL.

if (reader.HasRows)
{
    List<string> list = (from IDataRecord r in reader
                         select (string)r["FieldName"]).ToList();
}

But this returns a NULL. Anyone can redirect me to proper piece of information will highly be regarded.

score 2 · Accepted Answer · edited Jul 31 '15 at 06:35

2

SqlDataReader requires you to read and move the pointer one row by one row. If you want to get all the data at once, you could use SqlDataAdapter and DataTable.

edited Jul 31 '15 at 06:35

marc_s

732,580
175
1,330
1,459

answered Jul 31 '15 at 05:00

Alan

324
1
6

Thank you so much. Tried the Data Adapter and got what I wanted. – Waqar Jul 31 '15 at 05:44

score 0 · Answer 2 · answered Jul 31 '15 at 05:24

SqlConnection con = new SqlConnection("connection string");
            con.Open();
            SqlCommand cmd = new SqlCommand("select top 10 documentid from document order by 1 desc", con);
            SqlDataReader dr = cmd.ExecuteReader();             

            List<string> docids = (from IDataRecord r in dr select (string)r["documentid"]).ToList<string>();
            con.Close();

Saving the result of Select statement from SqlDataReader to list at once

2 Answers2