如何将List转换为DataTable以用于SqlBulkCopy批量写入SQL数据库

没问题，咱们把单条循环插入改成批量操作，这样能大幅提升写入效率。核心分为List转DataTable和SqlBulkCopy批量写入+去重逻辑两部分，下面是完整的实现代码和注意事项：

第一步：把List转换成DataTable #

首先写一个转换方法，把你的实体集合转成符合数据库表结构的DataTable，同时处理DateAddedToDb和FileName这两个计算字段：

private DataTable ConvertToDataTable(List<ExtractedInfo> extractedList)
{
    DataTable dt = new DataTable();
    // 对应数据库表的列，确保类型和正式表一致
    dt.Columns.Add("Date", typeof(DateTime));
    dt.Columns.Add("Client", typeof(string));
    dt.Columns.Add("Path", typeof(string));
    dt.Columns.Add("DateAddedToDb", typeof(DateTime));
    dt.Columns.Add("FileName", typeof(string));

    foreach (var item in extractedList)
    {
        DataRow row = dt.NewRow();
        // 注意：如果你的Date字符串格式不标准，建议用DateTime.TryParseSafe处理避免报错
        row["Date"] = DateTime.Parse(item.Date);
        row["Client"] = item.Client;
        row["Path"] = item.Path;
        row["DateAddedToDb"] = DateTime.Now;
        row["FileName"] = item.Path.Substring(item.Path.LastIndexOf("/") + 1);
        dt.Rows.Add(row);
    }
    return dt;
}

第二步：用SqlBulkCopy批量写入+去重 #

因为你原来的逻辑是Path不存在才插入，而SqlBulkCopy本身不支持直接判断存在性，所以我们可以用「临时表+MERGE语句」的方式实现，既保证批量效率，又保留去重逻辑：

List<ExtractedInfo> extractedList = new List<ExtractedInfo>();
// 假设这里已经填充了extractedList的数据

try
{
    Console.WriteLine("Writing to DB in bulk");
    DataTable bulkData = ConvertToDataTable(extractedList);

    using (SqlConnection conn = new SqlConnection(connectionStringPMT))
    {
        conn.Open();

        // 1. 创建临时表，结构和正式表一致
        using (SqlCommand createTempTableCmd = new SqlCommand(@"
            CREATE TABLE #TempFileTrckT (
                Date DATETIME,
                Client NVARCHAR(MAX),
                Path NVARCHAR(MAX),
                DateAddedToDb DATETIME,
                FileName NVARCHAR(MAX)
            )", conn))
        {
            createTempTableCmd.ExecuteNonQuery();
        }

        // 2. 用SqlBulkCopy批量写入临时表，这一步是效率提升的核心
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.DestinationTableName = "#TempFileTrckT";
            // 列映射（如果DataTable列名和临时表完全一致，可省略，但明确映射更稳妥）
            bulkCopy.ColumnMappings.Add("Date", "Date");
            bulkCopy.ColumnMappings.Add("Client", "Client");
            bulkCopy.ColumnMappings.Add("Path", "Path");
            bulkCopy.ColumnMappings.Add("DateAddedToDb", "DateAddedToDb");
            bulkCopy.ColumnMappings.Add("FileName", "FileName");
            
            // 可选：设置批量大小，根据你的数据量调整，比如1000条/批
            bulkCopy.BatchSize = 1000;
            bulkCopy.WriteToServer(bulkData);
        }

        // 3. 用MERGE语句把临时表的数据合并到正式表，只插入不存在的记录
        using (SqlCommand mergeCmd = new SqlCommand(@"
            MERGE INTO [FileTrckT] AS Target
            USING #TempFileTrckT AS Source
            ON Target.Path = Source.Path
            WHEN NOT MATCHED THEN
                INSERT (Date, Client, Path, DateAddedToDb, FileName)
                VALUES (Source.Date, Source.Client, Source.Path, Source.DateAddedToDb, Source.FileName);", conn))
        {
            mergeCmd.ExecuteNonQuery();
        }

        // 4. 删除临时表
        using (SqlCommand dropTempTableCmd = new SqlCommand("DROP TABLE #TempFileTrckT", conn))
        {
            dropTempTableCmd.ExecuteNonQuery();
        }

        conn.Close();
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message.ToString());
    Console.WriteLine("Error occured whilst inserting bulk data into sql table FiletrckT");
    log.Info("Error occured whilst inserting bulk data into sql table FiletrckT");
    log.Error($"{DateTime.Now}: {ex.Message.ToString()}");
}

几个关键注意事项 #

• 类型匹配：确保DataTable的列类型和数据库表完全一致，比如Date字段如果是字符串转DateTime，要处理格式异常（建议用DateTime.TryParse或者指定格式解析）。
• 索引优化：给FileTrckT表的Path列加唯一索引，这样MERGE语句的匹配效率会大幅提升，同时也能从数据库层面保证Path的唯一性。
• 批量大小：SqlBulkCopy.BatchSize可以根据你的数据量调整，默认是一次性写入所有数据，设置合适的批次大小能避免内存占用过高。
• 异常处理：如果数据量很大，建议加上事务控制，避免部分插入失败导致数据不一致。

内容的提问来源于stack exchange，提问作者YongaJ