【C#】SqlBulkCopy批量添加注意DataTable必须与表列顺序一致，否则报错，以及关闭自增列

全栈小5

于 2023-07-05 12:33:13 发布

阅读量1.2k

点赞数 15

分类专栏： C# 文章标签： c# 数据库 sql server DataTable

本文链接：https://blog.csdn.net/lmy_520/article/details/131531250

版权

C# 专栏收录该内容

122 篇文章 5 订阅

订阅专栏

前篇文章在测试一个批量添加的操作，发现一致添加不成功，最后分析是字段列顺序不一致的问题

1、列名不一致

1.1、错误信息

在调试时，一致提示如下错误信息，对比了数据和类型，并没有发现什么问题，经过分析，第一列是guid字符串，而表的第一个字段是自增id为整型，这就能解释下面的提示信息：
来自数据源的 String 类型的给定值不能转换为指定目标列的类型 int。
在这里插入图片描述

1.2、解决方法

在使用 SqlBulkCopy 进行批量添加 DataTable 时，确保 DataTable 的列与数据库表的列保持一致是非常重要的。

以下是必须满足的要求：

1）列名匹配：DataTable 中的列名必须与数据库表的列名一致。确保列名的大小写和拼写正确。

2）列的数量：DataTable 中的列数量必须与数据库表的列数量一致。

3）数据类型匹配：DataTable 中的列的数据类型必须与数据库表的列的数据类型相匹配。确保数据类型兼容，并且可以正确地转换。

如果 DataTable 的列与数据库表的列不一致，SqlBulkCopy 操作可能会失败，或者导致数据插入错误。

如果你想改变 DataTable 列的名称，可以使用 ColumnMapping 进行映射。

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // 创建一个 DataTable 并填充数据
    DataTable dataTable = CreateDataTable();

    // 创建一个 SqlBulkCopy 对象
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "YourDestinationTableName";

        // 映射 DataTable 的列到数据库表的列
        bulkCopy.ColumnMappings.Add("SourceColumn1", "DestinationColumn1");
        bulkCopy.ColumnMappings.Add("SourceColumn2", "DestinationColumn2");

        // 执行批量插入
        bulkCopy.WriteToServer(dataTable);
    }
}

在上述代码中，将 DataTable 列的名称映射到目标表的列名称，并确保映射的列数量和数据类型正确匹配。

总结起来，为了成功进行批量添加 DataTable 的操作，确保 DataTable 的列与数据库表的列保持一致。还可以使用 ColumnMappings 来映射列名称，以便在列名不一致的情况下进行操作。

2、关闭自增列

在批量添加 DataTable 数据时，如果需要处理自增的 ID 列，可以采取以下两种常见的方法：

2.1、不包含列

禁用目标表的自增特性：
在目标表的 ID 列上禁用自增特性，然后在 SqlBulkCopy 操作时，不将源表的 ID 列包括在映射中。这样，数据库引擎会自动为每一行分配新的自增 ID。

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // 创建一个 DataTable 并填充数据
    DataTable dataTable = CreateDataTable();

    // 创建一个 SqlBulkCopy 对象
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "YourDestinationTableName";

        // 不包括源表的 ID 列在映射中
        bulkCopy.ColumnMappings.Add("Column1", "Column1");
        bulkCopy.ColumnMappings.Add("Column2", "Column2");

        // 执行批量插入
        bulkCopy.WriteToServer(dataTable);
    }
}

在上述代码中，假设源表中的 ID 列不需要在 SqlBulkCopy 中映射为目标表的 ID 列。数据库引擎会为目标表生成自增的 ID 值。

2.2、特性关闭

在 DataTable 中关闭自增特性：
如果你希望保留源表的自增 ID 列，并且将其插入到目标表中，但避免与目标表的自增 ID 冲突，可以在 DataTable 中关闭自增特性。

// 创建一个 DataTable 并填充数据
DataTable dataTable = CreateDataTable();

// 关闭 ID 列的自增特性
dataTable.Columns["ID"].AutoIncrement = false;
dataTable.Columns["ID"].ReadOnly = true;

// 接下来使用 SqlBulkCopy 进行批量插入操作