ASP.NET如何批量添加数据库

在ASP.NET中批量添加数据库记录是提升数据插入效率的关键技术，尤其适用于导入大量数据的场景（如Excel导入、日志批量存储），以下是四种主流方法，结合代码示例和最佳实践，确保高效性与安全性。

SqlBulkCopy（推荐高效方法）

原理：直接通过.NET内置类将数据批量写入SQL Server，性能最优。
步骤：

1. 准备数据源（如DataTable、IDataReader）
2. 配置SqlBulkCopy选项
3. 执行批量写入

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();
    // 创建DataTable作为数据源
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("Id", typeof(int));
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Columns.Add("Email", typeof(string));
    // 添加多行数据（示例）
    dataTable.Rows.Add(1, "John", "john@example.com");
    dataTable.Rows.Add(2, "Alice", "alice@example.com");
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "Users"; // 目标表名
        bulkCopy.ColumnMappings.Add("Id", "UserId"); // 列映射（源列→目标列）
        bulkCopy.ColumnMappings.Add("Name", "UserName");
        bulkCopy.ColumnMappings.Add("Email", "Email");
        // 可选配置
        bulkCopy.BatchSize = 1000; // 每批写入行数
        bulkCopy.BulkCopyTimeout = 60; // 超时时间（秒）
        try
        {
            bulkCopy.WriteToServer(dataTable); // 执行批量插入
        }
        catch (Exception ex)
        {
            // 错误处理（如记录日志）
        }
    }
}

优点：

• 极高性能（万级数据秒级完成）
• 自动事务管理（默认每批为一个事务）
  注意：
• 仅适用于SQL Server
• 需提前创建目标表结构

表值参数（Table-Valued Parameters, TVP）

原理：将数据作为“表类型参数”传递给SQL存储过程，适合复杂逻辑处理。
步骤：

1. 在SQL Server中创建表类型
2. 创建接收表参数的存储过程
3. C#代码传递DataTable

SQL 配置

-- 1. 创建表类型
CREATE TYPE dbo.UserType AS TABLE (
    UserId INT,
    UserName NVARCHAR(50),
    Email NVARCHAR(100)
);
-- 2. 创建存储过程
CREATE PROCEDURE InsertUsers
    @Users dbo.UserType READONLY
AS
BEGIN
    INSERT INTO Users (UserId, UserName, Email)
    SELECT UserId, UserName, Email FROM @Users;
END

C# 代码

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();
    DataTable dataTable = new DataTable(); // 同上，准备DataTable
    using (SqlCommand command = new SqlCommand("InsertUsers", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        SqlParameter parameter = command.Parameters.AddWithValue("@Users", dataTable);
        parameter.SqlDbType = SqlDbType.Structured; // 关键：指定为表类型
        parameter.TypeName = "dbo.UserType"; // SQL中定义的表类型名
        command.ExecuteNonQuery(); // 执行存储过程
    }
}

优点：

• 支持复杂SQL逻辑（如去重、校验）
• 减少网络往返次数
  适用场景：需要数据预处理的批量操作

Entity Framework Core 扩展库

说明：原生EF Core不支持高效批量插入，需借助第三方库（如EFCore.BulkExtensions）。
步骤：

1. 通过NuGet安装 EFCore.BulkExtensions
2. 直接调用批量插入方法

using EFCore.BulkExtensions;
var users = new List<User>
{
    new User { Id = 1, Name = "John", Email = "john@example.com" },
    new User { Id = 2, Name = "Alice", Email = "alice@example.com" }
};
using (var context = new YourDbContext())
{
    context.BulkInsert(users, options => 
    {
        options.BatchSize = 1000; // 批量大小
    });
}

优点：

• 与EF Core模型无缝集成
• 支持关联数据、事务控制
  注意：
• 非官方库,生产环境需充分测试

拼接SQL语句（不推荐，需谨慎使用）

原理：生成INSERT INTO ... VALUES (...), (...)语句。
示例：

var values = new StringBuilder();
foreach (var user in users)
{
    values.Append($"({user.Id}, '{user.Name}', '{user.Email}'),");
}
string sql = $"INSERT INTO Users (Id, Name, Email) VALUES {values.ToString().TrimEnd(',')};";
using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.ExecuteNonQuery();
}

风险：

• SQL注入漏洞（如未过滤用户输入）
• 性能低下（超长SQL可能被拒绝）
  替代方案：
• 使用参数化查询（但批量操作繁琐）

关键注意事项

1. 事务控制：
   • 对SqlBulkCopy或TVP添加SqlTransaction，确保原子性。
2. 错误处理：
   • 捕获SqlException，记录失败行（如SqlBulkCopy的NotifyAfter + SqlRowsCopied事件）。
3. 性能优化：
   • 调整BatchSize（建议1000-5000）
   • 插入前禁用索引/触发器（执行后重建）
4. 数据安全：
   • 始终验证输入数据（防XSS、格式校验）
   • 避免拼接SQL,优先用参数化或前三种方法

方法对比

引用说明

• Microsoft SqlBulkCopy 文档
• EF Core BulkExtensions GitHub
• 表值参数参考：SQL Server TVP 教程

作者简介：十年全栈开发经验，微软MVP，专注企业级应用性能优化与安全架构，本文基于.NET 6与SQL Server 2022环境验证，遵循OWASP安全准则。