JSON.net format output like I need

Question

Hey all I have the following JSON output that I wrote up like I needed it to be outputted by JSON.net:

{"DATA": {
  "firstName": "Bill",
  "lastName": "Gates",
  "emailAddress": "billgates@microsoft.com",
  "phoneNum": "5552349856",
  "image": "https://upload.wikimedia.org/wikipedia/commons/1/19/Bill_Gates_June_2015.jpg",
  "title": "CEO",
  "clocked": [
      {"date": "12-13-2015 17:00:00", "type": "OUT"},
      {"date": "12-13-2015 13:00:00", "type": "IN"},
      {"date": "12-12-2015 14:30:00", "type": "OUT"},
      {"date": "12-12-2015 10:00:00", "type": "IN"},
      {"date": "12-11-2015 17:00:00", "type": "OUT"},
      {"date": "12-11-2015 13:00:00", "type": "IN"},
      {"date": "12-10-2015 10:30:00", "type": "OUT"},
      {"date": "12-10-2015 08:00:00", "type": "IN"},
      {"date": "12-09-2015 13:45:00", "type": "OUT"},
      {"date": "12-09-2015 09:00:00", "type": "IN"},
      {"date": "12-08-2015 12:30:00", "type": "OUT"},
      {"date": "12-08-2015 10:00:00", "type": "IN"},
      {"date": "12-07-2015 13:45:00", "type": "OUT"},
      {"date": "12-07-2015 08:30:00", "type": "IN"},
      {"date": "12-06-2015 12:10:00", "type": "OUT"},
      {"date": "12-06-2015 09:40:00", "type": "IN"},
      {"date": "12-05-2015 18:00:00", "type": "OUT"},
      {"date": "12-05-2015 14:10:00", "type": "IN"},
      {"date": "12-04-2015 12:30:00", "type": "OUT"},
      {"date": "12-04-2015 08:00:00", "type": "IN"}
  ]
}
}

Using a C# JSON to class online generator I get the following classes:

public class Clocked
{
    public string date { get; set; }
    public string type { get; set; }
}

public class DATA
{
    public string firstName { get; set; }
    public string lastName { get; set; }
    public string emailAddress { get; set; }
    public string phoneNum { get; set; }
    public string image { get; set; }
    public string title { get; set; }
    public List<Clocked> clocked { get; set; }
}

public class RootObject
{
    public DATA DATA { get; set; }
}

I'm getting the needed data to be filled in for these json string via a SQL server query:

 try
 {
     string connetionString = @"Data Source=(localdb)\v11.0;Initial Catalog=stantecUsers;Integrated Security=True";
     string sql = "SELECT * " +
                  "FROM [stantecUsers].[dbo].[users] AS stantecUsers " +
                  "INNER JOIN [stantecUsers].[dbo].[usersData] AS stantecUserData " +
                  "ON stantecUsers.link2Data = stantecUserData.link2Data" +
                  "WHERE stantecUsers.phoneNum = '" + phoneNum + "'" +
                  "ORDER BY [stantecUsers].ID ASC;";
     SqlConnection connection;
     SqlCommand command;
     connection = new SqlConnection(connetionString);

     try
     {
        connection.Open();
        command = new SqlCommand(sql, connection);
        SqlDataReader read = command.ExecuteReader();

        while (read.Read())
        {
           Debug.WriteLine(read["firstName"].ToString());
        }

        read.Close();
        command.Dispose();
        connection.Close();
     }
     catch (Exception ex)
     {
         Debug.WriteLine("Can not open connection ! " + ex.Message);
     }
   }
   catch (SqlException e)
   {
        return e.Message;
   }
}

So how would I go about getting the JSON.net output to look as my mock-up does?

Answer 1

You need to create an object that matches the structure of the JSON you want to obtain, then you can serialize that object to obtain the final JSON. The code could look similar to this:

string finalJSON = "";

try
{
   connection.Open();
   command = new SqlCommand(sql, connection);
   SqlDataReader read = command.ExecuteReader();

   // Create a new object that matches the structure of the JSON file.
   var root = new RootObject
   {
       DATA = new DATA { clocked = new List<Clocked>() }
   };

   while (read.Read())
   {
      root.DATA.firstName = read["firstName"].ToString();
      root.DATA.lastName = read["lastName"].ToString();
      // Continue with the other attributes...
      root.DATA.clocked.Add(new Clocked {date = read["date"].ToString(), type = read["type"].ToString() });
   }

   // Serialize the object using JSON.Net.
   finalJSON = JsonConvert.SerializeObject(root);

   read.Close();
   command.Dispose();
   connection.Close();
}