Serializing SQL Json response

Question

I have an SQL query that returns a json response. Query was parsed using FOR JSON AUTO on the database. The issue I am having is when I query the database in my application I get the backslashes. When I execute the stored procedure on SQL server management studio, the results come back well. But when I query from the application using ADO.net, I get the backslashes.

Response from SSMS

{
  "role": "Commercial Credit Solutions Manager",
  "Applications": [
    {
      "name": "LOS",
      "Authorizations": [
        {
          "permissions": "Pre-Approve",
          "AppPermissions": [
            {
              "NOTES": null
            }
          ]
        },
        {
          "permissions": "Pend",
          "AppPermissions": [
            {
              "NOTES": null
            }
          ]
        }
      ]
    },
    {
      "name": "DNA",
      "Authorizations": [
        {
          "permissions": "L05",
          "AppPermissions": [
            {
              "NOTES": null
            }
          ]
        },
        {
          "permissions": "L11",
          "AppPermissions": [
            {
              "NOTES": "Only on manager's approval"
            }
          ]
        }
      ]
    }
  ]
}

When I return a JsonResult from the controller I get the error: "VM26:1 Uncaught (in promise) SyntaxError: Unexpected token 'S', "System.Not"... is not valid JSON" on the client side.

In Debug mode this is the response but I get not valid Json on the client side.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
"{""role"":""Commercial Credit Solutions Manager"",""Applications"":[{""name"":""LOS"",""Authorizations"":[{""permissions"":""Pre-Approve"",""AppPermissions"":[{""NOTES"":null}]},{""permissions"":""Pend"",""AppPermissions"":[{""NOTES"":null}]}]},{""name"":""DNA"",""Authorizations"":[{""permissions"":""L05"",""AppPermissions"":[{""NOTES"":null}]},{""permissions"":""L11"",""AppPermissions"":[{""NOTES"":""Only on manager's approval""}]}]}]}"

when I serialize in my c# code I get the response below with backslashes:

QueryTables

public DataTable QueryTables(int id)
        {

            ConnectToDataBase();
            
            SqlCommand cmd = new("spGetAppAndPermissions", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("id", id);
            DataSet _dataSet = new();
            SqlDataAdapter adapter = new(cmd);
            DataTable response = new();
            adapter.Fill(response);
            con.Close();

            return response;
        }

dbContext

   public  DataTable GetAppsAndPermission(int id)
        {
            DataTable dbResponse = QueryTables(id);
            return dbResponse;
        }

Controller

     [HttpGet]
        [Route("home/GetAssignments/{id:int?}")]
        public JsonResult GetAssignments(int id)
        {
            DataTable response = dbContext.GetAppsAndPermission(id);
            //DataRow test = response.Rows[0][0];
            ((string)response.Rows[0][0]).Replace("\"", string.Empty);
            List < Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row;
            foreach (DataRow dr in response.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in response.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }
            //Replace(@"\""", @"""");
            //JsonConvert.SerializeObject(rows)

            return Json(JsonConvert.SerializeObject(rows));
        }

I get the response below after serializing:

[
   {
      "JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "{\"role\":\"Commercial Credit Solutions Manager\",\"Applications\":[{\"name\":\"LOS\",\"Authorizations\":[{\"permissions\":\"Pre-Approve\",\"AppPermissions\":[{\"NOTES\":null}]},{\"permissions\":\"Pend\",\"AppPermissions\":[{\"NOTES\":null}]}]},{\"name\":\"DNA\",\"Authorizations\":[{\"permissions\":\"L05\",\"AppPermissions\":[{\"NOTES\":null}]},{\"permissions\":\"L11\",\"AppPermissions\":[{\"NOTES\":\"Only on manager's approval\"}]}]}]}"
   }
]

To resole this I tried Json.stringify then chained the replace methon and parsing back to JSON but I still got back to to where I was initially. Invalid JSON. Please see stored procedure below:

CREATE PROCEDURE spGetAppAndPermissions(@ID AS INT)
AS
BEGIN
     SELECT ROLE_NAME AS role,APPLICATION_NAME AS name ,
     AUTH_LABEL AS permissions,NOTES 
     FROM AppPermissions
     JOIN roles
     ON roles.ROLE_ID = AppPermissions.ROLE_ID
     JOIN Applications
     ON Applications.APP_ID = AppPermissions.APP_ID
     JOIN Authorizations
     ON Authorizations.AUTH_ID = AppPermissions.AUTH_ID
     WHERE Roles.ROLE_ID = @ID
     FOR JSON AUTO,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER;
     

END;

Answer 1

Md Farid Uddin Kiron, I figured out where the issue was. I adopted Kane's suggestion.

However, since the response was already coming from the DB as a serialized object, parsing it in response.json call in the fetch request gets it serialized again. This causes a double serialization.

To address this issue, I stringified the response on the C# layer, then serialized on the client side.