如何在Foreach循环中复用双数据库连接，提升批量数据处理效率

问题分析 #

当前代码在循环内每行都创建并销毁数据库连接，导致数千次连接开关操作，严重拖慢处理速度并增加服务器负载。核心优化方向是将连接和命令对象的初始化移至循环外部，复用同一连接完成所有行的处理，仅在循环内更新参数值。

优化后代码实现 #

public void Main()
{
    // 声明变量
    int InputID;
    string OutputProcessedMsg;
    string SqlConn = "Data Source=xyx.com;Initial Catalog=ddddd;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    string AzureSqlConn = "Data Source=vvvv.dev.com;Initial Catalog=yyyy;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    Object AllData = Dts.Variables["User::VarAllPowerAppData"].Value;

    // 加载DataTable
    OleDbDataAdapter A = new OleDbDataAdapter();
    System.Data.DataTable dt = new System.Data.DataTable();
    A.Fill(dt, AllData);

    // 初始化本地SQL连接和命令（循环外创建，全程复用）
    using (SqlConnection onpremConn = new SqlConnection(SqlConn))
    {
        onpremConn.Open();
        using (SqlCommand onpremCmd = new SqlCommand("UpdateDataOnpremSQL", onpremConn))
        {
            onpremCmd.CommandType = CommandType.StoredProcedure;
            // 预定义参数，循环内仅更新值
            var paramID = onpremCmd.Parameters.Add("@ID", SqlDbType.VarChar, 15);
            var paramProcessedMsg = onpremCmd.Parameters.Add("@ProcessedMsg", SqlDbType.VarChar, -1);
            paramProcessedMsg.Direction = ParameterDirection.Output;

            // 初始化Azure SQL连接和命令（循环外创建，全程复用）
            using (OleDbConnection azureConn = new OleDbConnection(AzureSqlConn))
            {
                azureConn.Open();
                using (OleDbCommand azureCmd = new OleDbCommand("UpdateDataAzureSQL", azureConn))
                {
                    azureCmd.CommandType = CommandType.StoredProcedure;
                    // 预定义Azure参数，循环内仅更新值
                    var azureParamID = azureCmd.Parameters.Add("@ID", OleDbType.VarChar);
                    var azureParamMsg = azureCmd.Parameters.Add("@ProcessedMsg", OleDbType.VarChar);

                    // 循环处理每行数据
                    foreach (DataRow dr in dt.Rows)
                    {
                        InputID = Convert.ToInt32(dr[0]);
                        // 更新本地SQL参数并执行存储过程
                        paramID.Value = InputID.ToString();
                        onpremCmd.ExecuteNonQuery();
                        // 获取输出参数
                        OutputProcessedMsg = Convert.ToString(paramProcessedMsg.Value);

                        // 更新Azure SQL参数并执行存储过程
                        azureParamID.Value = InputID.ToString();
                        azureParamMsg.Value = OutputProcessedMsg;
                        azureCmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}

关键优化点说明 #

• 连接复用：将SqlConnection和OleDbConnection的创建移至循环外部，整个处理流程仅打开/关闭一次连接，彻底消除数千次连接开关的性能损耗。
• 命令与参数复用：预先创建命令对象和对应参数，循环内仅更新参数的Value属性，避免重复创建对象带来的额外开销。
• 资源安全释放：保留using块包裹连接和命令，确保即使发生异常，数据库资源也能被正确释放，不会出现连接泄漏。
• 代码修正：修复原代码中InputID与InputDealerID的变量名不一致问题，同时确保参数类型与存储过程定义匹配。

额外优化建议 #

• 事务原子性：如果需要保证每行的本地更新和Azure更新同时成功或失败，可以在连接上开启事务，循环内执行完两次操作后统一提交，异常时回滚。
• 批量处理备选：若业务允许，可将多行数据打包为表值参数（Table-Valued Parameter），调用一次存储过程处理批量数据，进一步提升处理效率。
• 连接池协同：即使启用了连接池，显式保持连接仍能减少池内连接的切换和验证开销，高并发场景下收益更明显。

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