求高效每日同步SQL库存表（1万行）至Access ACCDB的优化代码

阿华AIGC实验室

2026-5-6

优化SQL到Access的库存数据同步效率

你的代码慢的核心原因是逐行执行INSERT语句——每一行数据都要单独和Access建立一次数据库交互，1万行下来光是IO和网络开销就会累积到5分钟以上，而且还存在SQL注入风险和字符串格式错误的隐患（比如字段值里有单引号就会直接报错）。下面是几个层级的优化方案，从易实现到极致优化：

方案1：参数化命令 + 分批次提交（快速见效）

用参数化命令替代字符串拼接，同时开启事务并分批次提交（比如每1000行提交一次），减少事务日志开销和交互次数：

// 用using自动释放资源，避免连接泄漏
using (var sqlConn = new SqlConnection("你的SQL Server连接字符串"))
using (var accessConn = new OleDbConnection("你的Access连接字符串"))
{
    sqlConn.Open();
    accessConn.Open();

    // 从SQL Server读取全量库存数据
    var selectSql = "SELECT ITEMCODE,DESCRIPTION,ONHAND,COSTEXCL,COSTINCL,PRICEEXCL,PRICEINCL,LASTSOLD,LASTRECEIVED FROM stmast";
    var dtInventory = new DataTable();
    new SqlDataAdapter(selectSql, sqlConn).Fill(dtInventory);

    // 准备参数化的INSERT命令（OleDb参数按位置匹配，命名只是方便维护）
    var insertSql = @"INSERT INTO stmast(ITEMCODE,DESCRIPTION,ONHAND,COSTEXCL,COSTINCL,PRICEEXCL,PRICEINCL,LASTSOLD,LASTRECEIVED)
                      VALUES(@ITEMCODE, @DESCRIPTION, @ONHAND, @COSTEXCL, @COSTINCL, @PRICEEXCL, @PRICEINCL, @LASTSOLD, @LASTRECEIVED)";
    
    using (var cmd = new OleDbCommand(insertSql, accessConn))
    {
        // 添加对应字段的参数，注意匹配Access表的实际字段类型
        cmd.Parameters.Add("@ITEMCODE", OleDbType.VarChar, 50);
        cmd.Parameters.Add("@DESCRIPTION", OleDbType.VarChar, 200);
        cmd.Parameters.Add("@ONHAND", OleDbType.Double);
        cmd.Parameters.Add("@COSTEXCL", OleDbType.Currency);
        cmd.Parameters.Add("@COSTINCL", OleDbType.Currency);
        cmd.Parameters.Add("@PRICEEXCL", OleDbType.Currency);
        cmd.Parameters.Add("@PRICEINCL", OleDbType.Currency);
        cmd.Parameters.Add("@LASTSOLD", OleDbType.Date);
        cmd.Parameters.Add("@LASTRECEIVED", OleDbType.Date);

        // 开启事务，分批次提交降低日志压力
        using (var tran = accessConn.BeginTransaction())
        {
            cmd.Transaction = tran;
            const int batchSize = 1000;
            int rowCount = 0;

            foreach (DataRow row in dtInventory.Rows)
            {
                // 给参数赋值，处理DBNull情况
                cmd.Parameters["@ITEMCODE"].Value = row["ITEMCODE"] == DBNull.Value ? DBNull.Value : row["ITEMCODE"];
                cmd.Parameters["@DESCRIPTION"].Value = row["DESCRIPTION"] == DBNull.Value ? DBNull.Value : row["DESCRIPTION"];
                cmd.Parameters["@ONHAND"].Value = row["ONHAND"] == DBNull.Value ? DBNull.Value : row["ONHAND"];
                cmd.Parameters["@COSTEXCL"].Value = row["COSTEXCL"] == DBNull.Value ? DBNull.Value : row["COSTEXCL"];
                cmd.Parameters["@COSTINCL"].Value = row["COSTINCL"] == DBNull.Value ? DBNull.Value : row["COSTINCL"];
                cmd.Parameters["@PRICEEXCL"].Value = row["PRICEEXCL"] == DBNull.Value ? DBNull.Value : row["PRICEEXCL"];
                cmd.Parameters["@PRICEINCL"].Value = row["PRICEINCL"] == DBNull.Value ? DBNull.Value : row["PRICEINCL"];
                cmd.Parameters["@LASTSOLD"].Value = row["LASTSOLD"] == DBNull.Value ? DBNull.Value : row["LASTSOLD"];
                cmd.Parameters["@LASTRECEIVED"].Value = row["LASTRECEIVED"] == DBNull.Value ? DBNull.Value : row["LASTRECEIVED"];

                cmd.ExecuteNonQuery();
                rowCount++;

                // 每满1000行提交一次事务
                if (rowCount % batchSize == 0)
                {
                    tran.Commit();
                    tran = accessConn.BeginTransaction();
                    cmd.Transaction = tran;
                }
            }

            // 提交剩余的未完成批次
            if (rowCount % batchSize != 0)
            {
                tran.Commit();
            }
        }
    }

    MessageBox.Show("库存数据同步完成");
}

