ASP.NET C#中Web API JSON反序列化及SQL存储问题求助

一、反序列化返回null的常见原因 #

• 实体类属性名与JSON键名不匹配：比如JSON用user_name（蛇形命名），C#用UserName（驼峰命名）且未配置映射规则
• 实体类属性无公共get/set访问器
• JSON嵌套层级与实体类结构不一致：比如JSON包含嵌套对象，但实体类未对应定义子实体类
• 未处理JSON数组：JSON返回数组结构，但实体类定义为单个对象类型
• 反序列化器未忽略未知字段：JSON存在实体类未定义的字段，导致序列化匹配失败

二、正确的JSON反序列化实现示例 #

1. 使用System.Text.Json（ASP.NET Core默认序列化器） #

步骤1：定义匹配的实体类

// 对应示例JSON结构：
// {
//   "userId": 1,
//   "userName": "john_doe",
//   "email": "john@example.com",
//   "address": {
//     "street": "123 Main St",
//     "city": "New York"
//   }
// }

public class User
{
    // 用JsonPropertyName指定JSON键名，解决命名风格差异
    [JsonPropertyName("userId")]
    public int UserId { get; set; }

    [JsonPropertyName("userName")]
    public string UserName { get; set; }

    [JsonPropertyName("email")]
    public string Email { get; set; }

    [JsonPropertyName("address")]
    public Address UserAddress { get; set; }
}

public class Address
{
    [JsonPropertyName("street")]
    public string Street { get; set; }

    [JsonPropertyName("city")]
    public string City { get; set; }
}

步骤2：HttpClient获取并反序列化

using System.Net.Http;
using System.Text.Json;

public async Task<User> GetUserFromApi()
{
    using var httpClient = new HttpClient();
    var response = await httpClient.GetAsync("https://external-api-url/users/1");
    response.EnsureSuccessStatusCode(); // 确保请求成功

    var jsonString = await response.Content.ReadAsStringAsync();
    
    // 配置反序列化选项：忽略未知字段、允许大小写不敏感匹配
    var options = new JsonSerializerOptions
    {
        IgnoreNullValues = true,
        PropertyNameCaseInsensitive = true, // 可替代JsonPropertyName，自动匹配大小写差异的键名
        WriteIndented = true
    };

    var user = JsonSerializer.Deserialize<User>(jsonString, options);
    return user;
}

2. 使用Newtonsoft.Json（Json.NET，需通过NuGet安装） #

实体类定义（用JsonProperty特性）

using Newtonsoft.Json;

public class User
{
    [JsonProperty("userId")]
    public int UserId { get; set; }

    [JsonProperty("userName")]
    public string UserName { get; set; }

    public string Email { get; set; } // 若JSON键名与属性名一致，可省略特性

    [JsonProperty("address")]
    public Address UserAddress { get; set; }
}

public class Address
{
    [JsonProperty("street")]
    public string Street { get; set; }
    [JsonProperty("city")]
    public string City { get; set; }
}

反序列化代码

using System.Net.Http;
using Newtonsoft.Json;

public async Task<User> GetUserWithNewtonsoft()
{
    using var httpClient = new HttpClient();
    var response = await httpClient.GetAsync("https://external-api-url/users/1");
    response.EnsureSuccessStatusCode();

    var jsonString = await response.Content.ReadAsStringAsync();
    var user = JsonConvert.DeserializeObject<User>(jsonString);
    return user;
}

三、SQL存储过程与数据入库示例 #

1. 创建SQL存储过程 #

假设将用户及关联地址数据存入Users和Addresses表（外键关联）

-- 创建Addresses表
CREATE TABLE Addresses (
    AddressId INT IDENTITY(1,1) PRIMARY KEY,
    Street NVARCHAR(100) NOT NULL,
    City NVARCHAR(50) NOT NULL
);

-- 创建Users表
CREATE TABLE Users (
    UserId INT PRIMARY KEY,
    UserName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    AddressId INT FOREIGN KEY REFERENCES Addresses(AddressId)
);

-- 创建存储过程：插入用户及关联地址
CREATE PROCEDURE InsertUserWithAddress
    @UserId INT,
    @UserName NVARCHAR(50),
    @Email NVARCHAR(100),
    @Street NVARCHAR(100),
    @City NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 插入地址并获取自增ID
    DECLARE @AddressId INT;
    INSERT INTO Addresses (Street, City)
    VALUES (@Street, @City);
    SET @AddressId = SCOPE_IDENTITY();

    -- 插入用户数据
    INSERT INTO Users (UserId, UserName, Email, AddressId)
    VALUES (@UserId, @UserName, @Email, @AddressId);
END;

2. C#调用存储过程入库 #

using System.Data.SqlClient;
using System.Threading.Tasks;

public async Task InsertUserToDb(User user)
{
    // 替换为你的数据库连接字符串
    string connectionString = "Server=your-server;Database=your-db;User Id=your-user;Password=your-password;";

    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand("InsertUserWithAddress", connection);
    command.CommandType = System.Data.CommandType.StoredProcedure;

    // 添加存储过程参数
    command.Parameters.AddWithValue("@UserId", user.UserId);
    command.Parameters.AddWithValue("@UserName", user.UserName);
    command.Parameters.AddWithValue("@Email", user.Email);
    command.Parameters.AddWithValue("@Street", user.UserAddress.Street);
    command.Parameters.AddWithValue("@City", user.UserAddress.City);

    await command.ExecuteNonQueryAsync();
}

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