Customized JSON data from datatable in c# asp.net webforms

Question

I am currently trying to feed the cal-heatmap js with a json file in the project, with manual data entry it works fine, however I am failing to get the datatable converted to json that actually is in need, format which I am currently getting from the below code is given below

public void ConvertDataTabletoString()
{
    DataTable dtjson = new DataTable();
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter("select Date, count(id) as co from volunteer Group BY Date Order by Date", con);
    da.Fill(dtjson);
    con.Close();
    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;
    foreach (DataRow dr in dtjson.Rows)
    {
        row = new Dictionary<string, object>();

        foreach (DataColumn col in dtjson.Columns)
        {

                row.Add(col.ColumnName, dr[col]);
        }
        rows.Add(row);
    }

It is generating JSON format like this

{"Date":"2/15/2016","co":8},
{"Date":"2/24/2016","co":2},
{"Date":"2/25/2016","co":1},
{"Date":"2/6/2016","co":1},
{"Date":"2/7/2016","co":4},
{"Date":"2/8/2016","co":8},
{"Date":"3/19/2016","co":17},
{"Date":"3/21/2016","co":1}

But I want the data to be in following format with no column names and multiple brackets

{ 
"2/15/2016": 20,
"2/24/2016": 40
}

I do not mind if you can even go ahead and do it in text file too, rather than using JSON serialization

You can use JSON.parse(obj) in javascript to deserialize. And why do u use the JavascriptSerializer and not use that? — José Francisco, Apr 05 '16 at 13:55
@stuartd and how do you save it to a file in directory ? am failing to get this done despite using standard options, how do you write to a file ? — santhoshkumar B, Apr 05 '16 at 13:59
@JoséFrancisco Hi, Thanks for the reply, so you mean i can go ahead and use the same result set via JSON.parse(obj) ? can you temme how i can do in this case ? Thank you — santhoshkumar B, Apr 05 '16 at 14:01
Try do this: var ts = JSON.parse(objJson); var val = ts["Date"]; — José Francisco, Apr 05 '16 at 14:06
@JoséFrancisco but i am actually referencing the data to the json file itself, i have prepared plunkr for this and you can see in js i am referencing thomas.json file, which has the values in exact format it needs...http://plnkr.co/edit/YcSEWPZ8KfG8BLbzWWgr?p=preview pls suggest changes ..i thought i could just write the datatable results to a json file and let it get updated(overwritten everyday) wid new data. — santhoshkumar B, Apr 05 '16 at 14:10
Your answer... http://stackoverflow.com/questions/2546138/deserializing-json-data-to-c-sharp-using-json-net — José Francisco, Apr 05 '16 at 14:37
@JoséFrancisco what it asks for is deserialization of serialized json, but why ? i dont think i will have to deserialize, its just entering the data to a file and then referencing that file to jquery function, am i right ? correct me if am wrong — santhoshkumar B, Apr 05 '16 at 14:44

Sami · Accepted Answer · 2016-04-07T11:33:24.233

You have two options

For general case => any much columns you have and you don't need any column names then the valid data you can have could be like following, (list of lists)

[ 
    [row1col1value,row1col2value,row1col3value]
    [row2col1value,row2col2value,row2col3value]
]

You can get it like following

    List<List<string>> rows = new List<List<string>>();
    foreach (DataRow dr in dtjson.Rows)
    {
        List<string> row = new List<string>();

        foreach (DataColumn col in dtjson.Columns)
        {    
              row.Add(dr[col].toString());
        }
        rows.Add(row);
    }

If you need exactly what you wrote as sample then its purely your specific need, its not a general practice and following code is only for you, It shall give you the exact format you require i.e { "2/15/2016": 20, "2/24/2016": 40 }

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

    foreach (DataRow dr in dtjson.Rows)
    {   
        rows.[dtjson.col[0].toString()], dr[dtjson.col[1]]);
        rows.Add(row);
    }

Edit : OP's Contribution

foreach (DataRow drp in dtjso.Rows) 
{
    DateTime dat = Convert.ToDateTime(drp["Date"]);
    int epo = epoch(dat);
    string check = Convert.ToString(drp["co"]);
    string abc = string.Format("\"{0}\": {1},", epo, check);
    sb.Append(abc); 
}

Hi @Sami I did something like this foreach (DataRow drp in dtjso.Rows) { DateTime dat = Convert.ToDateTime(drp["Date"]); int epo = epoch(dat); string check = Convert.ToString(drp["co"]); string abc = string.Format("\"{0}\": {1},", epo, check); sb.Append(abc); } — santhoshkumar B, Apr 07 '16 at 07:55
Good. Actually you directly produced the required json string. That's fine. And the list produced by my code also would respond same to client (jquery). It is returned as json as well — Sami, Apr 07 '16 at 11:37

Customized JSON data from datatable in c# asp.net webforms

1 Answers1