How to convert DataTable values to json using C#?

Question

I have a DataTable with 4 Columns. (ID,TestName,TestResult,IsValidRequest). I want to transform TestName & Testresult into json.

enter image description here

string json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);

Current JSON in variable 'json':

[  
   {  
      "TestID":1,
      "TestName":"wifi_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":7,
      "TestName":"mic_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":8,
      "TestName":"video_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":12,
      "TestName":"touch_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":14,
      "TestName":"proximity_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":15,
      "TestName":"vibrator_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":20,
      "TestName":"power_key",
      "TestResult":"2",
      "IsValidRequest":1
   },
   {  
      "TestID":21,
      "TestName":"accelerometer",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":22,
      "TestName":"earphone",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":11,
      "TestName":"memory_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":29,
      "TestName":"memory_internalSD",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":30,
      "TestName":"memory_internalSDSize",
      "TestResult":"25.0GB",
      "IsValidRequest":1
   },
   {  
      "TestID":31,
      "TestName":"memory_externalSD",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":32,
      "TestName":"memory_externalSDSize",
      "TestResult":"",
      "IsValidRequest":1
   },
   {  
      "TestID":33,
      "TestName":"memory_internalflash",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":34,
      "TestName":"memory_internalflashSize",
      "TestResult":"2.0GB",
      "IsValidRequest":1
   },
   {  
      "TestID":35,
      "TestName":"memory_ram",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":36,
      "TestName":"memory_ramsize",
      "TestResult":"2.0GB",
      "IsValidRequest":1
   },
   {  
      "TestID":13,
      "TestName":"lcd_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":28,
      "TestName":"lcd_broken",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":37,
      "TestName":"key_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":16,
      "TestName":"vol_key_up",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":17,
      "TestName":"vol_key_down",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":18,
      "TestName":"menu_key",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":10,
      "TestName":"headset_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":38,
      "TestName":"headset_leftearphone",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":39,
      "TestName":"headset_rightearphone",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":27,
      "TestName":"camera_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":40,
      "TestName":"camera_cameracount",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":5,
      "TestName":"camera_frontcam",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":6,
      "TestName":"camera_backcam",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":2,
      "TestName":"battery_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":45,
      "TestName":"battery_type",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":41,
      "TestName":"battery_level",
      "TestResult":"83",
      "IsValidRequest":1
   },
   {  
      "TestID":42,
      "TestName":"battery_status",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":43,
      "TestName":"battery_voltage",
      "TestResult":"4204",
      "IsValidRequest":1
   },
   {  
      "TestID":44,
      "TestName":"battery_temperature",
      "TestResult":"310",
      "IsValidRequest":1
   },
   {  
      "TestID":3,
      "TestName":"battery_accharging",
      "TestResult":"0",
      "IsValidRequest":1
   },
   {  
      "TestID":4,
      "TestName":"battery_usbcharging",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":9,
      "TestName":"audio_result",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":19,
      "TestName":"home_key",
      "TestResult":"1",
      "IsValidRequest":1
   },
   {  
      "TestID":46,
      "TestName":"back_key",
      "TestResult":"1",
      "IsValidRequest":1
   }
]

Required JSON:

{  
   "wifi_result":"1",
   "mic_result":"1",
   "video_result":"1",
   "touch_result":"1",
   "proximity_result":"1",
   "vibrator_result":"1",
   "power_key":"2",
   "accelerometer":"0",
   "earphone":"1",
   "memory_result":"1",
   "memory_internalSD":"1",
   "memory_internalSDSize":"25.0GB",
   "memory_externalSD":"0",
   "memory_externalSDSize":"",
   "memory_internalflash":"1",
   "memory_internalflashSize":"2.0GB",
   "memory_ram":"1",
   "memory_ramsize":"2.0GB",
   "lcd_result":"1",
   "lcd_broken":"1",
   "key_result":"1",
   "vol_key_up":"1",
   "vol_key_down":"0",
   "menu_key":"1",
   "headset_result":"1",
   "headset_leftearphone":"1",
   "headset_rightearphone":"0",
   "camera_result":"1",
   "camera_cameracount":"1",
   "camera_frontcam":"0",
   "camera_backcam":"0",
   "battery_result":"1",
   "battery_type":"1",
   "battery_level":"83",
   "battery_status":"1",
   "battery_voltage":"4204",
   "battery_temperature":"310",
   "battery_accharging":"0",
   "battery_usbcharging":"1",
   "audio_result":"1",
   "home_key":"1",
   "back_key":"1"
}

Please suggest any solution?

You should rephrase the question - Json.NET already serializes DataTables but you actually want to transform your datatable to something else before serialization. Essentially, you are *pivoting* the datatable — Panagiotis Kanavos, Jul 13 '15 at 12:30

score 3 · Accepted Answer · answered Jul 13 '15 at 12:21

Convert the DataTable to Dictionary first, then serialize to json.

Dictionary<string, string> dict = 
    dt.AsEnumerable()
    .ToDictionary<DataRow, string, string>(row => row.Field<string>(1),
                                           row => row.Field<string>(2));

string json = Newtonsoft.Json.JsonConvert.SerializeObject(dict);

Tamir · Answer 2 · 2015-07-13T13:41:33.020

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

foreach (DataRow row in dt.Rows)
{
    var testName = (string)row["TestName"];
    var testResult = (string)row["TestResult"];

    if (!tests.ContainsKey(testName))
        tests.Add(testName, testResult);
}

string json = Newtonsoft.Json.JsonConvert.SerializeObject(tests);

you will put the relevant values in a dictionary when the keys are the new "properties" and the values are the desired values of the JSON output. when serializing, the JSON keys and values will be taken from the dictionary key value pairs.

The ContainsKey check is important because you don't have validation to ensure each test appear only once. If more than a single record have the same test when creating the dictionary you, it will throw ArgumentException

score 0 · Answer 3 · answered Jul 13 '15 at 12:23

Try:

        Dictionary<string, object> data = new Dictionary<string, object>();
        for (int i= 0; i < dt.Rows.Count; ++i)
            data.Add(dt.Rows[i]["TestName"].ToString(), dt.Rows[i]["TestResult"]);
        string json = Newtonsoft.Json.JsonConvert.SerializeObject(data);

How to convert DataTable values to json using C#?

3 Answers3