Construct Json from datatable and amend to the same Jobject

Hello All,

I have simple data table
image

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!

my string after converting

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

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

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

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?

Hello,
i use JsonConvert
image

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 !

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

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.

1 Like

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#!

In case you have problems to add items to the dictionary, check this topic :ambulance: :sos: [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?

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 .

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

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()

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

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

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

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.