Create JsonResult from C# SqlDataReader

Question

I need to serialize a SqlDataReader to Json.

I researched some examples on the internet, however, all the results were a "text" and not an object with lines.

What am I doing wrong?

My implementation:

public JsonResult Teste()
{
    using (SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Conexao"].ConnectionString))
    {
        db.Open();

        using (SqlCommand comando = new SqlCommand("select * from USERS", db))
        {
            using (SqlDataReader reader = comando.ExecuteReader())
            {
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);  

                var resultado = JsonConvert.SerializeObject(dataTable);

                return Json(resultado, JsonRequestBehavior.AllowGet);
            }
        }
    }
}

My results:

"[{\"UsuarioID\":1,\"Email\":\"admin\",\"Nome\":\"SISTEMA\"},{\"UsuarioID\":2,\"Email\":\"marlon.tiedt@gmail.com\",\"Nome\":\"Marlon Tiedt\"},{\"UsuarioID\":3,\"Email\":\"marlon.tiedt@megasul.com.br\",\"Nome\":\"Marlon - Megasul\"}]"

Desired results:

[{"UsuarioID":1,"Email":"admin","Nome":"SISTEMA"},{"UsuarioID":2,"Email":"marlon.tiedt@gmail.com","Nome":"Marlon Tiedt"},{"UsuarioID":3,"Email":"marlon.tiedt@megasul.com.br","Nome":"Marlon - Megasul"}]

What exactly is the problem, the escape characters? Those are just a result of viewing it in the debugger. What is "an object with lines"? — Ňɏssa Pøngjǣrdenlarp, Apr 17 '18 at 20:22
My problem is that the result is not an object but a text. My result is between the "" characters. This way I can not use it in View. @Plutonix — Tiedt Tech, Apr 17 '18 at 20:26
I have other Json, which comes from Linq, which generates the records correctly. Example: `{"rows":[{"UsuarioVisaoID":2,"Email":"admin","Nome":"SISTEMA","Status":"A","Fixo":"Não"},{"UsuarioVisaoID":5,"Email":"marlon.tiedt@gmail.com","Nome":"Marlon Tiedt","Status":"A","Fixo":"Não"}],"total":2}` — Tiedt Tech, Apr 17 '18 at 20:28
Right, you're double-serializing your JSON as shown in [JSON.NET Parser *seems* to be double serializing my objects](https://stackoverflow.com/q/25559179/3744182). So, don't do that and return your object directly. — dbc, Apr 17 '18 at 20:28

Sunny Sharma · Answer 1 · 2018-04-17T20:30:32.327

3

You're double-encoding the output, just pass the dataTable object to new Json(...) and it should work fine. See the modified code below

public JsonResult Teste()
{
    using (SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Conexao"]     .ConnectionString))
  {
    db.Open();
    using (SqlCommand comando = new SqlCommand("select * from USERS", db))
    {
        using (SqlDataReader reader = comando.ExecuteReader())
        {
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);  
            return Json(dataTable, JsonRequestBehavior.AllowGet);
        }
    }
  }
}

edited Apr 17 '18 at 20:30

answered Apr 17 '18 at 20:28

Sunny Sharma

4,688
5
35
73

Solution did not work. Generates the error: https://pastebin.com/mfnQHn9V – Tiedt Tech Apr 17 '18 at 20:37
1

@MarlonTiedt that's just a stack trace, you forgot to tell us the actual error message. But I'd suspect returning the whole datatable object isn't a good idea. Make a DTO object containing just the actual fields you need, read from the datatable into that, and send that back as the JSON. Or it may be as dbc says below, you're using an old version of MVC and the JsonSerializer class it used is no longer used or particularly recommended by Microsoft, they now delegated this functionality to JSON.NET because it's better at it. Switching your default serialiser to that might help as well. – ADyson Apr 17 '18 at 20:45
2

@MarlonTiedt - looks like you may be using an older version of [tag:asp.net-mvc] that uses `JavaScriptSerializer`. From https://techblog.dorogin.com/json-serializers-in-asp-net-and-other-e12d3d62933f: *ASP.NET MVC4 since 4.5 RC now uses Newtonsoft.Json (Json.Net)*; confirmed [here](https://forums.asp.net/p/1810654/5010838.aspx?ASP%20NET%20MVC%204%20Release%20Candidate%20released). To use Json.NET in an older version of MVC see [Setting the default JSON serializer in ASP.NET MVC](https://stackoverflow.com/q/14591750). – dbc Apr 17 '18 at 20:46

score 0 · Answer 2 · answered Apr 17 '18 at 20:28

Json internally serializes whatever data you pass to it.In your case you are passing serialized string instead of actual data so it is adding slashes in the response.So remove serialization and pass object directly to Json,you will get proper json response.

score 0 · Answer 3 · answered Apr 18 '18 at 05:43

 -- One Standard way of doing this is convert your datatable into List<Dictionary<string, object>>. Below is the sample code.


public JsonResult Teste()
{
    using (SqlConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Conexao"].ConnectionString))
    {
        db.Open();

        using (SqlCommand comando = new SqlCommand("select * from USERS", db))
        {
            using (SqlDataReader reader = comando.ExecuteReader())
            {
                DataTable dataTable = new DataTable();
                dataTable.Load(reader);  

                var resultado = GetTableRows(dataTable);

                return Json(resultado, JsonRequestBehavior.AllowGet);
            }
        }
    }
}






public List<Dictionary<string, object>> GetTableRows(DataTable dtData)
{
            List<Dictionary<string, object>> 
            lstRows = new List<Dictionary<string, object>>();
            Dictionary<string, object> dictRow = null;

            foreach (DataRow dr in dtData.Rows)
            {
                dictRow = new Dictionary<string, object>();
                foreach (DataColumn col in dtData.Columns)
                {
                    dictRow.Add(col.ColumnName, dr[col]);
                }
                lstRows.Add(dictRow);
            }
            return lstRows;
}

Create JsonResult from C# SqlDataReader

3 Answers3