HTTP nested variable JSON response to collection or SQL using vb.net

General
1

I am trying to write a backup program for a bunch of data in our CRM, because its native version lacks quite a bit of information we want secured. I’m needing to use vb.net (.Net Framework 4.8.1), and my end goal is mapping to a SQL server with a host of tables once I figure out exactly what values we’re backing up. The call is an HTTP GET, and the response is in nested JSON. Beyond that I want to back up as much as I can, and I don’t really care what methods/nuget packages (as long as reputable)/whatever I have to use to get there. I’ve never dealt with JSON before and I’m frankly in over my head.

The data is extremely variable and huge- there could be as many as 300 main properties, most of which keep version history, but not all. Blank values don’t appear to be returned, so the response is a bit unpredictable in structure.

Basic structure heavily anonymized:

{
  "vid": 12345,
  "canonical-vid": 12345,
  "merged-vids": [],
  "portal-id": 12345678,
  "is-contact": true,
  "properties": {
    "prop1": {
      "value": "12345678",
      "versions": [
        {
          "value": "12345678",
          "source-type": "CALCULATED",
          "source-id": "source1",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1717375078168,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        },
        {
          "value": "12345678;12345679",
          "source-type": "CALCULATED",
          "source-id": "source1",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1711381559120,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        },
        {
          "value": "12345679",
          "source-type": "CALCULATED",
          "source-id": "source1",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1711037348472,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        },
        {
          "value": "12345679;12345680",
          "source-type": "CALCULATED",
          "source-id": "source1",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1711027558111,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        },
        {
          "value": "12345681",
          "source-type": "CALCULATED",
          "source-id": "source1",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1710899316341,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        }
      ]
    },
    "prop2": {
      "value": "https://myurl.com/contact/",
      "versions": [
        {
          "value": "https://myurl.com/contact/",
          "source-type": "ANALYTICS",
          "source-id": "source2",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1685656187542,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        },
        {
          "value": "https://myurl.com/page 2/",
          "source-type": "ANALYTICS",
          "source-id": "source2",
          "source-label": null,
          "updated-by-user-id": null,
          "timestamp": 1685656121699,
          "selected": false,
          "data-sensitivity": null,
          "is-encrypted": null
        }
      ]
    },
'...continue like this for a variable amount of properties with a variable amount of nested versions each, though all the versions sections have the same properties, then finish off with the below...
},
  "form-submissions": [
    {
      "conversion-id": "alphanumeric_string",
      "timestamp": 1685656111147,
      "form-id": "alphanumeric_string",
      "portal-id": 12345678,
      "page-url": "https://myurl.com/form page/",
      "page-title": "My Page Title",
      "title": "#gform_2 .form",
      "form-type": "CAPTURED",
      "meta-data": []
    }
  ],
  "list-memberships": [],
  "identity-profiles": [
    {
      "vid": 12345,
      "saved-at-timestamp": 1685656112544,
      "deleted-changed-timestamp": 0,
      "identities": [
        {
          "type": "EMAIL",
          "value": "email@domain.com",
          "timestamp": 1685656111147,
          "is-primary": true
        },
        {
          "type": "LEAD_GUID",
          "value": "alphanumeric_string",
          "timestamp": 1685656112534
        }
      ]
    }
  ],
  "merge-audits": []
}

I’m thinking the version history sections are regular enough I can handle those by mapping to a SQL table with fields:

ID int identity no null, 
ObjType varchar(32) no null, 
ObjID int no null,
Prop varchar(128) no null,
value varchar no null,
sourceType varchar no null,
sourceId varchar null,
sourceLabel varchar null,
updatedByUserID varchar null,
timestamp varchar no null,
selected boolean no null,
dataSensitivity varchar null,
isEncrypted boolean null

I want to get to the point where I can say something like

Dim Fields as string = ""
Dim Values as string = ""
Dim VerHistory as List(Of VerHistoryCustomClass)
For Each prop as JsonMember in JsonObject
  Fields &= prop.Name & ","
  Values &= prop.Value & ","
  If (some test for presence of version history) Then
    dim hist as new VerHistoryCustomClass(variables...)
    VerHistory.Add(hist)
  End If
