C#批量插入数据到Sqlserver中的三种方式

前言

在开发过程中，我们经常需要将大量数据批量插入到SQL Server数据库中。不同的情况下，选择合适的插入方式可以提高插入效率和性能。本文将介绍三种常用的C#批量插入数据到SQL Server中的方式。

1. 使用SqlBulkCopy类

SqlBulkCopy类是.NET Framework提供的快速插入大量数据的一种方式。它可以将数据从一个数据源（如DataTable、DataSet或DataReader）快速地批量复制到SQL Server表中。

下面是使用SqlBulkCopy类批量插入数据到SQL Server的示例代码：

using System.Data.SqlClient;

// 创建DataTable并插入数据
DataTable dataTable = new DataTable();
// 添加列
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
// 添加行数据
dataTable.Rows.Add(1, "张三");
dataTable.Rows.Add(2, "李四");

// 创建SqlConnection对象
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User Id=UserName;Password=Password;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // 创建SqlBulkCopy对象
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        // 设置目标表名
        bulkCopy.DestinationTableName = "TableName";
        // 映射列名与DataTable的列名
        bulkCopy.ColumnMappings.Add("Id", "Id");
        bulkCopy.ColumnMappings.Add("Name", "Name");
        // 批量插入数据
        connection.Open();
        bulkCopy.WriteToServer(dataTable);
    }
}

上述代码首先创建了一个DataTable对象，并添加了需要插入的列和数据。然后，创建SqlConnection对象并指定数据库连接字符串。接下来，使用SqlBulkCopy类创建一个实例，设置目标表名和列映射关系，并调用WriteToServer方法将数据批量插入到SQL Server中。

2. 使用Table-Valued Parameters (TVP)

Table-Valued Parameters（TVP）是一种SQL Server提供的以表为参数的技术。它允许我们将一个表作为参数传递给存储过程或批量操作语句，从而实现批量插入数据。

使用TVP方式进行批量插入，需要按照以下步骤进行：

1. 首先，在SQL Server中定义一个用户自定义表类型（User-Defined Table Type）。
2. 在C#代码中创建一个DataTable对象，并按照自定义表类型的结构填充数据。
3. 在C#代码中调用存储过程或批量操作语句，将DataTable作为参数传递给SQL Server。

下面是使用TVP方式批量插入数据到SQL Server的示例代码：

using System;
using System.Data;
using System.Data.SqlClient;

// 创建用户自定义表类型
CREATE TYPE dbo.MyTableType AS TABLE
(
    Id int,
    Name nvarchar(50)
);

// 在存储过程中使用用户自定义表类型作为参数
CREATE PROCEDURE dbo.InsertData
(
    @data MyTableType READONLY
)
AS
BEGIN
    INSERT INTO TableName
    SELECT * FROM @data;
END

// C#代码中使用Table-Valued Parameters批量插入数据
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // 创建DataTable并插入数据
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("Id", typeof(int));
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Rows.Add(1, "张三");
    dataTable.Rows.Add(2, "李四");

    // 创建存储过程的SqlCommand
    using (SqlCommand command = new SqlCommand("dbo.InsertData", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        // 创建存储过程的参数，并设置参数类型和值
        SqlParameter parameter = command.Parameters.AddWithValue("@data", dataTable);
        parameter.SqlDbType = SqlDbType.Structured;

        // 执行存储过程
        connection.Open();
        command.ExecuteNonQuery();
    }
}    

上述代码中，首先在SQL Server中创建了一个用户自定义表类型，并在存储过程中使用该类型作为参数。然后，在C#代码中创建一个DataTable对象，并按照自定义表类型的结构和数据填充DataTable。最后，使用SqlCommand执行存储过程，并将DataTable作为参数传递给SQL Server。

3. 使用SQL语句的INSERT INTO SELECT语法

如果需要将数据从一个表复制到另一个表，可以使用INSERT INTO SELECT语法来批量插入数据。这种方式不需要额外创建DataTable，直接使用SQL语句进行插入。

下面是使用SQL语句的INSERT INTO SELECT语法进行批量插入数据到SQL Server的示例代码：

using System.Data.SqlClient;

string sourceTableName = "SourceTableName";
string destinationTableName = "DestinationTableName";

string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User Id=UserName;Password=Password;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // 创建插入数据的SqlCommand对象，并指定源表和目标表的列映射关系
    using (SqlCommand command = new SqlCommand($"INSERT INTO {destinationTableName} (Id, Name) SELECT Id, Name FROM {sourceTableName}", connection))
    {
        // 执行插入数据的SQL语句
        connection.Open();
        command.ExecuteNonQuery();
    }
}

上述代码使用SqlCommand对象执行插入数据的SQL语句，语句中使用INSERT INTO SELECT语法将源表的数据复制到目标表中。需要注意的是，源表和目标表的结构需要保持一致。

总结

本文介绍了C#中三种常用的批量插入数据到SQL Server的方式：使用SqlBulkCopy类、Table-Valued Parameters和SQL语句的INSERT INTO SELECT语法。通过选择合适的方式，可以提高数据插入的效率和性能。