.Net - Convert Generic Collection to Datatable

.NET - Convert Generic Collection to DataTable

Then presumably you'll need to lift them to the non-nullable form, using Nullable.GetUnderlyingType, and perhaps change a few null values to DbNull.Value...

Change the assignment to be:

row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

and when adding the columns to be:

table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(
            prop.PropertyType) ?? prop.PropertyType);

And it works. (?? is the null-coalescing operator; it uses the first operand if it is non-null, else the second operand is evaluated and used)

Convert generic List/Enumerable to DataTable?

Here's a nice 2013 update using FastMember from NuGet:

IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data)) {
    table.Load(reader);
}

This uses FastMember's meta-programming API for maximum performance. If you want to restrict it to particular members (or enforce the order), then you can do that too:

IEnumerable<SomeType> data = ...
DataTable table = new DataTable();
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) {
    table.Load(reader);
}

Editor's Dis/claimer: FastMember is a Marc Gravell project. It's gold and full-on flies!

---

Yes, this is pretty much the exact opposite of this one; reflection would suffice - or if you need quicker, HyperDescriptor in 2.0, or maybe Expression in 3.5. Actually, HyperDescriptor should be more than adequate.

For example:

// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ToDataTable<T>(this IList<T> data)
{
    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;        
}

Now with one line you can make this many many times faster than reflection (by enabling HyperDescriptor for the object-type T).

---

Edit re performance query; here's a test rig with results:

Vanilla 27179
Hyper   6997

I suspect that the bottleneck has shifted from member-access to DataTable performance... I doubt you'll improve much on that...

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
public class MyData
{
    public int A { get; set; }
    public string B { get; set; }
    public DateTime C { get; set; }
    public decimal D { get; set; }
    public string E { get; set; }
    public int F { get; set; }
}

static class Program
{
    static void RunTest(List<MyData> data, string caption)
    {
        GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
        GC.WaitForPendingFinalizers();
        GC.WaitForFullGCComplete();
        Stopwatch watch = Stopwatch.StartNew();
        for (int i = 0; i < 500; i++)
        {
            data.ToDataTable();
        }
        watch.Stop();
        Console.WriteLine(caption + "\t" + watch.ElapsedMilliseconds);
    }
    static void Main()
    {
        List<MyData> foos = new List<MyData>();
        for (int i = 0 ; i < 5000 ; i++ ){
            foos.Add(new MyData
            { // just gibberish...
                A = i,
                B = i.ToString(),
                C = DateTime.Now.AddSeconds(i),
                D = i,
                E = "hello",
                F = i * 2
            });
        }
        RunTest(foos, "Vanilla");
        Hyper.ComponentModel.HyperTypeDescriptionProvider.Add(
            typeof(MyData));
        RunTest(foos, "Hyper");
        Console.ReadLine(); // return to exit        
    }
}

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

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 a list into data table

Add this function and call it, it will convert List to DataTable.

public static 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)
        {
            //Defining type of data column gives proper data table 
            var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
            //Setting column names as Property names
            dataTable.Columns.Add(prop.Name, type);
        }
        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 a list to datatable

i only want a Datatable which contains only the rows which are in
List<Datum>

Since you already have the method it's simple:

DataTable tblDatum = ToDataTable(myobj.data)

How do I make a DataTable from a collection to use it as an SQL table parameter?

You can solve problems like these using generics. Here is a method that converts a collection of a type into a DataTable:

