C#控制台应用中含内部DataTable的SqlBulkCopy处理及JSON优化

针对你的问题，我来一步步梳理解决方案，先从最核心的「更合适的JSON读取方式」说起，再分别解决你提到的两种数据处理需求：

直接反序列化为DataTable处理嵌套JSON确实会带来层级混乱的问题，强类型模型反序列化才是更优雅且易维护的方案。你可以先定义与JSON结构完全匹配的C#类，这样反序列化后能直接操作对象，后续处理嵌套数据会轻松很多。

先给出适配你JSON结构的类定义（注意兼容了JSON里cpty和counterparty的字段差异）：

using Newtonsoft.Json;
using System;
using System.Collections.Generic;

public class Transaction
{
    [JsonProperty("cpty")]
    [JsonProperty("counterparty")] // 兼容JSON里两种字段名
    public string Counterparty { get; set; }
    public string InternalLegalEntity { get; set; }
    public DateTime? CreateDate { get; set; }
    public string PaymentCurrency { get; set; }
    public int Id { get; set; }
    public int Version { get; set; }
    public DateTime RevisionDate { get; set; }
    public decimal Amount { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime AsOfDate { get; set; }
    public bool IsDeleted { get; set; }
    public string Source { get; set; }
    public bool? IsProvisional { get; set; }
    public bool? IsProforma { get; set; }
    public List<Item> Items { get; set; }
    public List<Payment> Payments { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public string Currency { get; set; }
    public int ItemId { get; set; }
    public decimal Quantity { get; set; }
    public string Unit { get; set; }
    public DateTime EndDate { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime TransactionDate { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}

public class Payment
{
    public DateTime CreateDate { get; set; }
    public DateTime TransactionDate { get; set; }
    public string PaymentMethod { get; set; }
    public decimal Amount { get; set; }
    public string PaymentCurrency { get; set; }
    public int Id { get; set; }
}

反序列化代码就一行，简单明了：

var transactions = JsonConvert.DeserializeObject<List<Transaction>>(yourJsonString);

如果业务确实需要把嵌套的items/payments字段合并到主表中，要注意一对多关系会导致主表数据膨胀（比如主行对应2个items，就会生成2条主表数据）。用强类型模型处理的话，代码如下：

List<Dictionary<string, object>> mergedData = new List<Dictionary<string, object>>();

foreach (var trans in transactions)
{
    // 先提取主表的所有字段
    var mainFields = new Dictionary<string, object>
    {
        { "Counterparty", trans.Counterparty },
        { "InternalLegalEntity", trans.InternalLegalEntity },
        { "CreateDate", trans.CreateDate },
        { "PaymentCurrency", trans.PaymentCurrency },
        { "Id", trans.Id },
        { "Version", trans.Version },
        { "RevisionDate", trans.RevisionDate },
        { "Amount", trans.Amount },
        { "DueDate", trans.DueDate },
        { "AsOfDate", trans.AsOfDate },
        { "IsDeleted", trans.IsDeleted },
        { "Source", trans.Source },
        { "IsProvisional", trans.IsProvisional },
        { "IsProforma", trans.IsProforma }
    };

    // 合并Items数据
    if (trans.Items != null)
    {
        foreach (var item in trans.Items)
        {
            var mergedRow = new Dictionary<string, object>(mainFields);
            mergedRow.Add("Item_Id", item.Id);
            mergedRow.Add("Item_Amount", item.Amount);
            mergedRow.Add("Item_Currency", item.Currency);
            mergedRow.Add("Item_ItemId", item.ItemId);
            mergedRow.Add("Item_Quantity", item.Quantity);
            mergedRow.Add("Item_Unit", item.Unit);
            mergedRow.Add("Item_EndDate", item.EndDate);
            mergedRow.Add("Item_StartDate", item.StartDate);
            mergedRow.Add("Item_TransactionDate", item.TransactionDate);
            mergedRow.Add("Item_Description", item.Description);
            mergedRow.Add("Item_Price", item.Price);
            mergedData.Add(mergedRow);
        }
    }

    // 如果要合并Payments，同理，但注意同时合并会产生笛卡尔积，需根据业务选择是否单独处理
}

// 最后可以把mergedData转换成DataTable（如果需要）
DataTable mergedTable = new DataTable();
if (mergedData.Count > 0)
{
    foreach (var key in mergedData[0].Keys)
    {
        mergedTable.Columns.Add(key);
    }
    foreach (var row in mergedData)
    {
        mergedTable.Rows.Add(row.Values.ToArray());
    }
}

如果你坚持用原始的嵌套DataTable处理，逻辑类似：遍历主表每行，取出内部DataTable，复制主行字段+内部行字段到新行，最终生成扁平的DataTable。

这种方案更符合数据库的范式设计，因为你的JSON本身就是**主表（Transaction）+子表（Items、Payments）**的一对多关系。用强类型模型配合Dapper（或ADO.NET）操作非常方便，这里用Dapper示例（追求性能的话可以用SqlBulkCopy批量插入）：

using System.Data.SqlClient;
using Dapper;

// 替换成你的数据库连接字符串
string connectionString = "Server=yourServer;Database=yourDb;Trusted_Connection=True;";

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();

    foreach (var trans in transactions)
    {
        // 1. 插入主表Transactions
        string insertTransSql = @"
            INSERT INTO Transactions (Counterparty, InternalLegalEntity, CreateDate, PaymentCurrency, Id, Version, RevisionDate, Amount, DueDate, AsOfDate, IsDeleted, Source, IsProvisional, IsProforma)
            VALUES (@Counterparty, @InternalLegalEntity, @CreateDate, @PaymentCurrency, @Id, @Version, @RevisionDate, @Amount, @DueDate, @AsOfDate, @IsDeleted, @Source, @IsProvisional, @IsProforma)";
        
        conn.Execute(insertTransSql, trans);

        // 2. 插入子表Items（关联主表的Id作为外键）
        if (trans.Items != null)
        {
            string insertItemSql = @"
                INSERT INTO Items (TransactionId, Id, Amount, Currency, ItemId, Quantity, Unit, EndDate, StartDate, TransactionDate, Description, Price)
                VALUES (@TransactionId, @Id, @Amount, @Currency, @ItemId, @Quantity, @Unit, @EndDate, @StartDate, @TransactionDate, @Description, @Price)";
            
            foreach (var item in trans.Items)
            {
                conn.Execute(insertItemSql, new 
                { 
                    TransactionId = trans.Id, 
                    item.Id, 
                    item.Amount, 
                    item.Currency, 
                    item.ItemId, 
                    item.Quantity, 
                    item.Unit, 
                    item.EndDate, 
                    item.StartDate, 
                    item.TransactionDate, 
                    item.Description, 
                    item.Price 
                });
            }
        }

        // 3. 插入子表Payments（同理关联主表Id）
        if (trans.Payments != null)
        {
            string insertPaymentSql = @"
                INSERT INTO Payments (TransactionId, CreateDate, TransactionDate, PaymentMethod, Amount, PaymentCurrency, Id)
                VALUES (@TransactionId, @CreateDate, @TransactionDate, @PaymentMethod, @Amount, @PaymentCurrency, @Id)";
            
            foreach (var payment in trans.Payments)
            {
                conn.Execute(insertPaymentSql, new 
                { 
                    TransactionId = trans.Id, 
                    payment.CreateDate, 
                    payment.TransactionDate, 
                    payment.PaymentMethod, 
                    payment.Amount, 
                    payment.PaymentCurrency, 
                    payment.Id 
                });
            }
        }
    }
}

总结一下，强类型模型反序列化是最优选择，不仅解决了嵌套DataTable的混乱问题，后续的合并或分表操作都更直观、易维护。如果追求性能，批量插入可以用SqlBulkCopy代替逐行插入。

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