使用C#对比DB2与SQL Server库存表并同步差异数据

架构老林

2026-7-8

C#实现DB2与SQL Server库存数据对比、校验及同步

一、数据对比：筛选差异记录

你已获取DB2和SQL Server的库存数据，接下来可通过**库存编码（PARCAKOD/STOK_KODU）**匹配，筛选两类差异记录：

DB2存在但SQL Server不存在的新增记录
编码存在但其他字段（名称、单位等）不一致的更新记录

最高效的方式是先将SQL Server的库存编码存入HashSet实现快速查询，再通过Linq对比字段差异：

// 将SQL Server库存编码存入HashSet，用于快速判断是否存在
var sqlStockCodes = new HashSet<string>(sqlstok.Tables[0].AsEnumerable()
    .Select(row => row.Field<string>("STOK_KODU")));

// 筛选DB2中SQL Server不存在的新增记录
var newRecords = db2stok.Tables[0].AsEnumerable()
    .Where(row => !sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
    .CopyToDataTable();

// 筛选编码存在但字段不一致的更新记录
var updateRecords = db2stok.Tables[0].AsEnumerable()
    .Where(row => sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
    .Join(sqlstok.Tables[0].AsEnumerable(),
        db2Row => db2Row.Field<string>("PARCAKOD"),
        sqlRow => sqlRow.Field<string>("STOK_KODU"),
        (db2Row, sqlRow) => new { DB2Row = db2Row, SQLRow = sqlRow })
    .Where(join => 
        join.DB2Row.Field<string>("ADI") != join.SQLRow.Field<string>("ADI")
        || join.DB2Row.Field<string>("BIRIM") != join.SQLRow.Field<string>("BIRIM")
        || join.DB2Row.Field<decimal>("KDVYUZ") != join.SQLRow.Field<decimal>("KDVYUZ")
        || join.DB2Row.Field<string>("GRUP1") != join.SQLRow.Field<string>("GRUP1"))
    .Select(join => join.DB2Row)
    .CopyToDataTable();

// 合并所有差异记录到DataView
var diffTable = new DataTable();
diffTable.Merge(newRecords);
diffTable.Merge(updateRecords);
var diffView = new DataView(diffTable);

// 绑定到网格展示差异
metroGrid1.DataSource = diffView;
metroLabel1.Text = diffView.Count.ToString();

二、差异记录校验

同步前需校验数据合法性，避免脏数据入库，可根据业务规则添加校验逻辑：

bool ValidateRecord(DataRow row)
{
    // 校验必填字段非空
    if (string.IsNullOrEmpty(row.Field<string>("PARCAKOD")) || string.IsNullOrEmpty(row.Field<string>("ADI")))
    {
        MessageBox.Show($"无效记录：库存编码或名称为空，编码[{row.Field<string>("PARCAKOD")}]");
        return false;
    }
    // 校验KDV百分比范围（0-100）
    if (row.Field<decimal>("KDVYUZ") < 0 || row.Field<decimal>("KDVYUZ") > 100)
    {
        MessageBox.Show($"无效记录：KDV百分比超出范围，编码[{row.Field<string>("PARCAKOD")}]，值[{row.Field<decimal>("KDVYUZ")}]");
        return false;
    }
    // 可添加其他自定义校验规则（如单位格式、分组编码合法性等）
    return true;
}

// 批量校验差异记录
DataTable validRecords;
try
{
    validRecords = diffTable.AsEnumerable()
        .Where(row => ValidateRecord(row))
        .CopyToDataTable();
}
catch (InvalidOperationException)
{
    // 无有效记录时创建空表
    validRecords = diffTable.Clone();
}

三、同步差异记录到SQL Server

使用SqlBulkCopy批量插入新增记录，用参数化命令批量更新现有记录，提升同步效率：

void SyncToSqlServer(DataTable validRecords, HashSet<string> sqlStockCodes)
{
    if (validRecords.Rows.Count == 0)
    {
        MessageBox.Show("无有效记录可同步");
        return;
    }

    sqlbagla.Open();
    try
    {
        // 批量插入新增记录
        var insertTable = validRecords.AsEnumerable()
            .Where(row => !sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
            .CopyToDataTable();

        if (insertTable.Rows.Count > 0)
        {
            using (var bulkCopy = new SqlBulkCopy(sqlbagla))
            {
                bulkCopy.DestinationTableName = "TBLSTSABIT";
                // 映射DB2字段到SQL Server字段（需确保字段名匹配）
                bulkCopy.ColumnMappings.Add("PARCAKOD", "STOK_KODU");
                bulkCopy.ColumnMappings.Add("ADI", "ADI");
                bulkCopy.ColumnMappings.Add("BIRIM", "BIRIM");
                bulkCopy.ColumnMappings.Add("KDVYUZ", "KDVYUZ");
                bulkCopy.ColumnMappings.Add("GRUP1", "GRUP1");
                bulkCopy.WriteToServer(insertTable);
            }
        }

        // 批量更新现有记录
        var updateCmdText = @"UPDATE TBLSTSABIT 
                              SET ADI = @ADI, BIRIM = @BIRIM, KDVYUZ = @KDVYUZ, GRUP1 = @GRUP1
                              WHERE STOK_KODU = @STOK_KODU";

        using (var cmd = new SqlCommand(updateCmdText, sqlbagla))
        {
            cmd.Parameters.Add("@STOK_KODU", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@ADI", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@BIRIM", SqlDbType.VarChar, 20);
            cmd.Parameters.Add("@KDVYUZ", SqlDbType.Decimal, 5);
            cmd.Parameters.Add("@GRUP1", SqlDbType.VarChar, 50);

            foreach (DataRow row in validRecords.AsEnumerable().Where(r => sqlStockCodes.Contains(r.Field<string>("PARCAKOD"))))
            {
                cmd.Parameters["@STOK_KODU"].Value = row.Field<string>("PARCAKOD");
                cmd.Parameters["@ADI"].Value = row.Field<string>("ADI");
                cmd.Parameters["@BIRIM"].Value = row.Field<string>("BIRIM");
                cmd.Parameters["@KDVYUZ"].Value = row.Field<decimal>("KDVYUZ");
                cmd.Parameters["@GRUP1"].Value = row.Field<string>("GRUP1");
                cmd.ExecuteNonQuery();
            }
        }

        MessageBox.Show($"成功同步{validRecords.Rows.Count}条记录");
    }
    catch (Exception ex)
    {
        MessageBox.Show($"同步失败：{ex.Message}");
    }
    finally
    {
        sqlbagla.Close();
    }
}

整合后的完整方法

将上述逻辑整合到你的db2StokGetir方法中：

void db2StokGetir()
{
    try
    {
        // 注意：SQL查询需包含所有需对比的字段，仅查编码无法判断字段差异
        DB2DataAdapter db2da = new DB2DataAdapter("SELECT DISTINCT S.PARCAKOD,P.ADI,P.BIRIM,P.KDVYUZ,P.GRUP1 FROM DB2ADMIN.T0020031 S LEFT OUTER JOIN DB2ADMIN.T0050030 P ON S.PARCAKOD = P.KOD WHERE YEAR(S.TARIH) >= 2023 GROUP BY S.PARCAKOD,P.ADI,P.BIRIM,P.KDVYUZ,P.GRUP1", db2bagla);
        SqlDataAdapter sqlda = new SqlDataAdapter("SELECT STOK_KODU, ADI, BIRIM, KDVYUZ, GRUP1 FROM TBLSTSABIT", sqlbagla);

        DataSet db2stok = new DataSet();
        DataSet sqlstok = new DataSet();
        db2da.Fill(db2stok, "DMSSTOK");
        sqlda.Fill(sqlstok, "SQLSTOK");

        // 1. 获取SQL Server库存编码集合
        var sqlStockCodes = new HashSet<string>(sqlstok.Tables[0].AsEnumerable()
            .Select(row => row.Field<string>("STOK_KODU")));

        // 2. 筛选新增和更新的差异记录
        var newRecords = db2stok.Tables[0].AsEnumerable()
            .Where(row => !sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
            .CopyToDataTable();

        var updateRecords = db2stok.Tables[0].AsEnumerable()
            .Where(row => sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
            .Join(sqlstok.Tables[0].AsEnumerable(),
                db2Row => db2Row.Field<string>("PARCAKOD"),
                sqlRow => sqlRow.Field<string>("STOK_KODU"),
                (db2Row, sqlRow) => new { DB2Row = db2Row, SQLRow = sqlRow })
            .Where(join => 
                join.DB2Row.Field<string>("ADI") != join.SQLRow.Field<string>("ADI")
                || join.DB2Row.Field<string>("BIRIM") != join.SQLRow.Field<string>("BIRIM")
                || join.DB2Row.Field<decimal>("KDVYUZ") != join.SQLRow.Field<decimal>("KDVYUZ")
                || join.DB2Row.Field<string>("GRUP1") != join.SQLRow.Field<string>("GRUP1"))
            .Select(join => join.DB2Row)
            .CopyToDataTable();

        // 合并差异记录并展示
        DataTable diffTable = new DataTable();
        diffTable.Merge(newRecords);
        diffTable.Merge(updateRecords);
        var diffView = new DataView(diffTable);
        metroGrid1.DataSource = diffView;
        metroLabel1.Text = diffView.Count.ToString();

        // 3. 校验差异记录
        DataTable validRecords;
        try
        {
            validRecords = diffTable.AsEnumerable()
                .Where(row => ValidateRecord(row))
                .CopyToDataTable();
        }
        catch (InvalidOperationException)
        {
            validRecords = diffTable.Clone();
        }

        // 4. 同步到SQL Server（建议提取为独立按钮事件触发）
        // SyncToSqlServer(validRecords, sqlStockCodes);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message.ToString());
    }
}

// 校验方法
bool ValidateRecord(DataRow row)
{
    if (string.IsNullOrEmpty(row.Field<string>("PARCAKOD")) || string.IsNullOrEmpty(row.Field<string>("ADI")))
    {
        MessageBox.Show($"无效记录：库存编码或名称为空，编码[{row.Field<string>("PARCAKOD")}]");
        return false;
    }
    if (row.Field<decimal>("KDVYUZ") < 0 || row.Field<decimal>("KDVYUZ") > 100)
    {
        MessageBox.Show($"无效记录：KDV百分比超出范围，编码[{row.Field<string>("PARCAKOD")}]，值[{row.Field<decimal>("KDVYUZ")}]");
        return false;
    }
    return true;
}

// 同步方法
void SyncToSqlServer(DataTable validRecords, HashSet<string> sqlStockCodes)
{
    if (validRecords.Rows.Count == 0)
    {
        MessageBox.Show("无有效记录可同步");
        return;
    }

    sqlbagla.Open();
    try
    {
        var insertTable = validRecords.AsEnumerable()
            .Where(row => !sqlStockCodes.Contains(row.Field<string>("PARCAKOD")))
            .CopyToDataTable();

        if (insertTable.Rows.Count > 0)
        {
            using (var bulkCopy = new SqlBulkCopy(sqlbagla))
            {
                bulkCopy.DestinationTableName = "TBLSTSABIT";
                bulkCopy.ColumnMappings.Add("PARCAKOD", "STOK_KODU");
                bulkCopy.ColumnMappings.Add("ADI", "ADI");
                bulkCopy.ColumnMappings.Add("BIRIM", "BIRIM");
                bulkCopy.ColumnMappings.Add("KDVYUZ", "KDVYUZ");
                bulkCopy.ColumnMappings.Add("GRUP1", "GRUP1");
                bulkCopy.WriteToServer(insertTable);
            }
        }

        var updateCmdText = @"UPDATE TBLSTSABIT 
                              SET ADI = @ADI, BIRIM = @BIRIM, KDVYUZ = @KDVYUZ, GRUP1 = @GRUP1
                              WHERE STOK_KODU = @STOK_KODU";

        using (var cmd = new SqlCommand(updateCmdText, sqlbagla))
        {
            cmd.Parameters.Add("@STOK_KODU", SqlDbType.VarChar, 50);
            cmd.Parameters.Add("@ADI", SqlDbType.VarChar, 100);
            cmd.Parameters.Add("@BIRIM", SqlDbType.VarChar, 20);
            cmd.Parameters.Add("@KDVYUZ", SqlDbType.Decimal, 5);
            cmd.Parameters.Add("@GRUP1", SqlDbType.VarChar, 50);

            foreach (DataRow row in validRecords.AsEnumerable().Where(r => sqlStockCodes.Contains(r.Field<string>("PARCAKOD"))))
            {
                cmd.Parameters["@STOK_KODU"].Value = row.Field<string>("PARCAKOD");
                cmd.Parameters["@ADI"].Value = row.Field<string>("ADI");
                cmd.Parameters["@BIRIM"].Value = row.Field<string>("BIRIM");
                cmd.Parameters["@KDVYUZ"].Value = row.Field<decimal>("KDVYUZ");
                cmd.Parameters["@GRUP1"].Value = row.Field<string>("GRUP1");
                cmd.ExecuteNonQuery();
            }
        }

        MessageBox.Show($"成功同步{validRecords.Rows.Count}条记录");
    }
    catch (Exception ex)
    {
        MessageBox.Show($"同步失败：{ex.Message}");
    }
    finally
    {
        sqlbagla.Close();
    }
}

注意事项

确保SQL Server表TBLSTSABIT的字段名与代码中映射的一致，若字段名不同需调整ColumnMappings和更新语句。
大批量数据同步时，建议添加事务处理，避免部分同步失败导致数据不一致。
可将同步逻辑提取为独立按钮事件，避免在数据加载时直接执行。

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

使用C#对比DB2与SQL Server库存表并同步差异数据

一、数据对比：筛选差异记录 #

二、差异记录校验 #

三、同步差异记录到SQL Server #

整合后的完整方法 #

注意事项 #

一、数据对比：筛选差异记录

二、差异记录校验

三、同步差异记录到SQL Server

整合后的完整方法

注意事项