Convert data to JSON in C #

Question

• I want to get records from a database in a DataTable .
• Then convert the DataTable to a JSON object.
• Return the JSON object in my JavaScript function.

I use this code by calling:

 string result = JsonConvert.SerializeObject(DatatableToDictionary(queryResult, "Title"), Newtonsoft.Json.Formatting.Indented); 

To convert a DataTable to JSON, it works correctly and returns the following:

 { "1": { "viewCount": 703, "clickCount": 98 }, "2": { "viewCount": 509, "clickCount": 85 }, "3": { "viewCount": 578, "clickCount": 86 }, "4": { "viewCount": 737, "clickCount": 108 }, "5": { "viewCount": 769, "clickCount": 130 } } 

But I would like it to return the following:

 {"records":[ { "Title": 1, "viewCount": 703, "clickCount": 98 }, { "Title": 2, "viewCount": 509, "clickCount": 85 }, { "Title": 3, "viewCount": 578, "clickCount": 86 }, { "Title": 4, "viewCount": 737, "clickCount": 108 }, { "Title": 5, "viewCount": 769, "clickCount": 130 } ]} 

How can i do this?

Answer 1

This code snippet from Converting Datatable to JSON String in C #, VB.NET can help you. It uses System.Web.Script.Serialization.JavaScriptSerializer to serialize JSON content:

 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); } } } 

Answer 2

We can complete the task in two simple ways: one uses the Json.NET dll, and the other uses the StringBuilder class.

Using Newtonsoft Json.NET

 string JSONresult; JSONresult = JsonConvert.SerializeObject(dt); Response.Write(JSONresult); 

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; } } 

Answer 3

This is similar to the accepted answer, but uses LINQ to convert data to a 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 data, which usually takes a lot of code! Here are a few options:

 //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 the sake of completeness, I would mention that if you just want to filter rows from an existing data table, see this answer.

Answer 4

Alternative way without using javascript serializer:

  public static string DataTableToJSON(DataTable Dt) { string[] StrDc = new string[Dt.Columns.Count]; string HeadStr = string.Empty; for (int i = 0; i < Dt.Columns.Count; i++) { StrDc[i] = Dt.Columns[i].Caption; HeadStr += "\"" + StrDc[i] + "\":\"" + StrDc[i] + i.ToString() + "¾" + "\","; } HeadStr = HeadStr.Substring(0, HeadStr.Length - 1); StringBuilder Sb = new StringBuilder(); Sb.Append("["); for (int i = 0; i < Dt.Rows.Count; i++) { string TempStr = HeadStr; for (int j = 0; j < Dt.Columns.Count; j++) { TempStr = TempStr.Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[i][j].ToString().Trim()); } //Sb.AppendFormat("{{{0}}},",TempStr); Sb.Append("{"+TempStr + "},"); } Sb = new StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1)); if(Sb.ToString().Length>0) Sb.Append("]"); return StripControlChars(Sb.ToString()); } //To strip control characters: //A character that does not represent a printable character but //serves to initiate a particular action. public static string StripControlChars(string s) { return Regex.Replace(s, @"[^\x20-\x7F]", ""); } 

Answer 5

You can use the same as stated in Alireza Maddah, and if you want to use two data tables in the same json array, follow as follows:

 public string ConvertDataTabletoString() { DataTable dt = new DataTable(); DataTable dt1 = 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); } SqlCommand cmd1 = new SqlCommand("_another_query_", con); SqlDataAdapter da1 = new SqlDataAdapter(cmd1); da1.Fill(dt1); System.Web.Script.Serialization.JavaScriptSerializer serializer1 = new System.Web.Script.Serialization.JavaScriptSerializer(); Dictionary<string, object> row1; foreach (DataRow dr in dt1.Rows) //use the old variable rows only { row1 = new Dictionary<string, object>(); foreach (DataColumn col in dt1.Columns) { row1.Add(col.ColumnName, dr[col]); } rows.Add(row1); // Finally You can add into old json array in this way } return serializer.Serialize(rows); } } } 

In the same way, you can use as many data tables as you want.

Answer 6

Convert datatable to JSON using C # .net

  public static object DataTableToJSON(DataTable table) { var list = 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])); } list.Add(dict); } JavaScriptSerializer serializer = new JavaScriptSerializer(); return serializer.Serialize(list); } 

Answer 7

