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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
it_saigeDeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.