CODEWITHSUNDEEP
c#json
Alfaromeo
Alfaromeo

Reputation: 379

SQL Data to JSON C#

LIST  Time   Status
  A     22:05   0
  B     22:10   1
  C     22:30   1
  A     22:40   0
  C     22:50   1
  B     22:60   1

The above table needs to be converted to below JSON format

[

{ "name": "A",
data: [ [22:05,0], 
        [22:40,0]
      ]
},
{ "name": "B",
data: [ [22:10,1], 
        [22:60,1]
      ]
}
{ "name": "C",
data: [ [22:30,1], 
        [22:50,1]
      ]
 }
]

The above is in a DataTable , now need to format the JSON,the below code does not give me in the same format.

  List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> row = null;

        foreach (DataRow dr in dt.Rows)
        {
            row = new Dictionary<string, object>();
            foreach (DataColumn col in dt.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }

Upvotes: 0

Views: 106

Answers (1)

jensendp
jensendp

Reputation: 2135

I would suggest reading the data from your table and creating a custom class to represent the data that needs to be serialized. You data would be represented in a class that looks like this:

public class MyType {
    public string Name {get;set;}
    public List<List<string>> Data {get;set;}
}

Then you would need a method to parse through your DataTable. Something like this might do it:

public List<MyType> ParseTable(DataTable dt) {
   var myTypes = new List<MyType>();
   var dictionary = new Dictionary<string, List<List<string>>>();

   foreach(DataRow dr in dt.Rows) {
      var name = dr[0];
      var time = dr[1];
      var status = dr[2]; 
      if(!dictionary.ContainsKey(dr[0]) {
        dictionary[name] = new List<List<string>>();
      }

      dictionary[name].Add(new List<string>{time, status});
   }

   foreach(var key = dictionary.Keys) {
      myTypes.Add(new MyType {Name = key, Data = dictionary[key]});
   }

   return myTypes;
}

Then use a JSON serializer like http://james.newtonking.com/json to handle the actual serialization of your object into JSON.

That would look something like this:

public string Serialize(List<MyType> myTypes) {
   return JsonConvert.SerializeObject(myTypes);
}

Please understand that this is freehand and off the cuff. So it may not be optimal and may need some tweaking. But this should get you where you are trying to go.

Upvotes: 1

Related Questions