DataSet合并两张表后DataGridView仅显示第一张表数据的问题排查

代码中的核心问题 #

• DataAdapter误用：第二个查询填充dtCnt时，错误使用了第一个查询的dataAdapter，应该用新建的da，导致dtCnt未正确加载数据。
• 查询逻辑缺失：select2未按订单号分组，也没查询订单号字段，返回的是全局总和，无法和主订单表关联。
• 跨层查询错误：第三个查询试图直接在数据库中查询DataSet内存里的dtData和dtCnt表，数据库不存在这两个表，因此查询完全无效。

推荐方案：合并为单条SQL查询（高效简洁） #

直接在数据库层面完成关联与计算，避免内存处理的繁琐，同时保证性能：

SELECT 
    mo.fldOrderNum AS [Order], 
    mo.fldBatchName As Batch, 
    mo.fldShipWeekDate AS [Ship Week], 
    TRIM(CONCAT(mo.fldFinishDesc, ' ', mo.fldSpecialFX1Desc, ' ', mo.fldSpecialFX2Desc, ' ', mo.fldSpecialFX3Desc, ' ', mo.fldSpecialFX4Desc, ' ', mo.fldSpecialFinishName, ' ', mo.fldFinishNote1, ' ', mo.fldFinishNote2, ' ', mo.fldFinishNote3)) AS FinishDesc, 
    mo.fldVarnishTypeDesc AS Varnish, 
    mo.fldColorSampleNum AS [Sample #], 
    mo.fldNumberOfCabinets AS Items, 
    mo.fldDateWritten AS Written,
    COALESCE(SUM(CASE oi.fldProductID WHEN 'b' THEN oi.fldQuantity WHEN 'w' THEN oi.fldQuantity WHEN 'T' THEN oi.fldQuantity WHEN 'v' THEN oi.fldQuantity END), 0) AS Cabs
FROM tblMainOrder mo
LEFT JOIN tblOrderItems oi ON mo.fldOrderNum = oi.fldOrderNum
WHERE 
    mo.fldOrderStatus <> 99 
    AND mo.fldFinishingOrderedDate IS NULL 
    AND mo.fldDateShipped IS NULL 
    AND (mo.fldFinishDesc IN ('BM', 'Spec Paint', 'SPEC PAINT', 'Spec Stain', 'SPEC STAIN', 'Special P', 'SPECIAL R', 'SW'))
GROUP BY 
    mo.fldOrderNum, mo.fldBatchName, mo.fldShipWeekDate, FinishDesc, mo.fldVarnishTypeDesc, mo.fldColorSampleNum, mo.fldNumberOfCabinets, mo.fldDateWritten

对应的简化C#代码：

public string c = "你的数据源字符串";
public DataSet ds = new DataSet();

public void GetData()
{
    var select = @"SELECT 
        mo.fldOrderNum AS [Order], 
        mo.fldBatchName As Batch, 
        mo.fldShipWeekDate AS [Ship Week], 
        TRIM(CONCAT(mo.fldFinishDesc, ' ', mo.fldSpecialFX1Desc, ' ', mo.fldSpecialFX2Desc, ' ', mo.fldSpecialFX3Desc, ' ', mo.fldSpecialFX4Desc, ' ', mo.fldSpecialFinishName, ' ', mo.fldFinishNote1, ' ', mo.fldFinishNote2, ' ', mo.fldFinishNote3)) AS FinishDesc, 
        mo.fldVarnishTypeDesc AS Varnish, 
        mo.fldColorSampleNum AS [Sample #], 
        mo.fldNumberOfCabinets AS Items, 
        mo.fldDateWritten AS Written,
        COALESCE(SUM(CASE oi.fldProductID WHEN 'b' THEN oi.fldQuantity WHEN 'w' THEN oi.fldQuantity WHEN 'T' THEN oi.fldQuantity WHEN 'v' THEN oi.fldQuantity END), 0) AS Cabs
    FROM tblMainOrder mo
    LEFT JOIN tblOrderItems oi ON mo.fldOrderNum = oi.fldOrderNum
    WHERE 
        mo.fldOrderStatus <> 99 
        AND mo.fldFinishingOrderedDate IS NULL 
        AND mo.fldDateShipped IS NULL 
        AND (mo.fldFinishDesc IN ('BM', 'Spec Paint', 'SPEC PAINT', 'Spec Stain', 'SPEC STAIN', 'Special P', 'SPECIAL R', 'SW'))
    GROUP BY 
        mo.fldOrderNum, mo.fldBatchName, mo.fldShipWeekDate, FinishDesc, mo.fldVarnishTypeDesc, mo.fldColorSampleNum, mo.fldNumberOfCabinets, mo.fldDateWritten";

    var dataAdapter = new SqlDataAdapter(select, c);
    DataTable dtFnl = new DataTable();
    dataAdapter.Fill(dtFnl);

    DGV1.DataSource = dtFnl;
}

备选方案：修复DataSet内表关联逻辑 #

如果必须在内存中处理DataSet表，需修正以下几点：

1. 修正第二个查询的填充逻辑，补充分组与订单号字段
2. 使用LINQ关联两个表生成最终结果

public string c = "你的数据源字符串";
public DataSet ds = new DataSet();

public void GetData()
{
    // 加载主订单数据
    var select = "Select tblMainOrder.fldOrderNum AS [Order], fldBatchName As Batch, fldShipWeekDate AS [Ship Week], Trim(Concat(fldFinishDesc, ' ', fldSpecialFX1Desc, ' ', fldSpecialFX2Desc, ' ', fldSpecialFX3Desc, ' ', fldSpecialFX4Desc, ' ', fldSpecialFinishName, ' ', fldFinishNote1, ' ', fldFinishNote2, ' ', fldFinishNote3)) AS FinishDesc, fldVarnishTypeDesc AS Varnish, fldColorSampleNum AS [Sample #], fldNumberOfCabinets AS Items, fldDateWritten AS Written FROM tblMainOrder WHERE fldOrderStatus <> 99 AND fldFinishingOrderedDate IS NULL AND fldDateShipped IS NULL AND (fldFinishDesc = 'BM' OR fldFinishDesc = 'Spec Paint' OR fldFinishDesc = 'SPEC PAINT' OR fldFinishDesc = 'Spec Stain' OR fldFinishDesc = 'SPEC STAIN' OR fldFinishDesc = 'Special P' OR fldFinishDesc = 'SPECIAL R' OR fldFinishDesc = 'SW')";
    var dataAdapter = new SqlDataAdapter(select, c);
    DataTable dtData = new DataTable();
    ds.Tables.Add(dtData);
    dataAdapter.Fill(dtData);

    // 加载按订单分组的Cabs数据，必须包含fldOrderNum
    var select2 = "Select fldOrderNum, SUM(CASE fldProductID WHEN 'b' THEN fldQuantity WHEN 'w' THEN fldQuantity WHEN 'T' THEN fldQuantity WHEN 'v' THEN fldQuantity END) AS Cabs From tblOrderItems GROUP BY fldOrderNum";
    var da = new SqlDataAdapter(select2, c);
    DataTable dtCnt = new DataTable();
    ds.Tables.Add(dtCnt);
    da.Fill(dtCnt); // 修正为用当前查询的da填充

    // LINQ关联两个表
    var joinedData = from main in dtData.AsEnumerable()
                     join items in dtCnt.AsEnumerable()
                     on main.Field<string>("Order") equals items.Field<string>("fldOrderNum") into itemsGroup
                     from item in itemsGroup.DefaultIfEmpty()
                     select new
                     {
                         Order = main.Field<string>("Order"),
                         Batch = main.Field<string>("Batch"),
                         ShipWeek = main.Field<DateTime?>("Ship Week"),
                         FinishDesc = main.Field<string>("FinishDesc"),
                         Varnish = main.Field<string>("Varnish"),
                         SampleNum = main.Field<string>("Sample #"),
                         Items = main.Field<int>("Items"),
                         Cabs = item?.Field<int?>("Cabs") ?? 0,
                         Written = main.Field<DateTime?>("Written")
                     };

    // 转换为DataTable
    DataTable dtFnl = new DataTable();
    dtFnl.Columns.Add("Order", typeof(string));
    dtFnl.Columns.Add("Batch", typeof(string));
    dtFnl.Columns.Add("Ship Week", typeof(DateTime));
    dtFnl.Columns.Add("FinishDesc", typeof(string));
    dtFnl.Columns.Add("Varnish", typeof(string));
    dtFnl.Columns.Add("Sample #", typeof(string));
    dtFnl.Columns.Add("Items", typeof(int));
    dtFnl.Columns.Add("Cabs", typeof(int));
    dtFnl.Columns.Add("Written", typeof(DateTime));

    foreach (var row in joinedData)
    {
        dtFnl.Rows.Add(
            row.Order,
            row.Batch,
            row.ShipWeek,
            row.FinishDesc,
            row.Varnish,
            row.SampleNum,
            row.Items,
            row.Cabs,
            row.Written
        );
    }

    DGV1.DataSource = dtFnl;
}

内容的提问来源于stack exchange，提问作者s.d.c321