SQL Bulkcopy YYYYMMDD问题 [英] SQL Bulkcopy YYYYMMDD problem
问题描述
我在asp.net 3.5使用SQL Bulkcopy一个字符串到日期的转换问题,C#
I have a String to Date conversion problem using SQL Bulkcopy in asp.net 3.5 with C#
我看了一个大的CSV文件(的 CSV读者)。一个读应该被加载到SQL Server 2008中日期列中的字符串。
I read a large CSV file (with CSV reader). One of the strings read should be loaded into a SQL server 2008 Date column.
如果文本文件,例如包含字符串'2010-12-31',SQL Bulkcopy负荷。它没有任何问题到日期列
If the textfile contains for example the string '2010-12-31', SQL Bulkcopy loads it without any problems into the Date column.
不过,如果字符串为20101231,我得到一个错误:结果
的从数据源String类型的给定值不能转换为键入指定的目标列日
However, if the string is '20101231', I get an error:
The given value of type String from the data source cannot be converted to type date of the specified target column
该文件含有80万条记录,所以我不能创建数据表....
The file contains 80 million records so I cannot create a datatable....
SqlBulkCopy的Columnmappings等方面都ok了。也改变为DateTime于事无补。
SqlBulkcopy Columnmappings etc. are all ok. Also changing to DateTime does not help.
我试过
SET DATEFORMAT ymd;
但是,这并不能帮助。
But that does not help.
任何想法如何告诉SQL Server接受这种格式?否则,我将创建CSV读者自定义修复,但我宁愿在SQL的东西。
Any ideas how to tell SQL Server to accept this format? Otherwise I will create a custom fix in CSV reader but I would prefer something in SQL.
更新
上的两个跟进答案,我使用这样的SQL bulkcopy(在另一个问题提出#2):
update Following up on the two answers, I am using SQL bulkcopy like this (as proposed on Stackoverflow in another question):
CSV阅读(见上在CodeProject上的链接)返回字符串值(不强类型)。该CSVreader实现System.Data.IDataReader,所以我可以做这样的事情:
The CSV reader (see the link above on codeproject) returns string values (not strong typed). The CSVreader implements System.Data.IDataReader so I can do something like this:
using (CsvReader reader = new CsvReader(path))
using (SqlBulkCopy bcp = new SqlBulkCopy(CONNECTION_STRING))
{ bcp.DestinationTableName = "SomeTable";
// columnmappings
bcp.WriteToServer(reader); }
从IDataReader的来的所有字段都是字符串,所以,除非我改变,我不能使用C#方法在CSVreader颇有几分
All the fields coming from the iDataReader are strings, so I cannot use the c# approach unless I change quite a bit in the CSVreader
我的问题,因此没有就如何解决它在C#中的关系,我能做到这一点,但我想,以防止这一点。
My question is therefore not related on how to fix it in C#, I can do that but i want to prevent that.
这是奇怪的,因为如果你在SQL像
It is strange, because if you do a in sql something like
update set [somedatefield] = '20101231'
它也可以,只是不能与bulkcopy。
it also works, just not with bulkcopy.
任何想法,为什么?
谢谢,
Pleun
Thanks for any advice, Pleun
推荐答案
如果你可以在C#本身韩德尔它那么这段代码将有助于让字符串作为一个DateTime对象中,你可以通过直接
If you can handel it in C# itself then this code will help get the date in the string as a DateTime object which you can pass directly
//datestring is the string read from CSV
DateTime thedate = DateTime.ParseExact(dateString, "yyyyMMdd", null);
如果你想让它格式化为字符串,然后:
If you want it to be formatted as string then:
string thedate = DateTime.ParseExact(dateString, "yyyyMMdd", null).ToString("yyyy-MM-dd");
祝你好运。
Good luck.
更新
在您的情况我不知道为什么日期不自动格式化,但是从C#,你需要获得和传递的过程中干扰数据到在WriteToServer()
方法。最好的,我认为你可以做(牢记性能)是拥有的DataRow的项目缓存,并将其传递给的在WriteToServer()方法。我只是写的代码示例在一分钟...
In your scenario i don't know why date is not automatically formatted but from C# you need to get in and Interfere in the process of passing the data to the WriteToServer()
method. Best i think you can do (keeping in mind the Performance) is to have a cache of DataRow items and Pass them to the WriteToServer() method. I will just write the sample code in a minute...
//A sample code.. polish it before implementation
//A counter to track num of records read
long records_read = 0;
While(reader.Read())
{
//We will take rows in a Buffer of 50 records
int i = records_read;//initialize it with the num of records last read
DataRow[] buffered_rows = new DataRow[50];
for(;i<50 ;i++)
{
//Code to initialize each rows with the data in the reader
//.....
//Fill the column data with Date properly formatted
records_read++;
reader.Read();
}
bcp.WriteToServer(buffered_rows);
}
它不是完整的代码,但我认为你可以解决它...
Its not full code but i think you can work it out...
这篇关于SQL Bulkcopy YYYYMMDD问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!