C#中实现SQL Server的批量更新功能
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>一、 小数据量批量更新（1000 条以内，简洁高效）</li><ul class="second_class_ul"><li>方案 1：参数化 SQL 拼接（防 SQL 注入，推荐）</li><ul class="third_class_ul"><li>实现代码</li><li>优化：CASE WHEN 减少 SQL 语句数</li></ul><li>方案 2：循环单条更新（最简单，不推荐大数据量）</li><ul class="third_class_ul"><li>实现代码</li></ul></ul><li>二、 大数据量批量更新（1000 条以上，高性能）</li><ul class="second_class_ul"><li>方案 1：使用 SqlBulkCopy + 临时表（最优推荐，超高效率）</li><ul class="third_class_ul"><li>实现代码</li></ul><li>方案 2：使用 Dapper 框架（简洁高效，第三方库）</li><ul class="third_class_ul"><li>步骤 1：安装 Dapper 包</li><li>实现代码</li></ul></ul><li>三、 关键注意事项（避坑指南）</li><ul class="second_class_ul"></ul><li>总结</li><ul class="second_class_ul"></ul></ul></div><p class="maodian"></p><h2>一、 小数据量批量更新（1000 条以内，简洁高效）</h2><p>适用于更新数据量较少（如几百条）的场景，实现简单、无需额外依赖，核心分为「拼接 SQL 语句」和「参数化 SQL（推荐，防注入）」两种方式。</p>
<p class="maodian"></p><h3>方案 1：参数化 SQL 拼接（防 SQL 注入，推荐）</h3>
<p>通过拼接带参数的 <code>UPDATE</code> 语句，批量执行更新，兼顾简洁性和安全性，避免 SQL 注入风险。</p>
<p class="maodian"></p><p class="maodian"></p><p class="maodian"></p><p class="maodian"></p><h4>实现代码</h4>
<div class="jb51code"><pre class="brush:csharp;">using System;
using System.Data.SqlClient;
namespace SqlServerBatchUpdate
{
class SmallDataBatchUpdate
{
// 数据库连接字符串（根据你的实际环境修改）
private static readonly string _connectionString = "Data Source=你的服务器名;Initial Catalog=你的数据库名;User ID=你的用户名;Password=你的密码;";
/// <summary>
/// 批量更新员工薪资（小数据量）
/// </summary>
public static void BatchUpdateEmployeeSalary()
{
// 模拟需要更新的数据（实际可从业务逻辑中获取）
var updateList = new List<Employee>()
{
new Employee { EmployeeID = 1, Salary = 5500 },
new Employee { EmployeeID = 2, Salary = 6600 },
new Employee { EmployeeID = 3, Salary = 7700 }
};
// 拼接参数化SQL语句
var sqlBuilder = new System.Text.StringBuilder();
var sqlParameters = new List<SqlParameter>();
for (int i = 0; i < updateList.Count; i++)
{
var emp = updateList;
// 定义唯一参数名，避免冲突
string paramIdName = $"@EmpID{i}";
string paramSalaryName = $"@EmpSalary{i}";
// 拼接UPDATE语句（每条数据对应一个UPDATE，或用CASE WHEN优化）
sqlBuilder.AppendLine($"UPDATE Employee SET Salary = {paramSalaryName} WHERE EmployeeID = {paramIdName};");
// 添加参数
sqlParameters.Add(new SqlParameter(paramIdName, emp.EmployeeID));
sqlParameters.Add(new SqlParameter(paramSalaryName, emp.Salary));
}
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sqlBuilder.ToString(), conn))
{
// 添加所有参数
cmd.Parameters.AddRange(sqlParameters.ToArray());
// 执行批量更新
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine($"成功更新 {affectedRows} 条记录");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"批量更新失败：{ex.Message}");
}
}
}
// 员工实体类
public class Employee
{
public int EmployeeID { get; set; }
public decimal Salary { get; set; }
public string EmployeeName { get; set; }
public string Department { get; set; }
}
}
</pre></div>
<p class="maodian"></p><h4>优化：CASE WHEN 减少 SQL 语句数</h4>
<p>上述代码每条数据对应一个 <code>UPDATE</code>，可通过 <code>CASE WHEN</code> 优化为单条 <code>UPDATE</code>，提升执行效率：</p>
<div class="jb51code"><pre class="brush:csharp;">public static void BatchUpdateEmployeeSalaryWithCaseWhen()
{
var updateList = new List<Employee>()
{
new Employee { EmployeeID = 1, Salary = 5500 },
new Employee { EmployeeID = 2, Salary = 6600 },
new Employee { EmployeeID = 3, Salary = 7700 }
};
var caseBuilder = new System.Text.StringBuilder();
var idList = new List<int>();
var sqlParameters = new List<SqlParameter>();
// 拼接CASE WHEN语句
caseBuilder.Append("UPDATE Employee SET Salary = CASE EmployeeID ");
for (int i = 0; i < updateList.Count; i++)
{
var emp = updateList;
string paramIdName = $"@EmpID{i}";
string paramSalaryName = $"@EmpSalary{i}";
caseBuilder.AppendLine($"WHEN {paramIdName} THEN {paramSalaryName} ");
sqlParameters.Add(new SqlParameter(paramIdName, emp.EmployeeID));
sqlParameters.Add(new SqlParameter(paramSalaryName, emp.Salary));
idList.Add(emp.EmployeeID);
}
caseBuilder.Append("END ");
// 拼接WHERE条件，限定更新范围
caseBuilder.Append("WHERE EmployeeID IN (");
caseBuilder.Append(string.Join(",", idList));
caseBuilder.Append(");");
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(caseBuilder.ToString(), conn))
{
cmd.Parameters.AddRange(sqlParameters.ToArray());
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine($"成功更新 {affectedRows} 条记录");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"批量更新失败：{ex.Message}");
}
}
</pre></div>
<p class="maodian"></p><h3>方案 2：循环单条更新（最简单，不推荐大数据量）</h3>
<p>逐行遍历数据，执行单条 <code>UPDATE</code> 语句，实现最简单，但性能较差（多次数据库连接 / 交互），仅适用于极少数据（几十条以内）。</p>
<h4>实现代码</h4>
<div class="jb51code"><pre class="brush:csharp;">public static void LoopSingleUpdate()
{
var updateList = new List<Employee>()
{
new Employee { EmployeeID = 1, Salary = 5500 },
new Employee { EmployeeID = 2, Salary = 6600 }
};
string sql = "UPDATE Employee SET Salary = @Salary WHERE EmployeeID = @EmployeeID;";
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
foreach (var emp in updateList)
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Salary", emp.Salary);
cmd.Parameters.AddWithValue("@EmployeeID", emp.EmployeeID);
cmd.ExecuteNonQuery();
}
}
Console.WriteLine("全部更新完成");
}
}
catch (Exception ex)
{
Console.WriteLine($"更新失败：{ex.Message}");
}
}
</pre></div>
<p class="maodian"></p><h2>二、 大数据量批量更新（1000 条以上，高性能）</h2>
<p>当更新数据量较大（如 1 万、10 万甚至百万级）时，上述小数据量方案会出现性能瓶颈（多次交互、事务日志暴涨），推荐以下两种高性能方案。</p>
<p class="maodian"></p><h3>方案 1：使用 SqlBulkCopy + 临时表（最优推荐，超高效率）</h3>
<p>核心思路：</p>
<ol><li>将需要更新的数据先通过 <code>SqlBulkCopy</code> 批量插入 SQL Server 临时表；</li><li>通过 <code>JOIN</code> 语法，将临时表与目标表关联，执行批量更新；</li><li>删除临时表（可选，自动临时表会自动销毁）。</li></ol>
<p>该方案最大限度减少数据库交互，利用 <code>SqlBulkCopy</code> 的高性能批量写入能力，是大数据量更新的首选。</p>
<h4>实现代码</h4>
<div class="jb51code"><pre class="brush:csharp;">using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace SqlServerBatchUpdate
{
class BigDataBatchUpdate
{
private static readonly string _connectionString = "Data Source=你的服务器名;Initial Catalog=你的数据库名;User ID=你的用户名;Password=你的密码;";
/// <summary>
/// 大数据量批量更新（SqlBulkCopy + 临时表）
/// </summary>
public static void BatchUpdateWithBulkCopy()
{
// 模拟10000条需要更新的数据（实际可从业务中获取）
var updateList = new List<Employee>();
for (int i = 1; i <= 10000; i++)
{
updateList.Add(new Employee { EmployeeID = i, Salary = 5000 + i * 10 });
}
// 1. 将List转换为DataTable（SqlBulkCopy支持DataTable入参）
DataTable tempDt = ConvertListToDataTable(updateList);
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
// 开启事务，确保操作原子性
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
// 2. 创建临时表（#开头为局部临时表，仅当前连接可见）
string createTempTableSql = @"
CREATE TABLE #TempEmployee (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL(18, 2) NOT NULL
);";
using (SqlCommand cmd = new SqlCommand(createTempTableSql, conn, tran))
{
cmd.ExecuteNonQuery();
}
// 3. SqlBulkCopy 批量插入临时表
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
bulkCopy.DestinationTableName = "#TempEmployee"; // 目标临时表名
bulkCopy.BatchSize = 1000; // 每批次插入1000条
bulkCopy.BulkCopyTimeout = 30; // 超时时间30秒
// 映射DataTable列与临时表列（列名一致可省略，建议显式映射）
bulkCopy.ColumnMappings.Add("EmployeeID", "EmployeeID");
bulkCopy.ColumnMappings.Add("Salary", "Salary");
// 执行批量插入
bulkCopy.WriteToServer(tempDt);
}
// 4. 关联临时表与目标表，执行批量更新
string batchUpdateSql = @"
UPDATE E
SET E.Salary = T.Salary,
E.UpdateTime = GETDATE()
FROM Employee E
INNER JOIN #TempEmployee T
ON E.EmployeeID = T.EmployeeID;";
using (SqlCommand cmd = new SqlCommand(batchUpdateSql, conn, tran))
{
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine($"成功更新 {affectedRows} 条记录");
}
// 5. 删除临时表（可选，局部临时表连接关闭后自动销毁）
string dropTempTableSql = "DROP TABLE #TempEmployee;";
using (SqlCommand cmd = new SqlCommand(dropTempTableSql, conn, tran))
{
cmd.ExecuteNonQuery();
}
// 提交事务
tran.Commit();
}
catch (Exception ex)
{
// 出错回滚事务
tran.Rollback();
Console.WriteLine($"批量更新失败：{ex.Message}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"数据库连接失败：{ex.Message}");
}
}
/// <summary>
/// List转DataTable（通用方法）
/// </summary>
private static DataTable ConvertListToDataTable<T>(List<T> list)
{
DataTable dt = new DataTable();
var properties = typeof(T).GetProperties();
// 添加DataTable列
foreach (var prop in properties)
{
dt.Columns.Add(prop.Name, prop.PropertyType);
}
// 填充DataTable数据
foreach (var item in list)
{
DataRow row = dt.NewRow();
foreach (var prop in properties)
{
row = prop.GetValue(item) ?? DBNull.Value;
}
dt.Rows.Add(row);
}
return dt;
}
}
}
</pre></div>
<p class="maodian"></p><h3>方案 2：使用 Dapper 框架（简洁高效，第三方库）</h3>
<p>Dapper 是轻量级 ORM 框架，简化数据库操作，支持批量更新，通过拼接参数化 SQL 或使用 <code>Execute</code> 方法批量执行，兼顾简洁性和性能，需先安装 Dapper 包。</p>
<p class="maodian"></p><h4>步骤 1：安装 Dapper 包</h4>
<p>通过 NuGet 安装：<code>Install-Package Dapper</code> 或 <code>dotnet add package Dapper</code></p>
<h4>实现代码</h4>
<div class="jb51code"><pre class="brush:csharp;">using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Dapper;
namespace SqlServerBatchUpdate
{
class DapperBatchUpdate
{
private static readonly string _connectionString = "Data Source=你的服务器名;Initial Catalog=你的数据库名;User ID=你的用户名;Password=你的密码;";
/// <summary>
/// Dapper 批量更新
/// </summary>
public static void BatchUpdateWithDapper()
{
var updateList = new List<Employee>()
{
new Employee { EmployeeID = 1, Salary = 5500 },
new Employee { EmployeeID = 2, Salary = 6600 },
new Employee { EmployeeID = 3, Salary = 7700 }
};
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
// 批量执行更新（Dapper自动处理参数）
string sql = "UPDATE Employee SET Salary = @Salary WHERE EmployeeID = @EmployeeID;";
int affectedRows = conn.Execute(sql, updateList);
Console.WriteLine($"成功更新 {affectedRows} 条记录");
}
}
catch (Exception ex)
{
Console.WriteLine($"Dapper批量更新失败：{ex.Message}");
}
}
}
}
</pre></div>
<p class="maodian"></p><h2>三、 关键注意事项（避坑指南）</h2>
<ol><li><strong>防 SQL 注入</strong>：始终使用「参数化 SQL」（避免直接拼接字符串），无论是原生 ADO.NET 还是 Dapper，参数化查询是防止 SQL 注入的核心。</li><li><strong>事务保护</strong>：批量更新属于关键操作，建议开启事务（<code>SqlTransaction</code>），确保所有更新要么全部成功，要么全部回滚，避免数据不一致。</li><li><strong>连接释放</strong>：使用 <code>using</code> 语句包裹 <code>SqlConnection</code>、<code>SqlCommand</code> 等对象，自动释放数据库连接资源，避免连接泄露。</li><li><strong>批量大小控制</strong>：使用 <code>SqlBulkCopy</code> 时，合理设置 <code>BatchSize</code>（建议 1000~5000），过大可能占用过多内存，过小会降低效率。</li><li><strong>索引优化</strong>：目标表的关联字段（如 <code>EmployeeID</code>）建议建立主键或索引，提升 <code>JOIN</code> 操作和 <code>WHERE</code> 筛选的效率。</li><li><strong>超时设置</strong>：大数据量更新时，适当调整 <code>CommandTimeout</code>（默认 30 秒），避免因执行时间过长导致超时。</li></ol>
<p class="maodian"></p><h2>总结</h2>
<ol><li><strong>小数据量（<1000 条）</strong>：优先使用「参数化 SQL + CASE WHEN」（原生 ADO.NET）或 Dapper，简洁高效。</li><li><strong>大数据量（≥1000 条）</strong>：首选「SqlBulkCopy + 临时表」，性能最优，最大限度减少数据库交互。</li><li><strong>开发效率优先</strong>：使用 Dapper 框架，简化代码编写，兼顾性能和可读性。</li><li><strong>数据安全优先</strong>：开启事务保护、使用参数化查询、合理释放连接资源。</li></ol>
<p>以上就是C#中实现SQL Server的批量更新功能的详细内容，更多关于C# SQL Server批量更新的资料请关注琼殿技术社区其它相关文章！</p>
<div class="art_xg">
<b>您可能感兴趣的文章:</b><ul><li>C#更新SQLServer中TimeStamp字段(时间戳)的方法</li><li>使用C#与SQL Server数据库进行交互的详细流程</li><li>C#连接SQL server数据库命令的基本步骤</li><li>C#批量插入数据到sqlserver的方法详解</li><li>C#使用Winform连接SQL Server数据库的详细步骤</li></ul>
</div>
</div>
<!--endmain-->
頁:
[1]