C# Unable to update data table values

Mou_kol

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 occur. See my XML structure

ML
BofA Merrill Lynch
01-16-2018
<\_x0032\_010\_x0020\_FYA>1608.6500
<\_x0032\_011\_x0020\_FYA>1429.0610
<\_x0032\_012\_x0020\_FYA>1656.7500
<\_x0032\_013\_x0020\_FYA>1427.9330
<\_x0031\_Q\_x0020\_2014A>321.0100
<\_x0032\_Q\_x0020\_2014A>525.5670
<\_x0033\_Q\_x0020\_2014A>478.0100
<\_x0034\_Q\_x0020\_2014A>323.2360
<\_x0032\_014\_x0020\_FYA>1647.8230
<\_x0031\_Q\_x0020\_2015A>342.4010
<\_x0032\_Q\_x0020\_2015A>512.7390
<\_x0033\_Q\_x0020\_2015A>465.9940
<\_x0034\_Q\_x0020\_2015A>318.4440
<\_x0032\_015\_x0020\_FYA>1639.5780
<\_x0031\_Q\_x0020\_2016A>430.9940
<\_x0032\_Q\_x0020\_2016A>531.7920
<\_x0033\_Q\_x0020\_2016A>410.4750
<\_x0034\_Q\_x0020\_2016A>379.9890
<\_x0032\_016\_x0020\_FYA>1753.2500
<\_x0031\_Q\_x0020\_2017A>456.9130
<\_x0032\_Q\_x0020\_2017A>696.9010
<\_x0033\_Q\_x0020\_2017A>503.3780
<\_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 />
<\_x

OriginalGriff

You have no field in your XML called "Section ":

if (ds.Tables[0].AsEnumerable().Any(a => a.Field("Section ") == "ML"
&& ...
))

So the first test will always fail. You have "Section_x0020_" but no "Section " ...

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

Mou_kol

Yes issue fixed. sharing 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("Section ") == "ML"
         && a.Field("GroupKey").Split('~')\[0\].Trim() == "Consensus Model"
         && a.Field("GroupKey").Split('~')\[1\].Trim() == "Net Revenue"
         && a.Field("GroupKey").Split('~')\[3\].Trim() == "NBM"
         && a.Field("GroupKey").Split('~')\[5\].Trim() == "1"
         && a.Field("GroupKey").Split('~')\[6\].Trim() == "ML"
                ))
{

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

    //foreach (var row in rowsToUpdat)
    //{
    //    row.SetField("2010 FYA", "1200");
    //}
}

Mou_kol

Sir xml was created by data table writexml function data looks like & see how Section element looks in xml ZB that is why we need to add space end of Section other wise program will throw run time error.

ZB
B. Riley Securities
02-09-2021
<\_x0032\_010\_x0020\_FYA />
<\_x0032\_011\_x0020\_FYA />
<\_x0032\_012\_x0020\_FYA />
<\_x0032\_013\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2014A />
<\_x0032\_Q\_x0020\_2014A />
<\_x0033\_Q\_x0020\_2014A />
<\_x0034\_Q\_x0020\_2014A />
<\_x0032\_014\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2015A />
<\_x0032\_Q\_x0020\_2015A />
<\_x0033\_Q\_x0020\_2015A />
<\_x0034\_Q\_x0020\_2015A />
<\_x0032\_015\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2016A />
<\_x0032\_Q\_x0020\_2016A>3333.3865
<\_x0033\_Q\_x0020\_2016A>3878.1150
<\_x0034\_Q\_x0020\_2016A>4376.5020
<\_x0032\_016\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2017A>3340.3645
<\_x0032\_Q\_x0020\_2017A>3711.3744
<\_x0033\_Q\_x0020\_2017A>4310.8500
<\_x0034\_Q\_x0020\_2017A>5041.2855
<\_x0032\_017\_x0020\_FYA>16403.8744
<\_x0031\_Q\_x0020\_2018A>4867.8009
<\_x0032\_Q\_x0020\_2018A>5167.5060
<\_x0033\_Q\_x0020\_2018A />
<\_x0034\_Q\_x0020\_2018A />
<\_x0032\_018\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2019A />
<\_x0032\_Q\_x0020\_2019A>3545.9127
<\_x0033\_Q\_x0020\_2019A>3308.1080
<\_x0034\_Q\_x0020\_2019A>3911.1380
<\_x0032\_019\_x0020\_FYA />
<\_x0031\_Q\_x0020\_2020A>2798.3300
<\_x0032\_Q\_x0020\_2020A>3054.0900
<\_x0033\_Q\_x0020\_2020A>$3,851.0000
<\_x0034\_Q\_x0020\_2020A>4495.1620
<\_x0032\_020\_x0020\_FYA>14198.5820
<\_x0031\_Q\_x0020\_2021E>4187.6225
<\_x0032\_Q\_x0020\_2021E>4701.8271
<\_x0033\_Q\_x0020\_2021E>4902.8013
<\_x0034\_Q\_x0020\_2021E>4929.6456
<\_x0032\_021\_x0020\_FYE>18721.8968
<\_x0031\_Q\_x0020\_2022E>4980.5665
<\_x0032\_Q\_x0020\_2022E>5074.0862

Dave Kreskowiak

So what? You STILL don't have anything in that XML called "Section". If this XML is being written incorrectly, I think the problem would come down to the code that generated the original datatable and/or the code that wrote it to the file.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

OriginalGriff

The underscore character '_' is not a "special character" in XML data - it does not indicate to anything standard that the hex value between a pair of underscores should be treated as a single Unicode character. It's just a valid character in an element name, is all. So an XML element called "Section_x0020_" is not equivalent to a table column called "Section " or even "Section" - it becomes a column named "Section_x0020_" is all. Even if it was - which it isn't - ending a field or element name with an "invisible character" would be a very poor practice!

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

OriginalGriff

Just as a thought, see here: The Naming of Parts[^]

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

Lost User

I much prefer Naming of Parts[^]

Richard Deeming

.Any(...) followed by .Where(...).ToList() will be rather inefficient. Just call .Where(...).ToList(); if there are no matching elements, the list will be empty. You'd also be better skipping the .ToList(), and using a foreach loop instead. You're not changing anything that would cause the "collection was modified" error, or cause rows to be skipped, so you don't need the extra overhead of a List<T>.

IEnumerable<DataRow> recordsToUpdate = ds.Tables[0].AsEnumerable().Where(a => ...);
foreach (DataRow row in recordsToUpdate)
{
row["2010 FYA"] = 1200;
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mou_kol

Thanks for the guide line.

jschell

I think the preference depends on who is doing the reading (reading it out loud) and how much wine is involved.