Question 219829 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – How can I reorganize data from a table as a complex C# object?

I have a table containing several references to the same posts, themselves containing several references to the same languages like so :

postId language name value
1 en title Post 1 title
1 en body This is the content for the post 1
1 fr title Titre de l’article 1
1 fr body Voici le contenu de l’article 1
2 en title Post 2 title
2 en body This is the content for the post 2
3 en title Post 3 title
3 en body This is the content for the post 3
3 fr title Titre de l’article 3
3 fr body Voici le contenu de l’article 3
3 de title Titel von Artikel 3
3 de body Lesen Sie den Inhalt von Artikel 3

Base on this table, I would like to create a C# object which ressemble to this :

{
   "posts":[
      {
         "1":[
            {
               "en":{
                  "title":"Post 1 title",
                  "body":"This is the content for the post 1"
               }
            },
            {
               "fr":{
                  "title":"Titre de l'article 1",
                  "body":"Voici le contenu de l'article 1"
               }
            }
         ]
      },
      {
         "2":[
            {
               "en":{
                  "title":"Post 2 title",
                  "body":"This is the content for the post 2"
               }
            }
         ]
      },
      {
         "3":[
            {
               "en":{
                  "title":"Post 3 title",
                  "body":"This is the content for the post 3"
               }
            },
            {
               "fr":{
                  "title":"Titre de l'article 3",
                  "body":"Voici le contenu de l'article 3"
               }
            },
            {
               "de":{
                  "title":"Titel von Artikel 3",
                  "body":"Lesen Sie den Inhalt von Artikel 3"
               }
            }
         ]
      }
   ]
}

How can I achieve that in C# ?

After selecting my datas from my SQL database, I loop through every line, trying to add it to a dictionnary of dictionnary of dictionnary of string Dictionary<string, Dictionary<string, Dictionary<string, string>>>, testing if the key exists in the dictionnary. If it exists, I just assign the value. If not, I add the key and then assign the value, like so :

Dictionary<string, Dictionary<string, Dictionary<string, string>>> postsDictionnary= new();

foreach(var post in postsList)
{
    if (postsDictionnary.ContainsKey(post.postId))
    {
        if (postsDictionnary[post.postId].ContainsKey(post.language))
        {
            if (postsDictionnary[post.itemId][post.language].ContainsKey(post.name))
            {
                postsDictionnary[post.itemId][post.language][post.name] = post.value;
            }
            else
            {
                postsDictionnary[post.postId][post.language].Add(post.name, post.value);
            }
        }
        else
        {
            postsDictionnary[post.postId].Add(post.language, new Dictionary<string, string>());
            postsDictionnary[post.postId][post.language].Add(post.name, post.value);
        }
    }
    else
    {
        postsDictionnary.Add(post.postId, new Dictionary<string, Dictionary<string, string>>());
        postsDictionnary[post.postId].Add(post.language, new Dictionary<string, string>());
        postsDictionnary[post.postId][post.language].Add(post.name, post.value);
    }
}

postsDictionnary is quite well organized, but is there a better way to achieve this ? It seems pretty dirty to create a dictionnary of dictionnary of dictionnary of string…

Tags:

2

Answers


  1. 0

    I think you need to convert this DataTable into JSON. For that you use can Newtonsoft Json Library in .NET and serialize the data to a valid JSON.

    Something like this:

    string result;
result = JsonConvert.SerializeObject(dt); //where dt is content of DataTable

    For more info Newtonsoft Json

    Login or Signup to reply.
  2. 0

    Why not create a class that represent that object?

    internal class Post
{
    public int PostId { get; set; }
    public string Language { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

    You query will then return a list of these object that you can query by PostId, Language using LINQ.

    Depending of what you want to do in your app you might even aggregate these object, or transform the data in different data structure to have better performance.

    To convert the result of your SQL query into object, you can use an ORM. For this example you might want to use a micro ORM like dapper who will see this link https://www.learndapper.com/dapper-query/selecting-multiple-rows

    using (var connection = new SqlConnection(connectionString))
{
    var sql = "SELECT * FROM yourTable";
    var posts = await connection.QueryAsync<Post>(sql);
    
    foreach(var post in posts)
    {
        Console.WriteLine($"{post.PostId} {post.Language} {post.Name} {post.Value}");
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search