DataTable to JSON

Question

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.

Answer 1

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.

Answer 2

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.NET from James Newton King.