Try this custom feature.

  public static string DataTableToJsonObj(DataTable dt) { DataSet ds = new DataSet(); ds.Merge(dt); StringBuilder jsonString = new StringBuilder(); if (ds.Tables[0].Rows.Count > 0) { jsonString.Append("["); for (int rows = 0; rows < ds.Tables[0].Rows.Count; rows++) { jsonString.Append("{"); for (int cols = 0; cols < ds.Tables[0].Columns.Count; cols++) { jsonString.Append(@"""" + ds.Tables[0].Columns[cols].ColumnName + @""":"); /* //IF NOT LAST PROPERTY if (cols < ds.Tables[0].Columns.Count - 1) { GenerateJsonProperty(ds, rows, cols, jsonString); } //IF LAST PROPERTY else if (cols == ds.Tables[0].Columns.Count - 1) { GenerateJsonProperty(ds, rows, cols, jsonString, true); } */ var b = (cols < ds.Tables[0].Columns.Count - 1) ? GenerateJsonProperty(ds, rows, cols, jsonString) : (cols != ds.Tables[0].Columns.Count - 1) || GenerateJsonProperty(ds, rows, cols, jsonString, true); } jsonString.Append(rows == ds.Tables[0].Rows.Count - 1 ? "}" : "},"); } jsonString.Append("]"); return jsonString.ToString(); } return null; } private static bool GenerateJsonProperty(DataSet ds, int rows, int cols, StringBuilder jsonString, bool isLast = false) { // IF LAST PROPERTY THEN REMOVE 'COMMA' IF NOT LAST PROPERTY THEN ADD 'COMMA' string addComma = isLast ? "" : ","; if (ds.Tables[0].Rows[rows][cols] == DBNull.Value) { jsonString.Append(" null " + addComma); } else if (ds.Tables[0].Columns[cols].DataType == typeof(DateTime)) { jsonString.Append(@"""" + (((DateTime)ds.Tables[0].Rows[rows][cols]).ToString("yyyy-MM-dd HH':'mm':'ss")) + @"""" + addComma); } else if (ds.Tables[0].Columns[cols].DataType == typeof(string)) { jsonString.Append(@"""" + (ds.Tables[0].Rows[rows][cols]) + @"""" + addComma); } else if (ds.Tables[0].Columns[cols].DataType == typeof(bool)) { jsonString.Append(Convert.ToBoolean(ds.Tables[0].Rows[rows][cols]) ? "true" : "fasle"); } else { jsonString.Append(ds.Tables[0].Rows[rows][cols] + addComma); } return true; } 

Answer 8

To access the converted datatable value in the Json method, follow these steps:

 $.ajax({ type: "POST", url: "/Services.asmx/YourMethodName", 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); } }); 

Answer 9

With Cinchoo ETL , an open source library, you can easily export a DataTable to JSON with just a few lines of code

 StringBuilder sb = new StringBuilder(); string connectionstring = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True"; using (var conn = new SqlConnection(connectionstring)) { conn.Open(); var comm = new SqlCommand("SELECT * FROM Customers", conn); SqlDataAdapter adap = new SqlDataAdapter(comm); DataTable dt = new DataTable("Customer"); adap.Fill(dt); using (var parser = new ChoJSONWriter(sb)) parser.Write(dt); } Console.WriteLine(sb.ToString()); 

Exit:

 { "Customer": [ { "CustomerID": "ALFKI", "CompanyName": "Alfreds Futterkiste", "ContactName": "Maria Anders", "ContactTitle": "Sales Representative", "Address": "Obere Str. 57", "City": "Berlin", "Region": null, "PostalCode": "12209", "Country": "Germany", "Phone": "030-0074321", "Fax": "030-0076545" }, { "CustomerID": "ANATR", "CompanyName": "Ana Trujillo Emparedados y helados", "ContactName": "Ana Trujillo", "ContactTitle": "Owner", "Address": "Avda. de la Constitución 2222", "City": "México DF", "Region": null, "PostalCode": "05021", "Country": "Mexico", "Phone": "(5) 555-4729", "Fax": "(5) 555-3745" } ] } 

Answer 10

 public static string ConvertIntoJson(DataTable dt) { var jsonString = new StringBuilder(); if (dt.Rows.Count > 0) { jsonString.Append("["); for (int i = 0; i < dt.Rows.Count; i++) { jsonString.Append("{"); for (int j = 0; j < dt.Columns.Count; j++) jsonString.Append("\"" + dt.Columns[j].ColumnName + "\":\"" + dt.Rows[i][j].ToString().Replace('"','\"') + (j < dt.Columns.Count - 1 ? "\"," : "\"")); jsonString.Append(i < dt.Rows.Count - 1 ? "}," : "}"); } return jsonString.Append("]").ToString(); } else { return "[]"; } } public static string ConvertIntoJson(DataSet ds) { var jsonString = new StringBuilder(); jsonString.Append("{"); for (int i = 0; i < ds.Tables.Count; i++) { jsonString.Append("\"" + ds.Tables[i].TableName + "\":"); jsonString.Append(ConvertIntoJson(ds.Tables[i])); if (i < ds.Tables.Count - 1) jsonString.Append(","); } jsonString.Append("}"); return jsonString.ToString(); } 

