如何使用C#/SQL在.NET Core 3.1中实现批量更新千条及以上数据记录

现有代码已知问题 #

• 变量定义重复：同作用域下重复声明Text变量，存在语法错误
• 列表添加逻辑错误：仅当j>10000时才添加Client到列表，导致前9999条数据丢失
• 语法错误：if text.StartWith缺少括号、方法名应为StartsWith、变量大小写不匹配
• 资源未安全释放：未使用using管理连接、命令、读取器对象，存在资源泄漏风险

该方案是百万级数据场景下性能最优的实现，单次1万条更新的开销极低，核心逻辑为：

1. 每攒够1万条修改后的Client数据，创建与目标表更新字段结构一致的临时表
2. 用SqlBulkCopy将批量数据快速写入临时表
3. 执行关联更新SQL，通过ClientID匹配临时表与原Client表，一次性完成1万条数据更新
4. 清空临时列表处理下一批数据，循环结束后单独处理剩余不足1万条的记录

注意：临时表的字段类型、长度请和你实际的dbo.Client表字段保持一致，避免隐式转换导致的性能问题或数据截断

public void FixText()
{
    // 批次大小设置为1万
    const int BatchSize = 10000;
    List<Client> clients = new List<Client>(BatchSize);

    // 使用using自动释放连接资源
    using (SqlConnection cnn = new SqlConnection(strConn))
    {
        cnn.Open();
        // 读取原始数据
        string queryString = "SELECT ClientID, ServerName, Text FROM [dbo].[Client]";
        using (SqlCommand selectCmd = new SqlCommand(queryString, cnn))
        using (SqlDataReader reader = selectCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                int clientId = (int)reader["ClientID"];
                string serverName = reader["ServerName"].ToString();
                string text = reader["Text"].ToString();

                // 执行业务修改
                string updatedText = UpdateText(text);
                if (updatedText.StartsWith("doo"))
                {
                    serverName = "re";
                }

                clients.Add(new Client
                {
                    ClientID = clientId,
                    ServerName = serverName,
                    Text = updatedText
                });

                // 达到批次大小执行批量更新
                if (clients.Count >= BatchSize)
                {
                    BulkUpdateClients(cnn, clients);
                    clients.Clear();
                }
            }
        }

        // 处理最后一批不足1万条的剩余数据
        if (clients.Count > 0)
        {
            BulkUpdateClients(cnn, clients);
        }
    }
}

// 批量更新公共方法
private void BulkUpdateClients(SqlConnection existingConn, List<Client> clients)
{
    // 创建临时表
    string createTempTableSql = @"
        CREATE TABLE #ClientTemp (
            ClientID INT PRIMARY KEY,
            ServerName NVARCHAR(200) NOT NULL,
            Text NVARCHAR(MAX) NOT NULL
        )";
    using (SqlCommand createCmd = new SqlCommand(createTempTableSql, existingConn))
    {
        createCmd.ExecuteNonQuery();
    }

    // 批量写入临时表
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(existingConn))
    {
        bulkCopy.DestinationTableName = "#ClientTemp";
        // 映射字段
        bulkCopy.ColumnMappings.Add(nameof(Client.ClientID), nameof(Client.ClientID));
        bulkCopy.ColumnMappings.Add(nameof(Client.ServerName), nameof(Client.ServerName));
        bulkCopy.ColumnMappings.Add(nameof(Client.Text), nameof(Client.Text));
        
        DataTable dt = new DataTable();
        dt.Columns.Add(nameof(Client.ClientID), typeof(int));
        dt.Columns.Add(nameof(Client.ServerName), typeof(string));
        dt.Columns.Add(nameof(Client.Text), typeof(string));

        foreach (var client in clients)
        {
            dt.Rows.Add(client.ClientID, client.ServerName, client.Text);
        }

        bulkCopy.WriteToServer(dt);
    }

    // 关联更新原表
    string updateSql = @"
        UPDATE c
        SET c.ServerName = t.ServerName,
            c.Text = t.Text
        FROM [dbo].[Client] c
        INNER JOIN #ClientTemp t ON c.ClientID = t.ClientID";
    using (SqlCommand updateCmd = new SqlCommand(updateSql, existingConn))
    {
        updateCmd.ExecuteNonQuery();
    }

    // 清理临时表
    using (SqlCommand dropCmd = new SqlCommand("DROP TABLE #ClientTemp", existingConn))
    {
        dropCmd.ExecuteNonQuery();
    }
}

如果项目已引入Dapper，也可以用Dapper的批量参数化执行，代码更简洁但性能略低于临时表方案，仅需替换BulkUpdateClients的实现即可：

private void BulkUpdateClients(SqlConnection conn, List<Client> clients)
{
    string updateSql = @"
        UPDATE [dbo].[Client] 
        SET ServerName = @ServerName, Text = @Text 
        WHERE ClientID = @ClientID";
    conn.Execute(updateSql, clients);
}

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