SQL data to custom JSON.net structure

Question

Hey all I have the following SQL query that outputs the following:

ID  | Name    | theRole | THrs | Mon   |....| trainH1 | train58 | train52 | train05 |etc...
===========================================================================================
152 | BOB M.  | Admin   | 40   | 1-9pm |....| 168dGrc0 | 89220E | 2FDEx56 | 5569CCz |......

Ideally I would like the structure to look like this [Note: With additional table names below]:

{
    "scheduleName": "",
    "firstName": "",
    "lastName": "",
    "theRole": "",
    "linker": "",
    "Schedule": {
        "ID": "",
        "totalHrs": "",
        "Mon": "",
        "Tue": "",
        "Wed": "",
        "Thu": "",
        "Fri": "",
        "Sat": ""
    },
    "empInfo": {
        "ID": "",
        "Email": "",
        "Phone": "",
        "Active": "",
        "Img": "",
        "Badge": ""
    },
    "availability": {
        "ID": "",
        "Mon": "",
        "Tue": "",
        "Wed": "",
        "Thu": "",
        "Fri": "",
        "Sat": ""
    },
    "training": {
        "?": "?"
    }
}

The train is where I am having issues. These values from the database can change names at any given time or be added or deleted. Currently I have around 35 values for this. Therefore, I am unable to hard code those values within my class function as I can do with ID, Name, theRole, THrs, etc...

The VB.net class definition looks like this so far:

Public Class Schedule
    Private m_ID As String
    Private m_totalHrs As String
    Private m_Mon As String
    Private m_Tue As String
    Private m_Wed As String
    Private m_Thu As String
    Private m_Fri As String
    Private m_Sat As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property totalHrs() As String
        Get
            Return m_totalHrs
        End Get
        Set
            m_totalHrs = Value
        End Set
    End Property

    Public Property Mon() As String
        Get
            Return m_Mon
        End Get
        Set
            m_Mon = Value
        End Set
    End Property

    Public Property Tue() As String
        Get
            Return m_Tue
        End Get
        Set
            m_Tue = Value
        End Set
    End Property

    Public Property Wed() As String
        Get
            Return m_Wed
        End Get
        Set
            m_Wed = Value
        End Set
    End Property

    Public Property Thu() As String
        Get
            Return m_Thu
        End Get
        Set
            m_Thu = Value
        End Set
    End Property

    Public Property Fri() As String
        Get
            Return m_Fri
        End Get
        Set
            m_Fri = Value
        End Set
    End Property

    Public Property Sat() As String
        Get
            Return m_Sat
        End Get
        Set
            m_Sat = Value
        End Set
    End Property
End Class

Public Class EmpInfo
    Private m_ID As String
    Private m_Email As String
    Private m_Phone As String
    Private m_Active As String
    Private m_Img As String
    Private m_Badge As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property Email() As String
        Get
            Return m_Email
        End Get
        Set
            m_Email = Value
        End Set
    End Property

    Public Property Phone() As String
        Get
            Return m_Phone
        End Get
        Set
            m_Phone = Value
        End Set
    End Property

    Public Property Active() As String
        Get
            Return m_Active
        End Get
        Set
            m_Active = Value
        End Set
    End Property

    Public Property Img() As String
        Get
            Return m_Img
        End Get
        Set
            m_Img = Value
        End Set
    End Property

    Public Property Badge() As String
        Get
            Return m_Badge
        End Get
        Set
            m_Badge = Value
        End Set
    End Property
End Class

Public Class Availability
    Private m_ID As String
    Private m_Mon As String
    Private m_Tue As String
    Private m_Wed As String
    Private m_Thu As String
    Private m_Fri As String
    Private m_Sat As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property Mon() As String
        Get
            Return m_Mon
        End Get
        Set
            m_Mon = Value
        End Set
    End Property

    Public Property Tue() As String
        Get
            Return m_Tue
        End Get
        Set
            m_Tue = Value
        End Set
    End Property

    Public Property Wed() As String
        Get
            Return m_Wed
        End Get
        Set
            m_Wed = Value
        End Set
    End Property

    Public Property Thu() As String
        Get
            Return m_Thu
        End Get
        Set
            m_Thu = Value
        End Set
    End Property

    Public Property Fri() As String
        Get
            Return m_Fri
        End Get
        Set
            m_Fri = Value
        End Set
    End Property

    Public Property Sat() As String
        Get
            Return m_Sat
        End Get
        Set
            m_Sat = Value
        End Set
    End Property
End Class

