using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Data.OleDb; using MySql.Data.MySqlClient; using System.Data; namespace demo { class OleDbAccess { public static bool CreateAccdbTable(string filePath, string tableName) { bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; ADOX.Catalog catlog = new ADOX.Catalog(); if (!File.Exists(filePath)) { OleDbConnection conn = null; OleDbCommand cmd = null; try { catlog.Create("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"); //string sql = string.Format("CREATE TABLE {0}(ID AUTOINCREMENT,时间 TEXT(20),姓名 TEXT(20),身份证号码 TEXT(20),物流单号 TEXT(20),CONSTRAINT {1}_PK PRIMARY KEY(ID));",tableName,tableName); string sql = string.Format("CREATE TABLE {0}(邮件号 TEXT(20),内件号 TEXT(20),内件名称 TEXT(20),保险金额 TEXT(20),保价金额 TEXT(20),收件人城市 TEXT(20),收件人邮编 TEXT(20),收件人单位 TEXT(200),收件人姓名 TEXT(20),收件人街道 TEXT(200),收件人电话1 TEXT(20),收件人电话2 TEXT(20),寄件人姓名 TEXT (20),寄件人单位 TEXT (200),寄件人邮编 TEXT(20),寄件时间 TEXT(20),寄件人电话2 TEXT(20),寄件人街道 TEXT(200),其他费 TEXT(20));", tableName); conn = new OleDbConnection(connectionString); conn.Open(); cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } else { ret = true; } return ret; } #region 插入核验成功数据到本地数据库 public static bool GetsfzIDFrompostNo(string filePath, string tableName, string postNo, ref string sfzid) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 内件号 from {0} where 邮件号 = '{1}'", tableName, postNo); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); sfzid = obj.ToString(); ret = true; } catch(Exception ex) { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool InsertDataToAccess(string filePath, string tableName, string time, string name, string idNo, string traceNo) { lock (GlobalVariable.lockCheckAccess) { bool ret = false; string sfzID = null; if (GetsfzIDFrompostNo(filePath, tableName, traceNo, ref sfzID)) { if (sfzID == idNo) { return true; } } string recvAddress = null; ret = GetRecvAddressFromSfzID(GlobalVariable.accessLetterPath, GlobalVariable.tableName_Letter, idNo, ref recvAddress); if (!ret) { return ret; } string recvName = null; ret = GetRecvNameFromIDCNo(GlobalVariable.accessLetterPath, GlobalVariable.tableName_Letter, idNo, ref recvName); if (!ret) { return ret; } string recvNo = null; ret = GetRecvNoFromSfzID(GlobalVariable.accessLetterPath, GlobalVariable.tableName_Letter, idNo, ref recvNo); if (!ret) { return ret; } string city = recvAddress.Substring(3, 2); string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = null; try { conn.Open(); //string sql = string.Format("insert into {0}(时间,姓名,身份证号码,物流单号)values('{1}','{2}','{3}',{4})", tableName, time, name, idNo, traceNo); string sql = string.Format("insert into {0} values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')", tableName, traceNo, idNo, "身份证", "", "", city, "", recvAddress, recvName, recvAddress, recvNo, "", GlobalVariable.send_Name, GlobalVariable.send_Address, "", DateTime.Now.ToString(), "", GlobalVariable.send_Address, ""); cmd = new OleDbCommand(sql, conn); int i = cmd.ExecuteNonQuery(); if (i > 0) { ret = true; } } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } return ret; } } #endregion 插入核验成功数据到本地数据库 public static bool CreateLetterAccdbTable(string filePath, string tableName) { bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; ADOX.Catalog catlog = new ADOX.Catalog(); if (!File.Exists(filePath)) { OleDbConnection conn = null; OleDbCommand cmd = null; try { catlog.Create("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"); string sql = string.Format ("CREATE TABLE {0}(ID AUTOINCREMENT,yf_id varchar(64),时间 TEXT(20),收件人姓名 TEXT(20),收件人身份证号码 TEXT(20),收件人联系方式 TEXT(20),收件人地址 TEXT(200),物流单号 TEXT(20),寄件人姓名 TEXT(20),寄件人联系方式 TEXT(20),寄件人地址 TEXT(200),CONSTRAINT {1}_PK PRIMARY KEY(ID));", tableName, tableName); conn = new OleDbConnection(connectionString); conn.Open(); cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } else { ret = true; } return ret; } public static bool InsertDataToLetterAccess(string filePath, string tableName, string yf_id, string time, string recvName, string recvID, string recvNo, string recvAddress, string traceNo, string sendName, string sendNo, string sendAddress) {//use lock (GlobalVariable.lockCheckAccess) { bool ret = false; if (GetRecvInfoFromSfzId(filePath, tableName, recvID)) { return true; } string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; OleDbConnection conn = new OleDbConnection(connectionString); OleDbCommand cmd = null; try { conn.Open(); string sql = string.Format ("insert into {0}(yf_id,时间,收件人姓名,收件人身份证号码,收件人联系方式,收件人地址,物流单号,寄件人姓名,寄件人联系方式,寄件人地址)values('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", tableName, yf_id, time, recvName, recvID, recvNo, recvAddress, traceNo, sendName, sendNo, sendAddress); cmd = new OleDbCommand(sql, conn); int i = cmd.ExecuteNonQuery(); if (i == 1) { ret = true; } } catch (Exception ex) { string str = ex.ToString(); } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } return ret; } } public static bool GetRecvNameFromIDCNo(string filePath, string tableName, string idcNo, ref string recvName) {// bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 收件人姓名 from {0} where 收件人身份证号码 = '{1}'", tableName, idcNo); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); recvName = obj.ToString(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetRecvInfoFromSfzId(string filePath, string tableName, string sfzid) {//use bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select * from {0} where 收件人身份证号码 = '{1}'", tableName, sfzid); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); int i = cmd.ExecuteNonQuery(); if (i == 1) { ret = true; } } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetTraceNoFromRecvName(string filePath, string tableName, string recvName, ref string traceNo) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 物流单号 from {0} where 收件人姓名 = '{1}'", tableName, recvName); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); traceNo = obj.ToString(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetRecvIDNoFromTraceNo(string filePath, string tableName, string traceNo, ref string recvIDNo) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 收件人身份证号码 from {0} where 物流单号 = '{1}'", tableName, traceNo); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); recvIDNo = obj.ToString(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetYFIDFromTraceNo(string filePath, string tableName, string traceNo, ref string yf_id) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select yf_id from {0} where 物流单号 = '{1}'", tableName, traceNo); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); yf_id = obj.ToString(); if (yf_id != null) { ret = true; } } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetRecvAddressFromSfzID(string filePath, string tableName, string sfzID, ref string recvAddress) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 收件人地址 from {0} where 收件人身份证号码 = '{1}'", tableName, sfzID); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); recvAddress = obj.ToString(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool GetRecvNoFromSfzID(string filePath, string tableName, string sfzID, ref string recvNo) { bool ret = false; object obj = null; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; using (OleDbConnection conn = new OleDbConnection(connectionString)) { string sql = string.Format("select 收件人联系方式 from {0} where 收件人身份证号码 = '{1}'", tableName, sfzID); using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { conn.Open(); obj = cmd.ExecuteScalar(); recvNo = obj.ToString(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } } return ret; } public static bool UploadRemoteSqlToAccess(string accessPath, string tableName) { bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + accessPath + ";"; ADOX.Catalog catlog = new ADOX.Catalog(); OleDbConnection conn1 = null; OleDbCommand cmd1 = null; OleDbDataAdapter da = new OleDbDataAdapter(); if (!File.Exists(accessPath)) { try { catlog.Create("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + accessPath + ";"); string sql1 = string.Format("CREATE TABLE {0}", tableName); conn1 = new OleDbConnection(connectionString); conn1.Open(); cmd1 = new OleDbCommand(sql1, conn1); cmd1.ExecuteNonQuery(); ret = true; } catch { } finally { try { //cmd1.Dispose(); //conn1.Close(); } catch { ret = false; } } } string sql = null; MySqlConnection conn = new MySqlConnection(OperMySql.strConn); MySqlCommand cmd = null; DataTable dt = new DataTable(); string startTime = DateTime.Now.ToString("yyyy/MM/dd") + " 02:00"; string endTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm"); sql = string.Format("select * from yfj where yf_intime>='{0}' and yf_intime<='{1}'", startTime, endTime); cmd = new MySqlCommand(sql, conn); try { conn.Open(); MySqlDataAdapter sda = new MySqlDataAdapter(cmd); sda.Fill(dt); //for (int i = 0; i < dt.Rows.Count; i++) //{ // string sql2 = "insert into tableName values('" + dt.Rows[i]["列名"].ToString() + "','.....','....')"; //} da = new OleDbDataAdapter(); da.Fill(dt); } catch (System.Exception ex) { //MessageBox.Show(ex.ToString()); } finally { cmd1.Dispose(); conn1.Close(); cmd.Dispose(); conn.Close(); } return ret; } public static bool CreatePostInTable(string filePath, string tableName) { bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"; ADOX.Catalog catlog = new ADOX.Catalog(); if (!File.Exists(filePath)) { OleDbConnection conn = null; OleDbCommand cmd = null; try { catlog.Create("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";"); //string sql = string.Format("CREATE TABLE {0}(ID AUTOINCREMENT,时间 TEXT(20),姓名 TEXT(20),身份证号码 TEXT(20),物流单号 TEXT(20),CONSTRAINT {1}_PK PRIMARY KEY(ID));",tableName,tableName); string sql = string.Format("CREATE TABLE {0}(yf_id VARCHAR(64),yf_OddNumbers varchar(13),yf_applicant_sfzid varchar(18),yf_applicant_name varchar(32),yf_receiver_name varchar(20),yf_address varchar(200),yf_phone_number varchar(13),yf_intime time,yf_state varchar(2),yf_read_time time,yf_jgh varchar(8),yf_gh varchar(9),ztc_state varchar(2));", tableName); conn = new OleDbConnection(connectionString); conn.Open(); cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); ret = true; } catch { } finally { try { cmd.Dispose(); conn.Close(); } catch { ret = false; } } } else { ret = true; } return ret; } public static bool UpdatePostInAccess(DataTable temp, string tableName) { FileInfo file = new FileInfo(GlobalVariable.accessPostInPath); if (file.Length > 500000) { return true; } bool ret = false; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + GlobalVariable.accessPostInPath + ";"; OleDbConnection con = null; con = new OleDbConnection(connectionString); OleDbDataAdapter Bada = new OleDbDataAdapter(); Bada.SelectCommand = new OleDbCommand("select * from " + tableName, con); //OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=Data.MDB;Persist Security Info=False;Jet OLEDB:Database Password="); try { con.Open(); //OleDbDataAdapter Bada = new OleDbDataAdapter("SELECT * FROM TotalData where 1 =2", con);//建立一个DataAdapter对象 OleDbCommandBuilder cb = new OleDbCommandBuilder(Bada);//这里的CommandBuilder对象一定不要忘了,一般就是写在DataAdapter定义的后面 cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; DataSet ds = new DataSet();//建立DataSet对象 Bada.Fill(ds, tableName);//填充DataSet foreach (DataRow tempRow in temp.Rows) { DataRow dr = ds.Tables[tableName].NewRow(); dr.ItemArray = tempRow.ItemArray;//行复制 ds.Tables[tableName].Rows.Add(dr); } Bada.Update(ds, tableName);//用DataAdapter的Update()方法进行数据库的更新 ret = true; } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { con.Close(); } return ret; } } }