How does a formatted json response from SQL Server work?

Question

I have a stored procedure that returns the results of a query as json, and I want to understand how this query gets into my code in an ASP.NET application.

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

(fragment from 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 keep this json response in my code, but I don't know how to do it. Before making a json answer, I used SqlDataReaderc Read()to go through each entry, but how will this work if now the answer is json; is there another class that will specifically handle the json response?

Clarification please!

Answer 1

• JSON.NET NuGet.

• , json.

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

• json-

  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();
      }
  }
  

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

• !

Answer 2

SQL- . , json_encode json.

,

int [] marks = new int[]  { 99,  98, 92, 97, 95};
var jsonSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();
string json = jsonSerializer.Serialize(marks);