Converti datatable in JSON in C #

  1. Voglio ottenere record dal database in un DataTable .
  2. Quindi convertire DataTable in un object JSON.
  3. Restituisce l’object JSON alla mia funzione JavaScript.

Io uso questo codice chiamando:

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

Per convertire un DataTable in JSON, funziona correttamente e restituire quanto segue:

 { "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 } } 

Ma mi piacerebbe che restituisse quanto segue:

 {"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 } ]} 

Come posso fare questo?

Questo frammento di codice da Convert Datatable a JSON String in C #, VB.NET potrebbe aiutarti. Utilizza System.Web.Script.Serialization.JavaScriptSerializer per serializzare i contenuti in formato JSON:

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

Possiamo eseguire l’attività in due modi semplici: uno sta utilizzando la DLL di Json.NET e un’altra è usando la class StringBuilder.

Utilizzando Newtonsoft Json.NET

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

Link di riferimento : – Newtonsoft: Converti DataTable in object JSON in ASP.Net C #

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

Questo ha un approccio simile alla risposta accettata, ma usa LINQ per convertire datatable in una singola riga di codice.

 //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() .Select(c => new KeyValuePair(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); 

Questo è un modo incredibilmente utile per enumerare un datatable, che normalmente richiederebbe un sacco di codice! Ecco alcune varianti:

 //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("AGE")>21) .Select(r => r.Field("FULLNAME")) ); 

Un modo alternativo senza usare il serializzatore javascript:

  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]", ""); } 

Puoi usare la stessa modalità specificata da Alireza Maddah e se vuoi usare due tabelle di dati in un array di json seguire è la seguente:

 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> rows = new List>(); Dictionary row; foreach (DataRow dr in dt.Rows) { row = new Dictionary(); 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 row1; foreach (DataRow dr in dt1.Rows) //use the old variable rows only { row1 = new Dictionary(); 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); } } } 

Lo stesso modo può essere usato per tutte le tabelle di dati che vuoi.

Converti datatable in JSON usando C # .net

  public static object DataTableToJSON(DataTable table) { var list = new List>(); foreach (DataRow row in table.Rows) { var dict = new Dictionary(); foreach (DataColumn col in table.Columns) { dict[col.ColumnName] = (Convert.ToString(row[col])); } list.Add(dict); } JavaScriptSerializer serializer = new JavaScriptSerializer(); return serializer.Serialize(list); } 

Per accedere al valore databile della conversione nel metodo Json, seguire i seguenti passaggi:

 $.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 + "
" + "Latitude: " + jsondata.lat); }); }, error: function (XHR, errStatus, errorThrown) { var err = JSON.parse(XHR.responseText); errorMessage = err.Message; alert(errorMessage); } });

Prova questa funzione personalizzata.

  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; } 
 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(); } 
 //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 ColumnNames { get; set; } public List DataTypes { get; set; } public List 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(); transfer.ColumnNames = new List(); foreach (DataColumn c in dt.Columns) { transfer.ColumnNames.Add(c.ColumnName); transfer.DataTypes.Add(c.DataType.ToString()); } transfer.Data = new List(); 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(Response); if (transfer.Error == null) { DataTable dt = transfer.GetDataTable(); dbGrid.ItemsSource = dt.DefaultView; } else MessageBox.Show(transfer.Error.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); 

Passare il databile a questo metodo restituirebbe 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; } 

Ho una semplice funzione per convertire datatable in stringa json.

Ho usato Newtonsoft per generare stringhe. Non uso Newtonsoft per serializzare completamente Datatable. Stai attento a questo.

Forse questo può essere utile.

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

Con Cinchoo ETL , una libreria open source, puoi esportare DataTable in JSON con poche righe di codice

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

Produzione:

 {"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" } ]}