Converter datatable para JSON em c #

  1. Eu quero obter registros do database em um DataTable .
  2. Em seguida, converta o DataTable em um object JSON.
  3. Retorna o object JSON para minha function JavaScript.

Eu uso esse código chamando:

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

Para converter um DataTable em JSON, ele funciona corretamente e retorna o seguinte:

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

Mas eu gostaria que retornasse o seguinte:

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

Como posso fazer isso?

Este trecho de código de Convert Datatable para JSON String em C #, VB.NET pode ajudá-lo. Ele usa System.Web.Script.Serialization.JavaScriptSerializer para serializar o conteúdo para o 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); } } } 

Podemos realizar a tarefa de duas maneiras simples, uma usando a dll Json.NET e outra usando a class StringBuilder.

Usando o Newtonsoft Json.NET

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

Link de referência : – Newtonsoft: Converter DataTable para object JSON em ASP.Net C #

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

Isso tem uma abordagem semelhante à resposta aceita, mas usa o LINQ para converter a lista de dados em uma única linha de código.

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

Esta é uma maneira incrivelmente útil de enumerar uma tabela de dados, o que normalmente levaria uma tonelada de codificação! Aqui estão algumas variações:

 //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")) ); 

Uma maneira alternativa sem usar o serializador 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]", ""); } 

Você pode usar a mesma maneira especificada por Alireza Maddah e se vc quiser usar dois dados em uma tabela json array segue o caminho:

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

A mesma maneira pode ser usada para quantas tabelas de dados você desejar.

Converter datatable para 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); } 

Para acessar o valor da tabela de dados de conversão no método Json, siga as etapas abaixo:

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

Experimente esta function personalizada.

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

Passe o datable para este método que retornaria 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; } 

Eu tenho a function simples de converter datatable para json string.

Eu usei Newtonsoft para gerar string. Eu não uso Newtonsoft para totalmente serializar Datable. Tenha cuidado com isso.

Talvez isso possa ser útil.

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

Com o Cinchoo ETL – uma biblioteca de código aberto, você pode exportar DataTable para JSON facilmente com poucas linhas de código

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

Saída:

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