/// <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("导出完毕！", "提示！");
}
}