3

I have read countless questions and answers but have yet to see anything that solves the issue.

I am using newtonsoft JSON library and simply want to return some JSON from a table.

So in my controller I simply have (server and db details blanked for privacy):

 public IHttpActionResult GetHelloWorld() { string JSONResult; SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbname>;user=<username>;password=<password>"); DataTable table = new DataTable(); conn.Open(); SqlCommand cmd = new SqlCommand("select name, latitude, longitude from [<schema>].[<tablename>]", conn); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.Fill(table); } //System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer(); //List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); //Dictionary<string, object> row; //foreach (DataRow dr in table.Rows) //{ // row = new Dictionary<string, object>(); // foreach (DataColumn col in table.Columns) // { // row.Add(col.ColumnName, dr[col]); // } // rows.Add(row); //} //return Ok(serializer.Serialize(rows),); JSONResult = JsonConvert.SerializeObject(table); return Ok(JSONResult); }

Now it returns my result set as expected but I simply cannot get it to return without the double quotes being escaped, so the data looks like this:

"[{\"name\":\"Aaron's Hill\",\"latitude\":51.18276,\"longitude\":-0.63503},{\"name\":\"Abbas Combe\",\"latitude\":51.00113,\"longitude\":-2.42178},

Now many of the answers I have read simply say its because you are looking at the result in the IDE. In my case this is not true, this is the output in the browser window (tried both IE and chrome) and even fiddler complains when you say show me the JSON output it says invalid character at position 3.

You will see I have commented out code which is the other method of returning JSON which I found on here but that still escapes the quotes. Not sure what else to try?

Improve this question
1
  • 1
    You shouldn't return serialized object. You should return a class object which will be serialized by ASP.NET. Now, you serialize a serialized object - that's why you get escaped quotes. Commented Sep 3, 2015 at 9:54

1 Answer 1

Reset to default
6

You shouldn't serialize object yourself!
You should return a class object which will be serialized by ASP.NET. Now, ASP.NET serializes a serialized object - that's why you get escaped quotes.

It should be something like:

public IHttpActionResult GetHelloWorld() { SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbname>;user=<username>;password=<password>"); DataTable table = new DataTable(); conn.Open(); SqlCommand cmd = new SqlCommand("select name, latitude, longitude from [<schema>].[<tablename>]", conn); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.Fill(table); } return Ok(table); // DO NOT serialize }

However, returning a DataTable object sounds wrong - it won't be serialized properly.
You need to return your custom objects which represent your entities like this (just as an example, may not work):

public IHttpActionResult GetHelloWorld() { List<YourCustomClass> result = new List<YourCustomClass>(); SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbname>;user=<username>;password=<password>"); conn.Open(); SqlCommand cmd = new SqlCommand("select name, latitude, longitude from [<schema>].[<tablename>]", conn); var reader = cmd.ExecuteReader(); while (reader.Read()) { result.Add(new YourCustomClass { Name = (string)reader["name"], Latitude = (decimal)reader["latitude"], Longitude = (decimal)reader["longitude"] }); } return Ok(result); }

Looking ahead, I may suggest not to make SQL query in your ASP.NET classes. SQL connections and queries belong to Data Access Layer (DAL) - it should be stored in a separate logic layer - in a separate project or, at least, in a separate class. You may use Repository pattern (is it a pattern?):

// Somewhere in a class library public class Address { public string Name { get; set; } public decimal Latitude { get; set; } public decimal Longitude { get; set; } } public class AddressesRepository : IAddressesRepository { public Address[] ReadAll() { List<YourCustomClass> result = new List<YourCustomClass>(); var connection = SqlConnectionFactory.CreateAndOpenConnection(); var reader = new SqlCommand("select name, latitude, longitude from [<schema>].[<tablename>]", connection).ExecuteReader(); while (reader.Read()) { result.Add(new Address { Name = (string)reader["name"], Latitude = (decimal)reader["latitude"], Longitude = (decimal)reader["longitude"] }); } return result; } } // Somewhere in ASP.NET WebAPI private IAddressesRepository _addressesRepository = new AddressesRepository(); public IHttpActionResult GetHelloWorld() { var data = _addressesRepository.ReadAll(); return Ok(data); }

I advice to read some books on layered architecture and ASP.NET WebAPI itself in order to get answers to all fundamental ASP.NET questions.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for that comprehensive answer Yeldar!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.