Fastest way to create DataTable from List without for loop

We have a huge list (say 100,000) which needs to be converted to DataTable for SqlBulkcopy.

Can you guide what could be the fastest way to do this without using for loop? For now we are doing it like - in below code listDos is object list

using (var dataTable = new DataTable(dataTableName))
{
    dataTable.Locale = CultureInfo.CurrentCulture;
    var columns = new[]
        {
             new DataColumn("Id", typeof(int)),
             new DataColumn("FkId", typeof(int)),
             new DataColumn("Status", typeof(string)),
             new DataColumn("RecordFrom", typeof(DateTime))
        };

    dataTable.Columns.AddRange(columns);


    foreach (ObjectDo listDo in listDos)
    {
        var row = dataTable.NewRow();

        if (rebuildDo.Id != null) row["Id"] = rebuildDo.Id;

        if (rebuildDo.FkId!= null) row["FkId"] = rebuildDo.FkId;

        row["Status"] = rebuildDo.Status;

        row["RecordFrom"] = rebuildDo.RecordFrom;

        dataTable.Rows.Add(row);
    }

    return dataTable;
}

Open in new window

Vivek DeshmukhAsked:
Who is Participating?
 
AndyAinscowConnect With a Mentor Freelance programmer / ConsultantCommented:
https://msdn.microsoft.com/en-us/library/bb396189(v=vs.110).aspx

How about using the CopyToDataTable function ?  There is an example in the link.
0
 
it_saigeConnect With a Mentor DeveloperCommented:
You cannot populate a collection from another collection without resorting to some sort of looping mechanism.  Even if you use a well-defined function, internally it is still employing a loop to handle the workload.  Even the CopyToDataTable function employs a looping mechanism (internally it uses a do...while loop which processes Enumerator.MoveNext()); e.g. -
/// <summary>
///   This method takes an input sequence of DataRows and produces a DataTable object
///   with copies of the source rows.
///   Also note that this will cause the rest of the query to execute at this point in time
///   (e.g. there is no more delayed execution after this sequence operator).
/// </summary>
/// <param name="source">
///   The input sequence of DataRows
/// </param>
/// <returns>
///   DataTable containing copies of the source DataRows.
///   Properties for the DataTable table will be taken from first DataRow in the source.
/// </returns>
/// <exception cref="ArgumentNullException">if source is null</exception>
/// <exception cref="InvalidOperationException">if source is empty</exception>
public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
    where T : DataRow
{
    DataSetUtil.CheckArgumentNull(source, "source");
    return LoadTableFromEnumerable(source, null, null, null);
}

private static DataTable LoadTableFromEnumerable<T>(IEnumerable<T> source, DataTable table, LoadOption? options, FillErrorEventHandler errorHandler)
    where T : DataRow
{
    if (options.HasValue)
    {
        switch (options.Value)
        {
            case LoadOption.OverwriteChanges:
            case LoadOption.PreserveChanges:
            case LoadOption.Upsert:
                break;
            default:
                throw DataSetUtil.InvalidLoadOption(options.Value);
        }
    }


    using (IEnumerator<T> rows = source.GetEnumerator())
    {
        // need to get first row to create table
        if (!rows.MoveNext())
        {
            if (table == null)
            {
                throw DataSetUtil.InvalidOperation(Strings.DataSetLinq_EmptyDataRowSource);
            }
            else
            {
                return table;
            }
        }

        DataRow current;
        if (table == null)
        {
            current = rows.Current;
            if (current == null)
            {
                throw DataSetUtil.InvalidOperation(Strings.DataSetLinq_NullDataRow);
            }

            table = new DataTable();
            table.Locale = CultureInfo.CurrentCulture;
            // We do not copy the same properties that DataView.ToTable does.
            // If user needs that functionality, use other CopyToDataTable overloads.
            // The reasoning being, the IEnumerator<DataRow> can be sourced from
            // different DataTable, so we just use the "Default" instead of resolving the difference.

            foreach (DataColumn column in current.Table.Columns)
            {
                table.Columns.Add(column.ColumnName, column.DataType);
            }
        }

        table.BeginLoadData();
        try
        {
            do
            {
                current = rows.Current;
                if (current == null)
                {
                    continue;
                }

                object[] values = null;
                try
                {   // 'recoverable' error block
                    switch (current.RowState)
                    {
                        case DataRowState.Detached:
                            if (!current.HasVersion(DataRowVersion.Proposed))
                            {
                                throw DataSetUtil.InvalidOperation(Strings.DataSetLinq_CannotLoadDetachedRow);
                            }
                            goto case DataRowState.Added;
                        case DataRowState.Unchanged:
                        case DataRowState.Added:
                        case DataRowState.Modified:
                            values = current.ItemArray;
                            if (options.HasValue)
                            {
                                table.LoadDataRow(values, options.Value);
                            }
                            else
                            {
                                table.LoadDataRow(values, true);
                            }
                            break;
                        case DataRowState.Deleted:
                            throw DataSetUtil.InvalidOperation(Strings.DataSetLinq_CannotLoadDeletedRow);
                        default:
                            throw DataSetUtil.InvalidDataRowState(current.RowState);
                    }
                }
                catch (Exception e)
                {
                    if (!DataSetUtil.IsCatchableExceptionType(e))
                    {
                        throw;
                    }

                    FillErrorEventArgs fillError = null;
                    if (null != errorHandler)
                    {
                        fillError = new FillErrorEventArgs(table, values);
                        fillError.Errors = e;
                        errorHandler.Invoke(rows, fillError);
                    }
                    if (null == fillError)
                    {
                        throw;
                    }
                    else if (!fillError.Continue)
                    {
                        if (Object.ReferenceEquals(fillError.Errors ?? e, e))
                        {   // if user didn't change exception to throw (or set it to null)
                            throw;
                        }
                        else
                        {   // user may have changed exception to throw in handler
                            throw fillError.Errors;
                        }
                    }
                }
            } while (rows.MoveNext());
        }
        finally
        {
            table.EndLoadData();
        }
    }
    Debug.Assert(null != table, "null DataTable");
    return table;
}

