我正在尝试编写一个备份程序来备份我们 CRM 中的大量数据，因为它的原生版本缺少很多我们想要保护的信息。我需要使用 vb.net（.Net Framework 4.8.1），我的最终目标是，一旦我弄清楚我们要备份的确切值，就将其映射到具有大量表的 SQL 服务器。调用是 HTTP GET，响应是嵌套的 JSON。除此之外，我希望尽可能多地备份，我并不真正关心我必须使用什么方法/nuget 包（只要信誉良好）/任何我可以使用的东西来实现这一点。我以前从未处理过 JSON，坦率地说，我对此感到力不从心。
数据变化极大且庞大——主要属性可能有多达 300 个，其中大部分都保留了版本历史记录，但并非全部。空白值似乎不会返回，因此响应的结构有点不可预测。
基本结构高度匿名：
{
"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 protected]",
"timestamp": 1685656111147,
"is-primary": true
},
{
"type": "LEAD_GUID",
"value": "alphanumeric_string",
"timestamp": 1685656112534
}
]
}
],
"merge-audits": []
}
我认为版本历史部分足够有规律，我可以通过映射到带有字段的 SQL 表来处理它们：
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
我想说的是
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()
或者其类似的更新版本（如果该 ID 已经存在）。
我只是真的很难从返回的 JSON 中得到可以开始分配给变量的东西。我也不知道如何处理破坏模式并具有自身嵌套结构的最后几个属性。
以下是我尝试获取 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 testclient As HttpClient = GetCrmClient() 'adds headers if not there
'Dim email As String = "[email protected]"
'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 = "[email protected]"
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
我知道这很多，我可以使用您提供的任何关于从哪里开始的建议。
编辑：添加了.Net 版本（.Net Framework 4.8.1）