private void btnOk_Click(object sender, EventArgs e) { int t = 0,i = 0; String alert=""; if (cbStart.Text == "" || cbEnd.Text == "" || cbWorker.Text == "") { MessageBox.Show("请先选择导入方式"); return; } String sql = "select "; sql += cbStart.Text + ","; sql += cbEnd.Text + ","; sql += cbWorker.Text; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); String sqliteSql; object oj; while (odr.Read()) { i++; sqliteSql = "select worker from tb where stNo='" + odr[0].ToString()+"' "; sqliteSql += " and edNo='" + odr[1].ToString() + "'"; oj = db.dbScalar(sqliteSql); if (odr[0].ToString() == "728563173001") { i = i; } if(oj!=null) { if(oj.ToString()=="闸北凯旋门") { sqliteSql="delete from tb where stNo='"+odr[0].ToString()+"' "; sqliteSql+=" and edNo='"+odr[1].ToString()+"'"; db.dbNonQuery(sqliteSql); }; } sqliteSql = "select count(*) from tb where stNo='" + odr[0].ToString()+"' "; sqliteSql += " and edNo='" + odr[1].ToString() + "'"; if (db.dbScalar(sqliteSql).ToString() == "0") { sqliteSql = "insert into tb(stNo,edNo,worker) values('"; sqliteSql += odr[0].ToString() + "','"; sqliteSql += odr[1].ToString() + "','"; sqliteSql += odr[2].ToString() + "')"; if (odr[0].ToString().Length == 12) { t++; db.dbNonQuery(sqliteSql); } } else { alert += "第" + i.ToString() + "行存在数据重复\n"; } } MessageBox.Show(alert); MessageBox.Show("导入成功"); }
private void btnDelete_Click(object sender, EventArgs e) { DialogResult dr = MessageBox.Show("该操作将删除该业务员全部数据", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (dr == DialogResult.Yes) { if (tbNo.Text != "") { dbOperator db = new dbOperator(); if (db.dbNonQuery("delete from worker where 编号='" + tbNo.Text + "'") > 0) { MessageBox.Show("删除成功"); tbNo.Enabled = true; clearInput(); db.loadListview("select 编号,姓名,类型,查询密码 from worker", lvWorker); } } } }
private void btnSave_Click(object sender, EventArgs e) { if (tbNo.Text == "" || tbName.Text == "" || tbPwd.Text == "" || cbType.Text == "") { MessageBox.Show("输入的信息不完整,请重新输入"); return; } String sql; if (tbNo.Enabled) { sql = "insert into worker(编号,姓名,类型,查询密码) values('" + tbNo.Text + "','" + tbName.Text + "','" + cbType.Text + "','" + tbPwd.Text + "')"; } else { sql = "update worker set 姓名='" + tbName.Text + "',类型='" + cbType.Text + "',查询密码='" + tbPwd.Text + "' where 编号='" + tbNo.Text + "'"; } dbOperator db = new dbOperator(); if (db.dbNonQuery(sql)>0) { MessageBox.Show("保存成功"); } db.loadListview("select 编号,姓名,类型,查询密码 from worker",lvWorker); }
private void dataImport(String type) { if (type == "运单信息导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; sql += dvFormat.Rows[0].Cells[1].Value + ","; sql += dvFormat.Rows[1].Cells[1].Value + ","; sql += dvFormat.Rows[2].Cells[1].Value + ","; sql += dvFormat.Rows[3].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); while (odr.Read()) { mysql = "insert into epaper(运单编号,客户编号,收件人省,下单时间,导入时间) values('" + odr[0].ToString() + "','" + odr[1].ToString() + "','" + odr[2].ToString() + "','" + odr[3].ToString() + "',NOW())"; if (db.dbNonQuery(mysql) <= 0) { return; } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); //MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } else if (type=="重量信息导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String w; String mysql; String sql = "select "; sql += dvFormat.Rows[0].Cells[1].Value + ","; sql += dvFormat.Rows[4].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); while (odr.Read()) { if (odr[1].ToString() == "") { w = "0"; } else { w = odr[1].ToString(); } mysql = "update epaper set 重量=if(重量 is null or 重量<" + w + "," + w + ",重量) where 运单编号='" + odr[0].ToString() + "'"; db.dbNonQuery(mysql); i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); //MessageBox.Show(tot.ToString() + "条重量数据更新成功"); } }
private void btnClear_Click(object sender, EventArgs e) { dbOperator db = new dbOperator(); db.dbNonQuery("delete from tb"); MessageBox.Show("数据删除成功"); }
private void btnStart_Click(object sender, EventArgs e) { if (cbImportType.Text == "发放记录导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount-1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); Object oj; while (odr.Read()) { if (odr[0].ToString().Length == 12) { mysql = "select 使用人 from fafang where 起始单号='" + odr[0].ToString() + "' "; mysql += " and 结束单号='" + odr[1].ToString() + "'"; oj = db.dbScalar(mysql); if (oj != null) { if (oj.ToString() == "21015.001") { mysql = "delete from fafang where 起始单号='" + odr[0].ToString() + "' "; mysql += " and 结束单号='" + odr[1].ToString() + "'"; db.dbNonQuery(mysql); }; } mysql = "insert into fafang("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; db.dbNonQuery(mysql); } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "收件数据导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount - 1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; { dbOperator db = new dbOperator(); OleDbDataReader odr = ex.excelReader(sql); while (odr.Read()) { if (odr[0].ToString().Length == 12) { db.dbNonQuery("delete from shoujian where 运单编号='" + odr[0].ToString() + "'"); mysql = "insert into shoujian("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; db.dbNonQuery(mysql); } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); } MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "派件数据导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount - 1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); while (odr.Read()) { mysql = "insert into paijian("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; //MessageBox.Show(mysql); if (db.dbNonQuery(mysql) <= 0) { return; } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "") { MessageBox.Show("请选择导入方式"); } MessageBox.Show("当前有" + mydb.dbScalar("select count(*) from shoujian").ToString() + "条数据"); }