使用SqlBulkCopy的错误插入数据 [英] Error inserting data using SqlBulkCopy
本文介绍了使用SqlBulkCopy的错误插入数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想批量插入数据到SQL 2008中使用 SqlBulkCopy的
。
I'm trying to batch insert data into SQL 2008 using SqlBulkCopy
.
下面是我的表:
IF OBJECT_ID(N'statement', N'U') IS NOT NULL
DROP TABLE [statement]
GO
CREATE TABLE [statement](
[ID] INT IDENTITY(1, 1) NOT NULL,
[date] DATE NOT NULL DEFAULT GETDATE(),
[amount] DECIMAL(14,2) NOT NULL,
CONSTRAINT [PK_statement] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
下面是我的code:
private DataTable GetTable()
{
var list = new List<DataColumn>();
list.Add(new DataColumn("amount", typeof(SqlDecimal)));
list.Add(new DataColumn("date", typeof(SqlDateTime)));
var table = new DataTable("statement");
table.Columns.AddRange(list.ToArray());
var row = table.NewRow();
row["amount"] = (SqlDecimal)myObj.Amount; // decimal Amount { get; set; }
row["date"] = (SqlDateTime)myObj.Date; // DateTime Date { get; set }
table.Rows.Add(row);
return table;
}
private void WriteData()
{
using (var bulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
{
//table.Columns.ForEach(c => bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));
bulk.BatchSize = 25;
bulk.DestinationTableName = "statement";
bulk.WriteToServer(GetTable()); // a table from GetTable()
}
}
所以我得到错误:
So I'm getting error:
类型的给定值 SQLDATETIME
从数据源不能被转换为类型日期指定目标列
The given value of type
SqlDateTime
from the data source cannot be converted to typedate
of the specified target column.
为什么?我该如何解决呢?帮帮我吧,拜托了!
Why?? How can I fix that? Help me, please!
推荐答案
使用原始表脚本,下面code ++工程。
Using your original table script, the following code works.
private static DataTable GetTable()
{
var list = new List<DataColumn>();
list.Add(new DataColumn("amount", typeof(Double)));
list.Add(new DataColumn("date", typeof(DateTime)));
var table = new DataTable("statement");
table.Columns.AddRange(list.ToArray());
var row = table.NewRow();
row["amount"] = 1.2d;
row["date"] = DateTime.Now.Date;
table.Rows.Add(row);
return table;
}
private static void WriteData()
{
string strConnection = "Server=(local);Database=ScratchDb;Trusted_Connection=True;";
using (var bulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity & SqlBulkCopyOptions.KeepNulls))
{
bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("amount", "amount"));
bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("date", "date"));
bulk.BatchSize = 25;
bulk.DestinationTableName = "statement";
bulk.WriteToServer(GetTable());
}
}
如前所述由阿迈勒,你需要因为标识列的列映射。
As already stated by Amal, you need the column mappings because of the Identity column.
这篇关于使用SqlBulkCopy的错误插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文