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!
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.
SqlDateTime
represents the original datetime
type. Have you tried just using the DateTime
.NET type in the DataTable
? I would hope that it can convert this to either the TSQL datetime
or date
types. Ditto decimal
instead of SqlDecimal
.