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:

I'm getting splash() in the output. how can I exclude it in output. — Teja
– Teja, Commented Oct 1, 2022 at 5:08

TarHalda · Accepted Answer · 2022-09-30 17:11:01Z

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.

answered Sep 30, 2022 at 17:11

TarHalda

1,1612 gold badges13 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Teja · Accepted Answer · 2022-10-01 05:57:28Z

0

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

answered Oct 1, 2022 at 5:57

Teja

375 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related