ADO/ADO.NET - Converting DataTable to List

Asked By shilpa g on 15-Feb-12 06:11 AM
hi.......

Now i'm storing select qry result in datatable object and
i want to store datatble result in List.

Ex:

List<DataRow> Reminderlist = dtlistofreminders.Rows.Cast<DataRow>().ToList();

log.Info(" dtlistofreminders are " + Reminderlist.ToString());



The above code is not working , can anybody help me out.

Thnx in adv.
dipa ahuja replied to shilpa g on 15-Feb-12 06:13 AM
Try this

List<DataRow> list = dt.AsEnumerable().ToList();
Somesh Yadav replied to shilpa g on 15-Feb-12 06:17 AM

Hi,
I have another approach you might take a look. It's a helper method. Create a custom class file named CollectionHelper:

    public static IList<T> ConvertTo<T>(DataTable table)
    {
        if (table == null)
            return null;

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

        foreach (DataRow row in table.Rows)
            rows.Add(row);

        return ConvertTo<T>(rows);
    }

Imagine you want to get a list of costumers. Now you'll had the following caller:

List<Customer> myList = (List<Customer>)CollectionHelper.ConvertTo<Customer>(table);

The attributes you have in your DataTable must match your Customer class (fields like Name, Address, Telephone).

I hope it helps!

For who are willing to know why to use lists instead of DataTables: http://stackoverflow.com/questions/275269/does-a-datatable-consume-more-memory-than-a-listt

The full sample:

http://lozanotek.com/blog/archive/2007/05/09/Converting_Custom_Collections_To_and_From_DataTable.aspx

shilpa g replied to dipa ahuja on 15-Feb-12 06:20 AM

thnx for ur reply,
i tried with ur answer but its not working,

its just showing in logging like below,it's not showing list

System.Collections.Generic.List`1[System.Data.DataRow]
Web Star replied to shilpa g on 15-Feb-12 06:21 AM
you can convert datatable into list in two different way one is direct way using Enumerable
List<DataRow> lstCompany = ds.Tables[0].AsEnumerable().ToList();
and another is using loop and adding item into list from datatable as follows
           
            
            foreach (DataRow row in dt.Rows)
            {
                Sampleclass s = new Sampleclass();
                s.id = Convert.ToInt32(row["id"].ToString());
                s.mame = row["name"].ToString();
                s.title = row["title"].ToString();
                s.Address = row["Address"].ToString();
              s.url = row["url"].ToString();
                objlist.Add(s);
            }
            

shilpa g replied to Web Star on 15-Feb-12 06:26 AM
Actually my main aim is to avoid looping.

In my application i have selected some records and stored in datatable and
i want to delete records from datatable,so if i go with loopind means performance issue is there so wat i want to do is converting datatble records into list and once i can delete records.
Web Star replied to shilpa g on 15-Feb-12 06:32 AM
than you will go with first option using Enumerable you only include generic collection namespace on your page and if that not worked than you will try with IEnumerable as follows

IEnumerable<DataRow> sequence = dt.AsEnumerable(); 
shilpa g replied to Web Star on 15-Feb-12 06:39 AM
Hi,

Both are not working , is there any other way.
Web Star replied to shilpa g on 15-Feb-12 07:00 AM
You can try with using Linq as follows

List<string> NameList = (from r in ds.Tables["Default"].AsEnumerable()
                         select r.Field<string>("FirstName") + r.Field<string>("LastName")).ToList();
Sandeep Mittal replied to shilpa g on 16-Feb-12 02:47 AM
protected void Page_Load(object sender, EventArgs e)
{
  DataTable EmpTable = new DataTable();
  EmpTable.Columns.Add("EmpID");
  EmpTable.Columns.Add("EmpName");  
  DataRow EmpRow = EmpTable.NewRow();
  EmpRow["EmpID"] = "EMP0001";
  EmpRow["EmpName"] = "Sandeep Mittal";   
  EmpTable.Rows.Add(EmpRow);
  EmpTable.AcceptChanges();
 
  List<DataRow> listEmp = (List<DataRow>)ConvertTo<DataRow>(EmpTable);     
}
 
public static IList<T> ConvertTo<T>(DataTable table)
{
  if (table == null)
    return null;
  List<T> rows = new List<T>();
  foreach (T row in table.Rows)
    rows.Add(row);
  return rows;
}