在C#的2个SQL表中插入嵌套的JSON

using System;
using System.Data.SqlClient;
using Newtonsoft.Json;

// 嵌套的JSON数据结构
var jsonData = new
{
    parentField1 = "Parent Value 1",
    parentField2 = "Parent Value 2",
    childItems = new[]
    {
        new { childField1 = "Child Value 1", childField2 = "Child Value 2" },
        new { childField1 = "Child Value 3", childField2 = "Child Value 4" }
    }
};

// 将嵌套的JSON数据转换为字符串
var jsonStr = JsonConvert.SerializeObject(jsonData);

// 连接到数据库
using (var connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();

    // 插入父表记录
    using (var command = new SqlCommand("INSERT INTO ParentTable (JsonField) VALUES (@json)", connection))
    {
        command.Parameters.AddWithValue("@json", jsonStr);
        command.ExecuteNonQuery();
    }

    // 获取插入的父表记录的主键值
    int parentId;
    using (var command = new SqlCommand("SELECT SCOPE_IDENTITY()", connection))
    {
        parentId = Convert.ToInt32(command.ExecuteScalar());
    }

    // 插入子表记录
    foreach (var childItem in jsonData.childItems)
    {
        // 将子项的外键设置为父表记录的主键值
        childItem.parentId = parentId;

        // 将子项的JSON数据转换为字符串
        var childJsonStr = JsonConvert.SerializeObject(childItem);

        // 插入子表记录
        using (var command = new SqlCommand("INSERT INTO ChildTable (ParentId, JsonField) VALUES (@parentId, @json)", connection))
        {
            command.Parameters.AddWithValue("@parentId", parentId);
            command.Parameters.AddWithValue("@json", childJsonStr);
            command.ExecuteNonQuery();
        }
    }
}