How to convert datatable to json string using json.net?

Question

How to convert datatable to json using json.net? Any suggestion... I ve downloaded the necessary binaries... Which class should i use to get the conversion of my datatable to json? Thus far used this method to get json string by passing my datatable...

public string GetJSONString(DataTable table)
    {
        StringBuilder headStrBuilder = new StringBuilder(table.Columns.Count * 5); //pre-allocate some space, default is 16 bytes
        for (int i = 0; i < table.Columns.Count; i++)
        {
            headStrBuilder.AppendFormat("\"{0}\" : \"{0}{1}¾\",", table.Columns[i].Caption, i);
        }
        headStrBuilder.Remove(headStrBuilder.Length - 1, 1); // trim away last ,

        StringBuilder sb = new StringBuilder(table.Rows.Count * 5); //pre-allocate some space
        sb.Append("{\"");
        sb.Append(table.TableName);
        sb.Append("\" : [");
        for (int i = 0; i < table.Rows.Count; i++)
        {
            string tempStr = headStrBuilder.ToString();
            sb.Append("{");
            for (int j = 0; j < table.Columns.Count; j++)
            {
                table.Rows[i][j] = table.Rows[i][j].ToString().Replace("'", "");
                tempStr = tempStr.Replace(table.Columns[j] + j.ToString() + "¾", table.Rows[i][j].ToString());
            }
            sb.Append(tempStr + "},");
        }
        sb.Remove(sb.Length - 1, 1); // trim last ,
        sb.Append("]}");
        return sb.ToString();
    }

Now i thought of using json.net but dont know where to get started....

stackoverflow.com/questions/451460/datatable-to-json

Oblio
– Oblio

2010-06-05 10:17:37 +00:00
Commented Jun 5, 2010 at 10:17 — Oblio
– Oblio, Commented Jun 5, 2010 at 10:17

Christian Specht · Accepted Answer · 2011-08-18 21:19:38Z

74

string json = JsonConvert.SerializeObject(table, Formatting.Indented);

Edit: You don't need indented formatting, of course, but it makes it nice and readable.

edited Aug 18, 2011 at 21:19

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

answered Jun 5, 2010 at 10:16

k_b

2,49018 silver badges9 bronze badges

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

2 Comments

Pascal Over a year ago

How do I iterate the resulting Json with JQuery?

raffian Over a year ago

Is there a way to convert XML hierarchical to a DataTable, flattening it out?

Madagaga · Accepted Answer · 2017-03-08 10:13:33Z

Maybe it could help

Original version

public static class DataTableToJson
{
    public static JArray ToJson(this System.Data.DataTable source)
    {
        JArray result = new JArray();
        JObject row;
        foreach (System.Data.DataRow dr in source.Rows)
        {
            row = new JObject();
            foreach (System.Data.DataColumn col in source.Columns)
            {
                row.Add(col.ColumnName.Trim(), JToken.FromObject(dr[col]));
            }
            result.Add(row);
        }
        return result;
    }
}

Edited Version

There is a intermediate step because I needed to have a dictionary

public static IEnumerable<Dictionary<string, object>> ToDictionary(this DataTable table)
{
    string[] columns = table.Columns.Cast<DataColumn>().Select(c=>c.ColumnName).ToArray();
    IEnumerable<Dictionary<string, object>>  result = table.Rows.Cast<DataRow>()
            .Select(dr => columns.ToDictionary(c => c, c=> dr[c]));
    return result;
}

You can add JsonConverter.SerializeObject(result);, or another json serializer to get json string.

This is similar to @Hasan Javaid post

Uwe Keim · Accepted Answer · 2020-05-11 15:28:11Z

0

Check this.

private static string DataTableToJson(DataTable dataTable)
{
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    var rows = (from DataRow d in dataTable.Rows
        select dataTable.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName, col => d[col])).ToList();

    rows.AddRange(from DataRow d in dataTable.Rows
        select dataTable.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName, col => d[col]));
    return serializer.Serialize(rows);
}

edited May 11, 2020 at 15:28

Uwe Keim

40.9k61 gold badges193 silver badges308 bronze badges

answered May 11, 2016 at 12:50

Hasan Javaid

1111 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to convert datatable to json string using json.net?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related