JSON serialization with database in c#

Question

I need to create a json from a regular column to a json with a key. Only I don't understand how to do it in c#. I am using Postgresql database and I need to keep separate data there, and I need to pass to the server via api as I indicated below

What I got is.

{
  "id": '',
  "title": "",
  "description": "",
  "image": "",
  "question": ""
}

to

{
  "id": {
    "title": "",
    "description": "",
    "image": "",
    "question": ""
  }
}

Here's my code

[HttpGet]
public JsonResult Get(int id)
{
    string query =
        @"
        select id as ""id"", 
            title as ""title"",
            description as ""description"",
            image as ""image"",
            question as ""question""
        from card";

    DataTable dt = new DataTable();
    string sqlDataSource =
        _configuration.GetConnectionString("EmployeeAppCon");
    NpgsqlDataReader myReader;
    using (NpgsqlConnection myCon = new NpgsqlConnection(sqlDataSource))
    {
        myCon.Open();
        using (NpgsqlCommand myCommand = new NpgsqlCommand(query, myCon)
        )
        {
            myReader = myCommand.ExecuteReader();
            dt.Load (myReader);
            myReader.Close();
            myCon.Close();
        }
    }
    string temp = JsonConvert.SerializeObject(dt);

    return new JsonResult(dt);
}

Does id contain unique values?

Peter Csala
– Peter Csala

2021-11-23 15:49:07 +00:00
Commented Nov 23, 2021 at 15:49 — Peter Csala
– Peter Csala, Commented Nov 23, 2021 at 15:49
Yes it does, and is taken from the database

Note Devil
– Note Devil

2021-11-25 11:52:28 +00:00
Commented Nov 25, 2021 at 11:52 — Note Devil
– Note Devil, Commented Nov 25, 2021 at 11:52

Peter Csala · Accepted Answer · 2021-11-23 16:39:00Z

Based on your provided information I would assume that your query returns multiple records with unique ids. So, your final json might look like this:

{
  "id1": {
    "title": "",
    "description": "",
    "image": "",
    "question": ""
  },
  ...
  "idn": {
    "title": "",
    "description": "",
    "image": "",
    "question": ""
  }
}

In order to have a structure like this, you need to transform your DataTable into Dictionary<string, Data>. The Data is custom struct to store title ... question fields:

struct Data
{
   public string Title;
   public string Description;
   public string Image;
   public string Question;
}

The mapping can be written like this via LINQ:

var data = (from DataRow row in dt.Rows
            select new KeyValuePair<string, Data>
            (
                row["id"].ToString(),
                new Data
                {
                    Title = row["title"].ToString(),
                    Description = row["description"].ToString(),
                    Image = row["image"].ToString(),
                    Question = row["question"].ToString()
                }
            )).ToDictionary(pair => pair.Key, pair => pair.Value);

Finally all you need is to create a JsonSerializerSettings to specify that the output should use camel casing:

var camelCasingSettings = new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() };
var json = JsonConvert.SerializeObject(data, camelCasingSettings);

I've used the following data for testing:

var dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("title", typeof(string));
dt.Columns.Add("description", typeof(string));
dt.Columns.Add("image", typeof(string));
dt.Columns.Add("question", typeof(string));

dt.Rows.Add("1", "T1", "D1", "I1", "Q1");
dt.Rows.Add("n", "Tn", "Dn", "In", "Qn");

The output looks like this:

{
   "1":{
      "title":"T1",
      "description":"D1",
      "image":"I1",
      "question":"Q1"
   },
   "n":{
      "title":"Tn",
      "description":"Dn",
      "image":"In",
      "question":"Qn"
   }
}

persian-theme · Accepted Answer · 2021-11-23 15:10:32Z

0

Add the following classes

public class MyCalss
{
   public MyItem ID { get; set; }
}
public class MyItem
{
   public string Title { get; set; }
   public string Description { get; set; }
   public string Image { get; set; }
   public string Question { get; set; }
}

then Then add the following lines to your code

List<MyCalss> items = new List<MyCalss>();
foreach (DataRow row in dt.Rows)
{
   MyCalss item = new MyCalss();
   item.ID = new MyItem();
   item.ID.Title = row["title"].ToString();
   item.ID.Description = row["description"].ToString();
   item.ID.Image = row["image"].ToString();
   item.ID.Question = row["question"].ToString();
   items.Add(item);
}
string temp = JsonConvert.SerializeObject(items);

answered Nov 23, 2021 at 15:10

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Collectives™ on Stack Overflow

JSON serialization with database in c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related