question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked Viorel-1 edited

C# LINQ Not able to update datatable value

I am trying to find data in xml file using LINQ but my ANY() or WHERE clause found no data. what is the problem in my approach not clear.

I have a xml file which which has been created by Dataset WriteXml() function. that file i am querying by LINQ and data not found error i am getting.

See my XML structure

 <?xml version="1.0" standalone="yes"?>
 <TER_ViewAll>
 <dgvViewAll_Vertical>
     <Section_x0020_>ML</Section_x0020_>
     <LineItem>BofA Merrill Lynch</LineItem>
     <Revise_x0020_Date>01-16-2018</Revise_x0020_Date>
     <_x0032_010_x0020_FYA>1608.6500</_x0032_010_x0020_FYA>
     <_x0032_011_x0020_FYA>1429.0610</_x0032_011_x0020_FYA>
     <_x0032_012_x0020_FYA>1656.7500</_x0032_012_x0020_FYA>
     <_x0032_013_x0020_FYA>1427.9330</_x0032_013_x0020_FYA>
     <_x0031_Q_x0020_2014A>321.0100</_x0031_Q_x0020_2014A>
     <_x0032_Q_x0020_2014A>525.5670</_x0032_Q_x0020_2014A>
     <_x0033_Q_x0020_2014A>478.0100</_x0033_Q_x0020_2014A>
     <_x0034_Q_x0020_2014A>323.2360</_x0034_Q_x0020_2014A>
     <_x0032_014_x0020_FYA>1647.8230</_x0032_014_x0020_FYA>
     <_x0031_Q_x0020_2015A>342.4010</_x0031_Q_x0020_2015A>
     <_x0032_Q_x0020_2015A>512.7390</_x0032_Q_x0020_2015A>
     <_x0033_Q_x0020_2015A>465.9940</_x0033_Q_x0020_2015A>
     <_x0034_Q_x0020_2015A>318.4440</_x0034_Q_x0020_2015A>
     <_x0032_015_x0020_FYA>1639.5780</_x0032_015_x0020_FYA>
     <_x0031_Q_x0020_2016A>430.9940</_x0031_Q_x0020_2016A>
     <_x0032_Q_x0020_2016A>531.7920</_x0032_Q_x0020_2016A>
     <_x0033_Q_x0020_2016A>410.4750</_x0033_Q_x0020_2016A>
     <_x0034_Q_x0020_2016A>379.9890</_x0034_Q_x0020_2016A>
     <_x0032_016_x0020_FYA>1753.2500</_x0032_016_x0020_FYA>
     <_x0031_Q_x0020_2017A>456.9130</_x0031_Q_x0020_2017A>
     <_x0032_Q_x0020_2017A>696.9010</_x0032_Q_x0020_2017A>
     <_x0033_Q_x0020_2017A>503.3780</_x0033_Q_x0020_2017A>
     <_x0034_Q_x0020_2017A />
     <_x0032_017_x0020_FYA />
     <_x0031_Q_x0020_2018A />
     <_x0032_Q_x0020_2018A />
     <_x0033_Q_x0020_2018A />
     <_x0034_Q_x0020_2018A />
     <_x0032_018_x0020_FYA />
     <_x0031_Q_x0020_2019A />
     <_x0032_Q_x0020_2019A />
     <_x0033_Q_x0020_2019A />
     <_x0034_Q_x0020_2019A />
     <_x0032_019_x0020_FYA />
     <_x0031_Q_x0020_2020A />
     <_x0032_Q_x0020_2020A />
     <_x0033_Q_x0020_2020A />
     <_x0034_Q_x0020_2020A />
     <_x0032_020_x0020_FYA />
     <_x0031_Q_x0020_2021E />
     <_x0032_Q_x0020_2021E />
     <_x0033_Q_x0020_2021E />
     <_x0034_Q_x0020_2021E />
     <_x0032_021_x0020_FYE />
     <_x0031_Q_x0020_2022E />
     <_x0032_Q_x0020_2022E />
     <_x0033_Q_x0020_2022E />
     <_x0034_Q_x0020_2022E />
     <_x0032_022_x0020_FYE />
     <GroupKey>Consensus Model~Net Revenue~TRIN~NBM~~1~ML</GroupKey>
   </dgvViewAll_Vertical>
   </TER_ViewAll>

