/// <summary> /// 数据库初始化 /// </summary> private void DataBaseInit() { string filePath = ""; filePath = Application.StartupPath + @"\database\meeting.accdb";//数据库路径 string connectionStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", filePath); GlobalInfo.getOleDbConnection(connectionStr); }
/// <summary> /// 全部导出 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void AllExportToolStripMenuItem_Click(object sender, EventArgs e) { OleDbConnection oc = GlobalInfo.GlobalConnection; oc.Open(); string sql = "select topic as 会议主题,department as 办会部门,creater as 办会人, createtime as 会议开始时间, endtime as 会议结束时间,uuid as 标识 from meetingtable"; OleDbCommand ocmd = new OleDbCommand(sql, oc); OleDbDataAdapter oda = new OleDbDataAdapter(ocmd); DataTable dt = new DataTable("meetinghistory"); oda.Fill(dt); if (dt.Rows.Count == 0) { MessageBox.Show("未查到有效数据!"); return; } SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = readValueFromConfigByNode("backup").ToString() == "0"?"(*.xml)|*.xml":"(*.xls)|*.xls"; if (sfd.ShowDialog() == DialogResult.OK) { if (File.Exists(sfd.FileName)) { DialogResult dr = MessageBox.Show("文件已存在,是否覆盖?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (dr == DialogResult.Yes) { File.Delete(sfd.FileName);//先删除 } else { return; } } if (readValueFromConfigByNode("backup").ToString() == "0") { GlobalInfo.DataTableToXml(dt, sfd.FileName); } else { GlobalInfo.DataTableToExcel(dt, sfd.FileName); } MessageBox.Show("导出完毕!", "提示!"); } }
public int import(string sourceName) { if (!File.Exists(sourceName)) { return(-1); //文件不存在 } DataTable dt = GlobalInfo.xlsToDataTable(sourceName); //将xls转化成dt Console.WriteLine(dt.Rows.Count + "//" + dt.Columns.Count); OleDbConnection oc = GlobalInfo.GlobalConnection; oc.Open(); string sql = ""; OleDbCommand ocmd = new OleDbCommand(sql, oc); foreach (DataRow dr in dt.Rows) { sql = "select count(*) from meetingtable where uuid='" + dr["标识"].ToString() + "'"; ocmd.CommandText = sql; if (Int32.Parse(ocmd.ExecuteScalar().ToString()) != 0) //存在 { continue; } else { try { sql = "insert into meetingtable(topic,department,creater,createtime,endtime,uuid) values(@topic,@deparment,@creater,@createtime,@endtime,@uuid)"; ocmd.CommandText = sql; ocmd.Parameters.Clear(); ocmd.Parameters.Add("topic", OleDbType.Char); ocmd.Parameters.Add("department", OleDbType.Char); ocmd.Parameters.Add("creater", OleDbType.Char); ocmd.Parameters.Add("createtime", OleDbType.Date); ocmd.Parameters.Add("endtime", OleDbType.Date); ocmd.Parameters.Add("uuid", OleDbType.Char); ocmd.Parameters["topic"].Value = dr["会议主题"].ToString(); ocmd.Parameters["department"].Value = dr["办会部门"].ToString(); ocmd.Parameters["creater"].Value = dr["办会人"].ToString(); ocmd.Parameters["createtime"].Value = Convert.ToDateTime(dr["会议开始时间"].ToString()); ocmd.Parameters["uuid"].Value = dr["标识"].ToString(); } catch { ocmd.Dispose(); oc.Close(); return(-2); } if (dr["会议结束时间"].ToString() != "") { ocmd.Parameters["endtime"].Value = Convert.ToDateTime(dr["会议结束时间"].ToString()); } else { ocmd.Parameters["endtime"].Value = DBNull.Value; } try { ocmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("导入失败!" + ex.Message); ocmd.Dispose(); oc.Close(); return(-3); //导入失败! } } } return(0); }
/// <summary> /// 确定 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void okBtn_Click(object sender, EventArgs e) { OleDbConnection oc = GlobalInfo.GlobalConnection; oc.Open(); //根据条件进行添加 string createTimeStr = ""; bool createTimeFlag = false; string topicStr = ""; string departStr = ""; string createrStr = ""; if (radioButton2.Checked) { if (dateTimePicker2.Value < dateTimePicker1.Value) { MessageBox.Show("时间配置错误,结束时间不能小于开始时间!"); return; } createTimeStr = " and createtime>@time1 and createtime<@time2 "; createTimeFlag = true; } if (radioButton4.Checked) { if (topicKey.Text.Trim() == String.Empty) { MessageBox.Show("请输入关键字!"); return; } string[] tmp = topicKey.Text.Trim().Split(';');//使用中文分号 string list = ""; foreach (string str in tmp) { list = list + "topic like '%" + str + "%'" + " or "; } list = "(" + list.Substring(0, list.Length - 3) + ")"; topicStr = " and " + list; } if (radioButton6.Checked) { if (depart.Text.Trim() == String.Empty) { MessageBox.Show("请输入关键字!"); return; } string[] tmp = depart.Text.Trim().Split(';'); string list = ""; foreach (string str in tmp) { list = list + "department like '%" + str + "%'" + " or "; } list = "(" + list.Substring(0, list.Length - 3) + ")"; departStr = " and " + list; } if (radioButton8.Checked) { if (creater.Text.Trim() == String.Empty) { MessageBox.Show("请输入关键字!"); return; } string[] tmp = creater.Text.Trim().Split(';'); string list = ""; foreach (string str in tmp) { list = list + "creater like '%" + str + "%'" + " or "; } list = "(" + list.Substring(0, list.Length - 3) + ")"; createrStr = " and " + list; } string sql = "select topic as 会议主题,department as 办会部门,creater as 办会人, createtime as 会议开始时间, endtime as 会议结束时间,uuid as 标识 from meetingtable where 1=1 " + createTimeStr + topicStr + departStr + createrStr; OleDbCommand ocmd = new OleDbCommand(sql, oc); if (createTimeFlag) { ocmd.Parameters.Add("time1", OleDbType.Date); ocmd.Parameters.Add("time2", OleDbType.Date); ocmd.Parameters["time1"].Value = dateTimePicker1.Value; ocmd.Parameters["time2"].Value = dateTimePicker2.Value; } OleDbDataAdapter oda = new OleDbDataAdapter(ocmd); DataTable dt = new DataTable("meetinghistory"); oda.Fill(dt); if (dt.Rows.Count == 0) { MessageBox.Show("未查到有效数据!"); return; } SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = readValueFromConfigByNode("backup").ToString() == "0" ? "(*.xml)|*.xml" : "(*.xls)|*.xls"; if (sfd.ShowDialog() == DialogResult.OK) { if (File.Exists(sfd.FileName)) { DialogResult dr = MessageBox.Show("文件已存在,是否覆盖?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (dr == DialogResult.Yes) { File.Delete(sfd.FileName);//先删除 } else { return; } } if (readValueFromConfigByNode("backup").ToString() == "0") { GlobalInfo.DataTableToXml(dt, sfd.FileName); } else { GlobalInfo.DataTableToExcel(dt, sfd.FileName); } MessageBox.Show("导出完毕!", "提示!"); } }