Retrieving data from a database through SqlDataReader and converting it to JSON

Question

I want to convert my retrieved data into JSON; I am using a SqlDataReader for retrieving. For that I need to store my data into var. I am getting this error:

An exception of type 'System.Reflection.AmbiguousMatchException' occurred in mscorlib.dll but was not handled in user code

Additional information: Ambiguous match found.

in the following code:

public string GetDetails(int Id)
{   
    var jsonDoc = "";

    string con = "server=FACULTY01\\SQLSERVER2012ENT; database=SampleDb; uid=sa; pwd=sa9";
    SqlConnection scon = new SqlConnection(con);
    string qry = "Select * from Information where ID =" + Id;
    SqlCommand cmd = new SqlCommand(qry, scon);
    scon.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    var details = rdr;
    
    JavaScriptSerializer jss = new JavaScriptSerializer();
    jsonDoc = jss.Serialize(details);
    scon.Close();
    return jsonDoc;
}

Please show the *complete* stack trace. As asides: 1) you should be using `using` statements; 2) you should be using parameterized SQL. — Jon Skeet, Oct 31 '15 at 10:03
or any other method to stored `reader` into `var` ? I just want to store retried data into `var` for JSON serialization. — Mangrio, Oct 31 '15 at 10:17
Just because there's no more code doesn't mean there's no more *stack trace*. It's not clear whether your code is even being executed - perhaps this is actually a routing problem? (It's not clear why you've got a `details` variable at all, rather than just calling `jss.Serialize(rdr);` - it's not like using a variable declared using `var` changes anything. — Jon Skeet, Oct 31 '15 at 10:35
Possible duplicate of [convert from SqlDataReader to JSON](http://stackoverflow.com/questions/5083709/convert-from-sqldatareader-to-json) — Abdul Rehman Sayed, Nov 16 '16 at 12:34

score 4 · Accepted Answer · answered Oct 31 '15 at 21:06

You can't directly serialize a SqlDataReader and expect the output to contain the data from the SQL query. That's not the way that SqlDataReader works. SqlDataReader is a means to retrieve data from the SQL result. You can use it to populate some other object (such as a Dictionary<string, object> or a strongly-typed class you define) which you can then hand to the serializer to produce JSON.

Try the code below instead. (Also note the use of using statements and parameterized SQL in keeping with good coding practices, as mentioned by @Jon Skeet.)

public static string GetDetails(int Id)
{
    string con = "server=FACULTY01\\SQLSERVER2012ENT; database=SampleDb; uid=sa; pwd=sa9";
    using (SqlConnection scon = new SqlConnection(con))
    {
        string qry = "Select * from Information where ID = @id";
        SqlCommand cmd = new SqlCommand(qry, scon);
        cmd.Parameters.AddWithValue("@id", Id);
        scon.Open();

        var details = new Dictionary<string, object>();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.HasRows && rdr.Read())
            {
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    details.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i));
                }
            }
        }

        JavaScriptSerializer jss = new JavaScriptSerializer();
        string jsonDoc = jss.Serialize(details);
        scon.Close();
        return jsonDoc;
    }
}

Note that the above code is expecting to get a single row back from the reader. If you are expecting more than one row then you will need to use another loop and put the resulting data into a List<Dictionary<string, object>> instead. Here is the part you would need to change:

        ...

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

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    var dict = new Dictionary<string, object>();

                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        dict.Add(rdr.GetName(i), rdr.IsDBNull(i) ? null : rdr.GetValue(i));
                    }

                    details.Add(dict);
                }
            }
        }

        ...

score 1 · Answer 2 · edited Oct 31 '15 at 19:36

1

You need a loop to store the data from the reader. Use a DataTable to store the full table. A single variable will store single column data.

while (rdr.Read())
{
    var details = rdr["columnName"];
}

edited Oct 31 '15 at 19:36

Brian Rogers

125,747
31
299
300

answered Oct 31 '15 at 11:07

Nagib Mahfuz

833
10
19

score 1 · Answer 3 · edited May 23 '17 at 10:29

Encountered this post as it comes first in google search.

Have given an answer here (based on this):

The requirement is somewhat similar that is of converting sqldatareader result to a json string.

public static class MyExtensions
    {
        public async static Task<string> toJSON(this SqlDataReader reader)
        {            
            var results = await reader.GetSerialized();
            return JsonConvert.SerializeObject(results, Formatting.Indented);
        }
        public async static Task<IEnumerable<Dictionary<string, object>>> GetSerialized(this 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 (await reader.ReadAsync())
                results.Add(SerializeRow(cols, reader));

            return results;
        }
        private static 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;
        }
    }

Use As :

var result = await reader.GetSerialized(); //to get the result object

or

string strResult = await reader.toJSON(); //to get the result string

Retrieving data from a database through SqlDataReader and converting it to JSON

3 Answers3