c# - SqlBulkCopy Converting string to datetime with US Format -

c# - SqlBulkCopy Converting string to datetime with US Format -

i'm transferring date unknown source (could csv/sql server) application database, don't know info coming first import info holding table (all varchar columns) test value in column matches should , import database or flag info error

to transfer info load info datatable in c# utilize sqlbulkcopy insert info holding table.this working i'm getting issue datetime columns. in c# datatable date value in ymd format , stored string, column in holing table varchar(255) @ point transfer process date beingness converted dmy format causing issue when seek validate dates in holding table.

to load datatable do:

cmd.commandtext = string.format("set dateformat ymd; {0}", cmd.commandtext); sqldataadapter dataadapter = new sqldataadapter(cmd); datatable returntable = new datatable(); dataadapter.fill(returntable);

to transfer info do:

using (sqlbulkcopy bulkcopy = new sqlbulkcopy(dbcontext.connectionstring, sqlbulkcopyoptions.keepnulls)) { bulkcopy.destinationtablename = "holdingtable"; bulkcopy.batchsize = 10000; foreach (impmap im in importmapping.where(i => i.isimported)) { bulkcopy.columnmappings.add(im.mappingcolumn, im.tablefield.fieldname); } foreach (sqlbulkcopycolumnmapping m in columnmappings) { bulkcopy.columnmappings.add(m); } bulkcopy.writetoserver(datadatatable); }

c# sql-server datetime datatable