Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

JSON Serialization of a DataReader


:P
On this page:

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


The Voices of Reason


 

J.D. Mullin
April 24, 2009

# re: JSON Serialization of a DataReader

Cool, I used your original dataset serialization article as a base to do this same thing recently: http://jdmullin.blogspot.com/2009/03/create-net-objects-directly-from-any.html

Abhilash
April 25, 2009

# re: JSON Serialization of a DataReader

Hi,
thanks for the info.
Really cool.
[off-topic]
When I viewed this post in Newsgator online. there is some problem in rendering the program-codes. only first few lines of codes are visible. rest are trimmed out. However, there is no problem in rendering the contents. just for an info. dts all.
thanks.

Marc Brooks
April 26, 2009

# re: JSON Serialization of a DataReader

Not sure what your implementation of StripComma is like, but I simply adjust the length of the StringBuilder when doing concatenation. My inner loop would be:

    const string NewLine = "\r\n"; // blog comment messes this up.

    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(NewLine);                    
        }
        // strip off trailing comma
        if (reader.FieldCount > 0)
        {
           sb.Length -= 1;
           if (this.FormatJsonOutput)
              sb.Length -= 2;
        }

        sb.Append("},");
        if (this.FormatJsonOutput)
            sb.Append(NewLine);
        
        rowCount++;
    }

    // remove trailing comma
    if (rowCount > 0)
    {
        sb.Length -= 1;
        if (this.FormatJsonOutput)
            sb.Length -= 2;
    }

Rick Strahl
April 26, 2009

# re: JSON Serialization of a DataReader

@Marc - the StripComma code does exactly that with the StringBuilder - remove 1 or 3 characters by adjusting the length. I have it as a separate function because this code is called in a lot of places in the serializer - basically for each property/object/array element etc. so having that logic in one place is a good idea in case I decide to change the formatting (which is kind of basic in the first place as it doesn't deal with indentation).

Montana
May 18, 2015

# re: JSON Serialization of a DataReader

Rick Strahl! Man - I've been following your work from back in the Visual FoxPro (5 - I think it was) days! Got out of MS products altogether in favor of Linux platforms when they started killing VFP - and I've recently begun my trek in earnest back into considering MS development tools now that the CEO of MS is a little less of a psychopath.

Great to see a familiar face, I've always respected your work and remember looking to your stuff for guidance and best practices - VFP Magazine man!

Anyhow, you mention in this post you would not recommend using a DataReader for a JSON service, I'm wondering what you *would* use?

My usual approach for data api calls that need JSON serialization would be:
Retrieve any mapped structures via an API Key of some kind, construct[first look in cache] the data objects that house the required model - serialize that into a predictable structure for consumption, cache that data if need be, and move on.

My first pass at this in C# has led me to rendering data from planned queries into Dictionary objects as you've described here. Is this an undesired approach?

Since I haven't found any way to ask SQL Server for a Json object directly yet - I wonder what the best practices here are?

Rick Strahl
May 18, 2015

# re: JSON Serialization of a DataReader

@Montana - In general I think that using a DataReader is something that should be relegated to a business layer or internal implementation. The exposed interface to an application should usually have something a bit more concrete like an a collection of objects. Some sort of serialization.

If you're doing data work that means some sort of database framework which can be a full fledged ORM like entity framework or something smaller like Dapper, PetaPoco or Massive, or even my SqlDataAccess DAL library (from Westwind Toolkit). You really don't want to be writing manual ADO.NET code but use helpers that return something more user friendly to your application layer.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024