question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked DanielZhang-MSFT commented

C# How to join 2 datatable and update one datatable

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





dotnet-csharp
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TZacks-2728,
According to your code, you use the where statement to only obtain data with specific conditions and convert the query to a list. This is a good way to speed up the query. I add one more suggestion that if you are using a local sql-server, please try to use sqlite, it's faster in many ways.
Best Regards,
Daniel Zhang

0 Votes 0 ·

Sir for long time our company use sql server. now suddenly can not switch from sql server to sql lite.

0 Votes 0 ·

Hi @TZacks-2728,
You can also try to make some changes on the other to speed up the query update speed.
Such as using SqlCommand.Prepare method, using index.
Or looping your update, so that it acts on small a numbers of rows each iteration is a good way to go.
Best Regards,
Daniel Zhang


0 Votes 0 ·

0 Answers