Fastest way to fill DataTable manually in C#

Question

I want to know if someone of you know a faster way to fill a DataTable manually then I do.

Here is what I got, I have a List with about 1.7b entries. I want to fill this entries as fast as possible into DataTable with one column. An entry in my list looks like this here {"A2C","DDF","ER","SQ","8G"}

My code need about 7-8 seconds

for (int i = 0; i <= lists.Count; i++)
{
    table_list.Rows.Add();
}

for (int a = 0; a < list.Count; a++)
{
    table_list.Rows[a][0] = list[a][0] + list[a][1] + 
        list[a][2] + list[a][3] + list[a][4];
}

As I didn't find any similar question on the board (just questions about how to fill datatable by sql and fill method), I decided to post my question.

Any input is highly appreciated!

Answer 1

i add this DataTable into an sql server database (i do this by SqlBulkCopy)

This is a mistake; the DataTable is pure overhead here. What you should expose is an IDataReader over that data. This API is a bit tricky, but FastMember makes it easier. For example, it sounds like you have 1 column; so consider:

class Foo {
    public string ColumnName {get;set;}
}

Now write an iterator block method that converts this from the original list per item:

IEnumerable<Foo> Convert(List<TheOldType> list) {
    foreach(var row in list) {
        yield return new Foo { ColumnName = /* TODO */ };
    }
}

and now create an IDataReader via FastMember on top of that lazy sequence:

List<TheOldType> list
var data = Convert(list);
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "ColumnName"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

This works much better than populating a DataTable - in particular, it avoids populating a huge DataTable. Emphasis: the above is spooling - not buffered.