I’ve run into a scenario a few times now where I ended up with a DataReader at the end of a service call that I want to serialize into JSON to feed to the client. Don’t start -  I’m not recommending sticking a DataReader into your front end or JSON service! It’s not a common scenario and I certainly wouldn’t advocate that in a normal application scenario (DataReaders have no place in the front end), but in these cases I’ve been dealing with system components that spit out query results in DataReader format. Specifically in my current scenario I’m dealing with a custom logging implementation that’s returning only a DataReader from its API (presumably because there can be large amounts of data).

So my options with the DataReader are: convert the object into something that CAN be serialized (like a dictionary) or manually create JSON from the DataReader. I’m not fond of the former approach as it basically means copying the DataReader out into a concrete structure – which in case of the log files in question here would be quite large resulting in needless overhead.

So I figured it’s probably easier to create some code to allow direct serialization of the DataReader into JSON. I’ve been using an internal JSON Serializer for some time and the DataReader serialization code is pretty simple in this serializer:

void WriteDataReader(StringBuilder sb, IDataReader reader)
{
    if (reader == null || reader.FieldCount == 0)
    {
        sb.Append("null");
        return;
    }

    int rowCount = 0;

    sb.Append("{\"Rows\":[\r\n");

    while (reader.Read())
    {
        sb.Append("{");
        
        for (int i = 0; i < reader.FieldCount; i++)
        {
            sb.Append("\"" + reader.GetName(i) + "\":") ;
            this.WriteValue(sb,reader[i]);
            sb.Append(",");
            if (this.FormatJsonOutput)
                sb.Append("\r\n");                    
        }
        // strip off trailing comma
        if (reader.FieldCount > 0)
            this.StripComma(sb);

        sb.Append("},");
        if (this.FormatJsonOutput)
            sb.Append("\r\n");
        
        rowCount++;
    }

    // remove trailing comma
    if (rowCount > 0)
        this.StripComma(sb);

    sb.Append("]}");
}

There are a couple dependencies in here but it’s pretty easy to see that serializing a DataReader is a piece of cake. Basically a DataReader is just a ‘pseudo-record’ dictionary of key value pairs so it’s easy to loop through the ‘records’ and write them out.

Here’s what the output looks like (formatted for display):

{"Rows":[{"CustomerID":"ALFKI",
          "CompanyName":"Alfred\u0027s LINQ Futterkiste",
          "ContactName":"Maria Anders",
          "tstamp":[0,0,0,0,0,1,126,209]
         },
         {"CustomerID":"WELLI",
          "CompanyName":"Wellington Importadora",
          "ContactName":"Paula Parente",
          "tstamp":[0,0,0,0,0,1,126,209]
         }
        ]}

Notice that the DataReader is output as an object that has an array property called Rows rather than just returning an array directly. This is for compatibility with the JavaScriptSerializer  version I’ll talk about below.

All the individual values are written out with WriteValue() which is the generic routine that handles encoding of any values into JSON. WriteValue() is mostly a big if statement that checks types and figures out which type method to route the value to serialize to. For DataReader the routing checks for IDataReader:

else if (val is IDataReader)
{
    WriteDataReader(sb, val as IDataReader);
}
else if (val is IDictionary)
{
    WriteDictionary(sb, val as IDictionary);
}
else if (val is IEnumerable)
{
    WriteEnumerable(sb, val as IEnumerable);
}

You can check out the whole WestwindJsonSerializer class in the West Wind Web Toolkit repository.

I originally created this class before there was native JSON support in .NET, but I’ve stuck with my class even after JavaScriptSerializer because there’s been a few types that just didn’t work well and I needed a few options that weren’t available (date formatting options and output formatting of JSON) and to work without the dependency on System.Web.Extensions in older 2.x applications.

However, the serializer is pluggable (actually the higher level JSONSerializer class), so JavaScriptSerializer or Json.NET can be plugged in as needed. I’ve actually been real glad I’ve had this flexibility as I’ve had a few applications where needed to use one parser or the other due to support of certain types not supported by the others (or formats).

