DataTable到JSON [英] DataTable to JSON
问题描述
List< Dictionary<> ，这似乎有点尴尬和不必要。另一个处理所有值，如字符串。由于某种原因，我无法真正落后于任何一个，所以我决定自己滚动，这是在下面发布的。 从阅读 // TODO 评论可以看出，这在几个地方是不完整的。这个代码已经在这里生产了，所以它在基本意义上是工作的。不完整的地方，我们知道我们的生产数据目前不会打破它（db中没有时间盘或字节数组）。我发布在这里的原因是，我觉得这可以更好一些，我想帮助完成和改进这个代码。任何输入欢迎。
请注意，此功能内置于.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（dt.Rows中的DataRow行）
{
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（{）; （int i = 0; i< cols.Count; i ++）
{//使用索引而不是foreach，所以我们可以使用行和列集合的索引
result.Append（colDelimiter）.Append（\）
.Append（cols [i] .ColumnName）.Append（\：）
.Append（JSONValueFromDataRowObject（row [ i]，cols [i] .DataType））;
colDelimiter =，;
}
result.Append（}）;
return result.ToString（）;
}
//可能的类型：
// 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）};
//我不想为表中的每个日期单元重建此值
private static long EpochTicks = new DateTime（1970,1,1）.Ticks;
私有静态字符串JSONValueFromDataRowObject（对象值，类型DataType）
{
// null
if（value == DBNull.Value）returnnull;
// numeric
if（Array.IndexOf（numeric，DataType）> -1）
return value.ToString（）; // TODO：最终要使用更严格的格式。具体来说：从浮动类型中分离的积分类型并使用R（往返）格式说明符
// boolean
if（DataType == typeof（bool））
return（（bool）value）） 真假;
//日期 - 请参阅http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx
if（DataType == typeof（DateTime））
返回\\\ / Date（+新的TimeSpan（（（DateTime）值）.ToUniversalTime（）。Ticks - EpochTicks）.TotalMilliseconds.ToString（）+） \\ / \;
// TODO：添加Timespan支持
// TODO：添加Byte []支持
// TODO：这将是_much_更快的状态机
// TODO：在双引号或单引号文字编码之间选择的方式
// TODO：可能有单个\r或\换行符的数据库字符串的帐户
// string / char
返回\+ value.ToString（）替换（@\，@\\）替换（Environment.NewLine，@\\\
）替换（\@\）+\;
}
}
更新：
这是旧的，但我想指出一些关于这个代码如何处理日期的内容，我使用的格式但是，这个理由包括以下内容：
说实话，JSON模式可以通过使字符串subtype成da来解决问题但是这仍然是正在进行中，需要时间才能得到重大的采纳。
那么时间已经过去了。今天，只需使用 ISO 8601 日期格式就可以了。我不会打扰改变代码，因为真的：这是古老的。只需使用JSON.Net。
如果是微软的 .NET 2.0的AJAX扩展？
其中包括 System.Web.Script.Serialization.JavascriptSerializer ，它在最后一个链接在你的帖子。
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.
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.
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.
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:
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.
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.
Would it help you convince your bosses to install a library if it's Microsoft's AJAX extensions for .NET 2.0?
Included in them is System.Web.Script.Serialization.JavascriptSerializer, which is used in Step 4 of the last link in your post.
这篇关于DataTable到JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
