How to covert a DataTable to Json Format?

Question

I am trying to convert the DataTable that I have fetched from the database to Json format. But I am getting an error.

public string ConvertTableToJSON(DataTable objDataTable)
    {
        ArrayList columnNames = new ArrayList();
        int rowCount = objDataTable.Rows.Count;
        int currentRow = 1;

        string json = "";

        //fetching column names
        foreach (DataColumn objColumn in objDataTable.Columns)
        {
            columnNames.Add(objColumn.ColumnName);
        }

        //generating json string for each row
        foreach (DataRow objRow in objDataTable.Rows)
        {
            json = json + "{";
            json = json + ConvertRowToJSON(objRow, columnNames);
            json = json + "}";

            if (currentRow != rowCount)
            {
                json = json + ",";
            }

            currentRow = currentRow + 1;
        }

        return json;
    }

The above is the code for the conversion of the DataTable to Json format.

" Index was outside the bounds of the array. " is the error when debugging the code. This error occurs in the line

if (data[0] == '[' || data[0] == '{')

score 0 · Answer 1 · answered Jun 03 '16 at 09:42

This method is used to convert datatable to json string

public string ConvertDataTabletoJSON(DataTable dt)
{

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 dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}

It uses System.Web.Script.Serialization.JavaScriptSerializer to serialize the contents to JSON format:

score 0 · Answer 2 · answered Jun 03 '16 at 09:43

You can Convert DataTable to JSON using JavaScriptSerializer by using following code

public string DataTableToJsonWithJavaScriptSerializer(DataTable objDataTable)
{
 JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
 List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
 Dictionary<string, object> childRow;
 foreach (DataRow row in objDataTable.Rows)
 {
 childRow = new Dictionary<string, object>();
 foreach (DataColumn col in table.Columns)
 {
 childRow.Add(col.ColumnName, row[col]);
 }
 parentRow.Add(childRow);
 }
 return jsSerializer.Serialize(parentRow);
}

You can use Json.Net DLL and convert datatable to json like

public string DataTableToJsonWithJsonNet(DataTable objDataTable) 
{
   string jsonString=string.Empty;
   jsonString = JsonConvert.SerializeObject(objDataTable);
   return jsonString;
}

Include library.

Newtonsoft.Json;

How to covert a DataTable to Json Format?

2 Answers2