SqlBulkCopy InvalidCastException
使用 SqlBulkCopy 类批量插入数据时报了如下错误：
System.InvalidCastException：Invalid cast from 'Int32' to 'DateTime'.
经调查当 DataTable 中列的顺序和实际数据库中列的顺序不一致时会出现该异常。
解决办法就是通过 SqlBulkCopy.ColumnMappings 属性指定 DataTable 和数据库中列的映射关系。
修改前代码
csharp
/// <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);
}
}修改后代码
csharp
/// <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);
}
}