How can I populate a generic list of string with the results from a single-column DataTable?

Question

I see a lot of complex examples for converting a DataTable with multiple-member rows here but in my case, the query simply returns one column from a table, that is to say 0..N strings/varchars, such as:

[email protected]
[email protected]

I thought something like this should work:

DataTable UnitReportPairEmailValsDT = new DataTable();
string qry = string.Format(SQL.UnitReportPairEmailQuery, unit, rptId);
UnitReportPairEmailValsDT = SQL.ExecuteSQLReturnDataTable(
    qry,
    CommandType.Text,
    null
    );

List<String> emailAddresses = new List<string>();
foreach (string emailaddr in UnitReportPairEmailValsDT)
{
    emailAddresses.Add(emailaddr);
}

...but it won't compile ("foreach statement cannot operate on variables of type 'System.Data.DataTable' because 'System.Data.DataTable' does not contain a public definition for 'GetEnumerator'")

I tried appending ".AsEnumerable" to "in UnitReportPairEmailValsDT" too, but that also provoked the wrath of the compiler.

Answer 1

Error says you cannot loop through DataTable object itself, probably what you need is looping through DataRows.

Use this.

foreach(DataRow row in UnitReportPairEmailValsDT.Rows)
{
     emailAddresses.Add(row["emailaddr"].ToString()); // assuming you have emailaddr column.
}

Other option, use Linq

emailAddresses = UnitReportPairEmailValsDT
                         .AsEnumerable()
                         .Select(row=> row.Field<string>("emailaddr"))
                         .ToList();

Answer 2

try something like this:

List<String> emailAddresses = new List<string>();
foreach (DataRow row in UnitReportPairEmailValsDT.Rows)
{
    emailAddresses.Add(row.Item(0));
}

Answer 3

Suppose dt is your data table then using Linq: dt.Rows.Select(x=> x[0]+"").ToList() will give you List. Beware of using Select(x=>xToString()) as it is prone to error if column value is null. x[0]+"" makes sure that in case of null empty string is returned.