convert from SqlDataReader to JSON

Question

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

Answer 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);
}