/// <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); }