convert from SqlDataReader to JSON

public string toJSON(SqlDataReader o)
{
    StringBuilder s = new StringBuilder();
    s.Append("[");
    if (o.HasRows)
        while (o.Read())
            s.Append("{" + '"' + "Id" + '"' + ":" + o["Id"] + ", "
            + '"' + "CN" + '"' + ":" + o["CatName"] + ", "
            + '"' + "Ord" + '"' + ":" + o["Ord"] + ","
            + '"' + "Icon" + '"' + ":" + o["Icon"] + "}, ");
    s.Remove(s.Length - 2, 2);
    s.Append("]");
    o.Close();
    return s.ToString();
}

I'm using here my own function to do serialization . I need to know if this is a good way or I should use another . BTW I've tried to use the JavaScriptSerializer but this didn't work with SqlDataReader . thanx

Solution 1:

If you want something that'll convert to arbitrary JSON, you could convert by serializing it into a Dictionary(Of string, object) thusly:

public IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
{
    var results = new List<Dictionary<string, object>>();
    var cols = new List<string>();
    for (var i = 0; i < reader.FieldCount; i++) 
        cols.Add(reader.GetName(i));

    while (reader.Read()) 
        results.Add(SerializeRow(cols, reader));

    return results;
}
private Dictionary<string, object> SerializeRow(IEnumerable<string> cols, 
                                                SqlDataReader reader) {
    var result = new Dictionary<string, object>();
    foreach (var col in cols) 
        result.Add(col, reader[col]);
    return result;
}

And then use the NewtonSoft.Json JsonConvert object to get your JSON:

var r = Serialize(reader);
string json = JsonConvert.SerializeObject(r, Formatting.Indented);

UPDATE: If you just want to use built-in methods, and you happen to be using MVC, you can use the built in Json helper method on your newly serialized :

JsonResult Index(int id) {
    var r = Serialize(reader);
    return Json(r, JsonRequestBehavior.AllowGet);
}

Solution 2:

This should do the job

private String sqlDatoToJson(SqlDataReader dataReader)
{
    var dataTable = new DataTable();
    dataTable.Load(dataReader);
    string JSONString = string.Empty;
    JSONString = JsonConvert.SerializeObject(dataTable);
    return JSONString;
}

Solution 3:

I encounter use cases where the number of rows being returned by the data reader may become problematic with respect to memory consumption. The following code uses a JsonWriter (from JSON.NET) over a stream. One can certainly debate the utility of enormous JSON documents, but sometimes our use cases are dictated by others :-)

A few notes:

• My SqlDataReader may contain multiple result sets ('tables')
• I may be sending the output to a FileStream or an HttpResponse stream
• I've 'abstracted' my object names to match the first column returned per result set
• Because of the potential for large result sets, I use async methods of the SqlDataReader.
• I'm letting JSON.NET handle all the serialization issue of the actual data contained in the data reader results.

The code:

var stream = ... // In my case, a FileStream or HttpResponse stream
using (var writer = new JsonTextWriter(new StreamWriter(stream)))
{
    writer.WriteStartObject();  
    do
    {
        int row = 0;
        string firstColumn = null;
        while (await reader.ReadAsync())
        {
            if (row++ == 0)
            {
                firstColumn = reader.GetName(0);
                writer.WritePropertyName(string.Format("{0}Collection", firstColumn));
                writer.WriteStartArray();   
            }
            writer.WriteStartObject();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (!reader.IsDBNull(i)) { 
                    writer.WritePropertyName(reader.GetName(i));
                    writer.WriteValue(reader.GetValue(i));
                }
            }
            writer.WriteEndObject(); 
        }
        writer.WriteEndArray();
    } while (await reader.NextResultAsync());

    writer.WriteEndObject();
}

An example of heterogeneous output would be:

{
    "ContactCollection": {
        "ContactItem": [{
                "ContactID": "1",
                "Contact": "Testing",
            },
            {
                "ContactID": "2",
                "Contact": "Smith, John",
            },
            {
                "ContactID": "4",
                "Contact": "Smith, Jane",
            }
        ],
        "MessageItem": [{
                "MessageID": "56563",
                "Message": "Contract Review Changed",
            },
            {
                "MessageID": "56564",
                "Message": " Changed",
            },
            {
                "MessageID": "56565",
                "Message": "Contract Review - Estimated Completion Added.",
            }
        ]
    }
}

Reference:

• http://www.newtonsoft.com/json/help/html/Performance.htm

Solution 4:

Another option would be to use James Newton-King's excellent JSON.NET library - http://www.newtonsoft.com/json

Here's a quick example on how to use it to build up a collection and then output it as a JSON-serialized string:

using Newtonsoft.Json;

class Program
{
    static void Main(string[] args)
    {
        ArrayList objs = new ArrayList();

        //get the data reader, etc.
        while(o.Read())
        {
            objs.Add(new
            {
                Id = o["Id"],
                CN = o["CatName"],
                Ord = o["Ord"],
                Icon = o["Icon"]
            });
        }

        //clean up datareader

        Console.WriteLine(JsonConvert.SerializeObject(objs));
        Console.ReadLine();
    }
}

You could do the same with your looping by reading in each row of your SqlDataReader into an anonymous object and then use JSON.NET to serialize it to a string.

Hope this helps!