SqlDataReader to read into List<string> SqlDataReader to read into List<string> sql sql

SqlDataReader to read into List<string>


You are trying to load a DataTable via DataTable.Load >in a loop<. You just need that once. You're also using reader.Read() in the loop. SqlDataReader.Read() advances the reader to the next record without to consume it. If you're going to use DataTable.Load you don't need to read the reader first. So you just have to remove the loop completely to load the table.

But since you want to return a list you don't need the DataTable at all, just loop the reader:

List<string> result = new List<string>();using (conn){    conn.Open();    using (SqlDataReader reader = command.ExecuteReader())    {        while(reader.Read())        {            result.Add(Convert.ToString(reader["Health Insurance NO"]));        }    }}

Apart from that, you are open for sql-injection without sql-parameters.


I would do it this way:

 conn.Open(); using (SqlDataReader reader = command.ExecuteReader()) {     dt.Load(reader);                   } foreach (var row in dt.AsEnumerable()) {     result.Add(row["Health Insurance NO"].ToString()); }