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
– Jon Skeet, Commented Oct 31, 2015 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
– Mangrio, Commented Oct 31, 2015 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
– Jon Skeet, Commented Oct 31, 2015 at 10:35

Brian Rogers · Accepted Answer · 2015-10-31 21:06:27Z

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

        ...

Brian Rogers · Accepted Answer · 2015-10-31 19:36:46Z

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, 2015 at 19:36

Brian Rogers

130k31 gold badges315 silver badges315 bronze badges

answered Oct 31, 2015 at 11:07

Nagib Mahfuz

83310 silver badges21 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:29:23Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related