Convert datatable to JSON in C#
This code snippet from Convert Datatable to JSON String in C#, VB.NET might help you. It uses System.Web.Script.Serialization.JavaScriptSerializer to serialize the contents to JSON format:
public string ConvertDataTabletoString()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Initial Catalog=master;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select title=City,lat=latitude,lng=longitude,description from LocationDetails", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(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);
}
}
}
We can accomplish the task in two simple way one is using Json.NET dll and another is by using StringBuilder class.
Using Newtonsoft Json.NET
string JSONresult;
JSONresult = JsonConvert.SerializeObject(dt);
Response.Write(JSONresult);
Reference Link: Newtonsoft: Convert DataTable to JSON object in ASP.Net C#
Using StringBuilder
public string DataTableToJsonObj(DataTable dt)
{
DataSet ds = new DataSet();
ds.Merge(dt);
StringBuilder JsonString = new StringBuilder();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
JsonString.Append("[");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
JsonString.Append("{");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (j < ds.Tables[0].Columns.Count - 1)
{
JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\",");
}
else if (j == ds.Tables[0].Columns.Count - 1)
{
JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\"");
}
}
if (i == ds.Tables[0].Rows.Count - 1)
{
JsonString.Append("}");
}
else
{
JsonString.Append("},");
}
}
JsonString.Append("]");
return JsonString.ToString();
}
else
{
return null;
}
}
This has similar approach to the accepted answer, but uses LINQ to convert datatable to list in a single line of code.
//convert datatable to list using LINQ. Input datatable is "dt", returning list of "name:value" tuples
var lst = dt.AsEnumerable()
.Select(r => r.Table.Columns.Cast<DataColumn>()
.Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])
).ToDictionary(z=>z.Key,z=>z.Value)
).ToList();
//now serialize it
var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
return serializer.Serialize(lst);
This is an incredibly useful way to enumerate a datatable, which would normally take a ton of coding! Here are some variations:
//convert to list with array of values for each row
var list1 = dt.AsEnumerable().Select(r => r.ItemArray.ToList()).ToList();
//convert to list of first column values only
var list2 = dt.AsEnumerable().Select(r => r.ItemArray[0]).ToList();
// parse a datatable with conditions and get CSV string
string MalesOver21 = string.Join(",",
dt.AsEnumerable()
.Where(r => r["GENDER"].ToString()=="M" && r.Field<int>("AGE")>21)
.Select(r => r.Field<string>("FULLNAME"))
);
This is off topic to the original question but for completeness sake, I'd mention that if you just want to filter out rows from an existing datatable, See this answer