数据表转JSON [英] DataTable to JSON

问题描述

我最近需要将数据表序列化为 JSON.我所在的地方我们仍然在 .Net 2.0 上，所以我不能在 .Net 3.5 中使用 JSON 序列化程序.我想这一定是以前做过的，所以我去网上查找并找到数量 不同 选项.其中一些依赖于一个额外的库，我在这里很难推动.其他的需要先转成List，这看起来有点别扭和没必要.另一个将所有值视为字符串.出于某种原因，我无法真正支持他们中的任何一个，所以我决定推出自己的，发布在下面.

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 注释可以看出，它有几个地方不完整.这段代码已经在这里生产，所以它在基本意义上工作".它不完整的地方是我们知道我们的生产数据当前不会命中它的地方(数据库中没有时间跨度或字节数组).我在这里发帖的原因是我觉得这可以更好一点，我想帮助完成和改进这段代码.欢迎任何输入.

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 及更高版本中，因此今天使用此代码的唯一原因是您是否仍仅限于 .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 
 or 
 line breaks
        // string/char  
        return """ + value.ToString().Replace(@"", @"\").Replace(Environment.NewLine, @"
").Replace(""", @"""") + """;
    }
}

更新:
这已经很老了，但我想指出一些关于这段代码如何处理日期的内容.我使用的格式在当时是有意义的，因为 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 Schema 确实解决了这个问题，因为它可以将字符串子类型化"为日期文字，但这仍在进行中，要实现任何重要的采用还需要时间.

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 日期格式.我不会费心改变代码，因为真的:这很古老.就去使用 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.

推荐答案

如果它是 Microsoft 的 适用于 .NET 2.0 的 AJAX 扩展?

Would it help you convince your bosses to install a library if it's Microsoft's AJAX extensions for .NET 2.0?

其中包括 System.Web.Script.Serialization.JavascriptSerializer，用于您帖子中的最后一个链接.

这篇关于数据表转JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！