How to convert Datatable to JSON C#: This article explains how to convert DataTable to JSON string in Asp.net C#. ex. serialize DataTable to JSON array in C#.
For example, let’s say you have SQL database and now want to return SQL data to JSON in C# application ex. how to return JSON String from DataTable in Asp.net C#. There are 1 ways to achieve this task and that are: By using JavaScriptSerializer, By using Json.Net DLL ( newtonsoft )
public ActionResult convertjson()
{
SqlTransaction objTrans = null;
string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; // get it from Web.config file
SqlConnection objConn = new SqlConnection(strConnString);
objConn.Open();
objTrans = objConn.BeginTransaction();
SqlCommand cmd = new SqlCommand("select * from test order by id desc", objConn, objTrans);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
objTrans.Commit();
return Json(MRPLookup.DataTableToJSONWithJSONNet(dt), JsonRequestBehavior.AllowGet);
}
Now in this method we will convert our C# Datatable to JSON using the Newtonsoft DLL.
public static string DataTableToJSONWithJSONNet(DataTable table)
{
string JSONString = string.Empty;
JSONString = JsonConvert.SerializeObject(table);
return JSONString;
}
This technic explains how to convert a DataTable to JSON in ASP.NET C#. In any words how to return a JSON String from a DataTable in ASP.NET C#.
In any words, how to serialize a
DataTable to a JSON array in C#.
public string DataTableToJSONJavaScriptSerializer(DataTable table)
{
JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
List < Dictionary < string, object >> pRow = new List < Dictionary < string, object >> ();
Dictionary < string, object > childRow;
foreach(DataRow row in table.Rows)
{
childRow = new Dictionary < string, object > ();
foreach(DataColumn col in table.Columns)
{
childRow.Add(col.ColumnName, row[col]);
}
pRow.Add(childRow);
}
return jsSerializer.Serialize(pRow);
}
public static object DataTableToJSON(DataTable table)
{
var lst= new List<Dictionary<string, object>>();
foreach (DataRow row in table.Rows)
{
var dict = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
dict[col.ColumnName] = (Convert.ToString(row[col]));
}
lst.Add(dict);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(lst);
}
public static string ToJson(this DataTable dt)
{
List<Dictionary<string, object>> lst = new List<Dictionary<string, object>>();
Dictionary<string, object> item;
foreach (DataRow row in dt.Rows)
{
item = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
item.Add(col.ColumnName, (Convert.IsDBNull(row[col]) ? null : row[col]));
}
lst.Add(item);
}
return Newtonsoft.Json.JsonConvert.SerializeObject(lst);
}
$.ajax({
type: "POST",
url: "/Services.asmx/MethodName",
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
var parsed = $.parseJSON(data.d);
$.each(parsed, function (i, jsondata) {
$("#dividtodisplay").append("Title: " + jsondata.title + "<br/>" + "Latitude: " + jsondata.lat);
});
},
error: function (XHR, errStatus, errorThrown) {
var err = JSON.parse(XHR.responseText);
errorMessage = err.Message;
alert(errorMessage);
}
});
2020-02-02