VB.NET(.NET4.8.1)解析嵌套JSON并同步至SQL Server方案咨询

针对你遇到的动态嵌套JSON解析、可变结构处理及SQL入库问题，以下是可落地的实现方案：

一、动态JSON解析核心实现 #

由于CRM返回的JSON结构可变、空值不返回，强类型反序列化极易出错，推荐使用**Newtonsoft.Json（Json.NET）**的JObject/JToken进行动态解析，这是.NET Framework 4.8.1下处理非标准JSON的最优选择。

1. 发起HTTP请求并解析JSON #

Imports System.Net.Http
Imports Newtonsoft.Json.Linq

' 异步获取并解析CRM API数据
Async Function FetchAndParseCrmDataAsync(apiUrl As String, authToken As String) As Task(Of JObject)
    Using httpClient As New HttpClient()
        ' 设置授权头（根据CRM API要求调整）
        httpClient.DefaultRequestHeaders.Authorization = 
            New System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", authToken)
        
        Dim response = Await httpClient.GetAsync(apiUrl)
        response.EnsureSuccessStatusCode() ' 确保请求成功
        
        Dim rawJson = Await response.Content.ReadAsStringAsync()
        Return JObject.Parse(rawJson) ' 解析为动态JObject
    End Using
End Function

2. 处理动态属性与版本历史 #

遍历JSON节点时，通过JToken.Type判断节点类型，用ContainsKey和?.操作符避免空引用异常：

' 示例：解析properties中的主属性及版本历史
Dim crmData = Await FetchAndParseCrmDataAsync("your-crm-api-url", "your-auth-token")

If crmData.ContainsKey("properties") Then
    Dim properties = crmData("properties")
    For Each prop In properties.Properties()
        Dim propName = prop.Name
        Dim propValue = prop.Value

        ' 判断是否为版本历史数组
        If propValue.Type = JTokenType.Array Then
            ' 遍历每个版本记录
            For Each version In propValue
                ' 安全提取字段（空值时返回Nothing）
                Dim versionId = version("id")?.ToString()
                Dim versionValue = version("value")?.ToString()
                Dim createdAt = If(version("createdAt") IsNot Nothing, 
                    DateTime.Parse(version("createdAt").ToString()), Nothing)
                
                ' 此处可将版本数据存入SQL版本表
            Next
        Else
            ' 无版本历史的属性，提取当前值
            Dim currentValue = propValue.ToString()
            ' 存入SQL主表
        End If
    Next
End If

' 处理其他嵌套结构（如form-submissions）
If crmData.ContainsKey("form-submissions") Then
    Dim submissions = crmData("form-submissions")
    For Each submission In submissions
        Dim submissionId = submission("id")?.ToString()
        Dim formName = submission("form-name")?.ToString()
        ' 提取表单数据并存入对应SQL表
    Next
End If

二、非标准嵌套结构处理技巧 #

• 类型判断优先：每次提取节点前用JToken.Type判断是对象、数组还是值类型，避免类型转换错误
• 安全取值：用?.ToString()替代直接取值，空值时返回Nothing，适配API不返回空值的特性
• 存在性检查：用ContainsKey判断属性是否存在，再进行后续操作，避免KeyNotFoundException

三、SQL Server批量入库优化 #

针对大数据体量，推荐使用SqlBulkCopy批量插入，远高于单条INSERT的效率：

1. 定义版本历史模型与批量插入逻辑 #

Imports System.Data.SqlClient
Imports System.Data

' 版本历史数据模型
Public Class PropertyVersion
    Public Property PropertyName As String
    Public Property VersionId As String
    Public Property Value As String
    Public Property CreatedAt As DateTime?
End Class

' 批量插入版本历史到SQL Server
Sub BulkInsertPropertyVersions(versions As List(Of PropertyVersion), connectionString As String)
    Using bulkCopy As New SqlBulkCopy(connectionString)
        bulkCopy.DestinationTableName = "dbo.PropertyVersions"
        
        ' 映射模型字段与SQL表列
        bulkCopy.ColumnMappings.Add("PropertyName", "PropertyName")
        bulkCopy.ColumnMappings.Add("VersionId", "VersionId")
        bulkCopy.ColumnMappings.Add("Value", "Value")
        bulkCopy.ColumnMappings.Add("CreatedAt", "CreatedAt")

        ' 将List转换为DataTable
        Dim dataTable = New DataTable()
        dataTable.Columns.Add("PropertyName", GetType(String))
        dataTable.Columns.Add("VersionId", GetType(String))
        dataTable.Columns.Add("Value", GetType(String))
        dataTable.Columns.Add("CreatedAt", GetType(DateTime))

        For Each version In versions
            Dim row = dataTable.NewRow()
            row("PropertyName") = version.PropertyName
            row("VersionId") = version.VersionId
            row("Value") = version.Value
            row("CreatedAt") = If(version.CreatedAt.HasValue, version.CreatedAt.Value, DBNull.Value)
            dataTable.Rows.Add(row)
        Next

        bulkCopy.WriteToServer(dataTable)
    End Using
End Sub

2. 事务保障数据一致性 #

如果主表与版本表的插入需要原子性，用SqlTransaction包裹操作：

Using conn As New SqlConnection("your-connection-string")
    conn.Open()
    Using tran = conn.BeginTransaction()
        Try
            ' 执行主表插入
            ' 执行版本表批量插入
            tran.Commit()
        Catch ex As Exception
            tran.Rollback()
            ' 日志记录异常
            Throw
        End Try
    End Using
End Using

四、额外优化建议 #

• 日志记录：添加日志（如NLog、log4net）记录解析异常、缺失属性等信息，方便排查问题
• 分页请求：如果API返回数据量极大，拆分成分页请求，避免内存溢出
• 结构缓存：若CRM API结构不会频繁变动，缓存已识别的属性列表，减少重复判断开销

内容的提问来源于stack exchange，提问作者CarrierPigeon