the above xml is one records and xml file has many records like above one. i load that xml file by data table and querying by LINQ. this way i am querying.

 private void button1_Click(object sender, EventArgs e)
         {
             string QCViewPath_savepath = @"C:\RDSS WorkBench_Stage\Data\TER\TER_QC-ViewwAll.xml";
             DataSet ds = new DataSet();
             ds.ReadXml(QCViewPath_savepath);
    
             if (ds.Tables[0].AsEnumerable().Any(a => a.Field<string>("Section ") == "ML"
                             && a.Field<string>("GroupKey").Contains("Consensus Model")
                             && a.Field<string>("GroupKey").Contains("Net Revenue")
                             && a.Field<string>("GroupKey").Contains("NBM")
                             && a.Field<string>("GroupKey").Contains("1")
                             && a.Field<string>("GroupKey").Contains("ML")
                             ))
             {
                 //ds.Tables[0].AsEnumerable().Where(a => a.Field<string>("Section ") == "ML"
                 //     && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
                 //     && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
                 //     && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
                 //     && a.Field<string>("GroupKey").Split('~')[4].Trim() == "1"
                 //     && a.Field<string>("GroupKey").Split('~')[5].Trim() == "ML"
                 //     ).ToList<DataRow>()
                 //     .ForEach(r =>
                 //     {
                 //         r["2010 FYA"] = 1200;
                 //     });
    
                 var rowsToUpdat = ds.Tables[0].AsEnumerable().Where(a => a.Field<string>("Section ") == "ML"
                      && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
                      && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
                      && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
                      && a.Field<string>("GroupKey").Split('~')[4].Trim() == "1"
                      && a.Field<string>("GroupKey").Split('~')[5].Trim() == "ML"
                      );
    
                 foreach (var row in rowsToUpdat)
                 {
                     row.SetField("2010 FYA", "1200");
                     //row.SetField("enddate", enDate);
                 }
             }
         }


My object is to update data table periodical value and save that data at last after all update. please help with rectified code. Thanks






dotnet-csharp
· 1
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.

You could learn how to use Linq2XML for better performance.

https://www.dotnetcurry.com/linq/564/linq-to-xml-tutorials-examples




0 Votes 0 ·
TZacks-2728 avatar image
0 Votes"
TZacks-2728 answered Viorel-1 edited

Thanks to all of you. Issue sorted. here is working code

         string QCViewPath_savepath = @"C:\Test.xml";
         DataSet ds = new DataSet();
         ds.ReadXml(QCViewPath_savepath);

         if (ds.Tables[0].AsEnumerable().Any(a => a.Field<string>("Section ") == "ML"
                  && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
                  && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
                  && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
                  && a.Field<string>("GroupKey").Split('~')[5].Trim() == "1"
                  && a.Field<string>("GroupKey").Split('~')[6].Trim() == "ML"
                         ))
         {

             ds.Tables[0].AsEnumerable().Where(a => a.Field<string>("Section ") == "ML"
                  && a.Field<string>("GroupKey").Split('~')[0].Trim() == "Consensus Model"
                  && a.Field<string>("GroupKey").Split('~')[1].Trim() == "Net Revenue"
                  && a.Field<string>("GroupKey").Split('~')[3].Trim() == "NBM"
                  && a.Field<string>("GroupKey").Split('~')[5].Trim() == "1"
                  && a.Field<string>("GroupKey").Split('~')[6].Trim() == "ML"
                  ).ToList<DataRow>()
                  .ForEach(r =>
                  {
                      r["2010 FYA"] = 1200;
                  });

             //foreach (var row in rowsToUpdat)
             //{
             //    row.SetField("2010 FYA", "1200");
             //}
         }
· 1
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.


It seems that you used “Net Revenue” instead of “Total Revenue”, [6] instead of [5] and [5] instead of [4] (in several places).

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

Try “Net Revenue” instead of “Total Revenue”, [6] instead of [5] and [5] instead of [4] (in several places).

Separately, you can also consider some optimisations.

· 2
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.

Thanks for pointing the area. i fixed it now.
This is not clear you said -- [6] instead of [5] and [5] instead of [4] (in several places).

See my updated code but datatable periodical value not getting updated in data table. what i am missing in my code. please guide me. thanks

0 Votes 0 ·

This is not clear you said -- [6] instead of [5] and [5] instead of [4]…

See your solution above.




0 Votes 0 ·