In SQL we can join two table and update one like this way
UPDATE T1
SET T1.BitToUpdate = 1
FROM myTable1 T1
INNER JOIN myTable2 T2
ON T1.MyId = T2.MyId
WHERE T2.BitToCheck = 1
Here i am iterating a datatable and add data into a another data table. also query few datatable and update another data table. my code is working fine but when dealing with big iteration then it is taking very long time.
Here is code
for (int p = 0; p <= dtFilter.Rows.Count - 1; p++)
{
_dr = dtFilter.Rows[p];
EarningID = _dr.Field<string>("EarningID");
ClientName = (_dr.Field<string>("ClientName") ?? "");
InHouseCode = (_dr.Field<string>("InHouseCode") ?? "");
ID = _dr.Field<int>("ID");
ParentID = _dr.Field<int>("ParentID");
Section = (_dr.Field<string>("Section") ?? "");
LineItem = (_dr.Field<string>("LineItem") ?? "");
DisplayInCSM = _dr.Field<string>("DisplayInCSM");
//Broker= _dr.Field<string>("Broker");
Type = _dr.Field<string>("RowType");
indent = (_dr.Field<int?>("indent") ?? 0);
AllowedDecimalPlace = (_dr.Field<string>("AllowedDecimalPlace") == null ? 0 : Convert.ToInt32((_dr.Field<string>("AllowedDecimalPlace").Trim() == "" ? "0" : _dr.Field<string>("AllowedDecimalPlace"))));
LineItemID = (_dr.Field<int?>("LineItemID") ?? 0);
BMID = _dr.Field<string>("BM_Code");
//file_date = (_dr.Field<string>("Revise Date") ?? "");
CalculationMethod = (_dr.Field<string>("CalculationMethod") ?? "");
CurrencySign = (_dr.Field<string>("CurrencySign") ?? "");
AllowPercentageSign = (_dr.Field<string>("AllowPercentageSign") ?? "");
AllowComma = (_dr.Field<string>("AllowComma") ?? "");
FontName = (_dr.Field<string>("FontName") ?? "");
FontStyle = (_dr.Field<string>("FontStyle") ?? "");
FontSize = (_dr.Field<string>("FontSize") ?? "");
BGColor = (_dr.Field<string>("BGColor") ?? "");
FGColor = (_dr.Field<string>("FGColor") ?? "");
HeadingSubheading = (_dr.Field<string>("HeadingSubheading") ?? "");
Box = (_dr.Field<string>("Box") ?? "");
BlueMatrix1stElementFormulaText = (_dr.Field<string>("BlueMatrix1stElementFormula") ?? "");
dr = dtMain.NewRow();
//dr["RowNumber"] = RowNumber;
dr["InHouseCode"] = InHouseCode;
dr["ClientName"] = ClientName;
dr["EarningID"] = Convert.ToInt32(EarningID ?? "0");
dr["ID"] = ID;
dr["ParentID"] = ParentID;
dr["Section"] = Section;
dr["LineItem"] = LineItem;
dr["DisplayInCSM"] = DisplayInCSM;
dr["Type"] = Type;
dr["indent"] = indent;
dr["AllowedDecimalPlace"] = AllowedDecimalPlace;
dr["LineItemID"] = LineItemID;
dr["BMID"] = BMID;
dr["file_date"] = file_date;
dr["CalculationMethod"] = CalculationMethod;
dr["CurrencySign"] = CurrencySign;
dr["AllowPercentageSign"] = AllowPercentageSign;
dr["AllowComma"] = AllowComma;
dr["FontName"] = FontName;
dr["FontStyle"] = FontStyle;
dr["FontSize"] = FontSize;
dr["BGColor"] = BGColor;
dr["FGColor"] = FGColor;
dr["HeadingSubheading"] = HeadingSubheading;
dr["Box"] = Box;
dr["BlueMatrix1stElementFormula"] = BlueMatrix1stElementFormulaText;
dtMain.Rows.Add(dr);
if (Type == "Consensus")
{
foreach (BrokerDetails bd in Brokers)
{
foreach (string prd in Periods)
{
//dsDb contails full data which vertical SP returns
//dtFilter has all consensus & blue metrics data
var dataRow = dsDb.Tables[tableindex].AsEnumerable().AsParallel().Where(a => a.Field<string>("RowType") == "LineItem"
&& a.Field<int>("ParentID") == ParentID && a.Field<int>("ID") == ID
&& a.Field<string>("Broker") == bd.BrokerCode);
if (dataRow != null && dataRow.Count() > 0)
{
file_date = (dataRow.FirstOrDefault().Field<string>("Revise Date") ?? "");
PeriodValue = (dataRow.FirstOrDefault().Field<decimal?>(prd ?? "")).ToString();
tmpData = dtMain.AsEnumerable().AsParallel().Where(a => a.Field<string>("Type") == "Consensus"
&& a.Field<int>("ParentID") == ParentID && a.Field<int>("ID") == ID
&& a.Field<string>("Section") == Section && a.Field<string>("LineItem") == LineItem
&& a.Field<string>("DisplayInCSM") == DisplayInCSM);
if (tmpData != null && tmpData.Count() > 0)
{
tmpData.FirstOrDefault()["B_" + bd.BrokerCode + "_" + prd] = PeriodValue; // (PeriodValue <= 0 ? "" : PeriodValue.ToString());
tmpData.FirstOrDefault()["file_date"] = file_date;
}
tmpData = null;
}
file_date = "";
PeriodValue = "";
dataRow = null;
}
}
}
}
#endregion
}
So i am thinking to do the job joining multiple datatable by LINQ and update desired datatable.
this way i tried but i could not complete the code due to lack of knowledge. so anyone can help me to achieve the task.
var result= (from bk in Brokers
from prd in Periods
from _dsDb in dsDb.Tables[0].AsEnumerable()
join _dtMain in dtMain.AsEnumerable()
on new
{
val1 = _dsDb.Field<int>("ParentID"),
val2 = _dsDb.Field<int>("ID"),
val3 = _dsDb.Field<string>("Section"),
val4 = _dsDb.Field<string>("LineItem"),
val5 = _dsDb.Field<string>("DisplayInCSM")
}
equals new
{
val1 = _dtMain.Field<int>("ParentID"),
val2 = _dtMain.Field<int>("ID"),
val3 = _dtMain.Field<string>("Section"),
val4 = _dtMain.Field<string>("LineItem"),
val5 = _dtMain.Field<string>("DisplayInCSM")
}
where _dsDb.Field<string>("RowType") == "LineItem"
&& _dsDb.Field<int>("ParentID") == ParentID
&& _dsDb.Field<int>("ID") == ID
&& _dsDb.Field<string>("Broker") == bk.BrokerCode &&
_dtMain.Field<string>("Type") == "Consensus"
&& _dtMain.Field<int>("ParentID") == ParentID
&& _dtMain.Field<int>("ID") == ID
&& _dtMain.Field<string>("Section") == Section
&& _dtMain.Field<string>("LineItem") == LineItem
&& _dtMain.Field<string>("DisplayInCSM") == DisplayInCSM
select AllData=_dsDb, TranposeData= _dtMain);
Brokers & Periods will be cross join and two datatable will be inner join.
from join result i need to extract 2 info like Revise Date & PeriodValue data from this data table dsDb.Tables[tableindex] and update dtMain datatable with Revise Date & PeriodValue data.
please guide me how to compose my LINQ query as a result i can achieve my goal.
https://forums.asp.net/t/2064925.aspx?Update+a+datatable+column+with+join+of+two+datatables
Thanks
EDIT
At last i have done the job this way but not sure am i doing it correctly? does my approach will work very fast when there will be huge iteration and huge data in datatable ? please review my code and tell me how to refactor my code as a result i can speed up the time for datatable join & updation in for loop.
(from bk in Brokers
from prd in Periods
from _dsDb in dsDb.Tables[0].AsEnumerable()
join _dtMain in dtMain.AsEnumerable()
on new
{
val1 = _dsDb.Field<int>("ParentID"),
val2 = _dsDb.Field<int>("ID"),
val3 = _dsDb.Field<string>("Section"),
val4 = _dsDb.Field<string>("LineItem"),
val5 = _dsDb.Field<string>("DisplayInCSM")
}
equals new
{
val1 = _dtMain.Field<int>("ParentID"),
val2 = _dtMain.Field<int>("ID"),
val3 = _dtMain.Field<string>("Section"),
val4 = _dtMain.Field<string>("LineItem"),
val5 = _dtMain.Field<string>("DisplayInCSM")
}
where _dsDb.Field<string>("RowType") == "LineItem"
&& _dsDb.Field<int>("ParentID") == ParentID
&& _dsDb.Field<int>("ID") == ID
&& _dsDb.Field<string>("Broker") == bk.BrokerCode &&
_dtMain.Field<string>("Type") == "Consensus"
&& _dtMain.Field<int>("ParentID") == ParentID
&& _dtMain.Field<int>("ID") == ID
&& _dtMain.Field<string>("Section") == Section
&& _dtMain.Field<string>("LineItem") == LineItem
&& _dtMain.Field<string>("DisplayInCSM") == DisplayInCSM
select new { _dsDb, _dtMain, bk, prd }).ToList()
.ForEach(x =>{
x._dtMain.SetField("B_" + x.bk.BrokerCode + "_" + x.prd, (x._dsDb.Field<decimal?>(x.prd ?? "").ToString()));
x._dtMain.SetField("file_date", (x._dsDb.Field<string>("Revise Date")));
});
Full Code
for (int p = 0; p <= dtFilter.Rows.Count - 1; p++)
{
_dr = dtFilter.Rows[p];
EarningID = _dr.Field<string>("EarningID");
ClientName = (_dr.Field<string>("ClientName") ?? "");
InHouseCode = (_dr.Field<string>("InHouseCode") ?? "");
ID = _dr.Field<int>("ID");
ParentID = _dr.Field<int>("ParentID");
Section = (_dr.Field<string>("Section") ?? "");
LineItem = (_dr.Field<string>("LineItem") ?? "");
DisplayInCSM = _dr.Field<string>("DisplayInCSM");
//Broker= _dr.Field<string>("Broker");
Type = _dr.Field<string>("RowType");
indent = (_dr.Field<int?>("indent") ?? 0);
AllowedDecimalPlace = (_dr.Field<string>("AllowedDecimalPlace") == null ? 0 : Convert.ToInt32((_dr.Field<string>("AllowedDecimalPlace").Trim() == "" ? "0" : _dr.Field<string>("AllowedDecimalPlace"))));
LineItemID = (_dr.Field<int?>("LineItemID") ?? 0);
BMID = _dr.Field<string>("BM_Code");
//file_date = (_dr.Field<string>("Revise Date") ?? "");
CalculationMethod = (_dr.Field<string>("CalculationMethod") ?? "");
CurrencySign = (_dr.Field<string>("CurrencySign") ?? "");
AllowPercentageSign = (_dr.Field<string>("AllowPercentageSign") ?? "");
AllowComma = (_dr.Field<string>("AllowComma") ?? "");
FontName = (_dr.Field<string>("FontName") ?? "");
FontStyle = (_dr.Field<string>("FontStyle") ?? "");
FontSize = (_dr.Field<string>("FontSize") ?? "");
BGColor = (_dr.Field<string>("BGColor") ?? "");
FGColor = (_dr.Field<string>("FGColor") ?? "");
HeadingSubheading = (_dr.Field<string>("HeadingSubheading") ?? "");
Box = (_dr.Field<string>("Box") ?? "");
BlueMatrix1stElementFormulaText = (_dr.Field<string>("BlueMatrix1stElementFormula") ?? "");
dr = dtMain.NewRow();
//dr["RowNumber"] = RowNumber;
dr["InHouseCode"] = InHouseCode;
dr["ClientName"] = ClientName;
dr["EarningID"] = Convert.ToInt32(EarningID ?? "0");
dr["ID"] = ID;
dr["ParentID"] = ParentID;
dr["Section"] = Section;
dr["LineItem"] = LineItem;
dr["DisplayInCSM"] = DisplayInCSM;
dr["Type"] = Type;
dr["indent"] = indent;
dr["AllowedDecimalPlace"] = AllowedDecimalPlace;
dr["LineItemID"] = LineItemID;
dr["BMID"] = BMID;
dr["file_date"] = file_date;
dr["CalculationMethod"] = CalculationMethod;
dr["CurrencySign"] = CurrencySign;
dr["AllowPercentageSign"] = AllowPercentageSign;
dr["AllowComma"] = AllowComma;
dr["FontName"] = FontName;
dr["FontStyle"] = FontStyle;
dr["FontSize"] = FontSize;
dr["BGColor"] = BGColor;
dr["FGColor"] = FGColor;
dr["HeadingSubheading"] = HeadingSubheading;
dr["Box"] = Box;
dr["BlueMatrix1stElementFormula"] = BlueMatrix1stElementFormulaText;
dtMain.Rows.Add(dr);
if (Type == "Consensus")
{
(from bk in Brokers
from prd in Periods
from _dsDb in dsDb.Tables[0].AsEnumerable()
join _dtMain in dtMain.AsEnumerable()
on new
{
val1 = _dsDb.Field<int>("ParentID"),
val2 = _dsDb.Field<int>("ID"),
val3 = _dsDb.Field<string>("Section"),
val4 = _dsDb.Field<string>("LineItem"),
val5 = _dsDb.Field<string>("DisplayInCSM")
}
equals new
{
val1 = _dtMain.Field<int>("ParentID"),
val2 = _dtMain.Field<int>("ID"),
val3 = _dtMain.Field<string>("Section"),
val4 = _dtMain.Field<string>("LineItem"),
val5 = _dtMain.Field<string>("DisplayInCSM")
}
where _dsDb.Field<string>("RowType") == "LineItem"
&& _dsDb.Field<int>("ParentID") == ParentID
&& _dsDb.Field<int>("ID") == ID
&& _dsDb.Field<string>("Broker") == bk.BrokerCode &&
_dtMain.Field<string>("Type") == "Consensus"
&& _dtMain.Field<int>("ParentID") == ParentID
&& _dtMain.Field<int>("ID") == ID
&& _dtMain.Field<string>("Section") == Section
&& _dtMain.Field<string>("LineItem") == LineItem
&& _dtMain.Field<string>("DisplayInCSM") == DisplayInCSM
select new { _dsDb, _dtMain, bk, prd }).ToList()
.ForEach(x =>{
x._dtMain.SetField("B_" + x.bk.BrokerCode + "_" + x.prd, (x._dsDb.Field<decimal?>(x.prd ?? "").ToString()));
x._dtMain.SetField("file_date", (x._dsDb.Field<string>("Revise Date")));
});
}
}