End For

SQLComm.CommandText = "INSERT INTO objTable (" & Fields & ")
VALUES (" & Values & ")"
SQLcon.Open()
SQLcomm.ExecuteNonQuery()

For Each ver as VerHistoryCustomClass in VerHistory
  SQLcomm.CommandText = "INSERT INTO verTable VALUES (" & ver.toCommaDelimitedString & ")"
  SQLcomm.ExecuteNonQuery()
End For

SQLcon.Close()

or its similar update version if the ID was already there.

I’m just really struggling getting from the returned JSON to something I can start assigning to variables. I also don’t know how to necessarily deal with the last few properties that break the pattern and have nested structures of their own.

Here’s some of my trying to get the JSON back and dealing with it:

IMPORTS RestSharp
Public Class Test_Area
    Private ReadOnly HttpClientTest As New HttpClient
    Friend ReadOnly EndptContactsAllData As String = "https://apiurl.com/endpt"
    Private Async Sub btnTestGetSpecificContact_Click(sender As Object, e As EventArgs) Handles btnTestGetSpecificContact.Click
        'Dim testclient As HttpClient = GetCrmClient() 'adds headers if not there
        'Dim email As String = "person@domain.com"
        'Dim fullUri = EndptContactsAllData & "/contact/email/" & email & "/profile"
        'Dim response As HttpResponseMessage = Await testclient.GetAsync(fullUri)
        'If response.IsSuccessStatusCode Then
        '    Dim pause = True
        '    Dim raw = Await response.Content.ReadAsStringAsync()
        '    'Using jDoc As JsonDocument = JsonDocument.Parse(raw)
        '    '    Dim JRtElement As JsonElement = jDoc.RootElement
        '    '    pause = True
        '    'End Using
        'Else
        'End If
        Dim email As String = "person@domain.com"
        Dim fullUri = EndptContactsAllData & "/contact/email/" & email & "/profile"
        Dim testclient As New RestClient(fullUri)
        Dim testReq As New RestRequest(Method.GET)
        testReq.AddHeaders(GetCrmClientHeaders) 'GetCrmClientHeaders returns dictionary(Of string, string) for header values
        Dim testResponse As IRestResponse = Await testclient.ExecuteAsync(testReq)
        If testResponse.IsSuccessful Then
            'handle returned JSON
        Else
            'handle error codes
        End If
    End Sub

    Public Function GetCrmClient() As HttpClient
        If HttpIsInitialized = False Then
            HttpClientTest.DefaultRequestHeaders.Add("accept", "application/json")
            HttpClientTest.DefaultRequestHeaders.Add("authorization", "Bearer " & AccessToken)
        End If
        Return HttpClientTest
    End Function

    Public Function GetCrmClientHeaders() As Dictionary(Of String, String)
        Dim hdrDict As New Dictionary(Of String, String)
        hdrDict.Add("accept", "application/json")
        hdrDict.Add("authorization", "Bearer " & AccessToken)
        Return hdrDict

    End Function
END Class

I know this is a lot, I can use any suggestions of where to start that you can give.

Edit: Added .Net version (.Net Framework 4.8.1)

2

It sounds like you’re working on a fairly complex backup system for CRM data that requires fetching and parsing nested JSON, mapping that data to a SQL database, and handling version histories for various properties. I’ll walk you through the process of working with the JSON response, extracting values, and using those to insert data into SQL Server. Since you’re using VB.NET and .NET Framework 4.8.1, I’ll guide you step by step with code examples.

Key Concepts

  1. Parsing JSON: We’ll use the Newtonsoft.Json (Json.NET) library, which is a common and powerful tool for parsing and working with JSON in .NET. It’s easy to use and works well with nested JSON data.
  2. Mapping to SQL: Once we have the data, we’ll insert it into SQL tables based on your structure.
  3. Handling Nested Data: Since your JSON contains version histories and nested objects like form-submissions, list-memberships, and identity-profiles, we will extract the values from those and map them to a SQL schema.