Open in new window

- Source

That being said, for copying a List<T> to a DataTable, I usually employ an extension method:
public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source)
{
    DataTable table = new DataTable();
    var properties = TypeDescriptor.GetProperties(typeof(T));
    foreach (PropertyDescriptor property in properties)
    {
        if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
        else
            table.Columns.Add(property.Name, property.PropertyType);
    }

    object[] values = new object[properties.Count];
    foreach (var item in source)
    {
        for (int i = 0; i < properties.Count; i++)
            values[i] = properties[i].GetValue(item);
        table.Rows.Add(values);
    }
    return table;
}

Open in new window

Example usage -
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace EE_Q29078536
{
    class Program
    {
        static void Main(string[] args)
        {
            // This is our base datatable, it is initially created with 100,000 records in an 
            // Enumerable which is then Converted to a DataTable using an extension method
            var people = (from i in Enumerable.Range(0, 100000)
                          select new
                          {
                              ID = i,
                              Name = $"Person{i}",
                              Birthdate = DateTime.Now.AddHours(-i).ToString("MM/dd/yyyy"),
                              IsWorking = (i % 2 == 0)
                          }).ConvertToDataTable("Population Demographics");

            // This is a subset of our base datatable, we copy our subset to a new datetable
            var sixthGraders = (from student in people.AsEnumerable()
                                where student["Birthdate"] != null
                                let age = DateTime.Now.Year - Convert.ToDateTime(student["Birthdate"].ToString()).Year
                                where age > 10
                                select student).CopyToDataTable();
            sixthGraders.PrintToConsole();
            Console.ReadLine();
        }
    }

    static class Extensions
    {
        public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source, string name = null)
        {
            DataTable table = new DataTable(!string.IsNullOrWhiteSpace(name) ? name : string.Empty);
            var properties = TypeDescriptor.GetProperties(typeof(T));
            foreach (PropertyDescriptor property in properties)
            {
                if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                    table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
                else
                    table.Columns.Add(property.Name, property.PropertyType);
            }

            object[] values = new object[properties.Count];
            foreach (var item in source)
            {
                for (int i = 0; i < properties.Count; i++)
                    values[i] = properties[i].GetValue(item);
                table.Rows.Add(values);
            }
            return table;
        }

        static void DrawHorizontalSeperator(this int width, char seperator)
        {
            Console.WriteLine(new string(seperator, width));
        }

        public static void PrintToConsole(this DataTable table)
        {
            var width = (25 * table.Columns.Count) + table.Columns.Count;
            Console.WriteLine($"Table Name: {table.TableName}");
            width.DrawHorizontalSeperator('=');
            Console.WriteLine($"|{string.Join("|", table.Columns.Cast<DataColumn>().Select(x => $"  {x.ColumnName}  ".PadRight(25)))}|");
            width.DrawHorizontalSeperator('=');
            foreach (var row in table.Rows.Cast<DataRow>())
                Console.WriteLine($"|{string.Join("|", row.ItemArray.Select(x => $"  {x.ToString()}  ".PadRight(25)))}|");
            width.DrawHorizontalSeperator('-');
        }
    }
}

Open in new window


-saige-
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
That should be suitable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.