C# 数据表转JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451460/
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
DataTable to JSON
提问by Joel Coehoorn
I recently needed to serialize a datatable to JSON. Where I'm at we're still on .Net 2.0, so I can't use the JSON serializer in .Net 3.5. I figured this must have been done before, so I went looking online and founda numberof differentoptions. Some of them depend on an additional library, which I would have a hard time pushing through here. Others require first converting to List<Dictionary<>>, which seemed a little awkward and needless. Another treated all values like a string. For one reason or another I couldn't really get behind any of them, so I decided to roll my own, which is posted below.
我最近需要将数据表序列化为 JSON。我所在的地方我们仍然使用 .Net 2.0，所以我无法在 .Net 3.5 中使用 JSON 序列化程序。我想这一定让我去寻找在线和已经做过，发现一个数量的不同选项。其中一些依赖于一个额外的库，我在这里很难推动。其他人需要先转换为List<Dictionary<>>，这看起来有点尴尬和不必要。另一个将所有值视为字符串。出于某种原因，我无法真正支持他们中的任何一个，所以我决定推出自己的，发布在下面。
As you can see from reading the //TODOcomments, it's incomplete in a few places. This code is already in production here, so it does "work" in the basic sense. The places where it's incomplete are places where we know our production data won't currently hit it (no timespans or byte arrays in the db). The reason I'm posting here is that I feel like this can be a little better, and I'd like help finishing and improving this code. Any input welcome.
正如您从阅读//TODO评论中看到的那样，它在一些地方是不完整的。这段代码已经在这里投入生产，所以它在基本意义上确实“有效”。它不完整的地方是我们知道我们的生产数据当前不会命中它的地方（数据库中没有时间跨度或字节数组）。我在这里发帖的原因是我觉得这可以更好一点，我想帮助完成和改进这段代码。欢迎任何输入。
Note that this capability is built into .Net 3.5 and later, and so the only reason to use this code today is if you're still limited to .Net 2.0. Even then, JSON.Net has become the goto library for this kind of thing.
请注意，此功能内置于 .Net 3.5 及更高版本中，因此今天使用此代码的唯一原因是您是否仍仅限于 .Net 2.0。即便如此，JSON.Net 已经成为这种事情的 goto 库。
public static class JSONHelper
{
public static string FromDataTable(DataTable dt)
{
string rowDelimiter = "";
StringBuilder result = new StringBuilder("[");
foreach (DataRow row in dt.Rows)
{
result.Append(rowDelimiter);
result.Append(FromDataRow(row));
rowDelimiter = ",";
}
result.Append("]");
return result.ToString();
}
public static string FromDataRow(DataRow row)
{
DataColumnCollection cols = row.Table.Columns;
string colDelimiter = "";
StringBuilder result = new StringBuilder("{");
for (int i = 0; i < cols.Count; i++)
{ // use index rather than foreach, so we can use the index for both the row and cols collection
result.Append(colDelimiter).Append("\"")
.Append(cols[i].ColumnName).Append("\":")
.Append(JSONValueFromDataRowObject(row[i], cols[i].DataType));
colDelimiter = ",";
}
result.Append("}");
return result.ToString();
}
// possible types:
// http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(VS.80).aspx
private static Type[] numeric = new Type[] {typeof(byte), typeof(decimal), typeof(double),
typeof(Int16), typeof(Int32), typeof(SByte), typeof(Single),
typeof(UInt16), typeof(UInt32), typeof(UInt64)};
// I don't want to rebuild this value for every date cell in the table
private static long EpochTicks = new DateTime(1970, 1, 1).Ticks;
private static string JSONValueFromDataRowObject(object value, Type DataType)
{
// null
if (value == DBNull.Value) return "null";
// numeric
if (Array.IndexOf(numeric, DataType) > -1)
return value.ToString(); // TODO: eventually want to use a stricter format. Specifically: separate integral types from floating types and use the "R" (round-trip) format specifier
// boolean
if (DataType == typeof(bool))
return ((bool)value) ? "true" : "false";
// date -- see http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx
if (DataType == typeof(DateTime))
return "\"\/Date(" + new TimeSpan(((DateTime)value).ToUniversalTime().Ticks - EpochTicks).TotalMilliseconds.ToString() + ")\/\"";
// TODO: add Timespan support
// TODO: add Byte[] support
//TODO: this would be _much_ faster with a state machine
//TODO: way to select between double or single quote literal encoding
//TODO: account for database strings that may have single \r or \n line breaks
// string/char
return "\"" + value.ToString().Replace(@"\", @"\").Replace(Environment.NewLine, @"\n").Replace("\"", @"\""") + "\"";
}
}
Update:
This is old now, but I wanted to point out something about how this code handles dates. The format I used made sense at the time, for the exact rationale in the url. However, that rationale includes the following:
更新：
这已经很旧了，但我想指出一些有关此代码如何处理日期的信息。我使用的格式在当时是有意义的，因为 url 中的确切原因。但是，该理由包括以下内容：
To be perfectly honest, JSON Schema does solve the problem by making it possible to "subtype" a string as a date literal, but this is still work in progress and it will take time before any significant adoption is reached.
老实说，JSON Schema 确实通过将字符串“子类型化”为日期文字来解决这个问题，但这仍在进行中，在达到任何重要采用之前还需要时间。
Well, time has passed. Today, it's okay to just use the ISO 8601date format. I'm not gonna bother changing the code, 'cause really: this is ancient. Just go use JSON.Net.
嗯，时间过去了。今天，可以只使用ISO 8601日期格式。我不会费心改变代码，因为真的：这是古老的。就去使用 JSON.Net。
采纳答案by Powerlord
Would it help you convince your bosses to install a library if it's Microsoft's AJAX extensions for .NET 2.0?
如果它是微软的.NET 2.0的AJAX 扩展，它会帮助你说服你的老板安装一个库吗？
Included in them is System.Web.Script.Serialization.JavascriptSerializer, which is used in Step 4 of the last linkin your post.
其中包括System.Web.Script.Serialization.JavascriptSerializer，它用于您帖子中最后一个链接的第 4 步。
回答by tuinstoel
I found this: http://www.bramstein.com/projects/xsltjson/You can convert your datatable to xml and use a xslt stylesheet to convert the xml to json.
我发现了这个：http: //www.bramstein.com/projects/xsltjson/您可以将数据表转换为 xml 并使用 xslt 样式表将 xml 转换为 json。
It is more a workaround than a real solution.
它更像是一种解决方法，而不是真正的解决方案。
回答by TheVillageIdiot
Hey budy, its is all here in Rick's blog post Serializing DataTable using Json.NET. He explains in detail how you can accomplish it using Json.NETfrom James Newton King.
嗨，伙计，这一切都在 Rick 的博客文章使用 Json.NET 序列化数据表中。他详细地介绍了如何使用完成它Json.NET从詹姆斯·牛顿国王。
