I have created a stored procedure in SQL Server which returns in json format
ALTER PROCEDURE [dbo].[GetAllRoles]
AS
BEGIN
SELECT *
FROM dbo.roles
FOR JSON PATH;
END
Output of this stored procedure:
[
{ "role_id": 101, "role_description": "Trainee" },
{ "role_id": 102, "role_description": "Lead" },
{ "role_id": 103, "role_description": "Manager" },
{ "role_id": 104, "role_description": "Senior Tester Engineer" },
{ "role_id": 105, "role_description": "Junior Tester Engineer" },
{ "role_id": 106, "role_description": "Senior Developer AEM" },
{ "role_id": 107, "role_description": "Junior Developer AEM" }
]
I want to access this stored procedure in an ASP.NET APIController:
public StringBuilder Get()
{
var jsonResult = new StringBuilder();
var connect = new SqlConnection(ConfigurationManager.ConnectionStrings["SupplyCon"].ConnectionString);
connect.Open();
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "GetAllRoles";
cmd.CommandType = CommandType.StoredProcedure;
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
jsonResult.Append("[]");
}
else
{
while (reader.Read())
{
jsonResult.Append(reader.GetString(0).ToString());
}
}
return jsonResult;
}
After hitting through postman, I get below response. I’m expecting the same response which I got after running the stored procedure in SQL Server. But, I got something weird.
Result in Postman:
2
Answers
I overcame that problem by using this line.
json = JsonConvert.SerializeObject(dt).Replace(""","");
.You are getting the same output in Postman as you do from running the procedure in the database; it is merely formatted a little differently. The difference is that the output in Postman has escaped all of the double quotes because the results are part of a string. A JSON parser such as JsonSerializer will read it looking like your stored procedure output.