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);
}
//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;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
individuals.Close();
}
