C#实现DataGridView批量填充SQL Server相同NumCrimCase的Prosecutor字段

方案一：直接通过SQL语句更新数据库（推荐，效率更高） #

核心逻辑是先分组提取每个NumCrimCase对应的非空Prosecutor值，再用该值批量更新同组内的空值行。

执行以下SQL语句（替换YourTableName为你的实际表名）：

UPDATE t1
SET Prosecutor = t2.Prosecutor
FROM YourTableName t1
JOIN (
    -- 按NumCrimCase分组，取出每组的非空Prosecutor值
    SELECT NumCrimCase, MAX(Prosecutor) AS Prosecutor
    FROM YourTableName
    WHERE Prosecutor IS NOT NULL AND Prosecutor <> ''
    GROUP BY NumCrimCase
) t2 ON t1.NumCrimCase = t2.NumCrimCase
-- 只更新空值或空字符串的行
WHERE t1.Prosecutor IS NULL OR t1.Prosecutor = ''

注：如果同一NumCrimCase下存在多个不同的非空Prosecutor值，MAX()会取其中最大的字符串值；若需指定其他规则，可替换为MIN()或自定义逻辑。

方案二：在C#窗体代码中处理DataGridView数据 #

如果需要在前端处理后再提交到数据库，可按以下步骤实现：

private void btnFillProsecutor_Click(object sender, EventArgs e)
{
    // 存储每个NumCrimCase对应的有效Prosecutor值
    var prosecutorMap = new Dictionary<string, string>();

    // 第一遍遍历：收集所有非空的Prosecutor对应关系
    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        if (row.IsNewRow) continue;

        string numCase = row.Cells["NumCrimCase"].Value?.ToString();
        string prosecutor = row.Cells["Prosecutor"].Value?.ToString();

        if (!string.IsNullOrEmpty(numCase) && !string.IsNullOrEmpty(prosecutor))
        {
            if (!prosecutorMap.ContainsKey(numCase))
            {
                prosecutorMap[numCase] = prosecutor;
            }
        }
    }

    // 第二遍遍历：填充空值
    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        if (row.IsNewRow) continue;

        string numCase = row.Cells["NumCrimCase"].Value?.ToString();
        string currentProsecutor = row.Cells["Prosecutor"].Value?.ToString();

        if (!string.IsNullOrEmpty(numCase) && string.IsNullOrEmpty(currentProsecutor) && prosecutorMap.ContainsKey(numCase))
        {
            row.Cells["Prosecutor"].Value = prosecutorMap[numCase];
        }
    }

    // 提交更新到数据库（需替换为你的连接字符串和表名）
    using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
    {
        conn.Open();
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.IsNewRow) continue;
            string prosecutor = row.Cells["Prosecutor"].Value?.ToString();
            string numCase = row.Cells["NumCrimCase"].Value?.ToString();

            if (!string.IsNullOrEmpty(prosecutor) && !string.IsNullOrEmpty(numCase))
            {
                string updateSql = "UPDATE YourTableName SET Prosecutor = @Prosecutor WHERE NumCrimCase = @NumCrimCase AND (Prosecutor IS NULL OR Prosecutor = '')";
                using (SqlCommand cmd = new SqlCommand(updateSql, conn))
                {
                    cmd.Parameters.AddWithValue("@Prosecutor", prosecutor);
                    cmd.Parameters.AddWithValue("@NumCrimCase", numCase);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        MessageBox.Show("填充完成");
    }
}

注意事项 #

1. 操作数据库前建议备份数据，或使用事务包裹更新逻辑，避免数据异常。
2. 如果同一NumCrimCase下存在多个冲突的Prosecutor值，需先确认取值规则再执行操作。
3. 代码中需替换YourTableName和数据库连接字符串为实际内容。

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