Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

How to convert an IEnumerable to a DataTable in the same way as we use ToList or ToArray

How to convert an IEnumerable to a DataTable in the same way as we use ToList or ToArray

  • Comments 3

LINQ provides us some extension methods to convert an IEnumerable to a collection e.g. ToList(), ToArray() etc. But what if you want to convert the enumerable to a DataTable. There is no built in extension method for that currently.

 

                var v = (from x in collection Select x).ToDataTable();

 

But if required we can create our own extension method as shown below.

 

public static class Extenders

{

    public static DataTable ToDataTable<T>(this IEnumerable<T> collection, string tableName)

    {

        DataTable tbl = ToDataTable(collection);

        tbl.TableName = tableName;

        return tbl;

    }

 

    public static DataTable ToDataTable<T>(this IEnumerable<T> collection)

    {

        DataTable dt = new DataTable();

        Type t = typeof(T);

        PropertyInfo[] pia = t.GetProperties();

        //Create the columns in the DataTable

        foreach (PropertyInfo pi in pia)

        {

            dt.Columns.Add(pi.Name, pi.PropertyType);

        }

        //Populate the table

        foreach (T item in collection)

        {

            DataRow dr = dt.NewRow();

            dr.BeginEdit();

            foreach (PropertyInfo pi in pia)

            {

                dr[pi.Name] = pi.GetValue(item, null);

            }

            dr.EndEdit();

            dt.Rows.Add(dr);

        }

        return dt;

    }

}

 

Basically above code will create a DataTable with columns corresponding to the properties in the IEnumerable object. Then it will populate it with the data from that collection.

For calling it just import this namespace in your class and use it as shown below

 

      var v = (from x incollection Select x).ToDataTable();

 

 

Author : Naresh Joshi (MSFT) , SQL Developer Technical Lead, Microsoft

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • I can't use this code, it's unable.

    I copy this code to file Linq Designer.cs

    Then

    system return error message

    DataSet does not support System.Nullable<>.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.NotSupportedException: DataSet does not support System.Nullable<>.

    Source Error:

    Line 7902:            {

    Line 7903:

    Line 7904:                dt.Columns.Add(pi.Name, pi.PropertyType);

    Line 7905:

    Line 7906:            }

  • Looks like the collection which you are using contains some Nullable properties. Since DataTable doesn't support nullable values so it is throwing this error.

    To resolve it either you could avoid using Nullable properties in the collection which you are trying to convert to datatable or you could change the code inside first foreach loop (i.e. dt.Columns.Add(pi.Name, pi.PropertyType);) to

    {

                   Type propType = pi.PropertyType;

                   if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))

                       propType = pi.PropertyType.GetGenericArguments()[0];

                   dt.Columns.Add(pi.Name, propType);

    }

  • Thanks a Lot For Posting this tutorial..It will Help Me a Lot...

Page 1 of 1 (3 items)