CODEWITHSUNDEEP
c#jsonasp.net-mvcserializationjson.net
Peter Domonkoš
Peter Domonkoš

Reputation: 35

How to make JSON from a DataTable with variables as Key

I have a query from a database with many rows stored in a DataTable.

I have 40 ID's but each ID has 300 rows. The data looks like this:

DataTable data

I want to serialize this from the DataTable as JSON with ID and F_SD as a key.

After serializing the DataTable I get JSON looking like this:

undesirable JSON

Can anyone help me with this? I will use this data at frontend for a js graph. I want something like:

{
    "ID": {
        "f_sd1": {
            "value": 1.555
        }
        "f_sd2": {
            "value": xxxx
        }
    }
    "nextID": {
        "f_sd1": {
            "value": 1.555
        }
        "f_sd2": {
            "value": xxxx
        }
    }
}

I am serializing it like this right now: 

using (con = new OracleConnection(constr))
{
    using (cmd = new OracleCommand(query, con))
    {
        con.Open();

        OracleDataAdapter sda = new OracleDataAdapter(cmd);
        sda.Fill(dt1);                    
        var list = JsonConvert.SerializeObject(dt1, Formatting.None, new JsonSerializerSettings() {
            ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
        });

        return Content(list, "application/json");                    
    }
}

Upvotes: 3

Views: 574

Answers (4)

Brian Rogers
Brian Rogers

Reputation: 129707

You can use Linq methods to group the rows of your DataTable into an anonymous object structure and then serialize that:

    var obj = dt1.Rows
        .Cast<DataRow>()
        .GroupBy(row => (int)row["ID"])
        .ToDictionary(g => g.Key,
                      g => g.ToDictionary(row => (int)row["F_SD"],
                                          row => new { value = (decimal)row["VAL"] }));

    string json = JsonConvert.SerializeObject(obj, Formatting.Indented);

    return Content(json, "application/json");

Fiddle: https://dotnetfiddle.net/zWIUZ5

Upvotes: 2

mendy
mendy

Reputation: 127

try:

using Newtonsoft.Json.JsonWriter

 using (JsonWriter writer = new JsonTextWriter(sw))
 {
     for(int i = 0; i < table.Rows.Count; )
     {
        writer.WritePropertyName(table.Rows[i]["ID"]);
        writer.WriteStartObject();
        DataRow[] idsMatch = table.Select("ID = "+table.Rows[i]["ID"])+"");]

        foreach(DataRow row in idsMatch)
        {
          writer.WritePropertyName("f_sd"+row["F_SD"]);
          writer.WriteStartObject();
          writer.WritePropertyName("value");
          writer.WriteValue(row["VAL"]);
          writer.WriteEndObject();
          i++;
        }

        writer.WriteEndObject();
     }
   }

Upvotes: 2

Mdyahiya
Mdyahiya

Reputation: 177

Are you taking about list? If so Create your DataModel like this

 public class DataModel
        {
            public string ID { get; set; }
            public List<string> fid{ get; set; }
            public string Value { get; set; }      
        }

Then Serialize your Object using NewtonSoft Json

Newtonsoft.Json.JsonConvert.SerializeObject(DataMOdel))

Upvotes: 0

ARTAV
ARTAV

Reputation: 102

1- Make a dictionary object from your data table. 2- Add newtonsoft package with nuget by 'Install-Package Newtonsoft.Json' 3- Then use JsonConvert.SerializeObject method like this:

JsonConvert.SerializeObject(dictionaryObject);

Upvotes: 0

Related Questions