使用 SqlBulkCopy 插入数据时出错
我正在尝试使用 SqlBulkCopy
将数据批量插入到 SQL 2008 中。
这是我的表格:
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
这是我的代码:
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()
}
}
所以我收到错误:
数据源中
SqlDateTime
类型的给定值无法转换为指定目标列的date
类型。
为什么?? 我该如何解决这个问题? 请帮帮我!
I'm trying to batch insert data into SQL 2008 using SqlBulkCopy
.
Here is my table:
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
Here is my 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:
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!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
使用原始表脚本,以下代码可以工作。
正如 Amal 已经说过的,由于 Identity 列,您需要列映射。
Using your original table script, the following code works.
As already stated by Amal, you need the column mappings because of the Identity column.
SQL Date 类型与 SQL DateTime 类型不同。 我认为表中的日期列需要是 DateTime 类型,具体取决于您使用它的方式。
SQL 日期类型
SQL DateTime type
更新:
我认为马克的答案应该有效,但你可能需要指定从源 DataTable 到目标的 SqlBulkCopyColumnMappings,否则可能会导致映射错误,因为输入表的结构与输出表不完全匹配,即交换了日期和行列的顺序。
例如
The SQL Date type is different to the SQL DateTime type. I think the date column in your table needs to be of type DateTime, based on the way you are using it.
SQL Date Type
SQL DateTime type
Update:
I think Marc's answer should work, but you probably need to specify the SqlBulkCopyColumnMappings from your source DataTable to the destination, otherwise it might be getting the mapping wrong because the structure of your input table does not match the output table exactly ie order of date and row columns swapped.
eg
SqlDateTime
表示原始的datetime
类型。 您是否尝试过在DataTable
中仅使用DateTime
.NET 类型? 我希望它可以将其转换为 TSQLdatetime
或date
类型。 同上decimal
而不是SqlDecimal
。SqlDateTime
represents the originaldatetime
type. Have you tried just using theDateTime
.NET type in theDataTable
? I would hope that it can convert this to either the TSQLdatetime
ordate
types. Dittodecimal
instead ofSqlDecimal
.