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

Time:10-01

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

CodePudding user response:

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.

CodePudding user response:

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

enter image description here

  • Related