佳佳的博客
Menu
首页
IT
生活
学生
游戏
随机
SqlBulkCopy InvalidCastException
IT
C#
2019-07-30
目录
<!-- # SqlBulkCopy InvalidCastException --> <!-- sql-bulk-copy-invalid-cast-exception --> 使用 `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); } } ``` ### 参考文档 1. [SqlBulkCopy - The given ColumnMapping does not match up with any column in the source or destination.](https://sqlbulkcopy-tutorial.net/columnmapping-does-not-match) 1. [单个批量复制操作](https://docs.microsoft.com/zh-cn/dotnet/framework/data/adonet/sql/single-bulk-copy-operations) 1. [SqlBulkCopy.ColumnMappings Property](https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy.columnmappings?view=netframework-4.8)
版权声明:原创文章,未经允许不得转载。
https://www.liujiajia.me/2019/07/30/sql-bulk-copy-invalid-cast-exception/
« [Editor.md] markdownToHTML 的使用以及自定义代码高亮样式
[C#] AsyncLocal »
昵称
*
电子邮箱
*
回复内容
*
(回复审核后才会显示)
提交