SQL Data to JSON C#

Question

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

jensendp · Accepted Answer · 2014-04-16 17:10:16Z

1

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.

edited Apr 16, 2014 at 17:10

answered Apr 16, 2014 at 4:55

jensendp

2,12515 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alfaromeo Over a year ago

Thanks , I'm confused on extracting the data from the datatable above into this class .

jensendp Over a year ago

When you serialize an object into JSON, the serializer will typically turn it into a dictionary of key-value pairs. So if you want to model a JSON result into a type/class, you would choose the keys in the dictionary to be properties. Then if you have any JSON lists that you need to create, you simply use a List<> to represent those items.

Alfaromeo Over a year ago

Thanks,can you provide a sample .I got the DataTable with the data mentioned above .Need to create this strcuture .Let me know

jensendp Over a year ago

@Alfaromeo Updated my solution. I think that should help.

Alfaromeo Over a year ago

Thanks you were patient throughout and in explaining the working. The solution works like a charm . BTW the first values is date/time datatype and the second integer .How to go about making this change as I see all are in string.

|

Collectives™ on Stack Overflow

SQL Data to JSON C#

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related