skip to Main Content

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:

enter image description here

2

Answers


  1. Chosen as BEST ANSWER

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

    enter image description here


  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search