public void CopyTable(ADOX.Table tblAccess) { ADODB.Recordset recMaster = new ADODB.Recordset(); ADODB.Recordset recLoop = new ADODB.Recordset(); int intLoop = 0; string strInfile = ""; string strSQL = "SELECT "; string strRecord; string strLoadFilePath = strSourceDbPath.Replace("\\", "\\\\"); string strFileName = strTempPath + tblAccess.Name + ".txt"; StreamWriter sw = new StreamWriter(strFileName, false); //create the infile strInfile += "LOAD DATA LOCAL INFILE '" + strFileName + "' INTO TABLE " + strMySQLDBName + "." + tblAccess.Name + " "; strInfile += "FIELDS TERMINATED BY ',' "; strInfile += "ESCAPED BY '\\\\' "; strInfile += "LINES TERMINATED BY 0x0d0a "; strInfile += "("; //loop through fields to enumerate them for the infile and build a select statement for (intLoop = 0; intLoop < tblAccess.Columns.Count; intLoop++) { strInfile += MySQLName((tblAccess.Columns[intLoop].Name)); switch (tblAccess.Columns[intLoop].Type) { case ADOX.DataTypeEnum.adDate: //convert to MySQL datetime format strSQL += "FORMAT([" + tblAccess.Columns[intLoop].Name + "], 'YYYY-MM-DD HH:MM:SS') as " + tblAccess.Columns[intLoop].Name; break; default: strSQL += "[" + tblAccess.Columns[intLoop].Name + "]"; break; } if (intLoop < tblAccess.Columns.Count - 1) { strSQL += ","; strInfile += ", "; } } strInfile += ");"; strSQL += " FROM [" + tblAccess.Name + "]"; //open the "Master" recordset recMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient; recMaster.Open(strSQL, conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); //create the "Loop" recordset, this is a clone of the master, with the exception //that the definedsize for text fields is lengthened. This is because the added //escape characters could potentially exceed the field length in the master recordset recLoop.CursorLocation = ADODB.CursorLocationEnum.adUseClient; ADODB.Fields fdsLoop = recLoop.Fields; ADODB.Fields fdsMaster = recMaster.Fields; foreach (ADODB.Field fldIn in fdsMaster) { if (fldIn.Type.ToString().IndexOf("Char") > 0) { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize + 30, ADODB.FieldAttributeEnum.adFldIsNullable, null); } else { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize, ADODB.FieldAttributeEnum.adFldIsNullable, null); } } recLoop.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); recLoop.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value); while (!recMaster.EOF) { for (int columnIndex = 0; columnIndex < recMaster.Fields.Count; columnIndex++) { recLoop.Fields[columnIndex].Value = recMaster.Fields[columnIndex].Value; if (recLoop.Fields[columnIndex].Value.ToString().Length > 0) { if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf("\\", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace("\\", "\\\\"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(",", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(",", "\\,"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(System.Environment.NewLine, 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(System.Environment.NewLine, " "); } } } strRecord = recLoop.GetString(ADODB.StringFormatEnum.adClipString, 1, ",", System.Environment.NewLine, "\\N"); recLoop.MovePrevious(); sw.Write(strRecord); recMaster.MoveNext(); } recMaster.Close(); recMaster.ActiveConnection = null; try { recLoop.Close(); } catch { } sw.Close(); ExecuteSQL(strInfile); File.Delete(strFileName); recLoop = null; }