Using the converter it’s now as easy as retrieving the data from the log and pushing it out as a DataReader in my callback method in the JSON handler:

[CallbackMethod]
public IDataReader GetListData(LogDataFilter filter)
{           
    IDataReader reader;

    SqlLogAdapter adapter = LogManager.Current.LogAdapter as SqlLogAdapter;
    adapter.QueryFieldList = "Id,Url,QueryString,RequestDuration,Message,Entered,ErrorLevel,ErrorType";

    if (filter.StartDate != DateTime.MinValue)
        reader = adapter.GetEntries(filter.ErrorLevel, filter.Count);
    else
    {
        if (filter.EndDate == DateTime.MinValue)
            filter.EndDate = DateTime.Now.Date;
        
        reader = adapter.GetEntries(filter.ErrorLevel, filter.Count, filter.StartDate, filter.EndDate);
    }

    return reader;
}

A JavaScriptSerializer DataReader Converter

Above I’m using a custom serializer. If you rather stick with the native JavaScriptSerializer you can also add this functionality.  System.Web.Extensions.JavaScriptSerializer doesn’t serialize a DataReader directly. Well it does serialize, just not in format that’s very useful – it returns the .ToString() result of the data which looks like this:

[{"FieldCount":13},{"FieldCount":13},{"FieldCount":13}]

The JavaScriptSerializer does support extension with a JavaScriptConverter subclass. Some time ago I posted JavaScript Converters for DataSet/DataTable/DataRow objects. The process for DataReaders is similar, but there’s a caveat. More on that in a minute. Here’s the DataReaderConverter:

public class DataReaderConverter : JavaScriptConverter
{
    public override object Deserialize(IDictionary<string, object> dictionary, Type type, JavaScriptSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override IDictionary<string, object> Serialize(object obj, JavaScriptSerializer serializer)
    {
        
        IDataReader reader = obj as IDataReader;
        if (reader == null)
            return null;

        Dictionary<string,object> wrapper = new Dictionary<string,object>();

        List<Dictionary<string,object>> rows = new List<Dictionary<string,object>>();

        while (reader.Read())
        {
            Dictionary<string,object> row = new Dictionary<string,object>();
            for (int i = 0; i < reader.FieldCount - 1; i++)
            {
                row.Add( reader.GetName(i),reader[i]);
            }
            rows.Add(row);
        }

        wrapper.Add("Rows", rows);
        return wrapper;
    }

    public override IEnumerable<Type> SupportedTypes
    {
        get { return new Type[3] { typeof(SqlDataReader), 
                                   typeof(System.Data.OleDb.OleDbDataReader), 
                                   typeof(IDataReader) }; }
    }
}
   

Serialization of a DataReader is one way only so deserialization just throws a NotImplemented exception. Converters work by returning dictionaries of property value pairs that the serializer eventually outputs as JSON. Each field in a row is thrown into a dictionary and row is a list of these field dicitionaries. Finally the result from a converter has to be a Dictionary and so a top level node is required. This is where the “Rows” property I mentioned earlier comes in. I can’t see how you can return an array as the top level object <shrug>.

There’s another big gotcha here: The SupportedTypes property requires concrete types. At first I figured I could just specify IDataReader, but found that that didn’t work and the converter was bypassed. A concrete type like SqlDataReader is required in order to parse objects. So unlike the parser in the WestwindJsonSerializer which works with any IDataReader implementation, this version works only with whatever you specify explicitly in the type list.

You can find the DataReaderConverter as well as the other Ado.NET Converters in West Wind West Wind Web Toolkit Repository.

Not for Everyday Use

This isn’t something you’ll use a lot. Heck, hopefully you’ll NEVER have to use this converter. As I mentioned earlier using a DataReader in the front end or for a JSON service interface is probably not a good idea, but there can be situations where it happens. For me it’s been for lower level system components which is what prompted me to run into this issue in the first place. Thanks to the JSON serialization I can now fire off the DataReaders returned from the logging API directly without first creating objects to store the values into.

Related Posts

DataTable JSON Serialization in JSON.NET and JavaScriptSerializer

JSON and Date Embedding