/// <summary>
/// find reference of function name in dbf
/// </summary>
/// <param name="path"></param>
/// <param name="functionName"></param>
/// <returns></returns>
private static int FunctionRefExistsInDBF(string path, string functionName)
{
var skipDeleted = true;
//DbfDataReader.DbfDataReader reader = new DbfDataReader.DbfDataReader(kvp.Key);
using (var dbfTable = new DbfTable(path, System.Text.Encoding.UTF8))
{
try
{
var dbfRecord = new DbfRecord(dbfTable);
var found = 0;
while (dbfTable.Read(dbfRecord))
{
if (skipDeleted && dbfRecord.IsDeleted)
{
continue;
}
if (dbfRecord.Values.Any(s => s.GetValue().ToString().Contains(functionName)))
{
found++;
break;
}
}
return(found);
}
catch (Exception ex)
{
log.Error("error:", ex);
return(0);
}
}
}
private static void PrintCsv(Options options)
{
var encoding = GetEncoding();
using (var dbfTable = new DbfTable(options.Filename, encoding))
{
var columnNames = string.Join(",", dbfTable.Columns.Select(c => c.ColumnName));
if (!options.SkipDeleted)
{
columnNames += ",Deleted";
}
Console.WriteLine(columnNames);
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
if (options.SkipDeleted && dbfRecord.IsDeleted)
{
continue;
}
var values = string.Join(",", dbfRecord.Values.Select(v => EscapeValue(v)));
if (!options.SkipDeleted)
{
values += $",{dbfRecord.IsDeleted}";
}
Console.WriteLine(values);
}
}
}
private List <fakeDB> GetFakeDBs()
{
var fakeDBs = new List <fakeDB>();
var dbfPath = "../../SvgFile/Seoul.dbf";
List <int> list = new List <int>();
for (i = 0; i < 500; i++)
{
list.Add(i);
}
using (var dbf = new DbfTable(dbfPath, DbfDataReader.EncodingProvider.UTF8))
{
var dbfRecord = new DbfRecord(dbf);
i = 0;
while (dbf.Read(dbfRecord))//480번 반복
{
fakeDB fakeDB = new fakeDB();
fakeDB.emd_cd = int.Parse(dbfRecord.Values[0].ToString());
fakeDB.eng = dbfRecord.Values[1].ToString();
fakeDB.val1 = int.Parse(dbfRecord.Values[3].ToString());
//fakeDB.val1 = list[i++];
fakeDB.val2 = int.Parse(dbfRecord.Values[4].ToString());
fakeDB.val3 = int.Parse(dbfRecord.Values[5].ToString());
fakeDBs.Add(fakeDB);
}
}
return(fakeDBs);
}
/// <summary>
/// get project pages (from pages.dbf)
/// </summary>
/// <param name="path"></param>
public void GetProjectPages(string path, bool retrievePageANs = true)
{
using (var dbfTable = new DbfTable(path, System.Text.Encoding.UTF8))
{
var skipDeleted = true;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
if (skipDeleted && dbfRecord.IsDeleted)
{
continue;
}
CitectPages.Add(new CitectPages {
PageName = dbfRecord.Values[0].ToString(), ObjectANs = new List <int>()
});
}
}
var subdir = path.Substring(0, path.LastIndexOf('\\'));
if (retrievePageANs)
{
GetPageAns(subdir + @"\pgdynobj.dbf");
}
}
/// <summary>
/// open pgdynobject.dbf to get ANs
/// </summary>
/// <param name="path"></param>
private void GetPageAns(string path)
{
using (var dbfTable = new DbfTable(path, System.Text.Encoding.UTF8))
{
var skipDeleted = true;
var dbfRecord = new DbfRecord(dbfTable);
var cols = dbfTable.Columns.ToList();
var ANColIdx = 0;
if (cols.FirstOrDefault(s => s.Name.Contains("AN")) != null)
{
ANColIdx = cols.FirstOrDefault(s => s.Name.Contains("AN")).Index;
}
while (dbfTable.Read(dbfRecord))
{
if (skipDeleted && dbfRecord.IsDeleted)
{
continue;
}
//get citectpage object from list
var citectPage = CitectPages.Find(s => s.PageName.Equals(dbfRecord.GetValue(0).ToString()));
citectPage.ObjectANs.Add(int.Parse(dbfRecord.GetValue(ANColIdx).ToString()));
}
}
}
public ICollection <Location> GetAllFromDbf()
{
var locals = new List <Location>();
var coordinateRecords = new List <Tuple <int, int> >
{
new Tuple <int, int>(2, 3),
new Tuple <int, int>(57, 58),
new Tuple <int, int>(62, 63),
new Tuple <int, int>(71, 72),
new Tuple <int, int>(84, 85),
new Tuple <int, int>(89, 90),
new Tuple <int, int>(92, 93),
new Tuple <int, int>(96, 97),
new Tuple <int, int>(100, 101)
};
using var table = new DbfTable("Data/OD_2017.dbf", EncodingProvider.UTF8);
var record = new DbfRecord(table);
var personId = 1;
while (table.Read(record))
{
foreach (var coordinate in coordinateRecords)
{
try
{
if (record.Values[coordinate.Item1].GetValue() != null &&
record.Values[coordinate.Item2].GetValue() != null)
{
var x = (int)record.Values[coordinate.Item1].GetValue();
var y = (int)record.Values[coordinate.Item2].GetValue();
AddCoordinate(personId, x, y, locals);
}
}
catch
{
}
}
personId++;
}
return(locals);
}
public async Task <List <ExpandoObject> > ReadFoxTb(string file, Encoding encoding)
{
var tbdata = new List <ExpandoObject>();
var skipDeleted = true;
using (var dbfTable = new DbfTable(file, encoding))
{
var columns = dbfTable.Columns;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
if (skipDeleted && dbfRecord.IsDeleted)
{
continue;
}
var obj = new ExpandoObject();
foreach (var col in columns)
{
var val = dbfRecord.Values[col.Index].GetValue();
if (val == null)
{
((IDictionary <string, object>)obj).Add(col.Name, null);
}
else if (col.ColumnType == DbfColumnType.Character)
{
((IDictionary <string, object>)obj).Add(col.Name, (val ?? "").ToString().Trim());
}
else if (col.ColumnType == DbfColumnType.SignedLong)
{
((IDictionary <string, object>)obj).Add(col.Name, Convert.ToInt32(val)); // (int)(long)
}
else
{
((IDictionary <string, object>)obj).Add(col.Name, val);
}
}
tbdata.Add(obj);
}
dbfTable.Close();
}
return(tbdata);
}
/// <summary>
/// overloaded
/// </summary>
/// <param name="dbfTable"></param>
/// <param name="functionName"></param>
/// <returns></returns>
private static int FunctionRefExistsInDBF(DbfTable dbfTable, string functionName)
{
var dbfRecord = new DbfRecord(dbfTable);
var found = 0;
var skipDeleted = true;
while (dbfTable.Read(dbfRecord))
{
if (skipDeleted && dbfRecord.IsDeleted)
{
continue;
}
if (dbfRecord.Values.Any(s => s.GetValue().ToString().Contains(functionName)))
{
found++;
}
}
return(found);
}
private static void PrintCsv(Options options)
{
List <string> lines = new List <string>();
var encoding = GetEncoding();
using (var dbfTable = new DbfTable(options.Filename, encoding))
{
var columnNames = string.Join(",", dbfTable.Columns.Select(c => c.ColumnName));
if (!options.SkipDeleted)
{
columnNames += ",Deleted";
}
lines.Add(columnNames);
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
if (options.SkipDeleted && dbfRecord.IsDeleted)
{
continue;
}
var values = string.Join(",", dbfRecord.Values.Select(v => EscapeValue(v)));
if (!options.SkipDeleted)
{
values += $",{dbfRecord.IsDeleted}";
}
lines.Add(values);
}
}
WriteAllLines(options, lines.ToArray());
SetOutputFileTimestampToInputFile(options.Filename);
}
public async Task <List <TemplateFileListsDTO> > UploadAsync(IFileListEntry fileEntry, bool?FirstLineIsHeader)
{
var Templates = new List <TemplateFileListsDTO>();
string _tempheader = "", _tempvalue = "", path = "";
int _temprow = 0;
try
{
var g = Path.GetExtension(fileEntry.Name);
#region ReadExcel
if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
{
using (var stream = new MemoryStream())
{
await fileEntry.Data.CopyToAsync(stream);
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
//var result = reader.AsDataSet();
do
{
_temprow = 1;
while (reader.Read() && _temprow <= 2) //Each ROW
{
if (_temprow == 1)
{
for (int column = 0; column < reader.FieldCount; column++)
{
if (FirstLineIsHeader == true)
{
if (reader.GetValue(column) != null)
{
_tempheader = reader.GetValue(column).ToString().Trim();
}
else
{
_tempheader = "";
}
}
else
{
_tempheader = "Column" + (column + 1).ToString();
if (reader.GetValue(column) != null)
{
_tempvalue = reader.GetValue(column).ToString();
}
else
{
_tempvalue = "";
}
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int column = 0; column < reader.FieldCount; column++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (reader.GetValue(column) != null)
{
var fieldtype = reader.GetFieldType(column).ToString();
string temp_value = "";
if (fieldtype == "System.DateTime")
{
temp_value = reader.GetDateTime(column).ToString();
}
else
{
temp_value = reader.GetValue(column).ToString();
}
template.tmp_value = temp_value;
//var testvalue = reader.GetValue(column);
}
}
}
_temprow++;
}
} while (reader.NextResult());
}
}
}
#endregion
#region Readcsv
else if (Path.GetExtension(fileEntry.Name) == ".csv")
{
using (var stream = new MemoryStream())
{
await fileEntry.Data.CopyToAsync(stream);
var reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1252),
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' },
LeaveOpen = false,
AnalyzeInitialCsvRows = 0,
});
var result = reader.AsDataSet();
do
{
_temprow = 1;
while (reader.Read() && _temprow <= 2) //Each ROW
{
if (_temprow == 1)
{
for (int column = 0; column < reader.FieldCount; column++)
{
if (FirstLineIsHeader == true)
{
if (reader.GetValue(column) != null)
{
_tempheader = reader.GetValue(column).ToString().Trim();
}
else
{
_tempheader = "";
}
}
else
{
_tempheader = "Column" + (column + 1).ToString();
if (reader.GetValue(column) != null)
{
_tempvalue = reader.GetValue(column).ToString();
}
else
{
_tempvalue = "";
}
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int column = 0; column < reader.FieldCount; column++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (reader.GetValue(column) != null)
{
template.tmp_value = reader.GetValue(column).ToString();
var testvalue = reader.GetValue(column);
}
}
}
_temprow++;
}
} while (reader.NextResult());
}
}
#endregion
#region ReadText
else if (Path.GetExtension(fileEntry.Name) == ".txt")
{
string tempFilename = Guid.NewGuid().ToString() + ".txt";
path = Path.Combine(_environment.ContentRootPath, "TempFile", tempFilename);
var ms = new MemoryStream();
await fileEntry.Data.CopyToAsync(ms);
using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(file);
}
DataTable dt = new DataTable();
using (TextReader tr = File.OpenText("TempFile/" + tempFilename))
{
string line;
_temprow = 1;
while ((line = tr.ReadLine()) != null && _temprow <= 2)
{
string[] items = line.Split('\t');
if (_temprow == 1)
{
for (int i = 0; i < items.Length; i++)
{
if (FirstLineIsHeader == true)
{
_tempheader = items[i].ToString();
}
else
{
_tempheader = "Column" + (i + 1).ToString();
_tempvalue = items[i].ToString();
}
Templates.Add(new TemplateFileListsDTO
{
tmp_no = i,
tmp_header = _tempheader,
tmp_value = _tempvalue
}
);
}
}
else if (_temprow == 2 && FirstLineIsHeader == true)
{
for (int i = 0; i < items.Length; i++)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == i).FirstOrDefault();
template.tmp_value = items[i].ToString();
}
}
_temprow++;
}
}
File.Delete(path);
}
#endregion
else
{
string tempFilename = Guid.NewGuid().ToString() + ".dbf";
path = Path.Combine(_environment.ContentRootPath, "TempFile", tempFilename);
bool exists = System.IO.Directory.Exists(Path.Combine(_environment.ContentRootPath, "TempFile"));
if (!exists)
{
System.IO.Directory.CreateDirectory(Path.Combine(_environment.ContentRootPath, "TempFile"));
}
using (FileStream file = new FileStream(path, FileMode.Create))
{
try{
await fileEntry.Data.CopyToAsync(file);
}
catch (Exception ex)
{
}
finally {
file.Flush();
}
}
//using (var dbfTable = new DbfTable("TempFile/" + tempFilename, Encoding.GetEncoding(1252)))
using (var dbfTable = new DbfTable("TempFile/" + tempFilename, Encoding.GetEncoding(874)))
{
var header = dbfTable.Header;
var recordCount = header.RecordCount;
var column = 1;
if (FirstLineIsHeader == true)
{
foreach (var dbfColumn in dbfTable.Columns)
{
//var name = dbfColumn.Name;
//var columnType = dbfColumn.ColumnType;
//var length = dbfColumn.Length;
//var decimalCount = dbfColumn.DecimalCount;
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = dbfColumn.Name
}
);
column++;
}
}
else
{
foreach (var dbfColumn in dbfTable.Columns)
{
Templates.Add(new TemplateFileListsDTO
{
tmp_no = column,
tmp_header = "Column" + (column).ToString()
}
);
column++;
}
}
column = 1;
_temprow = 1;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord) && _temprow < 2)
{
foreach (var dbfValue in dbfRecord.Values)
{
TemplateFileListsDTO template = Templates.Where(t => t.tmp_no == column).FirstOrDefault();
if (dbfValue != null)
{
template.tmp_value = dbfValue.ToString();
}
//var stringValue = dbfValue.ToString();
//var obj = dbfValue.GetValue();
column++;
}
_temprow++;
}
}
File.Delete(path);
}
}
catch (Exception ex)
{
return(Templates);
}
return(Templates);
}
private Task <object> ConvertData(string yesno)
{
MySqlConnection con = null;
string connectstring = "";
int Lines = 20;
Sql:
if (yesno != "Y" && yesno != "N")
{
goto Sql;
}
else
{
if (yesno == "Y")
{
this.Invoke((MethodInvoker) delegate
{
Console.Write("SQL Host: ");
string host = textBox1.Text;
Console.Write("SQL Port: ");
string port = textBox2.Text;
Console.Write("SQL Username: "******"SQL Password: "******"SQL database: ");
string db = textBox6.Text;
connectstring = $"server={host};port={port};uid={userid};pwd={password};database={db}";
File.WriteAllText("sqldata.csv", textBox1.Text + "," + textBox2.Text + "," + textBox3.Text + "," + textBox5.Text + "," + textBox6.Text);
con = new MySqlConnection(connectstring);
});
}
this.Invoke((MethodInvoker) delegate
{
Lines = Convert.ToInt32(numericUpDown1.Value);
});
}
var files = Directory.GetFiles(Path.Combine(Environment.CurrentDirectory, "DBF"), "*.dbf");
if (!Directory.Exists(Path.Combine(Environment.CurrentDirectory, "SQL")))
{
Directory.CreateDirectory(Path.Combine(Environment.CurrentDirectory, "SQL"));
}
this.Invoke((MethodInvoker) delegate
{
progressBar1.Maximum = files.Length;
});
int filenum = 0;
foreach (var file in files)
{
Encoding dbfencode = Encoding.Default;
this.Invoke((MethodInvoker) delegate
{
progressBar1.Value++;
try
{
dbfencode = Encoding.GetEncoding(comboBox1.Text.Substring(0, comboBox1.Text.IndexOf('|')).Replace("\t", ""));
}
catch
{
}
});
bool ContainsMemo = false;
if (dbfencode == null)
{
dbfencode = Encoding.Default;
}
long recordCount;
using (var dbfTable = new DbfTable(file, dbfencode))
{
var header = dbfTable.Header;
var versionDescription = header.VersionDescription;
recordCount = header.RecordCount;
Console.WriteLine("Version: " + versionDescription + "\nRecords found:" + recordCount + "\nEncoding: " + dbfencode.BodyName);
var dbfRecord = new DbfRecord(dbfTable);
List <DbfColumnType> types = new List <DbfColumnType>();
List <string> field = new List <string>();
StringBuilder sb = new StringBuilder();
string tablename = file.Remove(0, file.LastIndexOf('\\') + 1).Replace(".dbf", "").Replace(".DBF", "");
sb.Append("CREATE TABLE " + tablename + " (\nid int NOT NULL AUTO_INCREMENT,\n");
foreach (var dbfColumn in dbfTable.Columns)
{
if (dbfColumn.ColumnType == DbfColumnType.Memo)
{
ContainsMemo = true;
if (!File.Exists(dbfTable.MemoPath()))
{
Console.WriteLine("Warning! No memo file found!");
ContainsMemo = false;
}
}
if (!dbfColumn.Name.Contains("_NullFlag"))
{
sb.Append(dbfColumn.Name.ToLower() + " ");
switch (dbfColumn.ColumnType)
{
case DbfColumnType.Boolean:
sb.Append("tinytext,");
break;
case DbfColumnType.Number:
if (dbfColumn.DecimalCount > 0)
{
sb.Append("float,");
}
else
{
sb.Append("int,");
}
break;
case DbfColumnType.Float:
case DbfColumnType.Double:
sb.Append("double,");
break;
case DbfColumnType.SignedLong:
sb.Append("bigint,");
break;
case DbfColumnType.DateTime:
sb.Append("timestamp NULL,");
break;
case DbfColumnType.Date:
sb.Append("date NULL,");
break;
default:
sb.Append("varchar(200),");
break;
}
types.Add(dbfColumn.ColumnType);
field.Add(dbfColumn.Name);
}
}
sb.Append("PRIMARY KEY(ID));\n");
if (!string.IsNullOrEmpty(connectstring))
{
try
{
var sql = sb.ToString().Replace(",)", ")").Replace(",\n;", ";\n");
var cmd = new MySqlCommand(sql, con);
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open();
}
var num = cmd.ExecuteNonQuery();
cmd.Connection.Close();
Console.WriteLine("Completed! Affected rows " + num);
}
catch (Exception ex)
{
if (!ex.Message.Contains("exist"))
{
Console.WriteLine(ex.ToString());
}
}
}
else
{
File.WriteAllText(file.Replace(".dbf", ".sql").Replace(".DBF", ".sql").Replace("\\DBF", "\\SQL"), sb.ToString(), Encoding.Unicode);
}
sb.Clear();
try
{
int rownum = 0;
while (dbfTable.Read(dbfRecord))
{
if (!ImportDeleted && dbfRecord.IsDeleted)
{
Console.WriteLine("Skipping deleted records.... Left " + recordCount + " rows to processed");
recordCount--;
continue;
}
try
{
if (rownum % Lines == 0)
{
recordCount -= rownum;
if (!string.IsNullOrEmpty(connectstring))
{
var sql = sb.ToString().Replace(",)", ")").Replace(",\n;", ";\n");
if (!string.IsNullOrEmpty(sql))
{
var cmd = new MySqlCommand(sql, con);
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open();
}
try
{
var num = cmd.ExecuteNonQuery();
Console.WriteLine("Completed! Affected rows " + num + ". Left " + recordCount + " rows to processed");
}
catch
{
Console.WriteLine("One row failed to execute. Writting sql into file...");
File.AppendAllText("error.sql", sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
}
cmd.Connection.Close();
}
sb.Clear();
}
else
{
if (sb.Length > 48064)
{
File.AppendAllText(file.Replace(".dbf", ".sql").Replace(".DBF", ".sql").Replace("\\DBF", "\\SQL"), sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
sb.Clear();
}
}
sb.Append("INSERT INTO " + tablename + " (");
foreach (var f in field)
{
if (f != field[field.Count - 1])
{
sb.Append(f + ",");
}
else
{
sb.Append(f);
}
}
sb.Append(") VALUES ");
rownum = 0;
}
sb.Append("(");
for (int x = 0; x < types.Count; x++)
{
try
{
if (dbfRecord.Values[x].ToString().Length > 0)
{
if (types[x] == DbfColumnType.DateTime)
{
if (DateTime.TryParse(dbfRecord.Values[x].ToString(), out DateTime output))
{
var stringValue = output.ToString("yyyy-MM-dd HH:mm:ss");
sb.Append("\"" + stringValue.Replace("\"", "'").Replace("\\", "/") + "\",");
}
else
{
var stringValue = dbfRecord.Values[x].ToString();
sb.Append("\"" + stringValue.Replace("\"", "'").Replace("\\", "/") + "\",");
}
}
else if (types[x] == DbfColumnType.Date)
{
var result = Convert.ToDateTime(dbfRecord.Values[x].ToString());
sb.Append("\"" + result.ToString("yyyy-MM-dd") + "\",");
}
else if (types[x] == DbfColumnType.Memo)
{
if (!ContainsMemo)
{
sb.Append("\"null\",");
}
else
{
var stringValue = dbfRecord.Values[x].ToString();
sb.Append("\"" + stringValue.Replace("\"", "'").Replace("\\", "/") + "\",");
}
}
else
{
var stringValue = dbfRecord.Values[x].ToString();
sb.Append("\"" + stringValue.Replace("\"", "'").Replace("\\", "/") + "\",");
}
}
else
{
sb.Append("null,");
}
}
catch
{
sb.Append("null,");
}
}
rownum++;
sb.Append(")");
if (rownum % Lines == 0)
{
sb.Append(";");
}
else
{
sb.Append(",");
}
sb.Append("\n");
}
catch
{
}
}
sb.Length -= 2;
sb.Append(";");
if (!string.IsNullOrEmpty(connectstring))
{
var sql = sb.ToString().Replace(",)", ")").Replace(",\n;", ";\n");
if (!string.IsNullOrEmpty(sql))
{
var cmd = new MySqlCommand(sql, con);
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open();
}
try
{
var num = cmd.ExecuteNonQuery();
Console.WriteLine("Completed! Affected rows " + num);
}
catch
{
Console.WriteLine("One row failed to execute. Writting sql into file...");
File.AppendAllText("error.sql", sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
}
cmd.Connection.Close();
}
sb.Clear();
}
else
{
File.AppendAllText(file.Replace(".dbf", ".sql").Replace(".DBF", ".sql").Replace("\\DBF", "\\SQL"), sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
sb.Clear();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
if (!string.IsNullOrEmpty(connectstring))
{
try
{
var sql = sb.ToString().Replace(",)", ")").Replace(",\n;", ";\n");
if (!string.IsNullOrEmpty(sql))
{
var cmd = new MySqlCommand(sql, con);
if (cmd.Connection.State != System.Data.ConnectionState.Open)
{
cmd.Connection.Open();
}
try
{
var num = cmd.ExecuteNonQuery();
Console.WriteLine("Completed! Affected rows " + num);
}
catch
{
Console.WriteLine("One row failed to execute. Writting sql into file...");
File.AppendAllText("error.sql", sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
}
cmd.Connection.Close();
}
sb.Clear();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.WriteLine("One row failed to execute. Writting sql into file...");
File.AppendAllText("error.sql", sb.ToString().Replace(",)", ")").Replace(",;", ";"), Encoding.Unicode);
}
}
else
{
File.AppendAllText(file.Replace(".dbf", ".sql").Replace(".DBF", ".sql"), sb.ToString().Replace(",)", ")"));
}
filenum++;
}
}
return(null);
}
//void loaddata(String path,String selectText)
//{
// string cnstr = @"Provider=VFPOLEDB.1;Data Source=" + path + ";Collating Sequence=MACHINE;Mode=Read";
// //string cnstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "; Extended Properties=dBASE IV;");
// // OleDbConnection oleDbCon = new OleDbConnection(cnstr);
// string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + " ;Extended Properties=dBASE IV;";
// OleDbConnection oleDbCon = new OleDbConnection(connectionString);
// oleDbCon.Open();
// table = new DataTable();
// String SQLstr = "";
// SQLstr = "Select MCUST.CUST_ID as CUST_ID,MCUST.CUST_NO as \"Customer Code\", MCUST.CUST_NAME as \"Company name\", MCUST.TYPE as Type, MCUST.CONTACT as \"Contact person\",";
// SQLstr = SQLstr + "MCUST.TEL as TEL, MCUST.FAX as FAX, MCUST.INPDATE as \"Create Date\",";
// SQLstr = SQLstr + "MCUST.INPUSER as \"Create Date By\",MCUST.UPDDATE as \"Last Update\",";
// SQLstr = SQLstr + "MCUST.UPDUSER as \"Last Update By\",MCUST.NAMETREE as \"Name Tree\",MCUST.JOBTITLE as \"Job Tile\",";
// SQLstr = SQLstr + "MCUST_D.TELEX as \"Telex Number\", MCUST_D.CABLE as Mobile,";
// SQLstr = SQLstr + "LTRIM(MCUST_D.ADD1) as Address1, MCUST_D.ADD2 as Address2, MCUST_D.ADD3 as Address3, MCUST_D.ADD4 as Address4,";
// SQLstr = SQLstr + "MCUST_D.REMARKS as Remarks,MCUST_D.MARKS as MARKS,MCUST_D.TRADETERM as \"Trade Term\",MCUST_D.PAYTERM as \"Payment Term\",MCUST_D.CUR as CURRENCY,";
// SQLstr = SQLstr + "MCUST_D.EMAIL,MCUST_D.HOMEPAGE,MCUST_D.ALIAS,MCUST_D.PASSWD as \"PASSWORD\" ,MCUST_D.BK_NO as \"BROKER NO\",MCUST_D.CCASS_ID as \"CCASS ID\",";
// SQLstr = SQLstr + "MCUST_D.PHOTO as \"Picture File Name\",MCUST_D.ISWHATSAPP as WHATSAPP, MCUST_D.ISWECHAT as WECHAT ,MCUST_D.ISLINE as LINE ";
// SQLstr = SQLstr + "FROM MCUST.DBF INNER JOIN MCUST_D.DBF ON MCUST.CUST_ID = MCUST_D.CUST_ID ";
// OleDbCommand oleDbcommand = new OleDbCommand("SELECT * FROM MCUST.DBF", oleDbCon);
// table.Load(oleDbcommand.ExecuteReader());
// oleDbCon.Close();
// dataGridView1.AutoGenerateColumns = true;
// for (int i = 0; i <= table.Rows.Count - 1; i++)
// {
// DataRow dr = table.Rows[i];
// // check mobile
// var mobile = table.Rows[i]["mobile"].ToString().Trim().RemoveNonNumeric();
// var email = table.Rows[i]["email"].ToString().Trim();
// var chkemail = false;
// var chkmobile = false;
// if (chkMobileEmty.Checked)
// {
// if (StringExtensions.ValidatePhoneNumber(mobile, true) == false)
// {
// dr.Delete();
// }
// else
// {
// table.Rows[i]["mobile"] = mobile;
// }
// }
// if (chkEmailEmty.Checked)
// {
// if (String.IsNullOrEmpty(email))
// {
// dr.Delete();
// }
// }
// if (chkMobileEmty.Checked && chkEmailEmty.Checked)
// {
// if (String.IsNullOrEmpty(email.Trim()))
// {
// if (StringExtensions.ValidatePhoneNumber(mobile.Trim(), true) == false)
// {
// dr.Delete();
// }
// }
// }
// }
// dataGridView1.DataSource = table;
// listBox1.Items.Clear();
// listBox2.Items.Clear();
// comboBox1.Items.Clear();
// for (int k = 0; k <= dataGridView1.ColumnCount - 1; k++)
// {
// string s = dataGridView1.Columns[k].HeaderText;
// listBox1.Items.Add(s);
// comboBox1.Items.Add(s);
// }
//}
void load(string path)
{
// comboBox1.SelectedIndex = 0;
string target = @"c:\temp";
Console.WriteLine("The current directory is {0}", path);
DbfTable dbfTable = null;
var dbfPath = path + "\\mcust.dbf";
dbfTable = new DbfTable(dbfPath, Encoding.GetEncoding(950));
DataTable dt = new DataTable();
dt.Clear();
foreach (var dbfColumn in dbfTable.Columns)
{
var name = dbfColumn.Name;
var columnType = dbfColumn.ColumnType;
var length = dbfColumn.Length;
var decimalCount = dbfColumn.DecimalCount;
name = name.Replace("CUST_NO", "Customer Code");
name = name.Replace("CUST_NAME", "Company name");
name = name.Replace("TEL", "Phone Number");
name = name.Replace("FAX", "Fax Number");
name = name.Replace("CONTACT", "Contact person");
name = name.Replace("INPDATE", "Create Date");
name = name.Replace("INPUSER", "Create Date By");
name = name.Replace("UPDDATE", "Last Update");
name = name.Replace("NAMETREE", "Name Tree");
name = name.Replace("UPDUSER", "Last Update By");
name = name.Replace("JOBTITLE", "Job Tile");
dt.Columns.Add(name);
}
DataColumn[] keyColumn = new DataColumn[2];
keyColumn[0] = dt.Columns["CUST_ID"];
dt.PrimaryKey = keyColumn;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
DataRow _ravi = dt.NewRow();
int I = 0;
foreach (var dbfValue in dbfRecord.Values)
{
_ravi[I] = dbfValue.ToString().Trim();
I++;
}
dt.Rows.Add(_ravi);
}
// dt.Columns.Remove("CABLE");
// label1.Text = dt.Rows.Count.ToString();
dataGridView1.DataSource = dt;
dbfPath = path + "\\mcust_d.dbf";
var dbfTable2 = new DbfTable(dbfPath, Encoding.GetEncoding(950));
var mcust_d_table = new DataTable();
mcust_d_table.Clear();
foreach (var dbfColumn in dbfTable2.Columns)
{
var name = dbfColumn.Name;
var columnType = dbfColumn.ColumnType;
var length = dbfColumn.Length;
var decimalCount = dbfColumn.DecimalCount;
name = name.Replace("TELEX", "TELEX NUMBER");
name = name.Replace("CABLE", "MOBILE");
name = name.Replace("ADD1", "ADDRESS1");
name = name.Replace("ADD2", "ADDRESS2");
name = name.Replace("ADD3", "ADDRESS3");
name = name.Replace("ADD4", "ADDRESS4");
// name = name.Replace("REMARKS", "Remarks");
name = name.Replace("PASSWD", "PASSWORD");
name = name.Replace("PHOTO", "Picture File Name");
name = name.Replace("ISWHATSAPP", "WHATSAPP");
name = name.Replace("TRADETERM", "TRADE TERM");
name = name.Replace("PAYTERM", "PAYMENT TERM");
name = name.Replace("CUR", "CURRENCY");
name = name.Replace("BK_NO", "BROKER NO");
name = name.Replace("CCASS_ID", "CCASS ID");
name = name.Replace("ISWHATSAPP", "WHATSAPP");
name = name.Replace("ISWECHAT", "WECHAT");
name = name.Replace("ISLINE", "LINE");
mcust_d_table.Columns.Add(name);
}
var dbfRecord2 = new DbfRecord(dbfTable2);
DataColumn[] keyColumn2 = new DataColumn[2];
keyColumn2[0] = mcust_d_table.Columns["CUST_ID"];
mcust_d_table.PrimaryKey = keyColumn2;
while (dbfTable2.Read(dbfRecord2))
{
DataRow _ravi2 = mcust_d_table.NewRow();
int I = 0;
foreach (var dbfValue2 in dbfRecord2.Values)
{
string mobile = "";
string email = "";
if (I == 2)
{
mobile = dbfValue2.ToString().Trim();
if (StringExtensions.ValidatePhoneNumber(mobile, true) && mobile.Length == 8)
{
_ravi2[I] = mobile;
}
}
else if (I == 12)
{
email = dbfValue2.ToString().Trim();
if (ValidateEmail(email))
{
_ravi2[I] = email;
}
else
{
_ravi2[I] = "";
}
}
else
{
_ravi2[I] = dbfValue2.ToString();
}
I++;
}
{
mcust_d_table.Rows.Add(_ravi2);
}
}
dt.Merge(mcust_d_table);
dt.Columns.Remove("EMAILSRH");
dt.Columns.Remove("CABLESRH");
dt.Columns.Remove("_NullFlags");
// label3.Text = dt.Rows.Count.ToString();
table = dt;
dataGridView1.DataSource = dt;
// dataGridView3.Columns["CUST_ID"].Visible = false;
listBox1.Items.Clear();
listBox2.Items.Clear();
comboBox1.Items.Clear();
for (int k = 0; k <= dataGridView1.ColumnCount - 1; k++)
{
string s = dataGridView1.Columns[k].HeaderText;
listBox1.Items.Add(s);
comboBox1.Items.Add(s);
}
dbfTable.Close();
dbfTable2.Close();
comboBox1.SelectedIndex = 0;
}
//public static DataTable GetOdbcDbfDataTable(string Database, string OdbcString)
//{
// DataTable myDataTable = new DataTable();
// OdbcConnection icn = OdbcDbfOpenConn(Database);
// OdbcDataAdapter da = new OdbcDataAdapter(OdbcString, icn);
// DataSet ds = new DataSet();
// ds.Clear();
// da.Fill(ds);
// myDataTable = ds.Tables[0];
// if (icn.State == ConnectionState.Open) icn.Close();
// return myDataTable;
//}
void loaddata2(String path)
{
// comboBox1.SelectedIndex = 0;
string target = @"c:\temp";
var dbfPath = path + "\\mcust.dbf";
var dbfTable = new DbfTable(dbfPath, Encoding.GetEncoding(950));
DataTable dt = new DataTable();
dt.Clear();
foreach (var dbfColumn in dbfTable.Columns)
{
var name = dbfColumn.Name;
var columnType = dbfColumn.ColumnType;
var length = dbfColumn.Length;
var decimalCount = dbfColumn.DecimalCount;
name = name.Replace("CUST_NO", "Customer Code");
name = name.Replace("CUST_NAME", "Company name");
name = name.Replace("TEL", "Phone Number");
name = name.Replace("FAX", "Fax Number");
name = name.Replace("CONTACT", "Contact person");
name = name.Replace("INPDATE", "Create Date");
name = name.Replace("INPUSER", "Create Date By");
name = name.Replace("UPDDATE", "Last Update");
name = name.Replace("NAMETREE", "Name Tree");
name = name.Replace("UPDUSER", "Last Update By");
name = name.Replace("JOBTITLE", "Job Tile");
dt.Columns.Add(name);
}
DataColumn[] keyColumn = new DataColumn[2];
keyColumn[0] = dt.Columns["CUST_ID"];
dt.PrimaryKey = keyColumn;
var dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
DataRow _ravi = dt.NewRow();
int I = 0;
foreach (var dbfValue in dbfRecord.Values)
{
_ravi[I] = dbfValue.ToString();
I++;
}
dt.Rows.Add(_ravi);
}
// dataGridView1.DataSource = dt;
dbfPath = path + "\\mcust_d.dbf";
var dbfTable2 = new DbfTable(dbfPath, Encoding.GetEncoding(950));
var mcust_d_table = new DataTable();
mcust_d_table.Clear();
foreach (var dbfColumn in dbfTable2.Columns)
{
var name = dbfColumn.Name;
var columnType = dbfColumn.ColumnType;
var length = dbfColumn.Length;
var decimalCount = dbfColumn.DecimalCount;
//SQLstr = SQLstr + "MCUST_D.TELEX as \"Telex Number\", MCUST_D.CABLE as Mobile,";
//SQLstr = SQLstr + "LTRIM(MCUST_D.ADD1) as Address1, MCUST_D.ADD2 as Address2, MCUST_D.ADD3 as Address3, MCUST_D.ADD4 as Address4,";
//SQLstr = SQLstr + "MCUST_D.REMARKS as Remarks,MCUST_D.MARKS as MARKS,MCUST_D.TRADETERM as \"Trade Term\",MCUST_D.PAYTERM as \"Payment Term\",MCUST_D.CUR as CURRENCY,";
//SQLstr = SQLstr + "MCUST_D.EMAIL,MCUST_D.HOMEPAGE,MCUST_D.ALIAS,MCUST_D.PASSWD as \"PASSWORD\" ,MCUST_D.BK_NO as \"BROKER NO\",MCUST_D.CCASS_ID as \"CCASS ID\",";
//SQLstr = SQLstr + "MCUST_D.PHOTO as \"Picture File Name\",MCUST_D.ISWHATSAPP as WHATSAPP, MCUST_D.ISWECHAT as WECHAT ,MCUST_D.ISLINE as LINE ";
//SQLstr = SQLstr + "FROM MCUST.DBF INNER JOIN MCUST_D.DBF ON MCUST.CUST_ID = MCUST_D.CUST_ID ";
name = name.Replace("TELEX", "TELEX NUMBER");
name = name.Replace("CABLE", "MOBILE");
name = name.Replace("ADD1", "ADDRESS1");
name = name.Replace("ADD2", "ADDRESS2");
name = name.Replace("ADD3", "ADDRESS3");
name = name.Replace("ADD4", "ADDRESS4");
// name = name.Replace("REMARKS", "Remarks");
name = name.Replace("PASSWD", "PASSWORD");
name = name.Replace("PHOTO", "Picture File Name");
name = name.Replace("ISWHATSAPP", "WHATSAPP");
name = name.Replace("TRADETERM", "TRADE TERM");
name = name.Replace("PAYTERM", "PAYMENT TERM");
name = name.Replace("CUR", "CURRENCY");
name = name.Replace("BK_NO", "BROKER NO");
name = name.Replace("CCASS_ID", "CCASS ID");
name = name.Replace("ISWHATSAPP", "WHATSAPP");
name = name.Replace("ISWECHAT", "WECHAT");
name = name.Replace("ISLINE", "LINE");
mcust_d_table.Columns.Add(name);
}
var dbfRecord2 = new DbfRecord(dbfTable2);
DataColumn[] keyColumn2 = new DataColumn[2];
keyColumn2[0] = mcust_d_table.Columns["CUST_ID"];
mcust_d_table.PrimaryKey = keyColumn2;
while (dbfTable2.Read(dbfRecord2))
{
DataRow _ravi2 = mcust_d_table.NewRow();
int I = 0;
foreach (var dbfValue2 in dbfRecord2.Values)
{
_ravi2[I] = dbfValue2.ToString();
I++;
}
mcust_d_table.Rows.Add(_ravi2);
}
// dataGridView2.DataSource = mcust_d_table;
dt.Merge(mcust_d_table);
dt.Columns.Remove("_NullFlags");
table = dt;
dataGridView1.AutoGenerateColumns = true;
}
public override DataTable ToDataTable(DataTableConvertParams context, Stream stream)
{
DataTable dt = new DataTable();
DbfTable dbfTable = new DbfTable(stream, Encoding.UTF8);
foreach (DbfColumn col in dbfTable.Columns)
{
#region Resolve Column Type
Type colType = typeof(String);
switch (col.ColumnType)
{
case DbfColumnType.Double:
case DbfColumnType.Float:
case DbfColumnType.Currency:
colType = typeof(Double);
break;
case DbfColumnType.Date:
case DbfColumnType.DateTime:
colType = typeof(DateTime);
break;
case DbfColumnType.SignedLong:
colType = typeof(Int64);
break;
case DbfColumnType.Boolean:
colType = typeof(Boolean);
break;
case DbfColumnType.Number:
colType = typeof(Int32);
break;
case DbfColumnType.Character:
case DbfColumnType.General:
case DbfColumnType.Memo:
default:
colType = typeof(String);
break;
}
#endregion Resolve Column Type
dt.Columns.Add(col.Name, colType);
}
DbfRecord dbfRecord = new DbfRecord(dbfTable);
while (dbfTable.Read(dbfRecord))
{
DataRow dr = dt.NewRow();
Object [] items = new Object[dbfTable.Columns.Count];
for (int c = 0; c < dbfTable.Columns.Count; c++)
{
Object val = null;
#region Resolve Column Value
switch (dbfRecord.Values[c])
{
case DbfValueDouble dvfDouble:
val = dvfDouble.Value;
break;
case DbfValueFloat dbfFloat:
val = dbfFloat.Value;
break;
case DbfValueDate dbfDate:
val = dbfDate.Value;
break;
case DbfValueDateTime dbfDateTime:
val = dbfDateTime.Value;
break;
case DbfValueLong dbfValueLong:
val = dbfValueLong.Value;
break;
case DbfValueInt dbfValueInt:
val = dbfValueInt.Value;
break;
case DbfValueCurrency dbfValueCurrency:
val = dbfValueCurrency.Value;
break;
case DbfValueDecimal dbfValueDecimal:
val = dbfValueDecimal.Value;
break;
case DbfValueBoolean dbfValueBoolean:
val = dbfValueBoolean.Value;
break;
case DbfValueMemo dbfValueMemo:
val = dbfValueMemo.Value;
break;
case DbfValueString dbfString:
val = dbfString.Value;
break;
case DbfValueNull dbfValueNull:
val = DBNull.Value;
break;
default:
val = dbfRecord.Values[c];
break;
}
#endregion Resolve Column Value
items[c] = val;
}
dr.ItemArray = items;
dt.Rows.Add(dr);
}
return(dt);
}
private static DetailedReturn ImportTigerData(ISQL toDatabase, DataTypes dataType, string fileToImport, int recordsPerBatch, string referenceURL, string referenceZipFile)
{
DetailedReturn ret = new DetailedReturn();
// CREATE the IMPORTDETAIL table in the Database if it doesnt already exist
toDatabase.CreateImportDetailTable();
// Make sure the file exists that we wish to import...
if (File.Exists(fileToImport))
{
// Read the dbfFile contents into a DbfTable object
using (var dbfTable = new DbfTable(fileToImport, Encoding.UTF8))
{
var header = dbfTable.Header;
var versionDescription = header.VersionDescription;
var hasMemo = dbfTable.Memo != null;
var recordCount = header.RecordCount;
int rowsAffected = 0;
ret.TotalRecordsInFile = recordCount;
// Get the IMPORTDETAIL record that matches the URL/File we are going to import
ImportDetail importDetail = toDatabase.GetImportDetail(referenceURL);
if (importDetail == null)
{
// If no matching IMPORTDETAIL file was found, create one
toDatabase.InsertImportDetails(referenceURL, referenceZipFile, dataType);
importDetail = toDatabase.GetImportDetail(referenceURL); //new ImportDetail { URL = referenceURL, LocalFile = referenceZipFile, FileType = dataType.ToString("g"), LastRecordNum = 0 };
}
ret.TotalRecordsAlreadyInDB = importDetail.LastRecordNum;
// Proceed only if the record count in the file exceeds that which we have already imported
if (recordCount > importDetail.LastRecordNum)
{
// The DataTypes enum name is going to be our TABLE name
string tableName = dataType.ToString("g");
// Generate a CREATE TABLE script representing the DbfTable
string tableCreate = toDatabase.GetCreateTableScript(dbfTable, tableName);
Debug.WriteLine(tableCreate);
// CREATE IF NOT EXISTS our TABLE in the DB
toDatabase.ExecuteNonQuery(tableCreate);
// Create the first part of our INSERT statement
string insertHeader = toDatabase.GetInsertHeader(dbfTable, tableName);
// Get the records from the DbfTable
var dbfRecord = new DbfRecord(dbfTable);
// We are going to INSERT multiple records with each DB Command to dramatically speed things up
StringBuilder multiInsert = new StringBuilder();
multiInsert.Append(insertHeader);
// Loop through each of our records...
while (dbfTable.Read(dbfRecord))
{
try
{
// We only want to start INSERTing records where we last left off
if (ret.TotalRecordsImported >= importDetail.LastRecordNum)
{
// Skip the record if it is marked as deleted
if (dbfRecord.IsDeleted)
{
continue;
}
int col = -1;
StringBuilder rowPart = new StringBuilder();
// Loop through each of the values in our record
foreach (var dbfValue in dbfRecord.Values)
{
col++;
// Get the column type of this value
DbfColumn c = dbfTable.Columns[col];
// Format the value properly for our INSERT statment
rowPart.Append(toDatabase.FormatValueForInsert(c.ColumnType, dbfValue.ToString()) + ",");
}
// Add the Import Detail ID for our last column value
rowPart.Append(importDetail.ID);
// Append the record values to our INSERT statement
multiInsert.Append("(" + rowPart.ToString() + "),");
// If we have collected xxx records...
if (ret.TotalRecordsImported % recordsPerBatch == 0)
{
// Its time to execute the INSERT
rowsAffected = toDatabase.ExecuteNonQuery(multiInsert.ToString().TrimEnd(','));
// If the INSERT was successful
if (rowsAffected > 0)
{
// UPDATE the IMPORTDETAILS table
toDatabase.UpdateImportDetails(referenceURL, ret.TotalRecordsImported, ret.TotalRecordsImported == recordCount);
}
// Prepare the next mass INSERT statement
multiInsert.Clear();
multiInsert.Append(insertHeader);
}
}
// Update the record count
ret.TotalRecordsImported++;
}
catch (Exception ex)
{
//Debug.WriteLine(ex.Message);
ret.Errors.Add(new ErrorDetail(ErrorTypes.Exception, dataType, ex.Message, referenceURL, fileToImport));
}
}
ret.TotalRecordsImported -= importDetail.LastRecordNum;
// If we have a remaining INSERT compiled....
if (multiInsert.ToString() != "" && multiInsert.ToString() != insertHeader)
{
// execute the INSERT
rowsAffected = toDatabase.ExecuteNonQuery(multiInsert.ToString().TrimEnd(','));
// If the INSERT was successful
if (rowsAffected > 0)
{
// UPDATE the IMPORTDETAILS table
toDatabase.UpdateImportDetails(referenceURL, ret.TotalRecordsImported, ret.TotalRecordsImported == recordCount);
}
}
}
else // If we already appeared to have imported all the records in this file
{
toDatabase.UpdateImportDetails(referenceURL, recordCount, true);
}
}
}
else // If the file does not exist
{
ret.Errors.Add(new ErrorDetail(ErrorTypes.BadOrMissingLocalFile, dataType, "File not found", referenceURL, fileToImport));
}
return(ret);
}
