Insert data from array to datatable

Question

I am joining datatables to create a new datatable,

Code :

var row = from r0w1 in dt_vi.AsEnumerable()
              join r0w2 in dt_w.AsEnumerable()
              on r0w1.Field<int>("ID") equals r0w2.Field<int>("iD")
              join r0w3 in dt_re.AsEnumerable()
              on r0w1.Field<int?>("ID") equals r0w3.Field<int?>("id")
              join r0w4 in dt_def.AsEnumerable()
              on r0w1.Field<int?>("ID") equals r0w4.Field<int?>("id") into ps
              from r0w4 in ps.DefaultIfEmpty()
              select r0w1.ItemArray.Concat(r0w2.ItemArray.Concat(r0w3.ItemArray.Concat(r0w4 != null ? r0w4.ItemArray : new object[] { }))).ToArray();


foreach (object[] values in row)
    dt.Rows.Add(values);

In the above code,

foreach (object[] values in row)
    dt.Rows.Add(values);

is slow for hundreds of thousands of rows. I want to put the data of row into dt datatable. I tried

dt=row.CopyToDataTable();

but it is giving error.

How can I use CopyToDataTable() function here to prevent looping ?

https://msdn.microsoft.com/en-us/library/bb386921(v=vs.110).aspx BTW it's not gonna help. DataTable is an _in-memory table_ and its not recommend obviously to store such huge data in it. — Rahul Singh, Dec 18 '15 at 10:14
@TimSchmelter, is there any best way I can use since above way I used is not efficient!! or is there any possibility that I can use CopyToDataTable ? — Harshit, Dec 18 '15 at 10:18
Can't you create your own type instead of using datatable? For every column use a `ConcurrentDictionary` or something, then you can create that object using TPL — Alexander Derck, Dec 18 '15 at 10:26
Possible duplicate of [Copy to Datatable](http://stackoverflow.com/questions/34198413/copy-to-datatable) — James Z, Dec 27 '15 at 06:42
Why must you load everything into a `DataTable`? Loading “hundreds of thousands of rows” into RAM is rarely useful or efficient. Is it totally impossible to create a query that returns the rows you're interested in and use a `DataReader` to load only the rows you want when you want them? — Dour High Arch, May 03 '16 at 19:03
Perhaps the problem is due to chained `Concat`. See [All About Iterators](https://blogs.msdn.microsoft.com/wesdyer/2007/03/23/all-about-iterators/) — Alexander Petrov, May 03 '16 at 19:09
Have you thought about using SqlBulkCopy to add the rows to a database table? — Steve Ford, Oct 24 '16 at 13:27

score 0 · Answer 1 · answered Jul 14 '17 at 12:43

Turn off notifications if the datatable is bounded to anything.:

dt.BeginLoadData(); //  stops notifications on each addition
foreach (object[] values in row)
    dt.Rows.Add(values);
dt.EndLoadData();

And remove .ToArray(); part to prevent second loop each rows again.

Insert data from array to datatable

1 Answers1