****************************************************************************sql sentence in mysql
*********************************************************************LOADDATALOCALINFILE'C:\\Users\\1382919\\Desktop\\1.csv'INTOTABLE terimal
CHARACTERSET utf8
FIELDSTERMINATEDBY','ENCLOSEDBY''LINESTERMINATEDBY'\r\n'IGNORE3LINESUPDATE terimal SET terimal.Test_Date = STR_TO_DATE(terimal.Test_Date,'%m/%d/%Y %H:%i')WHERE1=1
****************************************************************************THIS SQL SENTENCE HAS SOME ERROR OF FAIL PATH IN C#
*******SO WE UES MySqlBulkLoader FOR CSV TO DATABASE
*********************************************************************//----------------------SQL--------------------------//
string sql ="LOAD DATA LOCAL INFILE'"+ file +"'";
sql +="INTO TABLE terimal CHARACTER SET utf8 FIELDS TERMINATED BY ',' ";
sql +="ENCLOSED BY ''";
sql +="LINES TERMINATED BY '\r\n' IGNORE 3 LINES";//---------------------- MySqlBulkLoader--------------------//
using System.IO
string file = fi.FullName;
FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs);
string csv = sr.ReadToEnd();int insertCount =0;
sr.Close();
string tmpPath = Path.GetTempFileName();
File.WriteAllText(tmpPath, csv);
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ictnpf"].ConnectionString)){
conn.Open();
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)//数据批量导入:datatable转CSV->导入库{
FieldTerminator =",",
FieldQuotationCharacter ='"',
EscapeCharacter ='"',
LineTerminator ="\r\n",//行结束标志
FileName = tmpPath,
NumberOfLinesToSkip =3,//前几行不读取
TableName ="terimal",};
insertCount = bulk.Load();}}
catch (Exception e){
MessageBox.Show(e.Message);return false;}
File.Delete(tmpPath);return true;