Step 1: Install Newtonsoft.Json

First, you’ll need to install the Newtonsoft.Json package to work with JSON easily in your VB.NET project:

  1. Right-click on your project in Solution Explorer.
  2. Click Manage NuGet Packages.
  3. Search for Newtonsoft.Json and install it.

This will provide you with the classes and methods necessary to parse and manipulate JSON.

Step 2: Define Classes for Mapping

Based on the JSON structure you shared, you’ll want to define classes that map to the data. For example, here’s how you could map the version history part of your JSON:

Public Class VerHistoryCustomClass
    Public Property ID As Integer
    Public Property ObjType As String
    Public Property ObjID As Integer
    Public Property Prop As String
    Public Property Value As String
    Public Property SourceType As String
    Public Property SourceId As String
    Public Property SourceLabel As String
    Public Property UpdatedByUserID As String
    Public Property Timestamp As String
    Public Property Selected As Boolean
    Public Property DataSensitivity As String
    Public Property IsEncrypted As Boolean
End Class

Public Class JsonResponse
    Public Property Vid As Integer
    Public Property CanonicalVid As Integer
    Public Property MergedVids As List(Of Integer)
    Public Property PortalId As Integer
    Public Property IsContact As Boolean
    Public Property Properties As Dictionary(Of String, PropertyDetails)
    Public Property FormSubmissions As List(Of FormSubmission)
    Public Property ListMemberships As List(Of Object)
    Public Property IdentityProfiles As List(Of IdentityProfile)
    Public Property MergeAudits As List(Of Object)
End Class

Public Class PropertyDetails
    Public Property Value As String
    Public Property Versions As List(Of VersionHistory)
End Class

Public Class VersionHistory
    Public Property Value As String
    Public Property SourceType As String
    Public Property SourceId As String
    Public Property SourceLabel As String
    Public Property UpdatedByUserId As String
    Public Property Timestamp As Long
    Public Property Selected As Boolean
    Public Property DataSensitivity As String
    Public Property IsEncrypted As Boolean
End Class

Public Class FormSubmission
    Public Property ConversionId As String
    Public Property Timestamp As Long
    Public Property FormId As String
    Public Property PortalId As Integer
    Public Property PageUrl As String
    Public Property PageTitle As String
    Public Property Title As String
    Public Property FormType As String
    Public Property MetaData As List(Of Object)
End Class

Public Class IdentityProfile
    Public Property Vid As Integer
    Public Property SavedAtTimestamp As Long
    Public Property DeletedChangedTimestamp As Long
    Public Property Identities As List(Of Identity)
End Class

Public Class Identity
    Public Property Type As String
    Public Property Value As String
    Public Property Timestamp As Long
    Public Property IsPrimary As Boolean
End Class

Step 3: Parse the JSON Response

Now, when you get the JSON response from the CRM API, you can deserialize it into these objects using the JsonConvert class from Newtonsoft.Json.

Here’s how you would get the data from the API and parse it:

Imports Newtonsoft.Json
Imports RestSharp

