Efficient way for updating data in a data table from another data table

c# datatable performance

10,225

Solution 1

You can try it this way

dtOriginal.Merge(dtNew);

Solution 2

I would have thought that using this would be much quicker:

TableToUpdate.AsEnumerable().Join
(
    TableToUpdateFrom.AsEnumerable(),
    lMaster => lMaster["COMMON_FIELD"], lChild => lChild["COMMON_FIELD"],
    (lMaster, lChild) => new { lMaster, lChild }
    ).ToList().ForEach
(
o =>
{
    o.lMaster.SetField("FIELD_TO_BE_UPDATED1", o.lChild["FIELD_TO_BE_UPDATED_FROM1"].ToString());
    o.lMaster.SetField("FIELD_TO_BE_UPDATED2", o.lChild["FIELD_TO_BE_UPDATED_FROM2"].ToString());
    o.lMaster.SetField("FIELD_TO_BE_UPDATED3", o.lChild["FIELD_TO_BE_UPDATED_FROM3"].ToString());
    o.lMaster.SetField("FIELD_TO_BE_UPDATED_ETC", o.lChild["APPROVAL_SCORE_FROM_ETC"].ToString());
}
);

10,225

Author by

Imran Balouch

Sometimes I design, Sometimes I develop and Sometimes I execute Working in: ASP.NET, C#, SQLServer, .NET MVC, JQuery SOreadytohelp

Updated on June 04, 2022

Comments

Imran Balouch about 2 years
I have a data table in my ASP.NET web service project, this data table has around 500K records in it with 39 columns and is present in Cache. After a minute a background thread hits the database and brings update records from database, which I want to update in the Cached data table, I am using following approach but it is taking good enough time to do it:
```
foreach (DataRow dRNew in dtNew.Rows)
{
     DataRow row = dtOriginal.Select("ID=" + dRNew["ID"]).First();
     row["Column1"] = dRNew["Column1"];
     row["Column2"] = dRNew["Column2"];
     row["Column3"] = dRNew["Column3"];
}
```
I have replaced the following line:
```
DataRow row = dtOriginal.Select("ID=" + dRNew["ID"]).First();
```
with
```
DataRow row = dtOriginal.AsEnumerable().Where(r => ((Int64)r["ID"]).Equals(dRNew["ID"])).First();
```
but in vain, it is taking like around 5 minutes on my laptop.

Can anyone please guide my where and what am I doing wrong? With which approach can I do it efficiently, I am not sure if Dataset.Merge or some other approach can be used.
Admin almost 12 years

please ... do not forget about the outer foreach. maybe rather a join in a dataset?
M Afifi almost 12 years

That's essentially what Merge does.
M Afifi almost 12 years

why take a copy rather than just dtOriginal.Merge(dtNew)?
HatSoft almost 12 years

@MAfifi sorry I just gave quick example that you achieve it by these methods already available, please use in the order whats suitable for you code
Admin almost 12 years

yes and no ... there should get only 3 out of 39 columns updated - so essentially not a real merge
Imran Balouch almost 12 years

Thanks a lot for this answer, the only problem I faced was, Merge statement was adding new rows in the table, which I figured out that If I put a Primary Key in my data table, it will solve the problem.
East of Nowhere over 11 years

Can you do a .Merge if one of the tables has no primary key?
JOJO over 8 years

HI Naelem,Can you please tell me how to give two condition in this LINQ..exactly in above code ,how to add another common field?
Cogent almost 8 years

@Imran Balouch You need to specify the primary key of the table before merging in order to achieve the required results