使用 SqlBulkCopy 类批量插入数据时报了如下错误：

System.InvalidCastException：Invalid cast from ‘Int32’ to ‘DateTime’.

经调查当 DataTable 中列的顺序和实际数据库中列的顺序不一致时会出现该异常。
解决办法就是通过 SqlBulkCopy.ColumnMappings 属性指定 DataTable 和数据库中列的映射关系。

修改前代码

/// <summary>
/// 批量新增数据
/// </summary>
/// <param name="dt"></param>
/// <param name="dbContext"></param>
public void BulkAdd(DataTable dt, IDbContext dbContext)
{
    // 获取 fluent data 框架下的连接对象
    if (dbContext != null && dbContext.Data != null && (dbContext.Data.Connection is SqlConnection) && (dbContext.Data.Transaction is SqlTransaction))
    {
        SqlConnection conn = (SqlConnection)dbContext.Data.Connection;
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        SqlTransaction trsaction = (SqlTransaction)dbContext.Data.Transaction;
        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trsaction);
        sqlbulkcopy.DestinationTableName = dt.TableName;
        sqlbulkcopy.WriteToServer(dt);
    }
}

修改后代码

/// <summary>
/// 批量新增数据
/// </summary>
/// <param name="dt"></param>
/// <param name="dbContext"></param>
public void BulkAdd(DataTable dt, IDbContext dbContext)
{
    // 获取 fluent data 框架下的连接对象
    if (dbContext != null && dbContext.Data != null && (dbContext.Data.Connection is SqlConnection) && (dbContext.Data.Transaction is SqlTransaction))
    {
        SqlConnection conn = (SqlConnection)dbContext.Data.Connection;
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        SqlTransaction trsaction = (SqlTransaction)dbContext.Data.Transaction;
        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trsaction);
        sqlbulkcopy.DestinationTableName = dt.TableName;
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
        }
        sqlbulkcopy.WriteToServer(dt);
    }
}

参考文档

1. SqlBulkCopy – The given ColumnMapping does not match up with any column in the source or destination.
2. 单个批量复制操作
3. SqlBulkCopy.ColumnMappings Property

© 2019, 佳佳. 版权所有. 未经作者同意，严禁转载。