VB.NET解析Web API JSON响应到DataSet仅获第一条记录的解决办法

在VB.NET中使用WebRequest调用Web Service API，尝试将JSON响应解析为DataSet，但当前代码仅能解析第一条记录。JSON响应包含多条记录（示例中List数组有2条数据），请问如何解析所有记录？若存在超过10000条记录时，又该如何将全部记录解析到DataSet中？

示例JSON响应 #

"List": [
        {
            "ID": "1",
            "Name": "Karen",
            "PhoneNo": "0010000114248"
        },
        {
            "ID": "2",
            "Name": "Krystal",
            "PhoneNo": "0010000114250"
        }
    ]

当前代码 #

Dim apiUrl = ConfigurationManager.AppSettings("Url")
Dim request As WebRequest = WebRequest.Create(apiUrl)
Dim body = "{""PageIndex"": ""1"", ""PageSize"": ""10000""}"
Dim byteArray As Byte() = Encoding.UTF8.GetBytes(body)
request.Method = "POST"
request.ContentType = "application/json"
request.ContentLength = byteArray.Length
Dim dataStream As Stream = request.GetRequestStream()
dataStream.Write(byteArray, 0, byteArray.Length)
dataStream.Close()
Dim sr As StreamReader = New StreamReader(request.GetResponse().GetResponseStream)
Dim rte As String = sr.ReadToEnd()
Dim jsonObject As JObject = JObject.Parse(rte)
Dim id = If(jsonObject.SelectToken("List")(0)("ID") Is Nothing, "", jsonObject.SelectToken("List")(0)("ID").ToString)
Dim name = If(jsonObject.SelectToken("List")(0)("Name") Is Nothing, "", jsonObject.SelectToken("List")(0)("Name").ToString)
Dim phoneNo = If(jsonObject.SelectToken("List")(0)("PhoneNo") Is Nothing, "", jsonObject.SelectToken("List")(0)("PhoneNo").ToString)
Dim dt As DataTable = New DataTable("Result")
dt.Columns.Add("id")
dt.Columns.Add("name")
dt.Columns.Add("phoneNo")

dt.Rows.Add(id, name, phoneNo)
Dim ds As DataSet = New DataSet("Result")
ds.Tables.Add(dt)
Return ds.GetXml()

1. 解析所有记录到DataSet #

当前代码仅取了List数组的第0个元素（第一条记录），要获取全部记录，需取出整个List数组并循环遍历处理：

修改后的代码如下：

Dim apiUrl = ConfigurationManager.AppSettings("Url")
Dim request As WebRequest = WebRequest.Create(apiUrl)
Dim body = "{""PageIndex"": ""1"", ""PageSize"": ""10000""}"
Dim byteArray As Byte() = Encoding.UTF8.GetBytes(body)
request.Method = "POST"
request.ContentType = "application/json"
request.ContentLength = byteArray.Length

' 使用Using自动释放资源
Using dataStream As Stream = request.GetRequestStream()
    dataStream.Write(byteArray, 0, byteArray.Length)
End Using

Using response = request.GetResponse()
    Using sr As New StreamReader(response.GetResponseStream())
        Dim rte As String = sr.ReadToEnd()
        Dim jsonObject As JObject = JObject.Parse(rte)
        
        ' 获取完整的List数组
        Dim listArray As JArray = jsonObject.SelectToken("List")
        
        Dim dt As DataTable = New DataTable("Result")
        dt.Columns.Add("id")
        dt.Columns.Add("name")
        dt.Columns.Add("phoneNo")
        
        ' 遍历数组中每条记录，添加到DataTable
        For Each item As JObject In listArray
            Dim id = If(item("ID") Is Nothing, "", item("ID").ToString)
            Dim name = If(item("Name") Is Nothing, "", item("Name").ToString)
            Dim phoneNo = If(item("PhoneNo") Is Nothing, "", item("PhoneNo").ToString)
            dt.Rows.Add(id, name, phoneNo)
        Next
        
        Dim ds As DataSet = New DataSet("Result")
        ds.Tables.Add(dt)
        Return ds.GetXml()
    End Using
End Using

关键修改点： #

• 提取List对应的JArray对象，而非仅取索引0的元素
• 通过For Each循环遍历所有记录，逐个提取字段并添加到DataTable
• 用Using语句管理流和响应对象，自动释放资源，避免内存泄漏

2. 处理超过10000条记录的情况 #

若总记录数超过10000，API通常会分页返回数据，需循环请求各分页数据直至获取全部记录：

前提：确认API分页机制 #

假设API返回的完整JSON包含总记录数TotalCount和当前页记录List，示例如下：

{
    "TotalCount": 25000,
    "List": [/* 当前页记录 */]
}

分页获取完整数据的代码 #

Dim apiUrl = ConfigurationManager.AppSettings("Url")
Dim dt As DataTable = New DataTable("Result")
dt.Columns.Add("id")
dt.Columns.Add("name")
dt.Columns.Add("phoneNo")

Dim pageIndex As Integer = 1
Const pageSize As Integer = 10000
Dim totalCount As Integer = Integer.MaxValue
Dim fetchedCount As Integer = 0

Do While fetchedCount < totalCount
    ' 构造当前页请求体
    Dim body = $"{{""PageIndex"": ""{pageIndex}"", ""PageSize"": ""{pageSize}""}}"
    Dim byteArray As Byte() = Encoding.UTF8.GetBytes(body)
    
    Using request As WebRequest = WebRequest.Create(apiUrl)
        request.Method = "POST"
        request.ContentType = "application/json"
        request.ContentLength = byteArray.Length
        
        Using dataStream As Stream = request.GetRequestStream()
            dataStream.Write(byteArray, 0, byteArray.Length)
        End Using
        
        Using response = request.GetResponse()
            Using sr As New StreamReader(response.GetResponseStream())
                Dim rte As String = sr.ReadToEnd()
                Dim jsonObject As JObject = JObject.Parse(rte)
                
                ' 首次请求获取总记录数
                If pageIndex = 1 Then
                    totalCount = If(jsonObject.SelectToken("TotalCount") Is Nothing, 0, jsonObject.SelectToken("TotalCount").ToObject(Of Integer))
                End If
                
                ' 获取当前页记录数组
                Dim listArray As JArray = jsonObject.SelectToken("List")
                If listArray Is Nothing OrElse listArray.Count = 0 Then
                    Exit Do ' 无更多记录，退出循环
                End If
                
                ' 添加当前页记录到DataTable
                For Each item As JObject In listArray
                    Dim id = If(item("ID") Is Nothing, "", item("ID").ToString)
                    Dim name = If(item("Name") Is Nothing, "", item("Name").ToString)
                    Dim phoneNo = If(item("PhoneNo") Is Nothing, "", item("PhoneNo").ToString)
                    dt.Rows.Add(id, name, phoneNo)
                Next
                
                fetchedCount += listArray.Count
                pageIndex += 1
            End Using
        End Using
    End Using
Loop

Dim ds As DataSet = New DataSet("Result")
ds.Tables.Add(dt)
Return ds.GetXml()

关键要点： #

• 用Do While循环，根据总记录数和已获取记录数判断是否继续请求下一页
• 首次请求时获取总记录数，后续递增PageIndex
• 处理空数组的情况，避免无限循环
• 全程用Using管理资源，防止内存泄漏

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