How does a json formatted response from a SQL Server work?

Question

I have a stored procedure that returns query results as json and I want to understand how this query will get to my code behind in an ASP.NET app.

Stored procedure:

Select 
       SUBJECT AS [subject],
       STARTDATE AS [start]
       ENDDATE AS [end],
       ID AS [id]
FROM 
       SOME_TABLE
FOR JSON PATH

Json format from stored procedure:

[
 {
  "subject": _,
  "start": _,
  "end":_,
  "id":_
 },
 ...]

aspx.cs codebehind

(snippet from a function)

  try
        {
            if (sqlcon.State == ConnectionState.Closed)
            {
                sqlcon.Open();
            }

            SqlCommand sccmd = new SqlCommand("MY_STORED_PROCEDURE", sqlcon);
            sccmd.CommandType = CommandType.StoredProcedure;
            sccmd.Parameters.AddWithValue("@value1", valueID);
            sccmd.Parameters.AddWithValue("@value2", valueID);
            SqlDataReader sdrreader = sccmd.ExecuteReader();

            while (sdrreader.Read())
            {
                // lost on what to do here
            }

            sdrreader.Close();
        }
        catch (Exception ex){}
        finally { sqlcon.Close(); }

I want to store this json response in my code behind, but I don't know how. Before making the response json I was using SqlDataReader with Read() to walk through each record, but how would this work if the response is now json; is there another class that will specifically handle a json response?

Clarification please!

Answer 1

1. Install JSON.NET via NuGet.

2. Create a code model to mirror your json.

   public class JsonModel {  
       public int id {get;set;}  
       public DateTime start {get;set;}   
       public DateTime end {get;set;}  
       public string subject {get;set;}  
   }  
   

3. Grab your json encoded data from the database

   string json = String.Empty;
   using (SqlConnection connection = new SqlConnection("... your connection string ...") {
       connection.Open();
       using (SqlCommand command = new SqlCommand("SELECT FROM... FOR JSON PATH", connection) {
           json = command.ExecuteScalar();
       }
   }
   

4. Deserialize it to your model

   var JsonModel model = JsonConvert.DeserializeObject<JsonModel>(json);
   

5. Drink tequila and eat fajitas!