Fastest way to create DataTable from List without for loop

Question

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;
}

If you want linq please show a linq effort. Also the linq query will not have the syntax of a loop but it does loop (iterate) over all items.. — Gilad Green
– Gilad Green, Commented Jan 16, 2018 at 14:20
why "without for loop"? that seems a very arbitrary restriction... since you're dealing with lots of data, it is expected that something somewhere is going to need to loop... — Marc Gravell
– Marc Gravell, Commented Jan 16, 2018 at 14:25
I'm voting to close this question as off-topic because there is no problem to solve (only how to improve) and perhaps belongs on another Stack Exchange site. Plus motives are not explained — user585968
– user585968, Commented Jan 16, 2018 at 14:26
@ScottHannen it's not the loop, it's the duplication of data that will cause problems. Especially if there are a lot of data. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 16, 2018 at 14:26
@MickyD on the contrary, this is a significant concern when using SqlBulkCopy. If you have 10K items, creating a DataTable with them doubles the memory usage and creates a lot of temporary objects that have to be garbage collected. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 16, 2018 at 14:28

Marc Gravell · Accepted Answer · 2018-01-16 14:26:01Z

8

The fastest way would be: not to.

SqlBulkCopy can take an IDataReader. "FastMember" (on NuGet) can expose a List<T> as an IDataReader:

List<ObjectDo> listDos = ...
using(var bcp = new SqlBulkCopy(connection)) 
using(var reader = ObjectReader.Create(listDos,
    "Id", "FkId", "Status", "RecordFrom")) 
{ 
  bcp.DestinationTableName = "SomeTable"; 
  bcp.WriteToServer(reader); 
}

This is now a direct reader over the list: no duplication of all the contents.

edited Jan 16, 2018 at 14:26

answered Jan 16, 2018 at 14:23

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Oxygen Over a year ago

Thanks for the inputs. Tried this and it seems almost three times faster than going with SqlBulkCopy and data table.. Just for curiosity, why ObjectReader is faster?

Marc Gravell Over a year ago

@Oxygen possibly because it is simpler? DataTable has quite a lot of overhead, including the fact that it actually stores fields vertically rather than horizontally (which it does to avoid having to do lots of boxing) - so you actually get pretty poor memory locality. Note that without seeing a specific performance test: I can only speculate

Oxygen Over a year ago

@Mac. Need one more small help. I have miss match in class properties and table column names, is it mandatory that reader should match table properties? Also if table contains 10 columns, how can I create Object reader with only required 5 columns?

Marc Gravell Over a year ago

@Oxygen look at bcp.ColumnMappings. Add mapping between the desired source and destination columns.

Oxygen Over a year ago

Thank you so much for all your time and direction.

Collectives™ on Stack Overflow

Fastest way to create DataTable from List without for loop

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related