Public Class Training
    Private m_something1 As String
    Private m_something2 As String
    Private m_something3 As String

    Public Property something1() As String
        Get
            Return m_something1
        End Get
        Set
            m_something1 = Value
        End Set
    End Property

    Public Property something2() As String
        Get
            Return m_something2
        End Get
        Set
            m_something2 = Value
        End Set
    End Property

    Public Property something3() As String
        Get
            Return m_something3
        End Get
        Set
            m_something3 = Value
        End Set
    End Property
End Class

Public Class RootObject
    Private m_scheduleName As String
    Private m_firstName As String
    Private m_lastName As String
    Private m_theRole As String
    Private m_linker As String
    Private m_Schedule As Schedule
    Private m_empInfo As EmpInfo
    Private m_availability As Availability
    Private m_training As Training

    Public Property scheduleName() As String
        Get
            Return m_scheduleName
        End Get
        Set
            m_scheduleName = Value
        End Set
    End Property

    Public Property firstName() As String
        Get
            Return m_firstName
        End Get
        Set
            m_firstName = Value
        End Set
    End Property

    Public Property lastName() As String
        Get
            Return m_lastName
        End Get
        Set
            m_lastName = Value
        End Set
    End Property

    Public Property theRole() As String
        Get
            Return m_theRole
        End Get
        Set
            m_theRole = Value
        End Set
    End Property

    Public Property linker() As String
        Get
            Return m_linker
        End Get
        Set
            m_linker = Value
        End Set
    End Property

    Public Property Schedule() As Schedule
        Get
            Return m_Schedule
        End Get
        Set
            m_Schedule = Value
        End Set
    End Property

    Public Property empInfo() As EmpInfo
        Get
            Return m_empInfo
        End Get
        Set
            m_empInfo = Value
        End Set
    End Property

    Public Property availability() As Availability
        Get
            Return m_availability
        End Get
        Set
            m_availability = Value
        End Set
    End Property

    Public Property training() As Training
        Get
            Return m_training
        End Get
        Set
            m_training = Value
        End Set
    End Property
End Class

How can I create a structure for the json.net to serialize when I do not know what the data names will be? I was thinking of a type of List(as String) or even a Dictionary(String, String) but still am unsure how to go about adding those to the class and populating it using the sql data for those values.

So here is what I am asking:

(1) How can I get the class structure to accept sql column names and values when I do not know what those table names will be?

(2) Able to form the json structure as I am wanting from the data in the class functions.

Brian Rogers · Accepted Answer

Originally I was thinking a Dictionary(Of String, String) would be perfect for this, but it has become clear from your comments that there could be duplicate column names for the training items. Because of this, a Dictionary won't work here, and moreover, the JSON format you want won't work either. You will need to change the training section of the JSON to an array of objects in order to handle the potential duplicates.

Here's how I'm thinking you can make it work:

Change your Training class to have two public properties, Name and Value.

In your RootObject class, define the training property as List(Of Training) instead of a single Training.

Public Class RootObject
    ...
    Private m_training As List(Of Training)

    ...
    Public Property training() As List(Of Training)
        Get
            If m_training Is Nothing Then
                m_training = New List(Of Training))
            End If
            Return m_training
        End Get
        Set(value As List(Of Training))
            m_training = value
        End Set
    End Property
End Class

In your SQL query, make sure that all the training-related columns come last in the SELECT clause. Since you said the column names don't share a common prefix, they only way I can see to identify them is by position.
```
SELECT ID, Name, theRole, THrs, Mon, ..., EMPTR.*
FROM ...
```

In your reader loop, after populating all the known properties for the row, you can loop over the remaining fields in the reader to populate the list of training items for that row.

Const firstTrainingColumnIndex As Integer = 5 'Adjust as necessary for your actual data '

Dim i As Integer
For i = firstTrainingColumnIndex To reader.FieldCount - 1
    trainingItem.Name = reader.GetName(i)
    trainingItem.Value = IIf(reader.IsDBNull(i), Nothing, reader.GetString(i))
    rootObj.training.Add(trainingItem)
Next

To create the JSON output, just serialize like you normally would. Json.Net already knows how to handle a List.

Dim json as String = JsonConvert.SerializeObject(rootObj, Formatting.Indented)

The output should end up looking like this:

{
  "scheduleName": "Bob M",

  ...

  "training": [
    {
      "Name": "trainH1",
      "Value": "168dGrc0"
    },
    {
      "Name": "train58",
      "Value": "89220E"
    },
    {
      "Name": "train52",
      "Value": "2FDEx56"
    },
    {
      "Name": "train05",
      "Value": "5569CCz"
    }
  ]
}

SQL data to custom JSON.net structure

Answers (1)

Related Questions