How To Convert 'system.collections.generic.list ' to 'system.data.datatable'

Question

Here i am trying to bind the user list uow.Repository<User>().GetAll().ToList(); to DataTable dt but there is a error saying cannot convert type 'system.collections.generic.list ' to 'system.data.datatable' in line dt = uow.Repository<User>().GetAll().ToList();.

Below is my code

 public DataTable GetAllUser1()
    {
        DataTable dt = new DataTable();
        dt = uow.Repository<User>().GetAll().ToList();
        return dt;
    }

Any Help will be highy appreciated.Thank you

You need to construct table using User's properties and then insert rows for every user object. — Sunil, Dec 25 '17 at 05:33

score 3 · Answer 1 · answered Dec 25 '17 at 05:42

Without much code and mess use Linq to data set:

IEnumerable<DataRow> query = uow.Repository<User>().GetAll().AsEnumerable();

DataTable dt = query.CopyToDataTable<DataRow>();

The CopyToDataTable method uses the following process to create a DataTable from a query:

1.The CopyToDataTable method clones a DataTable from the source table (a DataTable object that implements the IQueryable interface). The IEnumerable source has generally originated from a LINQ to DataSet expression or method query.

2.The schema of the cloned DataTable is built from the columns of the first enumerated DataRow object in the source table and the name of the cloned table is the name of the source table with the word "query" appended to it.

3.For each row in the source table, the content of the row is copied into a new DataRow object, which is then inserted into the cloned table. The RowState and RowError properties are preserved across the copy operation. An ArgumentException is thrown if the DataRow objects in the source are from different tables.

4.The cloned DataTable is returned after all DataRow objects in the input queryable table have been copied. If the source sequence does not contain any DataRow objects, the method returns an empty DataTable.

score 0 · Answer 2 · answered Dec 25 '17 at 05:33

You can use the following method to convert list to DataTable

public DataTable GetAllUser1()
{
    DataTable dt = new DataTable();
    dt =ToDataTable(uow.Repository<User>().GetAll().ToList());
    return dt;
}

public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection properties = 
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    foreach (PropertyDescriptor prop in properties)
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
             row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
        table.Rows.Add(row);
    }
    return table;
}

score 0 · Answer 3 · answered Dec 25 '17 at 05:42

DataTable dt = new DataTable();
dt = uow.Repository<User>().GetAll().ToList();

It doesn't work like that. You should use the DataTable methods to fill it. Providing an example would provide better understanding. For example; the User class contains these properties;

public class User
{
    public int Id { get; set; }

    public string Name { get; set; }
}

You should specify the columns for DataTable;

    DataTable dt = new DataTable();
    dt.Columns.Add("Id");
    dt.Columns.Add("Name");

Now, you can add rows in a simple loop;

    var userList = uow.Repository<User>().GetAll().ToList();
    DataTable dt = new DataTable();
    dt.Columns.Add("Id");
    dt.Columns.Add("Name");
    foreach (var user in userList)
    {
        var newRow = dt.NewRow();
        newRow["Id"] = user.Id;
        newRow["Name"] = user.Name;
        dt.Rows.Add(newRow);
    }

Also, you can provide a solution dynamically by getting the properties and accessing them using Reflection.

score 0 · Answer 4 · answered Dec 25 '17 at 05:45

You can not directly convert list to Data Table

instead you can build up a generic function. mentioned in below code. You need to convert your list to build up Data Table Rows and Columns.

public DataTable ConvertTODataTable<T>(IList<T> data)// T is any generic type
{         
 PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));

        DataTable table = new DataTable();
        for (int i = 0; i < props.Count; i++)
        {
            PropertyDescriptor prop = props[i];
            table.Columns.Add(prop.Name, prop.PropertyType);
        }
        object[] values = new object[props.Count];
        foreach (T item in data)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(item);
            }
            table.Rows.Add(values);
        }
        return table;
    }

score 0 · Accepted Answer · answered Dec 25 '17 at 05:50

You can use following method to convert List to DataTable

public DataTable GetAllUser1()
{
    DataTable dt = new DataTable();
    dt = ToDataTable(uow.Repository<User>().GetAll().ToList());
    return dt;
}


public DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item, null);

        }
        dataTable.Rows.Add(values);
    }
    //put a breakpoint here and check datatable
    return dataTable;

}

How To Convert 'system.collections.generic.list ' to 'system.data.datatable'

5 Answers5