从 C# .NET 中的 DataTable 到 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2312982/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
From DataTable in C# .NET to JSON
提问by cc0
I am pretty new at C# and .NET, but I've made this code to call a stored procedure, and I then want to take the returned DataTable and convert it to JSON.
我是 C# 和 .NET 的新手，但我已经编写了这段代码来调用存储过程，然后我想获取返回的 DataTable 并将其转换为 JSON。
SqlConnection con = new SqlConnection("connection string here");
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("getDates", con);
SqlParameter par = new SqlParameter("@PlaceID", SqlDbType.Int);
par.Value = 42;
da.SelectCommand = cmd;
cmd.Parameters.Add(par);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
con.Open();
try{
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(ds);
}
My question then is what is the best/simplest way to do that? An example would be great as I'm still very new to this.
那么我的问题是最好/最简单的方法是什么？一个例子会很棒，因为我对此还是很陌生。
采纳答案by Ariel
Instead of a datatable you should use a datareader. Your code is inefficient and somewhat hard to read - you may want to do something like this:
您应该使用数据读取器而不是数据表。您的代码效率低下且难以阅读 - 您可能想要执行以下操作：
StringBuilder json = new StringBuilder();
using(SqlConnection cnn = new SqlConnection(your_connection_string))
{
cnn.open();
using(SqlCommand cmd = new SqlCommand("name_of_stored_procedure", cnn))
{
cmd.Paramters.AddWithValue("@Param", "value");
using(SqlDataReader reader = cmd.ExecuteReader())
{
while(reader.Read())
{
json.AppendFormat("{{\"name\": \"{0}\"}}", reader["name"]);
}
}
}
cnn.close();
}
you can then use json.ToStringto get the outpt
然后您可以使用它json.ToString来获取输出
回答by Dan Randolph
Thanks Ariel. Your answer was very helpful. Here is a version that builds on your answer.
谢谢阿里尔。你的回答很有帮助。这是一个以您的答案为基础的版本。
public string ReadToJson(SqlDataReader reader)
{
List<string> cols = new List<string>(10);
int ncols = reader.FieldCount;
for (int i = 0; i < ncols; ++i)
{
cols.Add(reader.GetName(i));
}
StringBuilder sbJson = new StringBuilder("[");
//process each row
while (reader.Read())
{
sbJson.Append("{");
foreach (string col in cols)
{
sbJson.AppendFormat("\"{0}\":{1}, ", col, reader[col]);
}
sbJson.Replace(", ", "},", sbJson.Length - 2, 2);
}
sbJson.Replace("},", "}]", sbJson.Length - 2, 2);
return sbJson.ToString();
}
回答by Karl Wenzel
Although the JavaScriptSerializer (System.Web.Script.Serialization.JavaScriptSerializer) cannot convert a DataTable directly into JSON, it is possible to unpack a DataTable into a List that may then be serialized.
尽管 JavaScriptSerializer (System.Web.Script.Serialization.JavaScriptSerializer) 无法将 DataTable 直接转换为 JSON，但可以将 DataTable 解包为 List，然后进行序列化。
The following function converts an arbitrary DataTable into a JSON string (without prior knowledge about field names or data types):
以下函数将任意 DataTable 转换为 JSON 字符串（无需事先了解字段名称或数据类型）：
public static string 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] = row[col];
}
list.Add(dict);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(list);
}
回答by Chris Herring
You could use the JSON.NET library: http://json.codeplex.com/to serialize/deserialize the DataTable.
您可以使用 JSON.NET 库：http://json.codeplex.com/来序列化/反序列化 DataTable。
string json = JsonConvert.SerializeObject(table);
which serializes to something like this:
序列化为这样的东西：
[ { "Column1": "Row Value", "Column2": "2" } ]
If you need to serialize more info about the DataTable e.g. column schema, primary key, table name then you could use the custom converter I wrote: https://github.com/chris-herring/DataTableConverter. Use it like this:
如果您需要序列化有关 DataTable 的更多信息，例如列架构、主键、表名，那么您可以使用我写的自定义转换器：https: //github.com/chris-herring/DataTableConverter。像这样使用它：
string json = JsonConvert.SerializeObject(table, new Serialization.DataTableConverter());
DataTable table = JsonConvert.DeserializeObject<DataTable>(json, new Serialization.DataTableConverter());
which serializes to something like this:
序列化为这样的东西：
{
"TableName": "TestTable",
"Columns": [
{
"AllowDBNull": false,
"AutoIncrement": true,
"AutoIncrementSeed": 2,
"AutoIncrementStep": 1,
"Caption": "PrimaryKey",
"ColumnName": "PrimaryKey",
"DataType": "Int32",
"DateTimeMode": "UnspecifiedLocal",
"DefaultValue": null,
"MaxLength": -1,
"Ordinal": 0,
"ReadOnly": false,
"Unique": true
}
],
"Rows": [
[
1
],
[
2
],
[
3
]
],
"PrimaryKey": ["PrimaryKey"]
}
回答by Gil Allen
Thank you Karl Wenzel. I had a problem where I could only receive data table from an old asmx webservice. Now I wrote a web page which can parse this DataTable and return it in JSON.
谢谢卡尔文策尔。我遇到了一个问题，我只能从旧的 asmx 网络服务接收数据表。现在我写了一个网页，它可以解析这个 DataTable 并以 JSON 形式返回它。
public static string DataTableToJSON(DataTable table)
{
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in table.Rows)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
dict[col.ColumnName] = row[col];
}
list.Add(dict);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(list);
}
回答by Durai Amuthan.H
An alternative way without using javascript serializer:
不使用 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());
}
//Function 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]", "");
}
回答by Roman Martynenko
I use JavaScriptSerializer + LINQ
我使用 JavaScriptSerializer + LINQ
return new JavaScriptSerializer().Serialize(
dataTable.Rows.Cast<DataRow>()
.Select(row => row.Table.Columns.Cast<DataColumn>()
.ToDictionary(col => col.ColumnName, col => row[col.ColumnName]))
.ToList()
);
