convert from SqlDataReader to JSON

c#, json, sqldatareader

Solution

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

Problem

``` 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

Original source