.NET(C#) 使用 ADO.NET 上万条海量数据大批量插入优化的方法

1、ADO.NET 简介及使用

ADO.NET（ActiveX Data Objects .NET）是.NET平台上的一组数据访问技术，用于访问和操作各种数据源，如数据库、XML文档和Web服务。ADO.NET提供了一种强大的方法来连接、检索、更新和操作数据，是.NET应用程序与数据存储之间的桥梁。

1）执行查询

string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand selectCommand = new SqlCommand("SELECT * FROM TableName", connection))
    {
        using (SqlDataReader reader = selectCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                // 读取数据并进行处理
                string value = reader["ColumnName"].ToString();
            }
        }
    }
}

2）执行插入

string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // 插入数据
    string insertQuery = "INSERT INTO TableName (Column1, Column2) VALUES (@Value1, @Value2)";
    using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
    {
        insertCommand.Parameters.AddWithValue("@Value1", value1);
        insertCommand.Parameters.AddWithValue("@Value2", value2);
        int rowsAffected = insertCommand.ExecuteNonQuery();
        // rowsAffected 变量包含插入的行数
    }
}

3）执行事务

string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        try
        {
            // 执行多个数据库操作
            // 如果所有操作成功，调用 transaction.Commit() 提交事务
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // 如果发生异常，调用 transaction.Rollback() 回滚事务
            transaction.Rollback();
        }
    }
}

2、大批量插入优化及使用

将插入操作放在一个事务中，以减少提交次数，从而提高性能。将大批量数据分割成较小的批次，然后逐批插入。可以减少内存占用并减轻数据库服务器的负担。代码如下，

var sqlStatements = new List<string>();
string sqlStatement = string.Empty;
int index = 0;
// process all items
foreach (var obj in reprocessTskObjs)
{
    index++;
    sqlStatement += "insert into TakeStockInfo(Named, OrderCode, Status, Remark)" +
                    $"Values('{obj.Named}', '{obj.OrderCode}', '{obj.Status}', '{obj.Remark}');";
    // 为每1000条创建大量插入
    if (index == 1000)
    {
        sqlStatements.Add(sqlStatement);
        sqlStatement = string.Empty;
        index = 0;
    }
}
// 如果在大容量插入查询中有数据
if (index > 0)
{
    sqlStatements.Add(sqlStatement);
    sqlStatement = string.Empty;
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
    await conn.OpenAsync();
    
    using SqlTransaction trans = conn.BeginTransaction();
    try
    {
        // process all bulks of insert statements and execute
        foreach (var statement in sqlStatements)
        {
            using (SqlCommand cmd = new SqlCommand(statement, conn, trans))
            {
                await cmd.ExecuteNonQueryAsync();
            }
        }
        await trans.CommitAsync();
    }
    catch
    {
        trans.Rollback();
        throw;
    }
}