C# 使用 SqlBulkCopy 插入数据时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1024006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error inserting data using SqlBulkCopy
提问by abatishchev
I'm trying to batch insert data into SQL 2008 using SqlBulkCopy.
我正在尝试使用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
SqlDateTimefrom the data source cannot be converted to typedateof the specified target column.
SqlDateTime来自数据源的 type 给定值无法转换为date指定目标列的类型。
Why?? How can I fix that? Help me, please!
为什么？？我该如何解决？请帮帮我！
采纳答案by Kim Major
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.
正如 Amal 已经说过的，由于 Identity 列，您需要列映射。
回答by Amal Sirisena
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 类型不同于 SQL DateTime 类型。我认为表格中的日期列需要是 DateTime 类型，具体取决于您使用它的方式。
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.
我认为 Marc 的答案应该有效，但您可能需要指定从源 DataTable 到目标的 SqlBulkCopyColumnMappings，否则可能会导致映射错误，因为您的输入表的结构与输出表不完全匹配，即日期和行列交换。
eg
例如
var amount = new SqlBulkCopyColumnMapping("amount", "amount");
var date = new SqlBulkCopyColumnMapping("date", "date");
bulk.ColumnMappings.Add(amount);
bulk.ColumnMappings.Add(date);
回答by Marc Gravell
SqlDateTimerepresents the original datetimetype. Have you tried just using the DateTime.NET type in the DataTable? I would hope that it can convert this to either the TSQL datetimeor datetypes. Ditto decimalinstead of SqlDecimal.
SqlDateTime代表原始datetime类型。您是否尝试过在DateTime.NET 中使用.NET 类型DataTable？我希望它可以将其转换为 TSQLdatetime或date类型。同上decimal而不是SqlDecimal.