Public Class Test_Area
    Private ReadOnly HttpClientTest As New HttpClient
    Friend ReadOnly EndptContactsAllData As String = "https://apiurl.com/endpt"

    Private Async Sub btnTestGetSpecificContact_Click(sender As Object, e As EventArgs) Handles btnTestGetSpecificContact.Click
        Dim email As String = "person@domain.com"
        Dim fullUri = EndptContactsAllData & "/contact/email/" & email & "/profile"
        Dim testclient As New RestClient(fullUri)
        Dim testReq As New RestRequest(Method.GET)
        testReq.AddHeaders(GetCrmClientHeaders) ' GetCrmClientHeaders returns dictionary(Of string, string)

        ' Execute the GET request asynchronously
        Dim testResponse As IRestResponse = Await testclient.ExecuteAsync(testReq)

        If testResponse.IsSuccessful Then
            ' Parse the JSON response into the JsonResponse object
            Dim jsonResponse As JsonResponse = JsonConvert.DeserializeObject(Of JsonResponse)(testResponse.Content)

            ' Example: Process the data, like inserting it into SQL
            ProcessJsonData(jsonResponse)
        Else
            ' Handle error codes
            MsgBox("Error: " & testResponse.StatusCode)
        End If
    End Sub

    ' This function can process the JSON data and prepare it for insertion into SQL
    Private Sub ProcessJsonData(jsonResponse As JsonResponse)
        Dim fields As String = ""
        Dim values As String = ""
        Dim verHistory As New List(Of VerHistoryCustomClass)()

        ' Iterate through the properties
        For Each prop In jsonResponse.Properties
            fields &= prop.Key & ","
            values &= prop.Value.Value & ","

            ' Check if there is version history for the property
            If prop.Value.Versions IsNot Nothing AndAlso prop.Value.Versions.Any() Then
                For Each ver In prop.Value.Versions
                    Dim hist As New VerHistoryCustomClass With {
                        .ObjType = "Property",  ' You may customize this based on your logic
                        .ObjID = jsonResponse.Vid,
                        .Prop = prop.Key,
                        .Value = ver.Value,
                        .SourceType = ver.SourceType,
                        .SourceId = ver.SourceId,
                        .SourceLabel = ver.SourceLabel,
                        .UpdatedByUserID = ver.UpdatedByUserId,
                        .Timestamp = ver.Timestamp.ToString(),
                        .Selected = ver.Selected,
                        .DataSensitivity = ver.DataSensitivity,
                        .IsEncrypted = ver.IsEncrypted
                    }
                    verHistory.Add(hist)
                Next
            End If
        Next

        ' Perform SQL operations (you can use a helper function to insert data into your database)
        InsertDataIntoSql(fields, values, verHistory)
    End Sub

    ' Function to insert the data into SQL
    Private Sub InsertDataIntoSql(fields As String, values As String, verHistory As List(Of VerHistoryCustomClass))
        ' Assuming you have an existing SQL connection and command setup
        Using connection As New SqlConnection("YourConnectionStringHere")
            connection.Open()

            ' Insert into the main object table
            Dim command As New SqlCommand("INSERT INTO objTable (" & fields.TrimEnd(","c) & ") VALUES (" & values.TrimEnd(","c) & ")", connection)
            command.ExecuteNonQuery()

            ' Insert the version history
            For Each ver In verHistory
                Dim verFields As String = "ObjType, ObjID, Prop, Value, SourceType, SourceId, SourceLabel, UpdatedByUserID, Timestamp, Selected, DataSensitivity, IsEncrypted"
                Dim verValues As String = String.Join(",", ver.ObjType, ver.ObjID, ver.Prop, ver.Value, ver.SourceType, ver.SourceId, ver.SourceLabel, ver.UpdatedByUserID, ver.Timestamp, ver.Selected, ver.DataSensitivity, ver.IsEncrypted)
                Dim verCommand As New SqlCommand("INSERT INTO verTable (" & verFields & ") VALUES (" & verValues & ")", connection)
                verCommand.ExecuteNonQuery()
            Next
        End Using
    End Sub
End Class

Step 4: Handling Nested Structures

For the form-submissions, list-memberships, and identity-profiles sections (which have nested arrays), you’ll need to loop through each section and extract the values in a similar manner. For example, for form-submissions, you might need to create a separate class and handle it like the other nested data.

Final Thoughts:

  • Deserialization: We used JsonConvert.DeserializeObject(Of JsonResponse) to parse the entire JSON response into a strongly-typed object (JsonResponse). This gives you the ability to easily access the data and map it to SQL tables.
  • SQL Insertion: After processing the JSON, you prepare the SQL fields and values strings, then use SqlCommand to insert the data.
  • Nested Data: You handle nested data like version history or form submissions by checking for nested arrays and processing each item individually.

This approach will allow you to extract values from complex, nested JSON and insert them into a SQL database efficiently.