Answer 11

 //Common DLL client, server public class transferDataTable { public class myError { public string Message { get; set; } public int Code { get; set; } } public myError Error { get; set; } public List<string> ColumnNames { get; set; } public List<string> DataTypes { get; set; } public List<Object> Data { get; set; } public int Count { get; set; } } public static class ExtensionMethod { public static transferDataTable LoadData(this transferDataTable transfer, DataTable dt) { if (dt != null) { transfer.DataTypes = new List<string>(); transfer.ColumnNames = new List<string>(); foreach (DataColumn c in dt.Columns) { transfer.ColumnNames.Add(c.ColumnName); transfer.DataTypes.Add(c.DataType.ToString()); } transfer.Data = new List<object>(); foreach (DataRow dr in dt.Rows) { foreach (DataColumn col in dt.Columns) { transfer.Data.Add(dr[col] == DBNull.Value ? null : dr[col]); } } transfer.Count = dt.Rows.Count; } return transfer; } public static DataTable GetDataTable(this transferDataTable transfer, bool ConvertToLocalTime = true) { if (transfer.Error != null || transfer.ColumnNames == null || transfer.DataTypes == null || transfer.Data == null) return null; int columnsCount = transfer.ColumnNames.Count; DataTable dt = new DataTable(); for (int i = 0; i < columnsCount; i++ ) { Type colType = Type.GetType(transfer.DataTypes[i]); dt.Columns.Add(new DataColumn(transfer.ColumnNames[i], colType)); } int index = 0; DataRow row = dt.NewRow(); foreach (object o in transfer.Data) { if (ConvertToLocalTime && o != null && o.GetType() == typeof(DateTime)) { DateTime dat = Convert.ToDateTime(o); row[index] = dat.ToLocalTime(); } else row[index] = o == null ? DBNull.Value : o; index++; if (columnsCount == index) { index = 0; dt.Rows.Add(row); row = dt.NewRow(); } } return dt; } } //Server [OperationContract] [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.WrappedRequest, UriTemplate = "json/data")] transferDataTable _Data(); public transferDataTable _Data() { try { using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["myConnString"])) { con.Open(); DataSet ds = new DataSet(); SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tbGalleries", con); myAdapter.Fill(ds, "table"); DataTable dt = ds.Tables["table"]; return new transferDataTable().LoadData(dt); } } catch(Exception ex) { return new transferDataTable() { Error = new transferDataTable.myError() { Message = ex.Message, Code = ex.HResult } }; } } //Client Response = Vossa.getAPI(serviceUrl + "json/data"); transferDataTable transfer = new JavaScriptSerializer().Deserialize<transferDataTable>(Response); if (transfer.Error == null) { DataTable dt = transfer.GetDataTable(); dbGrid.ItemsSource = dt.DefaultView; } else MessageBox.Show(transfer.Error.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); 

Answer 12

I have a simple function to convert data to a JSON string.

I used Newtonsoft to generate the string. I do not use Newtonsoft to fully serialize Datatable. Be careful with that.

Maybe this can be helpful.

  private string DataTableToJson(DataTable dt) { if (dt == null) { return "[]"; }; if (dt.Rows.Count < 1) { return "[]"; }; JArray array = new JArray(); foreach(DataRow dr in dt.Rows) { JObject item = new JObject(); foreach(DataColumn col in dt.Columns) { item.Add(col.ColumnName, dr[col.ColumnName]?.ToString()); } array.Add(item); } return array.ToString(Newtonsoft.Json.Formatting.Indented); } 

Answer 13

Passing data to this method will return a json String.

 public DataTable GetTable() { string str = "Select * from GL_V"; OracleCommand cmd = new OracleCommand(str, con); cmd.CommandType = CommandType.Text; DataTable Dt = OracleHelper.GetDataSet(con, cmd).Tables[0]; return Dt; } public string DataTableToJSONWithJSONNet(DataTable table) { string JSONString = string.Empty; JSONString = JsonConvert.SerializeObject(table); return JSONString; } public static DataSet GetDataSet(OracleConnection con, OracleCommand cmd) { // create the data set DataSet ds = new DataSet(); try { //checking current connection state is open if (con.State != ConnectionState.Open) con.Open(); // create a data adapter to use with the data set OracleDataAdapter da = new OracleDataAdapter(cmd); // fill the data set da.Fill(ds); } catch (Exception ex) { throw; } return ds; } 

Answer 14

try this (ExtensionMethods):

 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); } 

and use:

 DataTable dt = new DataTable(); . . . var json = dt.ToJson();