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...

Pwninstein made an interesting point. Why are the name and age on person static? — Chad Ruppert, Feb 07 '11 at 04:37

score 11 · Accepted Answer · edited May 23 '17 at 12:25

11

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.

edited May 23 '17 at 12:25

Community

1
1

answered Feb 07 '11 at 03:59

Robert Wagner

17,515
9
56
72

though the linq version is not working... there is no `dt.Rows.Select` for the data table – yonan2236 Feb 07 '11 at 14:59
Sorry, use dt.AsEnumerable(), I was not specifically familiar with the dr.Row collection. Post is fixed – Robert Wagner Feb 09 '11 at 04:14
That works --> except for really handling nulls or you get Unable to cast object of type 'System.DBNull' to type 'System.String` but I realize that wasn't for you to handle errors for the answer :) – Tom Stickel Jul 31 '15 at 22:36
DataReader is not good solution for performance. Maybe some inheritance from DataTable/DataRow would be better – Hamid Nov 10 '15 at 07:13
@Hamid Can you clarify the concerns, what would be a more performant solution? IIRC most solutions to reading data using ADO.NET will use a data reader (including filling a datatable). The only thing I can think of is that you don't want to keep the datareader open too long, which is achieved by having a tight loop. In most cases DB read performance isn't a big problem. – Robert Wagner Nov 11 '15 at 11:21
@RobertWagner DataReader will takes long time when you want to using it in network system (i.e. Server and client connected via LAN ) and better solution is filling datatable by adapter or command and then creating child objects from datatable rows. – Hamid Nov 12 '15 at 15:05

score 3 · Answer 2 · answered Feb 07 '11 at 04:05

3

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.

answered Feb 07 '11 at 04:05

Roman

19,581
6
68
84

This is probably better, I'm not familiar with Linq to DataSets as I avoid them like the plague. – Robert Wagner Feb 07 '11 at 04:35
@Robert Wagner: As do I, but every now and then we have to work with legacy code. – Roman Feb 07 '11 at 14:10
So true, but thankfully I havn't had the intersection of C# 3.0 and datasets – Robert Wagner Feb 09 '11 at 04:15

score 3 · Answer 3 · answered Feb 07 '11 at 04:22

In addition to the other options presented, you could defer the execution until needed with a yield:

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;
            }           
        }
    }
}

This is potentially dangerous as the connection is left open while the the calling code does the iteration. Also, what happens if the caller does not get to the end of the collection? Do the connection get disposed? — Robert Wagner, Feb 07 '11 at 04:33
I suppose most code could be potentially dangerous. Your point is understood and should be taken into consideration. Here's a good read on [C# IEnumerator/yield structure with databases](http://stackoverflow.com/questions/803878/c-ienumerator-yield-structure-potentially-bad) — Metro Smurf, Feb 07 '11 at 05:10

score 2 · Answer 4 · answered Feb 07 '11 at 03:54

First, you'll want to make your Name and Age fields non-static (so each instance of the class will have their own values for these properties.

Then you could 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!

score 1 · Answer 5 · answered Feb 07 '11 at 03:53

1

Yes you can. Iterate through the items in dt.Rows and convert them manually to Person objects.

answered Feb 07 '11 at 03:53

OJ.

28,944
5
56
71

score -2 · Answer 6 · edited Jul 13 '11 at 12:53

-2

A user recently asked me a question on converting a DataTable to a List<> in .NET 2.0. Here’s the code to do so:

C#

// Assuming there is a DataTable called dt

List<DataRow> drlist = new List<DataRow>();

foreach (DataRow row in dt.Rows)
{
    drlist.Add((DataRow)row);
}

edited Jul 13 '11 at 12:53

takrl

6,356
3
60
69

answered Jul 13 '11 at 06:22

BHaveeN Sanandiya

11

Can I Fill a List using a DataAdapter C#

6 Answers6