how to access the data from SQL Server which is in JSON format using ASP.NET WEB API controller

Question

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:

Answer 1

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.

Answer 2

I overcame that problem by using this line. json = JsonConvert.SerializeObject(dt).Replace("\"","");.