方案2：使用OleDbDataAdapter批量更新（更简洁）

利用OleDbDataAdapter的Update方法，它内部会自动优化批量写入逻辑，代码更简洁：

using (var sqlConn = new SqlConnection("你的SQL Server连接字符串"))
using (var accessConn = new OleDbConnection("你的Access连接字符串"))
{
    sqlConn.Open();
    accessConn.Open();

    // 读取SQL Server数据到DataTable
    var selectSql = "SELECT ITEMCODE,DESCRIPTION,ONHAND,COSTEXCL,COSTINCL,PRICEEXCL,PRICEINCL,LASTSOLD,LASTRECEIVED FROM stmast";
    var dtInventory = new DataTable();
    new SqlDataAdapter(selectSql, sqlConn).Fill(dtInventory);

    // 配置Access的DataAdapter，绑定参数和字段
    var accessAdapter = new OleDbDataAdapter();
    accessAdapter.InsertCommand = new OleDbCommand(@"INSERT INTO stmast(ITEMCODE,DESCRIPTION,ONHAND,COSTEXCL,COSTINCL,PRICEEXCL,PRICEINCL,LASTSOLD,LASTRECEIVED)
                                                      VALUES(@ITEMCODE, @DESCRIPTION, @ONHAND, @COSTEXCL, @COSTINCL, @PRICEEXCL, @PRICEINCL, @LASTSOLD, @LASTRECEIVED)", accessConn);
    
    // 直接绑定DataTable字段到参数，无需手动赋值
    accessAdapter.InsertCommand.Parameters.Add("@ITEMCODE", OleDbType.VarChar, 50, "ITEMCODE");
    accessAdapter.InsertCommand.Parameters.Add("@DESCRIPTION", OleDbType.VarChar, 200, "DESCRIPTION");
    accessAdapter.InsertCommand.Parameters.Add("@ONHAND", OleDbType.Double, 0, "ONHAND");
    accessAdapter.InsertCommand.Parameters.Add("@COSTEXCL", OleDbType.Currency, 0, "COSTEXCL");
    accessAdapter.InsertCommand.Parameters.Add("@COSTINCL", OleDbType.Currency, 0, "COSTINCL");
    accessAdapter.InsertCommand.Parameters.Add("@PRICEEXCL", OleDbType.Currency, 0, "PRICEEXCL");
    accessAdapter.InsertCommand.Parameters.Add("@PRICEINCL", OleDbType.Currency, 0, "PRICEINCL");
    accessAdapter.InsertCommand.Parameters.Add("@LASTSOLD", OleDbType.Date, 0, "LASTSOLD");
    accessAdapter.InsertCommand.Parameters.Add("@LASTRECEIVED", OleDbType.Date, 0, "LASTRECEIVED");

    // 设置批量更新大小，提升写入效率
    accessAdapter.UpdateBatchSize = 1000;

    // 执行批量写入
    accessAdapter.Update(dtInventory);

    MessageBox.Show("库存数据同步完成");
}