JSON反序列化为列表并入库失败，求RESPONSE数据插入方案

问题根源 #

你的代码无法完成反序列化的核心原因有两点：

1. 实体类APICallsResponse中RESPONSE字段定义为string类型，但接口返回的JSON里RESPONSE是数组对象，类型完全不匹配。
2. 你把外层响应字段和RESPONSE数组内的条目字段混在同一个类中，同时部分字段名拼写错误（比如QUERY_MCAT_NAMEE对应JSON中的QUERY_MCAT_NAME，CALL_DURATIONE对应CALL_DURATION），导致反序列化映射失败。

修正实体类 #

拆分出两个类：外层响应类和RESPONSE数组的条目类，同时修正字段类型和拼写错误：

// 外层API响应类，对应JSON根结构
public class APICallsResponse
{
    public int CODE { get; set; } // JSON中CODE是数字，改为int类型
    public string STATUS { get; set; }
    public string MESSAGE { get; set; }
    public int TOTAL_RECORDS { get; set; } // JSON中是数字，改为int类型
    public List<QueryRecord> RESPONSE { get; set; } // 改为数组对应的List类型
}

// RESPONSE数组中的单条记录实体类
public class QueryRecord
{
    public string UNIQUE_QUERY_ID { get; set; }
    public string QUERY_TYPE { get; set; }
    public string QUERY_TIME { get; set; }
    public string SENDER_NAME { get; set; }
    public string SENDER_MOBILE { get; set; }
    public string SENDER_EMAIL { get; set; }
    public string SUBJECT { get; set; }
    public string SENDER_COMPANY { get; set; }
    public string SENDER_ADDRESS { get; set; }
    public string SENDER_CITY { get; set; }
    public string SENDER_STATE { get; set; }
    public string SENDER_PINCODE { get; set; }
    public string SENDER_COUNTRY_ISO { get; set; }
    public string SENDER_MOBILE_ALT { get; set; }
    public string SENDER_PHONE { get; set; }
    public string SENDER_PHONE_ALT { get; set; }
    public string SENDER_EMAIL_ALT { get; set; }
    public string QUERY_PRODUCT_NAME { get; set; }
    public string QUERY_MESSAGE { get; set; }
    public string QUERY_MCAT_NAME { get; set; } // 修正拼写错误
    public string CALL_DURATION { get; set; } // 修正拼写错误
    public string RECEIVER_MOBILE { get; set; } // 修正拼写错误
}

反序列化JSON #

使用Newtonsoft.Json（Json.NET）或System.Text.Json完成反序列化：

方案1：Newtonsoft.Json

using Newtonsoft.Json;

// 假设jsonString是接口返回的原始JSON字符串
var apiResponse = JsonConvert.DeserializeObject<APICallsResponse>(jsonString);
var queryRecords = apiResponse.RESPONSE; // 提取数组中的所有记录

方案2：System.Text.Json

using System.Text.Json;

var apiResponse = JsonSerializer.Deserialize<APICallsResponse>(jsonString);
var queryRecords = apiResponse.RESPONSE;

逐条插入数据库 #

以下提供两种常见的数据库插入实现：

方案1：Entity Framework Core

假设你已经配置好数据库上下文AppDbContext，且包含DbSet<QueryRecord> QueryRecords：

using (var dbContext = new AppDbContext())
{
    foreach (var record in queryRecords)
    {
        dbContext.QueryRecords.Add(record);
    }
    await dbContext.SaveChangesAsync(); // 批量提交，性能更优
}

方案2：ADO.NET

直接使用SQL命令插入：

using System.Data.SqlClient;

var connectionString = "你的数据库连接字符串";
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    foreach (var record in queryRecords)
    {
        var insertCmd = new SqlCommand(@"
            INSERT INTO QueryRecords (
                UNIQUE_QUERY_ID, QUERY_TYPE, QUERY_TIME, SENDER_NAME, SENDER_MOBILE,
                SENDER_EMAIL, SUBJECT, SENDER_COMPANY, SENDER_ADDRESS, SENDER_CITY,
                SENDER_STATE, SENDER_PINCODE, SENDER_COUNTRY_ISO, SENDER_MOBILE_ALT,
                SENDER_PHONE, SENDER_PHONE_ALT, SENDER_EMAIL_ALT, QUERY_PRODUCT_NAME,
                QUERY_MESSAGE, QUERY_MCAT_NAME, CALL_DURATION, RECEIVER_MOBILE
            ) VALUES (
                @UNIQUE_QUERY_ID, @QUERY_TYPE, @QUERY_TIME, @SENDER_NAME, @SENDER_MOBILE,
                @SENDER_EMAIL, @SUBJECT, @SENDER_COMPANY, @SENDER_ADDRESS, @SENDER_CITY,
                @SENDER_STATE, @SENDER_PINCODE, @SENDER_COUNTRY_ISO, @SENDER_MOBILE_ALT,
                @SENDER_PHONE, @SENDER_PHONE_ALT, @SENDER_EMAIL_ALT, @QUERY_PRODUCT_NAME,
                @QUERY_MESSAGE, @QUERY_MCAT_NAME, @CALL_DURATION, @RECEIVER_MOBILE
            )", connection);

        // 绑定参数，避免SQL注入
        insertCmd.Parameters.AddWithValue("@UNIQUE_QUERY_ID", record.UNIQUE_QUERY_ID ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@QUERY_TYPE", record.QUERY_TYPE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@QUERY_TIME", record.QUERY_TIME ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_NAME", record.SENDER_NAME ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_MOBILE", record.SENDER_MOBILE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_EMAIL", record.SENDER_EMAIL ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SUBJECT", record.SUBJECT ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_COMPANY", record.SENDER_COMPANY ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_ADDRESS", record.SENDER_ADDRESS ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_CITY", record.SENDER_CITY ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_STATE", record.SENDER_STATE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_PINCODE", record.SENDER_PINCODE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_COUNTRY_ISO", record.SENDER_COUNTRY_ISO ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_MOBILE_ALT", record.SENDER_MOBILE_ALT ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_PHONE", record.SENDER_PHONE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_PHONE_ALT", record.SENDER_PHONE_ALT ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@SENDER_EMAIL_ALT", record.SENDER_EMAIL_ALT ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@QUERY_PRODUCT_NAME", record.QUERY_PRODUCT_NAME ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@QUERY_MESSAGE", record.QUERY_MESSAGE ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@QUERY_MCAT_NAME", record.QUERY_MCAT_NAME ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@CALL_DURATION", record.CALL_DURATION ?? DBNull.Value);
        insertCmd.Parameters.AddWithValue("@RECEIVER_MOBILE", record.RECEIVER_MOBILE ?? DBNull.Value);

        insertCmd.ExecuteNonQuery();
    }
}

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