Can I Fill a List using a DataAdapter C#

Question

Suppose I have this code (pseudocode)

class SomeClass
{
   class Person
   {
      public static string Name { get; set; }
      public static int Age { get; set; }
   }

   List<Person> person = new List<person>;

   public void SelectPerson()
   {
      DataTable dt = new DataTable();

      SqlConnection conn = GetConnection();
      conn.Open();

      SqlDataAdapter da = new SqlDataAdapter("SELECT name, age FROM person", conn);
      da.Fill(dt);
   }
}

Can I fill the List (person) based on the result of my DataAdapter? How should I do it? Or is there any workaround? Thanks...

Answer 1

Probably the best way is not to read into a datatable first:

var dr = new DataReader(....) // Fill in what is needed, can't remember offhand
while(dr.Next())
{
    persons.Add(
        new Person() {
            Name = (string) r["Name"], 
            Age = (int) r["Age"] 
        }
    );
}

Caveat: You want to close the DataReader/connection quickly, don't do lots of processing. The above code is more efficient than using a DataTable as an intermediary.

But if you do want to use a data table first, you could use LINQ:

var list = dt.AsEnumerable().Select(r => new Person() { 
    Name = (string) r["Name"], 
    Age = (int) r["Age"] }
).ToList()

or just itterate of dt.Rows and create a new person and add it to the list

You should also use Using() statements around your connection and reader.

Answer 2

There's also Linq to DataSet that you could use to do something along these lines

var list = (from tr in dt.AsEnumerable()
    select new Person() { 
        Name = tr.Field<string>("Name"), 
        Age = tr.Field<int>("Age") 
    }).ToList();

Robert beat me to the answer, just using slightly different syntax.