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
– José Francisco, Commented Apr 5, 2016 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
– santhoshkumar B, Commented Apr 5, 2016 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
– santhoshkumar B, Commented Apr 5, 2016 at 14:01
Try do this: var ts = JSON.parse(objJson); var val = ts["Date"]; — José Francisco
– José Francisco, Commented Apr 5, 2016 at 14:06

Sami · Accepted Answer · 2016-04-07 11:33:24Z

1

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

edited Apr 7, 2016 at 11:33

answered Apr 5, 2016 at 15:17

Sami

8,4599 gold badges72 silver badges104 bronze badges

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

2 Comments

santhoshkumar B Over a year ago

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

Sami Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related