Issue with implementing javascriptserializer library to seralize datatable to JSON string and remove blank values

Question

0.00/5 (No votes)

See more:

The problem boils down to as below:

PS: The datatable structure is defined as in the image: 

https://i.stack.imgur.com/AEaPj.png

I am using C#.Net core to solve the problem of removing the '[' and ']' from each of datatable column values. I would also like to convert the complete datatable by serializing as JSON string first and the deseralize to create well formed XML construct.

The issue I am facing is that I want the column values under column name: 'XMLNodePath' as XML node tag and values under column name: 'XMLElementValue' as XML node value and also XML construct should be wrapped at the start and at the end with

Shown in image: https://i.stack.imgur.com/zvyuQ.png

What I have tried:

I have tried to achieve the same with below code but it is unable to achieve the same XML construct:

using System.Web.Script.Serialization;
using Newtonsoft.Json;

foreach (DataRow dr in dt.Rows)
{
   row = new Dictionary<string, string="">();
   if (dr.Field<string>("SecLegInd") != "")
   {
      dr.Field<string>("SecLegInd").Remove(1, 1);
      dr.Field<string>("SecLegInd").Remove(dr.Field<string> 
      ("SecLegInd").Length - 1, 1);
   }
   if (dr.Field<string>("XMLNodePath") != "")
   {
      dr.Field<string>("XMLNodePath").Remove(0, 1);
      dr.Field<string>("XMLNodePath").Remove(dr.Field<string> 
      ("XMLNodePath").Length - 1, 1);
    }
    if (dr.Field<string>("XMLElementValue") != "")
   {
      dr.Field<string>("XMLElementValue").Remove(0, 1);
      dr.Field<string>("XMLElementValue").Remove(dr.Field<string> 
      ("XMLElementValue").Length - 1, 1);
    }
    row.Add(dr.Field<string>("XMLNodePath"), dr.Field<string>("XMLElementValue"));
    rows.Add(row);
}
rows.Where(pair => pair.Count > 0)
                              .ToDictionary(pair => pair.Keys, pair => pair.Values);
string JSONstring = JsonConvert.SerializeObject(rows, new JsonSerializerSettings()
{
  NullValueHandling = NullValueHandling.Ignore,
});
var temp = JArray.Parse(JSONstring);
temp.Descendants()
                .OfType<jproperty>()
                .Where(attr => attr.Value.ToString() == "")
                .ToList() 
                .ForEach(attr => attr.Remove());
 JSONstring = temp.ToString();
 xml = JsonConvert.DeserializeXmlNode("{\"envelope\":" + JSONstring + "}", "envelope");

Please anyone let me know the thoughts on how to achieve the same.

Posted 19-Jun-21 5:19am

Member 15254125

Updated 19-Jun-21 21:39pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2021-06-19T21:39:00

Solution 1

You are losing all your Remove changes. The String.Remove Method (System) | Microsoft Docs[^] returns the changed string, but you are throwing each one away.

[edit]
You need something like the following to capture the content of your DataTable:

C#

// NB this is .NET Framework code so may vary from .NET Core
foreach (DataRow dr in dt.Rows)
{
    Dictionary<string, string> row = new Dictionary<string, string>();

// I commented this out as you do not do anything with this column.

    // if (dr.Field<string>("SecLegInd") != "")
    // {
    //     dr.Field<string>("SecLegInd").Remove(1, 1);
    //     dr.Field<string>("SecLegInd").Remove(dr.Field<string> 
    //     ("SecLegInd").Length - 1, 1);
    // }
    string key = "";
    if (dr.Field<string>("XMLNodePath") != "")
    {
        // the 'key' variable captures the modified text
        key = dr.Field<string>("XMLNodePath").Remove(0, 1);
        key = key.Remove(key.Length - 1, 1);
    }
    string value = "";
    if (dr.Field<string>("XMLElementValue") != "")
    {
        value = dr.Field<string>("XMLElementValue").Remove(0, 1);
        value = value.Remove(value.Length - 1, 1);
    }
    rows.Add(key, value);
}

[/edit]

Posted 19-Jun-21 21:39pm

Richard MacCutchan

Updated 20-Jun-21 2:19am

v2

Comments

Member 15254125 20-Jun-21 5:15am

Hi Richard,

Thanks for the prompt reply!
I have checked the code after implementing Remove() method earlier as well but the string format showed '[<xmlnode>]' as key and '[<xmlelementvalue>]' as Value

[no name] 20-Jun-21 7:13am

Sorry I don't know what you mean. In the following code:

      dr.Field<string>("SecLegInd").Remove(1, 1);
      dr.Field<string>("SecLegInd").Remove(dr.Field<string> 
      ("SecLegInd").Length - 1, 1);

The strings returned from the Remove calls are not being saved so you are not changing anything.

Member 15254125 20-Jun-21 8:13am

Thanks Richard, I got it!

I have now got a json string which must be wrapped with start tag <element> and end tag to create xml construct but it is getting added like this.

<envelope><sectypecd>SWAPIRS<envelope><swaptenor>10Y<envelope><effectivedate>2017-02-27<envelope><firstpaymentdate>2017-01-24<envelope>
I have tried to code it using this:

xml = JsonConvert.DeserializeXmlNode("{\"envelope\":" + JSONstring + "}", "envelope");

expected is like this:

<envelope><sectypecd>SWAPIRS<swaptenor>10Y<effectivedate>2017-02-27<firstpaymentdate>2017-01-24<envelope>

[no name] 20-Jun-21 8:24am

Sorry, I have no experience of using NewtonSoft. I suggest you open a new question, and include the actual data that you are passing in to the SerializObject method.

Member 15254125 20-Jun-21 8:28am

Original json string looks like this:

[{
"secTypeCd": "SWAPIRS"
},
{
"swapTenor": "10Y"
}]

I am ok to use some other library other than NewtonSoft.

In order to get this XMl construct:

<envelope><sectypecd>SWAPIRS<swaptenor>10Y<effectivedate>2017-02-27<envelope>

[no name] 20-Jun-21 8:36am

As I suggested, you should open a new question which is specifically about using the Newtonsoft classes.