你是否还在为大量数据导入而头疼？传统的逐条Insert让你的应用卡顿不堪？今天就来分享一个C#开发中的性能神器——SqlBulkCopy ，它能让你的数据导入速度提升10倍以上！
无论你是在做数据迁移、批量导入Excel数据，还是处理海量业务数据，掌握SqlBulkCopy都能让你的应用性能脱胎换骨。本文将通过完整的WinForm实战项目，手把手教你用好这个高性能工具。
💥 传统Insert的性能痛点 在日常开发中，我们经常遇到这样的场景：
Excel数据导入系统 数据库迁移任务 批量业务数据处理 传统做法通常是这样：
// 传统方式：逐条插入，性能极差 foreach(var item in dataList) { string sql = "INSERT INTO Employees VALUES(@name, @email, @age)" ; // 执行单条插入... } 问题分析：
每条记录都要建立数据库连接 大量的网络往返开销 事务日志频繁写入 10万条数据可能需要几十分钟！ 🔥 SqlBulkCopy解决方案 SqlBulkCopy是.NET Framework提供的高性能批量插入工具，它的核心优势：
✅ 批量操作 ：一次性处理大量数据
✅ 最小化日志 ：减少事务日志开销
✅ 网络优化 ：减少数据库往返次数
✅ 内存友好 ：支持流式处理大数据集
💻 完整实战项目解析 让我们通过一个完整的WinForm项目来掌握SqlBulkCopy的使用：
🏗️ 项目架构设计 public partial class Form1 : Form { private string connectionString = "Server=localhost;Database=dbtest;Integrated Security=true;Connect Timeout=90;Encrypt=True;TrustServerCertificate=True;" ; public Form1 () { InitializeComponent(); InitializeData(); } } 📊 核心功能实现 1️⃣ 测试数据生成 private void GenerateTestData ( int recordCount) { Random random = new Random(); string [] firstNames = { "张" , "李" , "王" , "刘" , "陈" , "杨" , "赵" , "黄" , "周" , "吴" }; string [] lastNames = { "伟" , "芳" , "娜" , "秀英" , "敏" , "静" , "丽" , "强" , "磊" , "军" }; for ( int i = 1 ; i <= recordCount; i++) { string firstName = firstNames[random.Next(firstNames.Length)]; string lastName = lastNames[random.Next(lastNames.Length)]; string name = firstName + lastName + i.ToString( "000" ); string email = $ "user{i}@test.com" ; int age = random.Next( 20 , 60 ); decimal salary = random.Next( 3000 , 20000 ); DateTime createDate = DateTime.Now.AddDays(-random.Next( 0 , 365 )); // 添加到DataGridView显示 dataGridView1.Invoke( new Action(() => { dataGridView1.Rows.Add(i, name, email, age, salary, createDate); })); // 进度更新 if (i % 100 == 0 ) { int progress = ( int )(( double )i / recordCount * 100 ); progressBar1.Invoke( new Action(() => progressBar1.Value = progress)); } } } 2️⃣ 高性能批量插入核心代码 private void ExecuteBulkInsert ( string connStr) { // 1. 创建目标表 CreateTargetTableIfNotExists(connStr); // 2. 准备DataTable数据源 DataTable dataTable = CreateDataTable(); FillDataTableFromGrid(dataTable); // 3. 执行批量插入 using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); // 清空目标表 using (SqlCommand cmd = new SqlCommand( "TRUNCATE TABLE Employees" , connection)) { cmd.ExecuteNonQuery(); } // 配置SqlBulkCopy - 这里是性能优化关键！ using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "Employees" ; bulkCopy.BatchSize = 5000 ; // 批处理大小 bulkCopy.BulkCopyTimeout = 300 ; // 5分钟超时 // 列映射 - 确保数据正确对应 bulkCopy.ColumnMappings.Add( "ID" , "ID" ); bulkCopy.ColumnMappings.Add( "Name" , "Name" ); bulkCopy.ColumnMappings.Add( "Email" , "Email" ); bulkCopy.ColumnMappings.Add( "Age" , "Age" ); bulkCopy.ColumnMappings.Add( "Salary" , "Salary" ); bulkCopy.ColumnMappings.Add( "CreateDate" , "CreateDate" ); // 进度监控 bulkCopy.NotifyAfter = 1000 ; bulkCopy.SqlRowsCopied += (s, e) => { int progress = ( int )(( double )e.RowsCopied / dataTable.Rows.Count * 100 ); progressBar1.Invoke( new Action(() => { progressBar1.Value = Math.Min(progress, 100 ); labelStatus.Text = $ "已插入 {e.RowsCopied} 条记录..." ; })); }; // 执行批量插入 bulkCopy.WriteToServer(dataTable); } } } 3️⃣ 数据表创建和数据准备 private void CreateTargetTableIfNotExists ( string connStr) { string createTableSql = @ " IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Employees' AND xtype='U') BEGIN CREATE TABLE Employees ( ID int NOT NULL, Name nvarchar(100) NOT NULL, Email nvarchar(200) NOT NULL, Age int NOT NULL, Salary decimal(18,2) NOT NULL, CreateDate datetime NOT NULL, CONSTRAINT PK_Employees PRIMARY KEY (ID) ) END" ; using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); using (SqlCommand cmd = new SqlCommand(createTableSql, connection)) { cmd.ExecuteNonQuery(); } } } private DataTable CreateDataTable () { DataTable dt = new DataTable(); dt.Columns.Add( "ID" , typeof( int )); dt.Columns.Add( "Name" , typeof( string )); dt.Columns.Add( "Email" , typeof( string )); dt.Columns.Add( "Age" , typeof( int )); dt.Columns.Add( "Salary" , typeof(decimal)); dt.Columns.Add( "CreateDate" , typeof(DateTime)); return dt; } 🎯 关键性能优化技巧 1️⃣ BatchSize优化 bulkCopy.BatchSize = 5000 ; // 根据数据量调整 2️⃣ 连接配置优化 private string connectionString = "Server=localhost;Database=dbtest;Integrated Security=true;Connect Timeout=90;Encrypt=True;TrustServerCertificate=True;" ; 3️⃣ 异步处理用户体验 private async void buttonBulkInsert_Click (object sender, EventArgs e) { try { buttonBulkInsert.Enabled = false ; Stopwatch stopwatch = Stopwatch.StartNew(); await Task.Run(() => ExecuteBulkInsert(connStr)); stopwatch.Stop(); MessageBox.Show($ "批量插入成功！耗时: {stopwatch.ElapsedMilliseconds} 毫秒" ); } finally { buttonBulkInsert.Enabled = true ; } } 🔆 完整代码 using System.Data; using System.Diagnostics; using Microsoft.Data.SqlClient; namespace AppWinformSqlBulkCopy { public partial class Form1 : Form { private string connectionString = "Server=localhost;Database=dbtest;Integrated Security=true;Connect Timeout=90;Encrypt=True;TrustServerCertificate=True;" ; public Form1 () { InitializeComponent(); InitializeData(); } private void InitializeData () { // 初始化DataGridView列 dataGridView1.Columns.Add( "ID" , "ID" ); dataGridView1.Columns.Add( "Name" , "姓名" ); dataGridView1.Columns.Add( "Email" , "邮箱" ); dataGridView1.Columns.Add( "Age" , "年龄" ); dataGridView1.Columns.Add( "Salary" , "薪资" ); dataGridView1.Columns.Add( "CreateDate" , "创建日期" ); // 设置列宽 dataGridView1.Columns[ "ID" ].Width = 60 ; dataGridView1.Columns[ "Name" ].Width = 100 ; dataGridView1.Columns[ "Email" ].Width = 200 ; dataGridView1.Columns[ "Age" ].Width = 60 ; dataGridView1.Columns[ "Salary" ].Width = 100 ; dataGridView1.Columns[ "CreateDate" ].Width = 150 ; // 设置连接字符串到文本框 textBoxConnectionString.Text = connectionString; // 设置默认记录数 numericUpDownRecords.Value = 10000 ; // 设置默认批处理大小 numericUpDownBatchSize.Value = 5000 ; } private async void buttonGenerateData_Click (object sender, EventArgs e) { try { buttonGenerateData.Enabled = false ; progressBar1.Value = 0 ; labelStatus.Text = "正在生成测试数据..." ; int recordCount = ( int )numericUpDownRecords.Value; await Task.Run(() => GenerateTestData(recordCount)); labelStatus.Text = $ "成功生成 {recordCount} 条测试数据" ; buttonBulkInsert.Enabled = true ; } catch (Exception ex) { MessageBox.Show($ "生成数据时发生错误: {ex.Message}" , "错误" , MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { buttonGenerateData.Enabled = true ; } } private void GenerateTestData ( int recordCount) { dataGridView1.Invoke( new Action(() => dataGridView1.Rows.Clear())); Random random = new Random(); string [] firstNames = { "张" , "李" , "王" , "刘" , "陈" , "杨" , "赵" , "黄" , "周" , "吴" }; string [] lastNames = { "伟" , "芳" , "娜" , "秀英" , "敏" , "静" , "丽" , "强" , "磊" , "军" }; for ( int i = 1 ; i <= recordCount; i++) { string firstName = firstNames[random.Next(firstNames.Length)]; string lastName = lastNames[random.Next(lastNames.Length)]; string name = firstName + lastName + i.ToString( "000" ); string email = $ "user{i}@test.com" ; int age = random.Next( 20 , 60 ); decimal salary = random.Next( 3000 , 20000 ); DateTime createDate = DateTime.Now.AddDays(-random.Next( 0 , 365 )); dataGridView1.Invoke( new Action(() => { dataGridView1.Rows.Add(i, name, email, age, salary, createDate); })); // 更新进度条 if (i % 100 == 0 ) { int progress = ( int )(( double )i / recordCount * 100 ); progressBar1.Invoke( new Action(() => progressBar1.Value = progress)); } } progressBar1.Invoke( new Action(() => progressBar1.Value = 100 )); } private async void buttonBulkInsert_Click (object sender, EventArgs e) { try { buttonBulkInsert.Enabled = false ; progressBar1.Value = 0 ; labelStatus.Text = "正在执行批量插入..." ; string connStr = textBoxConnectionString.Text.Trim(); if ( string .IsNullOrEmpty(connStr)) { MessageBox.Show( "请输入连接字符串" , "提示" ); return ; } Stopwatch stopwatch = Stopwatch.StartNew(); await Task.Run(() => ExecuteBulkInsert(connStr)); stopwatch.Stop(); labelStatus.Text = $ "批量插入完成，耗时: {stopwatch.ElapsedMilliseconds} 毫秒" ; progressBar1.Value = 100 ; MessageBox.Show($ "批量插入成功完成！\n插入记录数: {dataGridView1.Rows.Count}\n耗时: {stopwatch.ElapsedMilliseconds} 毫秒" , "成功" , MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show($ "批量插入时发生错误: {ex.Message}" , "错误" , MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { buttonBulkInsert.Enabled = true ; } } private void ExecuteBulkInsert ( string connStr) { // 首先创建目标表（如果不存在） CreateTargetTableIfNotExists(connStr); // 创建DataTable DataTable dataTable = CreateDataTable(); // 从DataGridView填充DataTable FillDataTableFromGrid(dataTable); // 执行批量插入 using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); // 清空目标表 using (SqlCommand cmd = new SqlCommand( "TRUNCATE TABLE Employees" , connection)) { cmd.ExecuteNonQuery(); } // 配置SqlBulkCopy using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "Employees" ; bulkCopy.BatchSize = ( int )numericUpDownBatchSize.Invoke( new Func<decimal>(() => numericUpDownBatchSize.Value)); bulkCopy.BulkCopyTimeout = 300 ; // 5分钟超时 // 映射列 bulkCopy.ColumnMappings.Add( "ID" , "ID" ); bulkCopy.ColumnMappings.Add( "Name" , "Name" ); bulkCopy.ColumnMappings.Add( "Email" , "Email" ); bulkCopy.ColumnMappings.Add( "Age" , "Age" ); bulkCopy.ColumnMappings.Add( "Salary" , "Salary" ); bulkCopy.ColumnMappings.Add( "CreateDate" , "CreateDate" ); // 进度通知 bulkCopy.NotifyAfter = 1000 ; bulkCopy.SqlRowsCopied += (s, e) => { int progress = ( int )(( double )e.RowsCopied / dataTable.Rows.Count * 100 ); progressBar1.Invoke( new Action(() => { progressBar1.Value = Math.Min(progress, 100 ); labelStatus.Text = $ "已插入 {e.RowsCopied} 条记录..." ; })); }; // 执行批量插入 bulkCopy.WriteToServer(dataTable); } } } private void CreateTargetTableIfNotExists ( string connStr) { string createTableSql = @ " IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Employees' AND xtype='U') BEGIN CREATE TABLE Employees ( ID int NOT NULL, Name nvarchar(100) NOT NULL, Email nvarchar(200) NOT NULL, Age int NOT NULL, Salary decimal(18,2) NOT NULL, CreateDate datetime NOT NULL, CONSTRAINT PK_Employees PRIMARY KEY (ID) ) END" ; using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); using (SqlCommand cmd = new SqlCommand(createTableSql, connection)) { cmd.ExecuteNonQuery(); } } } private DataTable CreateDataTable () { DataTable dt = new DataTable(); dt.Columns.Add( "ID" , typeof( int )); dt.Columns.Add( "Name" , typeof( string )); dt.Columns.Add( "Email" , typeof( string )); dt.Columns.Add( "Age" , typeof( int )); dt.Columns.Add( "Salary" , typeof(decimal)); dt.Columns.Add( "CreateDate" , typeof(DateTime)); return dt; } private void FillDataTableFromGrid (DataTable dataTable) { dataGridView1.Invoke( new Action(() => { foreach (DataGridViewRow row in dataGridView1.Rows) { if (row.IsNewRow) continue ; DataRow dataRow = dataTable.NewRow(); dataRow[ "ID" ] = Convert.ToInt32(row.Cells[ "ID" ].Value); dataRow[ "Name" ] = row.Cells[ "Name" ].Value.ToString(); dataRow[ "Email" ] = row.Cells[ "Email" ].Value.ToString(); dataRow[ "Age" ] = Convert.ToInt32(row.Cells[ "Age" ].Value); dataRow[ "Salary" ] = Convert.ToDecimal(row.Cells[ "Salary" ].Value); dataRow[ "CreateDate" ] = Convert.ToDateTime(row.Cells[ "CreateDate" ].Value); dataTable.Rows.Add(dataRow); } })); } private void buttonTestConnection_Click (object sender, EventArgs e) { try { string connStr = textBoxConnectionString.Text.Trim(); if ( string .IsNullOrEmpty(connStr)) { MessageBox.Show( "请输入连接字符串" , "提示" ); return ; } using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); MessageBox.Show( "数据库连接成功！" , "成功" , MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { MessageBox.Show($ "数据库连接失败: {ex.Message}" , "错误" , MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void buttonClearGrid_Click (object sender, EventArgs e) { dataGridView1.Rows.Clear(); buttonBulkInsert.Enabled = false ; progressBar1.Value = 0 ; labelStatus.Text = "就绪" ; } } }
⚠️ 实战踩坑指南 🔥 常见问题及解决方案 1. 列映射错误
// 错误：列名不匹配 bulkCopy.ColumnMappings.Add( "UserName" , "Name" ); // 正确：确保源列和目标列名称对应 bulkCopy.ColumnMappings.Add( "Name" , "Name" ); 2. 数据类型不匹配
// 确保DataTable列类型与数据库表一致 dt.Columns.Add( "Salary" , typeof(decimal)); // 不是string! 3. 连接超时问题
bulkCopy.BulkCopyTimeout = 300 ; // 设置足够的超时时间 📈 性能对比测试
🎯 实际应用场景 📊 Excel数据导入 // 读取Excel数据到DataTable DataTable excelData = ReadExcelToDataTable(filePath); // 直接使用SqlBulkCopy导入 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "ImportTable" ; bulkCopy.WriteToServer(excelData); } 🔄 数据库迁移 // 从源数据库查询 string selectSql = "SELECT * FROM SourceTable" ; using (SqlDataAdapter adapter = new SqlDataAdapter(selectSql, sourceConnection)) { DataTable sourceData = new DataTable(); adapter.Fill(sourceData); // 批量插入到目标数据库 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConnection)) { bulkCopy.DestinationTableName = "TargetTable" ; bulkCopy.WriteToServer(sourceData); } } 💡 最佳实践总结 合理设置BatchSize 使用事务控制 监控进度 异常处理 资源管理 掌握了SqlBulkCopy，你就拥有了C#开发中的性能利器 ！无论是日常的数据导入还是大型数据迁移项目，都能游刃有余。下次遇到大数据量操作，记得用SqlBulkCopy让你的应用飞起来！
阅读原文：原文链接
400 186 1886
