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" tuplesvar 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 itvar 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 rowvar list1 = dt.AsEnumerable().Select(r => r.ItemArray.ToList()).ToList();//convert to list of first column values onlyvar list2 = dt.AsEnumerable().Select(r => r.ItemArray[0]).ToList();// parse a datatable with conditions and get CSV stringstring 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