Can I populate a list using C # DataAdapter

Question

Suppose I have this code (pseudo code)

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 populate a list (identity) based on the result of my DataAdapter? How am I supposed to do this? Or is there a workaround? Thanks...

Answer 1

Probably the best way is not to read datatable at 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"] } ); } 

Caution You want to quickly close the DataReader / connection, do not do much processing. The above code is more efficient than using a DataTable as an intermediary.

But if you want to use the data table first, you can use LINQ:

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

or just enter dt.Rows and create a new person and add him to the list

You should also use the Use () operators around your connection and reading .

Answer 2

First, you want your name and age fields not to be static (so each instance of the class will have its own values ​​for these properties.

Then you can do something like this:

 foreach(DataRow row in dt.Rows){ Person p = new Person(){ Name = Convert.ToString(row["name"]), Age = Convert.ToInt32(row["age"]) } person.Add(p); } 

Hope this helps!

Answer 3

There is also a Linq to DataSet that you could use to do something in that direction

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

Robert defeated me in response, just using a slightly different syntax.

Answer 4

In addition to the other options presented, you can delay execution until it is needed with the exit:

 public static IEnumerable<Person> GetPeople() { using( var conn = GetConnection() ) { conn.Open(); string sql = "SELECT name, age FROM person"; var cmd = new SqlCommand( sql, conn ); using( SqlDataReader rdr = cmd.ExecuteReader() ) { if( rdr == null ) { throw new NullReferenceException( "No People Available." ); } while( rdr.Read() ) { var person = new Person(); person.Name = rdr["name"].ToString(); person.Age = Convert.ToInt32 ( rdr["age"] ); yield return person; } } } } 

Answer 5

Yes, you can. Go through the elements in dt.Rows and convert them manually to Person objects.

Answer 6

A user recently asked me a question about converting a DataTable to List <> in .NET 2.0. Here is the code for this:

WITH#

 // Assuming there is a DataTable called dt List<DataRow> drlist = new List<DataRow>(); foreach (DataRow row in dt.Rows) { drlist.Add((DataRow)row); }