Construct Json from datatable and amend to the same Jobject

[Hazem_Saleh]

Hello All,

I have simple data table

I can covert this to Json object but dont know how to add values

for example i want to send the column names and the description in them

im trying to achieve this

{
“startedDateTime”: “2022-01-03T12:13:14Z”,
“endedDateTime”: “2022-01-03T12:13:14Z”,
“outcome”: “f”,
“message”: null,
“rowCount”: 2,
“columns”: [
{
“type”: “string”,
“name”: “SolutionID”
},
{
“type”: “string”,
“name”: “ClientID”
}
],
“keysColumns”: [
“SolutionID”,
“ClientID”,

],
"descriptionColumns": [
    "solution id",
    "Client id"
    
],
"rows": [
    {
        "o": "p",
        "e": null,
        "k": [
            "1",
            "x"
            
        ],
        "d": [
           "optional description",
		   "test"
        ],
        "v": [
            "1",
            
            "2"
        ]
    }
    
]

}

any tips around it!

[Hazem_Saleh]

my string after converting

“[{"SolutionID":"1","ClientID":"x"},{"SolutionID":"2","ClientID":"y"}]”

[efelantti]

Can you show how you are doing the serialization to Json?

Why do you need the different format? Where is the sample output from?

[ppr (Peter Preuss)]

@Hazem_Saleh

I can covert this to Json object but dont know how to add values

sounds like you are more interested on constructing a json instead of converting a datatable to JSON. Before risking to run into a XY Problem, can you elaborate more on

• what is the business case and what is needed for?

[Hazem_Saleh]

Hello,
i use JsonConvert

the sample output im collecting during the process
The o "outcomes " if the item is completed , e if i have any error i will add it to the same row.

d is only description i can take the column names !

[Hazem_Saleh]

You are right , i need to carry it as report ( same on our Orch queues but with additional elements),

[efelantti]

Some ideas:
a) Create the JObject in start of process, from a .json file containing the basic keys. Update it when necessary during the process.
b) Use a Dictionary<String,Object> instead of a JObject, and serialize it when needed.
c) Create a .NET class representing the structure you require.

[Hazem_Saleh]

Thanks, but how to add the dictionary. to the current Jstring from this dt?
I found. concat function but didnt manage it to work in c#!

[efelantti]

In case you have problems to add items to the dictionary, check this topic [FirstAid] Migration to Windows target Framework - Missing Microsoft.Activities.Extensions package - Dictionary Activities - News / Vote on Tutorials - UiPath Community Forum - https://forum.uipath.com/.

Not sure what you would want to do with the Concat?

[Hazem_Saleh]

Thanks alot thats helps,

But couldn’t find how to add (thats why i want to use concat).

I need to have Dictionary of string string and of string list so i can convert them to json

if you look above at my goal will look like when its comes to list for example the column names here

“startedDateTime”: “2022-01-03T12:13:14Z”,
“endedDateTime”: “2022-01-03T12:13:14Z”,
“outcome”: “f”,
“message”: null,
“rowCount”: 2,
“columns”: [
{
“type”: “string”,
“name”: “SolutionID”
},

Any tips .

[efelantti]

If you go with Dictionaries, each of the Json objects would become a Dict<String,Object>. Here’s a sample in C#:

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
					
public class Program
{
	public static void Main()
	{
       var topLevelDict = new Dictionary<String, Object>();
		topLevelDict.Add("key1", "value1");
		topLevelDict.Add("key2", "value2");
		
		var columns = new List<Dictionary<String, Object>>();
		
		var column1Dict = new Dictionary<String, Object>();
		column1Dict.Add("type", "string");
		column1Dict.Add("name", "SolutionID");
		
		columns.Add(column1Dict);
		
		topLevelDict.Add("columns", columns);
		
		var serializedDict1 = JsonConvert.SerializeObject(topLevelDict);
		
		Console.WriteLine(serializedDict1);
	}
}

[Hazem_Saleh]

Thanks alot,

But sorry im trying to add so i can get the column names and type dynamic

        List<Dictionary<string, string>> columnsList = dt.Columns.Cast<DataColumn>()
            .Select(column => new Dictionary<string, string>
            {
                { "name", column.ColumnName },
                { "type", column.DataType.FullName }
            })
            .ToList();

but i got convert error which is a bit hard for me to solve

even im converting everything ToList()

[efelantti]

Can you provide more code? As running what you posted does not cause any exceptions on my system

[Hazem_Saleh]

Hi, sure
Im using your code just added columns.Add(columnsList);

var topLevelDict = new Dictionary<String, Object>();
		topLevelDict.Add("key1", "value1");
		topLevelDict.Add("key2", "value2");
		
		var columns = new List<Dictionary<String, Object>>();

	 var columnsList = new List<Dictionary<String, Object>>();
         columnsList = dt.Columns.Cast<DataColumn>()
            .Select(column => new Dictionary<string, Object>
            {
                { "name", column.ColumnName },
                { "type", column.DataType.FullName }
            })
            .ToList();
		
		var column1Dict = new Dictionary<String, Object>();
		column1Dict.Add("type", "string");
		column1Dict.Add("name", "SolutionID");
		
		columns.Add(columnsList);
		
		topLevelDict.Add("columns", columns);
		
		var serializedDict1 = JsonConvert.SerializeObject(topLevelDict);
		
		Console.WriteLine(serializedDict1);

[efelantti]

Your columnsList is already of type List<Dictionary<String,Object>>, so instead of adding it to the columns list, it is the columns list:

		var topLevelDict = new Dictionary<String, Object>();
		topLevelDict.Add("key1", "value1");
		topLevelDict.Add("key2", "value2");
		
		var columns = new List<Dictionary<String, Object>>();

	 	var columnsList = new List<Dictionary<String, Object>>();
         columnsList = dt.Columns.Cast<DataColumn>()
            .Select(column => new Dictionary<string, Object>
            {
                { "name", column.ColumnName },
                { "type", column.DataType.FullName }
            })
            .ToList();
		
		var column1Dict = new Dictionary<String, Object>();
		column1Dict.Add("type", "string");
		column1Dict.Add("name", "SolutionID");
		
		columns = columnsList;

		topLevelDict.Add("columns", columns);
		
		var serialized = JsonConvert.SerializeObject(topLevelDict);
		Console.WriteLine(serialized);

Edit: Or you could just skip initializing variable columns, and just add columnsList directly to the topLevelDict…