数据表以JSON [英] DataTable to JSON
问题描述
我最近需要序列化数据表到JSON。我在哪里我们仍然在NET 2.0，所以我不能使用JSON序列化在.NET 3.5中。我想这一定是有人做过，所以我去寻找联机和发现一中的数= http://schotime.net/blog/index.php/2008/07/27/dataset-datatable-to-json/相对=nofollow>不同的选项。他们中的一些依赖于额外的库，我想也很难通过这里推。其他的则需要先转换成名单，其中，字典＆LT;＆GT;＆GT; ，这似乎有点尴尬和不必要的。另一种对待所有值就像一根绳索。对于由于某种原因，我不能真正得到后面的任何人，所以我决定推出自己的，这是贴在下面。
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 found a number of different options. 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.
你可以从读 // TODO 的意见看，它是不完整的少数几个地方。这code已经在这里生产，所以它在基本意义上的工作。其中，它的地方不完全是地方，我们知道我们的生产数据将目前不打它（无时间跨度或DB字节数组）。我张贴在这里的原因是，我觉得这可能是好一点，我想帮助整理和完善这一code。任何投入的欢迎。
As you can see from reading the //TODO comments, 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.
注意，此功能是内置的.Net 3.5和更高版本，所以唯一理由使用code今天，如果你仍然仅限于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("\"", @"\""") + "\"";
}
}
更新：
这是老了，但我想指出一些关于如何code处理日期。我使用的格式在当时是有道理的，在URL中的完全理由。然而，理由包括以下内容：
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:
要坦白地讲，JSON模式不通过就可以解决这个问题，以子类型字符串作为文字的日期，但这是工作仍然在进行中，这将需要一段时间的任何显著领养之前达到。
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.
好了，时间已经过去了。如今，它的好，只是使用 ISO 8601 的日期格式。我不会去改变的code，导致真正做到：这是古老的。只要去使用JSON.Net。
Well, time has passed. Today, it's okay to just use the ISO 8601 date format. I'm not gonna bother changing the code, 'cause really: this is ancient. Just go use JSON.Net.
推荐答案
它会帮助你说服你的老板来安装一个库，如果它是微软的<一href="http://www.microsoft.com/downloads/details.aspx?FamilyID=ca9d90fa-e8c9-42e3-aa19-08e2c027f5d6&displaylang=en">AJAX扩展.NET 2.0 ？
Would it help you convince your bosses to install a library if it's Microsoft's AJAX extensions for .NET 2.0?
包括在他们的<一个href="http://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptserializer.aspx">System.Web.Script.Serialization.JavascriptSerializer,它采用的是最后一个环节步骤4在您的帖子。
Included in them is System.Web.Script.Serialization.JavascriptSerializer, which is used in Step 4 of the last link in your post.
这篇关于数据表以JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
