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": "[email protected]",
  "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?

Gabriel Claudiu Georgiu · Accepted Answer · 2015-12-26 20:54:53Z

4

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

answered Dec 26, 2015 at 20:54

Gabriel Claudiu Georgiu

7602 gold badges14 silver badges22 bronze badges

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

1 Comment

StealthRT Over a year ago

Great example. Thanks!

Collectives™ on Stack Overflow

JSON.net format output like I need

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related