public static DataTable CreateDataTable<T>(ICollection<T> values)
{
    var table = new DataTable();

    // Get the generic type from the collection
    var type = values.GetType().GetGenericArguments()[0];

    // Add columns base on the type's properties
    foreach (var property in type.GetProperties())
    {
        /* It is necessary to evaluate whether each property is nullable or not.
         * This is because DataTables only support null values in the form of
         * DBNull.Value.
         */
        var propertyType = property.PropertyType;
        var computedType =
            // If the type is nullable
            propertyType.IsGenericType
                && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>) 
            // Get its underlying type
            ? propertyType.GetGenericArguments()[0]
            // If it isn't, get return the property type.
            : propertyType;

        table.Columns.Add(new DataColumn(property.Name, computedType));
    }

    // Add rows into the DataTable based off of the values
    foreach (var value in values)
    {
        var row = table.NewRow();
        foreach (var property in value.GetType().GetProperties())
        {
            // Create a container to hold the data in the value
            object data = null;
            // If the property we are adding exists...
            if (row.Table.Columns.Contains(property.Name))
                // Then get the value of that property
                data = value.GetType().GetProperty(property.Name).GetValue(value, null);

            // If the value is null, convert the value to DBNull
            row[property.Name] = data ?? DBNull.Value;
        }
        table.Rows.Add(row);
    }

    return table;
}

You can then use the DataTable returned from the function above to create an SqlParameter and pass it to the SqlCommand of your choice.

var parameter = new SqlParameter("Table", SqlDbType.Structured)
    {
        Value = CreateDataTable(model.Sales),
        TypeName = "SqlDefinedTableType"
    };

Edit: I edited the function in my original answer to support nullable types.

Collection to DataTable

You should bind your grid view to the Customer collection property:

gvDisplay.DataSource = customers.Customer;

The reason why your code doesn't work is because you were binding it directly to the customers variable which is of type CustomerCollection which in turn (despite its name) is not a collection at all, it's just a simple .NET class with a Customer property.

Convert Generic Nested List to Datatable

Just a quick solution:

public DataTable CreateNestedDataTable<TOuter, TInner>(IEnumerable<TOuter> list, string innerListPropertyName)
{
    PropertyInfo[] outerProperties = typeof(TOuter).GetProperties().Where(pi => pi.Name != innerListPropertyName).ToArray();
    PropertyInfo[] innerProperties = typeof(TInner).GetProperties();
    MethodInfo innerListGetter = typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;

    // set up columns
    DataTable table = new DataTable();
    foreach (PropertyInfo pi in outerProperties)
        table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);
    foreach (PropertyInfo pi in innerProperties)
        table.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType);

    // iterate through outer items
    foreach (TOuter outerItem in list)
    {
        var innerList = innerListGetter.Invoke(outerItem, null) as IEnumerable<TInner>;
        if (innerList == null || innerList.Count() == 0)
        {
            // outer item has no inner items
            DataRow row = table.NewRow();
            foreach (PropertyInfo pi in outerProperties)
                row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        else
        {
            // iterate through inner items
            foreach (object innerItem in innerList)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo pi in outerProperties)
                    row[pi.Name] = pi.GetValue(outerItem) ?? DBNull.Value;
                foreach (PropertyInfo pi in innerProperties)
                    row[pi.Name] = pi.GetValue(innerItem) ?? DBNull.Value;
                table.Rows.Add(row);
            }
        }
    }

    return table;
}

One could probably expand this even further, so it could work with multiple nested lists, or automatically recognize the properties that are nested lists. But I kept it simple here.

It’s used like this:

var table = CreateNestedDataTable<foo, bar>(GenericList, "GenericNestedList");

Tested with your examples, it produces the desired results:

---

Above code uses PropertyInfo.GetMethod and PropertyInfo.GetValue which were introduced with .NET 4.5. For 4.0, make the following replacements:

// before
typeof(TOuter).GetProperty(innerListPropertyName).GetMethod;

// after
typeof(TOuter).GetProperty(innerListPropertyName).GetGetMethod(true);

// for each row assignment
// before
row[pi.Name] = pi.GetValue(item) ?? DBNull.Value;

// after
row[pi.Name] = pi.GetValue(item, null) ?? DBNull.Value;

Convert generic list to dataset in C#

Have you tried binding the list to the datagridview directly? If not, try that first because it will save you lots of pain. If you have tried it already, please tell us what went wrong so we can better advise you. Data binding gives you different behaviour depending on what interfaces your data object implements. For example, if your data object only implements IEnumerable (e.g. List), you will get very basic one-way binding, but if it implements IBindingList as well (e.g. BindingList, DataView), then you get two-way binding.

---

Related Topics