CODEWITHSUNDEEP
asp.netentity-framework-6
GCoe
GCoe

Reputation: 885

SQL Server with returning JSON

I'm trying to get data from a controller with FOR JSON PATH as below : 

DbConnection.Open();
SqlDataReader rdr;
Object res ;

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from myTable FOR JSON PATH";
cmd.Connection = DbConnection;

rdr = cmd.ExecuteReader();

rdr.Read();
res = rdr[0];

DbConnection.Close ();
return Ok(res);

But the resulting object comes with back slashes before each property :

"[{\"codigo_do_registro\":1,\"descricao\":\"PLUVIOMETRO 01\",\"centro_de_custos\":\"DOM PEDRO\"

Instead of :

[{"codigo_do_registro":1,"descricao":"PLUVIOMETRO 01","centro_de_custos":"DOM PEDRO"

I think it's necessary other parameters to return data in the correct way but can't find it.

Any help please ?

Upvotes: 0

Views: 207

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

The \" are escape sequence for double quotes, because " treated as special characters when written as string.

Here is an explanation taken from How FOR JSON escapes special characters and control characters:

If the source data contains special characters, the FOR JSON clause escapes them in the JSON output with \. This escaping occurs both in the names of properties and in their values.

To remove escape sequences, just use Regex.Unescape() method when returning response:

DbConnection.Open();
SqlDataReader rdr;
Object res;
using (SqlCommand cmd = new SqlCommand())
{
   cmd.CommandText = "select * from myTable FOR JSON PATH";
   cmd.Connection = DbConnection;
   rdr = cmd.ExecuteReader();
   rdr.Read();
   res = rdr[0];
   DbConnection.Close ();
   return Ok(Regex.Unescape(res.ToString())); // unescape all escape sequences
}

Reference:

SQL Server - Format Query Results as JSON with FOR JSON

Upvotes: 1

Related Questions