private void btnUpdate_Click(object sender, EventArgs e) //更新此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
frmAchievementUpdate achievement_update = new frmAchievementUpdate(txt_achievement_name); //实例化成果更新窗体
achievement_update.ShowDialog(); //成果更新窗体以模式对话框的方式打开
#region 更新DataGridView控件内的内容
try
{
DataTable dt = MyDatabase.GetDataSetBySql(sql_backup);
if (dt == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (dt.Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此检索成果到文件按钮可点击
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt; //DataGridView控件显示数据
this.txt_achievement_name = this.dgvData.Rows[0].Cells[0].Value.ToString().Trim();
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
#endregion
}
private void btnChooseDirectory_Click(object sender, EventArgs e) //选择目录
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
DialogResult result = this.fbdChooseDirectory.ShowDialog();
if (result == DialogResult.OK)//点击了确定
{
this.txtSaveDirectory.Text = fbdChooseDirectory.SelectedPath;
}
else//点击了取消
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("选择目录过程中出错！", "提示");
return;
}
}
private void btnChooseFile_Click(object sender, EventArgs e) //选择文件
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
this.ofdLoadFromFile.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls|文本文档(*.txt)|*.txt"; //设置打开文件类型
this.ofdLoadFromFile.AddExtension = true; //自动添加扩展名
DialogResult result = this.ofdLoadFromFile.ShowDialog();
if (result == DialogResult.OK) //点击了打开
{
txtFilePath.Text = ofdLoadFromFile.FileName;
}
else//点击了取消
{
return;
}
}
catch (Exception ex)
{
MessageBox.Show("选择文件过程中出错！", "提示");
return;
}
}
private void btnDelete_Click(object sender, EventArgs e) //删除此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
DialogResult result = MessageBox.Show("是否确定删除此项成果？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)//确定
{
if (txt_achievement_name != string.Empty)
{
string sql = string.Format("delete from datainfo where datainfo.AchievementName='{0}';", txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql)) //删除成功
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToFile.Text = "导出此项成果到文件"; //导出此项成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此项成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("删除成功！", "提示");
return;
}
}
MessageBox.Show("删除失败！", "提示");
return;
}
else//取消
{
return;
}
}
private void btnOutToFileFail_Click(object sender, EventArgs e) //将导入失败的成果导出到文件
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
DataTable dt = MyExcel.GetDgvToTable(dgvDataFail);
this.sfdOutToFile.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|文本文档(*.txt)|*.txt"; //设置保存类型
this.sfdOutToFile.AddExtension = true; //自动添加扩展名
DialogResult result = this.sfdOutToFile.ShowDialog();
if (result == DialogResult.OK) //点击了保存
{
btnOutToFileFail.Text = "正在导出中。。。"; //将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = false; //将导入失败的成果导出到文件按钮不可点击
if (MyData.DataConvert(dt) == false) //对dt内的数据进行处理，尤其是“(数据格式有误)”这种情况
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //设置将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true; //设置将导入失败的成果导出到文件按钮可点击
MessageBox.Show("导出失败！", "提示");
return;
}
if (MyData.GetFileExtByFileName(sfdOutToFile.FileName) == "xlsx") //导出到Excel文件
{
MyExcel.SaveDataToExcel(dt, sfdOutToFile.FileName); //此过程很慢
}
else if (MyData.GetFileExtByFileName(sfdOutToFile.FileName) == "txt") //导出到txt文件
{
MyTxt.SaveDataToTxt(dt, sfdOutToFile.FileName);
}
else//扩展名有误
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件";//设置将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true;//设置将导入失败的成果导出到文件按钮可点击
MessageBox.Show("导出类型为不支持的类型，导出失败！", "提示");
return;
}
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true; //将导入失败的成果导出到文件按钮可点击
MessageBox.Show("导出成功！", "提示");
return;
}
else if (result == DialogResult.Cancel) //点击了取消
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //设置将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true; //设置将导入失败的成果导出到文件按钮可点击
return;
}
}
catch (Exception ex)
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //设置将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true; //设置将导入失败的成果导出到文件按钮可点击
MessageBox.Show("导出失败！", "提示");
return;
}
}
private void btnUpdate_Click(object sender, EventArgs e) //更新
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (this.txtAchievementName.Text.ToString().Trim() == string.Empty || this.txtAchievementType.Text.ToString().Trim() == string.Empty || this.dtpAchievementDate.Text.ToString().Trim() == string.Empty || this.txtAchievementAuthor.Text.ToString().Trim() == string.Empty || this.txtAchievementCompany.Text.ToString().Trim() == string.Empty || this.txtAchievementMoney.Text.ToString().Trim() == string.Empty)//是否所有数据均未改动检测
{
MessageBox.Show("信息输入不完整，请重新检查之后再重试！", "提示");
return;
}
try//对成果时间和成果支撑基金的数据格式进行检测(如数据有误会throw异常，程序进入catch捕捉代码中)
{
Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim());
Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim());
}
catch (Exception ex)
{
MessageBox.Show("信息输入格式有误，请重新检查之后再重试！", "提示");
//throw new Exception(ex.Message);
return;
}
//所有数据的合法性均检测完毕
string sql_1 = string.Format("update datainfo set AchievementName='{0}', AchievementType='{1}', AchievementDate='{2}', AchievementAuthor='{3}', AchievementCompany='{4}', AchievementMoney={5} where AchievementName='{6}';", this.txtAchievementName.Text.ToString().Trim(), this.txtAchievementType.Text.ToString().Trim(), Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()), this.txtAchievementAuthor.Text.ToString().Trim(), this.txtAchievementCompany.Text.ToString().Trim(), Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim()), txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //更新成功
{
//更新DataGridView控件内的内容
txt_achievement_name = this.txtAchievementName.Text.ToString().Trim();
string sql_2 = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementName='{0}';", txt_achievement_name);
DataSet ds = MyDatabase.GetDataSetBySql(sql_2);
if (ds == null)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
MessageBox.Show("更新成功！", "提示");
}
else//更新失败
{
MessageBox.Show("更新失败！", "提示");
}
}
private void btnCount_Click(object sender, EventArgs e) //开始统计个数信息
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (txtSaveDirectory.Text == string.Empty) //判断成果保存目录是否为空
{
txtPartNum.Text = "0"; //零件图个数显示内容
txtAssemblyNum.Text = "0"; //装配图个数显示内容
txtDrawingNum.Text = "0"; //工程图纸个数显示内容
MessageBox.Show("成果保存目录不能为空！", "提示");
return;
}
if (System.IO.Directory.Exists(txtSaveDirectory.Text) == false) //判断成果保存目录是否存在
{
txtPartNum.Text = "0"; //零件图个数显示内容
txtAssemblyNum.Text = "0"; //装配图个数显示内容
txtDrawingNum.Text = "0"; //工程图纸个数显示内容
MessageBox.Show("成果保存目录不存在！", "提示");
return;
}
try
{
btnCount.Text = "正在统计中。。。"; //设置开始统计个数信息按钮显示内容
btnCount.Enabled = false; //开始统计个数信息按钮不可点击
int[] num = new int[3];
num[0] = 0; //零件图个数
num[1] = 0; //装配图个数
num[2] = 0; //工程图纸个数
countFile(txtSaveDirectory.Text, num);
txtPartNum.Text = num[0].ToString(); //零件图个数显示内容
txtAssemblyNum.Text = num[1].ToString(); //装配图个数显示内容
txtDrawingNum.Text = num[2].ToString(); //工程图纸个数显示内容
btnCount.Text = "开始统计个数信息"; //设置开始统计个数信息按钮显示内容
btnCount.Enabled = true; //开始统计个数信息按钮可点击
MessageBox.Show("个数信息统计完成！", "提示");
return;
}
catch (Exception ex)
{
txtPartNum.Text = "0"; //零件图个数显示内容
txtAssemblyNum.Text = "0"; //装配图个数显示内容
txtDrawingNum.Text = "0"; //工程图纸个数显示内容
btnCount.Text = "开始统计个数信息"; //设置开始统计个数信息按钮显示内容
btnCount.Enabled = true; //开始统计个数信息按钮可点击
MessageBox.Show("个数信息统计失败！", "提示");
return;
}
}
private void btnOutToExcel_Click(object sender, EventArgs e) //导出此检索结果到Excel
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (txt_achievement_name != string.Empty)
{
try
{
DataTable dt = MyExcel.GetDgvToTable(dgvData);
this.sfdOutToExcel.Filter = "Excel 工作簿(*.xlsx)|*.xlsx"; //设置保存类型
DialogResult result = this.sfdOutToExcel.ShowDialog();
if (result == DialogResult.OK) //点击了保存
{
btnOutToExcel.Text = "正在导出中。。。"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮显示不可点击
if (MyExcel.DateTimeRemoveTime(dt) == false) //对dt内的数据进行单独处理，去除时间(默认值00:00:00)，只保留日期
{
btnOutToExcel.Text = "导出此检索成果到Excel"; //设置导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //设置导出此检索成果到Excel按钮显示可点击
MessageBox.Show("导出失败！", "提示");
return;
}
MyExcel.SaveDataToExcel(dt, sfdOutToExcel.FileName); //此过程很慢
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //导出此检索成果到Excel按钮显示可点击
MessageBox.Show("导出成功！", "提示");
return;
}
else if (result == DialogResult.Cancel) //点击了取消
{
btnOutToExcel.Text = "导出此检索成果到Excel"; //设置导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //设置导出此检索成果到Excel按钮显示可点击
return;
}
}
catch (Exception ex)
{
btnOutToExcel.Text = "导出此检索成果到Excel"; //设置导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //设置导出此检索成果到Excel按钮显示可点击
MessageBox.Show("导出失败！", "提示");
return;
}
}
btnOutToExcel.Text = "导出此检索成果到Excel"; //设置导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //设置导出此检索成果到Excel按钮显示可点击
MessageBox.Show("导出失败！", "提示");
}
private string txt_achievement_name = string.Empty; //存储数据库内的主键的值，用于更新记录和删除记录
private void frmAchievementUpdate_Load(object sender, EventArgs e) //窗体载入时初始化
{
btnOutToExcel.Enabled = true; //导出此项结果到Excel按钮可点击
btnOutToExcel.Text = "导出此项结果到Excel"; //导出此项结果到Excel按钮显示内容
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
btnOutToExcel.Enabled = false; //导出此项结果到Excel按钮不可点击
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
string sql = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementName='{0}';", txt_achievement_name);
DataSet ds = MyDatabase.GetDataSetBySql(sql);
if (ds == null) //数据库连接失败
{
btnOutToExcel.Enabled = false; //导出此项结果到Excel按钮不可点击
btnOutToExcel.Text = "导出此项结果到Excel"; //导出此项结果到Excel按钮显示内容
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
txtAchievementName.Text = txt_achievement_name;
txtAchievementType.Text = this.dgvData.Rows[0].Cells[1].Value.ToString().Trim();
dtpAchievementDate.Text = this.dgvData.Rows[0].Cells[2].Value.ToString().Trim();
txtAchievementAuthor.Text = this.dgvData.Rows[0].Cells[3].Value.ToString().Trim();
txtAchievementCompany.Text = this.dgvData.Rows[0].Cells[4].Value.ToString().Trim();
txtAchievementMoney.Text = this.dgvData.Rows[0].Cells[5].Value.ToString().Trim();
return;
}
catch (Exception ex)
{
btnOutToExcel.Enabled = false; //导出此项结果到Excel按钮不可点击
btnOutToExcel.Text = "导出此项结果到Excel"; //导出此项结果到Excel按钮显示内容
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("更新界面初始化！", "提示");
return;
}
}
private void btnUpdate_Click(object sender, EventArgs e) //更新
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (this.txtAchievementName.Text.ToString().Trim() == string.Empty || this.txtAchievementType.Text.ToString().Trim() == string.Empty || this.dtpAchievementDate.Text.ToString().Trim() == string.Empty || this.txtAchievementAuthor.Text.ToString().Trim() == string.Empty || this.txtAchievementCompany.Text.ToString().Trim() == string.Empty || this.txtAchievementMoney.Text.ToString().Trim() == string.Empty)//是否所有数据均未改动检测
{
MessageBox.Show("信息输入不完整，请重新检查之后再重试！", "提示");
return;
}
try//对成果时间和成果支撑基金的数据格式进行检测(如数据有误会throw异常，程序进入catch捕捉代码中)
{
Convert.ToDateTime(MyDatabase.CharFilter(this.dtpAchievementDate.Text.ToString().Trim()));
Convert.ToDouble(MyDatabase.CharFilter(this.txtAchievementMoney.Text.ToString().Trim()));
}
catch (Exception ex)
{
MessageBox.Show("信息输入格式有误，请重新检查之后再重试！", "提示");
//throw new Exception(ex.Message);
return;
}
//所有数据的合法性均检测完毕
string sql_1 = string.Format("update datainfo set AchievementName='{0}', AchievementType='{1}', AchievementDate='{2}', AchievementAuthor='{3}', AchievementCompany='{4}', AchievementMoney={5} where AchievementName='{6}';", MyDatabase.CharFilter(this.txtAchievementName.Text.ToString().Trim()), MyDatabase.CharFilter(this.txtAchievementType.Text.ToString().Trim()), Convert.ToDateTime(MyDatabase.CharFilter(this.dtpAchievementDate.Text.ToString().Trim())), MyDatabase.CharFilter(this.txtAchievementAuthor.Text.ToString().Trim()), MyDatabase.CharFilter(this.txtAchievementCompany.Text.ToString().Trim()), Convert.ToDouble(MyDatabase.CharFilter(this.txtAchievementMoney.Text.ToString().Trim())), txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //更新成功
{
//更新DataGridView控件内的内容
txt_achievement_name = MyDatabase.CharFilter(this.txtAchievementName.Text.ToString().Trim());
string sql_2 = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementName='{0}';", txt_achievement_name);
DataTable dt = MyDatabase.GetDataTableBySql(sql_2);
if (dt == null)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt; //DataGridView控件显示数据
MessageBox.Show("更新成功！", "提示");
}
else//更新失败
{
MessageBox.Show("更新失败！", "提示");
}
}
private string txt_achievement_name = string.Empty; //存储数据库内的主键的值，用于删除记录
private void frmAchievementAddByWrite_Load(object sender, EventArgs e) //窗体载入时初始化
{
txt_achievement_name = string.Empty;
txtAchievementType.Visible = false; //成果类型编辑框不可见
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToFile.Text = "导出此项成果到文件"; //导出此项成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此项成果到文件按钮不可点击
this.dgvData.DataSource = null; //DataGridView控件显示数据
this.dgvData.AllowUserToAddRows = false; //不允许用户添加新行
#region 成果类型组合框初始化
if (MyDatabase.TestMyDatabaseConnect() == false)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
DataTable dt = MyDatabase.GetDataSetBySql("select distinct AchievementType from datainfo;");
if (dt == null)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
this.cboAchievementType.Items.Add(dt.Rows[i][0].ToString().Trim());
}
this.cboAchievementType.Items.Add("(其他)");
this.cboAchievementType.SelectedIndex = 0;
//防止数据库中没有任何成果类型而使得成果类型中只有"(其他)"而使得永远无法触发cboAchievementType_SelectedIndexChanged函数使得成果类型编辑框永远不可见，所以加入以下判断代码在初始化代码中
if (string.Compare(this.cboAchievementType.SelectedItem.ToString(), "(其他)") == 0)
{
txtAchievementType.Visible = true;//成果类型编辑框可见
}
else
{
txtAchievementType.Visible = false;//成果类型编辑框不可见
}
}
catch (Exception ex)//数据库可能连接不上，MyDatabase.GetDataSetBySql函数会出错，返回的DataSet值为null，导致下面引用DataSet具体值时会抛出异常(后经改进在DataSet具体值使用之前加入了值是否为null的判断，后又经过改进在每次事件处理前均加入了检测能否成功连接的函数)
{
MessageBox.Show(ex.Message);
}
#endregion
}
private string txt_achievement_type = string.Empty; //存储数据库内的成果类型的值，用于更新记录和删除记录之后的更新DataGridView控件内的数据
private void frmAchievementSearchEasy_Load(object sender, EventArgs e) //窗体载入时初始化
{
txt_achievement_name = string.Empty;
txt_achievement_type = string.Empty;
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToFile.Text = "导出此检索结果到文件"; //导出此检索结果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索结果到文件按钮不可点击
dgvData.DataSource = null; //DataGridView控件显示数据
this.dgvData.AllowUserToAddRows = false; //不允许用户添加新行
#region 成果类型组合框初始化
if (MyDatabase.TestMyDatabaseConnect() == false)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
DataTable dt = MyDatabase.GetDataTableBySql("select distinct AchievementType from datainfo;");
if (dt == null)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
this.cboAchievementType.Items.Add(dt.Rows[i][0].ToString().Trim());
}
try//检测数据库中是否没有任何成果类型
{
this.cboAchievementType.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show("无任何成果类型，无法检索！", "提示");
return;
}
}
catch (Exception ex)//数据库可能连接不上，MyDatabase.GetDataSetBySql函数会出错，返回的DataSet值为null，导致下面引用DataSet具体值时会抛出异常(后经改进在DataSet具体值使用之前加入了值是否为null的判断，后又经过改进在每次事件处理前均加入了检测能否成功连接的函数)
{
MessageBox.Show(ex.Message);
}
#endregion
}
private string txt_achievement_name = string.Empty; //存储数据库内的主键的值，用于更新记录和删除记录
private void frmAchievementUpdate_Load(object sender, EventArgs e) //窗体载入时初始化
{
btnOutToFile.Enabled = true; //导出此项结果到文件按钮可点击
btnOutToFile.Text = "导出此项结果到文件"; //导出此项结果到文件按钮显示内容
this.dgvData.AllowUserToAddRows = false; //不允许用户添加新行
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
btnOutToFile.Enabled = false; //导出此项结果到文件按钮不可点击
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
string sql = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementName='{0}';", txt_achievement_name);
DataTable dt = MyDatabase.GetDataTableBySql(sql);
if (dt == null) //数据库连接失败
{
btnOutToFile.Enabled = false; //导出此项结果到文件按钮不可点击
btnOutToFile.Text = "导出此项结果到文件"; //导出此项结果到文件按钮显示内容
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt; //DataGridView控件显示数据
txtAchievementName.Text = txt_achievement_name;
txtAchievementType.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[1].Value.ToString().Trim());
dtpAchievementDate.Text = this.dgvData.Rows[0].Cells[2].Value.ToString().Trim();
txtAchievementAuthor.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[3].Value.ToString().Trim());
txtAchievementCompany.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[4].Value.ToString().Trim());
txtAchievementMoney.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[5].Value.ToString().Trim());
return;
}
catch (Exception ex)
{
btnOutToFile.Enabled = false; //导出此项结果到文件按钮不可点击
btnOutToFile.Text = "导出此项结果到文件"; //导出此项结果到文件按钮显示内容
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("更新界面初始化失败！", "提示");
return;
}
}
private void btnSaveAndTest_Click(object sender, EventArgs e)//保存此配置并测试连接
{
try
{
btnSaveAndTest.Text = "正在测试连接。。。"; //保存此配置并测试连接按钮显示内容
btnSaveAndTest.Enabled = false; //保存此配置并测试连接按钮不可点击
MyDatabase.SetServer(txtServer.Text.Trim());
MyDatabase.SetPort(txtPort.Text.Trim());
MyDatabase.SetDatabase(txtDatabase.Text.Trim());
MyDatabase.SetUid(txtUid.Text.Trim());
MyDatabase.SetPwd(txtPwd.Text.Trim());
if (MyDatabase.TestMyDatabaseConnect() == true)
{
frm_main.tsmniAchievementManage.Enabled = true; //成果管理可点击
frm_main.tsmniMechanicalDrawing.Enabled = true; //机械图管理可点击
btnSaveAndTest.Text = "保存此配置并测试连接"; //保存此配置并测试连接按钮显示内容
btnSaveAndTest.Enabled = true; //保存此配置并测试连接按钮可点击
MessageBox.Show("数据库连接成功！\n此配置已保存！");
this.Close(); //关闭窗体
}
else
{
frm_main.tsmniAchievementManage.Enabled = false; //成果管理不可点击
frm_main.tsmniMechanicalDrawing.Enabled = false; //机械图管理不可点击
btnSaveAndTest.Text = "保存此配置并测试连接"; //保存此配置并测试连接按钮显示内容
btnSaveAndTest.Enabled = true; //保存此配置并测试连接按钮可点击
MessageBox.Show("数据库连接失败！\n请重新修改配置信息！");
return;
}
}
catch (Exception ex)
{
frm_main.tsmniAchievementManage.Enabled = false; //成果管理不可点击
frm_main.tsmniMechanicalDrawing.Enabled = false; //机械图管理不可点击
btnSaveAndTest.Text = "保存此配置并测试连接"; //保存此配置并测试连接按钮显示内容
btnSaveAndTest.Enabled = true; //保存此配置并测试连接按钮可点击
//MessageBox.Show(ex.Message);
//throw new Exception(ex.Message);
MessageBox.Show("数据库连接失败！\n请重新修改配置信息！");
return;
}
}
private string sql_backup = string.Empty; //存储搜索的sql语句，用于更新记录和删除记录之后的DataGridView控件内的数据更新
private void frmAchievementSearchComplex_Load(object sender, EventArgs e) //窗体载入时初始化
{
txt_achievement_name = string.Empty;
sql_backup = string.Empty;
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索结果到Excel"; //导出此检索结果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索结果到Excel按钮不可点击
dgvData.DataSource = null; //DataGridView控件显示数据
this.cboLogic.SelectedIndex = 0; //逻辑选择combox默认选中第一个逻辑(or)
cbAchievementDate.Checked = false; //成果时间不限制不选中
cbAchievementMoney.Checked = false; //支撑基金不限制不选中
this.dtpAchievementDate.Enabled = true; //成果时间DateTimePacker选择控件可用
this.txtAchievementMoney.Enabled = true; //支撑基金输入控件可用
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
}
private void frmAchievementAddByFile_Load(object sender, EventArgs e) //窗体载入时初始化
{
btnOutToFileSuccess.Text = "将导入成功的成果导出到文件"; //将导入成功的成果导出到文件按钮显示内容
btnOutToFileSuccess.Enabled = false; //将导入成功的成果导出到文件按钮不可点击
this.dgvDataSuccess.DataSource = null; //导入成功的信息显示DataGridView控件显示数据
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = false; //将导入失败的成果导出到文件按钮不可点击
this.dgvDataFail.DataSource = null; //导入失败的信息显示DataGridView控件显示数据
txtResultSuccess.Text = "0"; //导入成功成果数目为0
txtResultFail.Text = "0"; //导入失败成果数目为0
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
}
private void btnUpdate_Click(object sender, EventArgs e) //更新机械图信息
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
if (txtSaveDirectory.Text == string.Empty) //判断成果保存目录是否为空
{
txtSaveDirectory.Text = "(Null)"; //成果保存目录如果为空，则置其值为(Null)(数据库中默认值)
}
string sql_1 = string.Format("update mechanicaldrawing set mechanicaldrawing.AchievementName='{0}', mechanicaldrawing.Software='{1}', mechanicaldrawing.SaveDirectory='{2}', mechanicaldrawing.PartNum={3}, mechanicaldrawing.AssemblyNum={4}, mechanicaldrawing.DrawingNum={5} where mechanicaldrawing.AchievementName='{6}';", MyDatabase.CharFilter(this.txtAchievementName.Text.ToString().Trim()), MyDatabase.CharFilter(this.cboSoftware.Text.ToString().Trim()), MyDatabase.CharFilter(this.txtSaveDirectory.Text.ToString().Trim()), Convert.ToInt32(MyDatabase.CharFilter(this.txtPartNum.Text.ToString().Trim())), Convert.ToInt32(MyDatabase.CharFilter(this.txtAssemblyNum.Text.ToString().Trim())), Convert.ToInt32(MyDatabase.CharFilter(this.txtDrawingNum.Text.ToString().Trim())), MyDatabase.CharFilter(this.txtAchievementName.Text.ToString().Trim())); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //更新成功
{
//更新DataGridView控件内的内容
string sql_2 = "select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金', mechanicaldrawing.Software as '所用软件', mechanicaldrawing.SaveDirectory as '成果保存目录', mechanicaldrawing.PartNum as '零件图个数', mechanicaldrawing.AssemblyNum as '装配图个数', mechanicaldrawing.DrawingNum as '工程图纸个数' from datainfo join mechanicaldrawing on datainfo.AchievementName=mechanicaldrawing.AchievementName;";
DataTable dt_2 = MyDatabase.GetDataTableBySql(sql_2);
this.dgvData.DataSource = dt_2; //机械图信息显示DataGridView控件显示数据
btnUpdate.Enabled = true; //更新机械图信息按钮可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此视图内容到文件按钮可点击
MessageBox.Show("更新成功！", "提示");
return;
}
else//更新失败
{
MessageBox.Show("更新失败！", "提示");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("更新失败！", "提示");
return;
}
}
private void btnDelete_Click(object sender, EventArgs e) //删除此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (txt_achievement_name != string.Empty)
{
string sql = string.Format("delete from datainfo where datainfo.AchievementName='{0}';", txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql)) //删除成功
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToExcel.Text = "导出此项成果到Excel"; //导出此项成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此项成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("删除成功！", "提示");
return;
}
}
MessageBox.Show("删除失败！", "提示");
}
private void btnSearch_Click(object sender, EventArgs e) //检索
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try//防止将所有成果类型均删除后，成果类型为空时检索，会导致未引用的异常
{
if (this.cboAchievementType.SelectedItem.ToString() == string.Empty)
{
MessageBox.Show("成果类型选择有误", "提示");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("无任何待检索成果类型，检索失败！", "提示");
return;
}
try
{
string sql = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementType='{0}';", MyDatabase.CharFilter(this.cboAchievementType.SelectedItem.ToString().Trim()));
DataTable dt = MyDatabase.GetDataTableBySql(sql);
if (dt == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (dt.Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("未检索到符合要求的成果信息！", "提示");
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此检索成果到文件按钮可点击
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt; //DataGridView控件显示数据
this.txt_achievement_name = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[0].Value.ToString().Trim());
this.txt_achievement_type = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[1].Value.ToString().Trim());
MessageBox.Show("检索成功！", "提示");
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("检索失败！", "提示");
return;
}
}
private void btnOutToFile_Click(object sender, EventArgs e) //导出此检索结果到文件
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (txt_achievement_name != string.Empty)
{
try
{
DataTable dt = MyExcel.GetDgvToTable(dgvData);
this.sfdOutToFile.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|文本文档(*.txt)|*.txt"; //设置保存类型
this.sfdOutToFile.AddExtension = true; //自动添加扩展名
DialogResult result = this.sfdOutToFile.ShowDialog();
if (result == DialogResult.OK) //点击了保存
{
btnOutToFile.Text = "正在导出中。。。"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮显示不可点击
if (MyData.DataConvert(dt) == false) //对dt内的数据进行单独处理，去除时间(默认值00:00:00)，只保留日期
{
btnOutToFile.Text = "导出此检索成果到文件"; //设置导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此检索成果到文件按钮显示可点击
MessageBox.Show("导出失败！", "提示");
return;
}
if (MyData.GetFileExtByFileName(sfdOutToFile.FileName) == "xlsx") //导出到Excel文件
{
MyExcel.SaveDataToExcel(dt, sfdOutToFile.FileName); //此过程很慢
}
else if (MyData.GetFileExtByFileName(sfdOutToFile.FileName) == "txt") //导出到txt文件
{
MyTxt.SaveDataToTxt(dt, sfdOutToFile.FileName);
}
else//扩展名有误
{
btnOutToFile.Text = "导出此检索成果到文件";//设置导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true;//设置导出此检索成果到文件按钮显示可点击
MessageBox.Show("导出类型为不支持的类型，导出失败！", "提示");
return;
}
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此检索成果到文件按钮显示可点击
MessageBox.Show("导出成功！", "提示");
return;
}
else if (result == DialogResult.Cancel) //点击了取消
{
btnOutToFile.Text = "导出此检索成果到文件"; //设置导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此检索成果到文件按钮显示可点击
return;
}
}
catch (Exception ex)
{
btnOutToFile.Text = "导出此检索成果到文件"; //设置导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此检索成果到文件按钮显示可点击
MessageBox.Show("导出失败！", "提示");
return;
}
}
btnOutToFile.Text = "导出此检索成果到文件"; //设置导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此检索成果到文件按钮显示可点击
MessageBox.Show("导出失败！", "提示");
}
private void btnDelete_Click(object sender, EventArgs e) //删除此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
DialogResult result = MessageBox.Show("是否确定删除此项成果？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)//确定
{
if (txt_achievement_name != string.Empty)
{
string sql_1 = string.Format("delete from datainfo where datainfo.AchievementName='{0}';", txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //删除成功
{
//更新删除后DataGridView控件内的数据
try
{
string sql_2 = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementType='{0}';", txt_achievement_type);
DataTable dt_2 = MyDatabase.GetDataTableBySql(sql_2);
if (dt_2.Rows.Count == 0) //删除此条成果后数据库中已无此成果类型的成果
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
#region 成果类型组合框更新
if (MyDatabase.TestMyDatabaseConnect() == false)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
this.cboAchievementType.Items.Clear(); //清空combox内所有选项
DataTable dt_3 = MyDatabase.GetDataTableBySql("select distinct AchievementType from datainfo;");
if (dt_3.Rows.Count == 0) //删除此条成果后数据库中已无其他成果类型
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("删除成功！", "提示");
return;
}
for (int i = 0; i < dt_3.Rows.Count; i++)
{
this.cboAchievementType.Items.Add(MyDatabase.CharFilter(dt_3.Rows[i][0].ToString().Trim()));
}
this.cboAchievementType.SelectedIndex = 0;
}
catch (Exception ex)//数据库可能连接不上，MyDatabase.GetDataSetBySql函数会出错，返回的DataSet值为null，导致下面引用DataSet具体值时会抛出异常(后经改进在DataSet具体值使用之前加入了值是否为null的判断，后又经过改进在每次事件处理前均加入了检测能否成功连接的函数)
{
MessageBox.Show(ex.Message);
}
#endregion
MessageBox.Show("删除成功！", "提示");
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此检索成果到文件按钮可点击
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt_2; //DataGridView控件显示数据
txt_achievement_name = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[0].Value.ToString().Trim());
txt_achievement_type = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[1].Value.ToString().Trim());
MessageBox.Show("删除成功！", "提示");
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("删除失败！", "提示");
return;
}
}
}
MessageBox.Show("删除失败！", "提示");
return;
}
else//取消
{
return;
}
}
private void btnUpdate_Click(object sender, EventArgs e) //更新此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
frmAchievementUpdate achievement_update = new frmAchievementUpdate(txt_achievement_name); //实例化成果更新窗体
achievement_update.ShowDialog(); //成果更新窗体以模式对话框的方式打开
#region 成果类型组合框更新
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
this.cboAchievementType.Items.Clear(); //清空combox内所有选项
DataTable dt_1 = MyDatabase.GetDataTableBySql("select distinct AchievementType from datainfo;");
if (dt_1.Rows.Count == 0) //数据库中已无任何成果类型
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
for (int i = 0; i < dt_1.Rows.Count; i++)
{
this.cboAchievementType.Items.Add(MyDatabase.CharFilter(dt_1.Rows[i][0].ToString().Trim()));
}
bool checkresult = false;
for (int i = 0; i < this.cboAchievementType.Items.Count; i++)
{
if (string.Compare(cboAchievementType.GetItemText(cboAchievementType.Items[i]), txt_achievement_type) == 0)//成果类型combox中还有之前的成果类型
{
this.cboAchievementType.SelectedIndex = i;
checkresult = true;
break;
}
}
if (checkresult == false)//成果类型combox中已无之前的成果类型
{
this.cboAchievementType.SelectedIndex = 0;
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
dgvData.DataSource = null;
return;
}
}
catch (Exception ex)//数据库可能连接不上，MyDatabase.GetDataSetBySql函数会出错，返回的DataSet值为null，导致下面引用DataSet具体值时会抛出异常(后经改进在DataSet具体值使用之前加入了值是否为null的判断，后又经过改进在每次事件处理前均加入了检测能否成功连接的函数)
{
MessageBox.Show(ex.Message);
}
#endregion
#region DataGridView控件内容更新
try
{
string sql_2 = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementType='{0}';", txt_achievement_type);
DataTable dt_2 = MyDatabase.GetDataTableBySql(sql_2);
this.dgvData.DataSource = dt_2;//DataGridView控件显示数据
txt_achievement_name = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[0].Value.ToString().Trim());
txt_achievement_type = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[1].Value.ToString().Trim());
return;
}
catch (Exception ex)
{
MessageBox.Show("数据更新出错！");
return;
}
#endregion
}
private void btnAdd_Click(object sender, EventArgs e) //录入此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (this.txtAchievementName.Text.ToString().Trim() == string.Empty || this.dtpAchievementDate.Text.ToString().Trim() == string.Empty || this.txtAchievementAuthor.Text.ToString().Trim() == string.Empty || this.txtAchievementCompany.Text.ToString().Trim() == string.Empty || this.txtAchievementMoney.Text.ToString().Trim() == string.Empty)//数据是否为空检测
{
MessageBox.Show("信息输入不完整，请重新检查之后再重试！", "提示");
return;
}
if (string.Compare(this.cboAchievementType.SelectedItem.ToString(), "(其他)") == 0)//对成果类型数据是否为空进行单独判断
{
if (this.txtAchievementType.Text.ToString().Trim() == string.Empty)
{
MessageBox.Show("信息输入不完整，请重新检查之后再重试！", "提示");
return;
}
}
try //对成果时间和成果支撑基金的数据格式进行检测(如数据有误会throw异常，程序进入catch捕捉代码中)
{
Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()).ToString("yyyy-MM-dd"); //最后加入ToString("yyyy-MM-dd")是为了去除时间只留下日期
Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim());
}
catch (Exception ex)
{
MessageBox.Show("信息输入格式有误，请重新检查之后再重试！", "提示");
//throw new Exception(ex.Message);
return;
}
//所有数据的合法性均检测完毕
string sql_1 = string.Empty;
if (string.Compare(this.cboAchievementType.SelectedItem.ToString(), "(其他)") == 0) //对成果类型数据进行判断
{
sql_1 = string.Format("insert into datainfo values('{0}', '{1}', '{2}', '{3}', '{4}', {5});", this.txtAchievementName.Text.ToString().Trim(), this.txtAchievementType.Text.ToString().Trim(), Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()).ToString("yyyy-MM-dd"), this.txtAchievementAuthor.Text.ToString().Trim(), this.txtAchievementCompany.Text.ToString().Trim(), Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim())); //sql语句
}
else
{
sql_1 = string.Format("insert into datainfo values('{0}', '{1}', '{2}', '{3}', '{4}', {5});", this.txtAchievementName.Text.ToString().Trim(), this.cboAchievementType.SelectedItem.ToString().Trim(), Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()).ToString("yyyy-MM-dd"), this.txtAchievementAuthor.Text.ToString().Trim(), this.txtAchievementCompany.Text.ToString().Trim(), Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim())); //sql语句
}
if (MyDatabase.UpdateDataBySql(sql_1)) //添加成功
{
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnOutToFile.Text = "导出此项成果到文件"; //导出此项成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此项成果到文件按钮可点击
txt_achievement_name = this.txtAchievementName.Text.ToString().Trim(); //记录数据库内主键的值
string sql_2 = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementName='{0}';", this.txtAchievementName.Text.ToString().Trim());
DataSet ds = MyDatabase.GetDataSetBySql(sql_2);
if (ds == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnOutToFile.Text = "导出此项成果到文件"; //导出此项成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此项成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
MessageBox.Show("添加成功！", "提示");
}
else//添加失败
{
btnDelete.Enabled = false;//删除此项成果按钮不可点击
btnOutToFile.Text = "导出此项成果到文件"; //导出此项成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此项成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("添加失败！", "提示");
}
}
private void btnDelete_Click(object sender, EventArgs e) //删除此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
DialogResult result = MessageBox.Show("是否确定删除此项成果？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)//确定
{
if (txt_achievement_name != string.Empty)
{
string sql_1 = string.Format("delete from datainfo where datainfo.AchievementName='{0}';", txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //删除成功
{
#region 更新DataGridView控件内的内容
try
{
DataSet ds = MyDatabase.GetDataSetBySql(sql_backup);
if (ds == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (ds.Tables[0].Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //导出此检索成果到Excel按钮可点击
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
this.txt_achievement_name = this.dgvData.Rows[0].Cells[0].Value.ToString().Trim();
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
#endregion
}
}
MessageBox.Show("删除失败！", "提示");
return;
}
else//取消
{
return;
}
}
private void btnUpdate_Click(object sender, EventArgs e) //更新此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
frmAchievementUpdate achievement_update = new frmAchievementUpdate(txt_achievement_name); //实例化成果更新窗体
achievement_update.ShowDialog(); //成果更新窗体以模式对话框的方式打开
#region 更新DataGridView控件内的内容
try
{
DataSet ds = MyDatabase.GetDataSetBySql(sql_backup);
if (ds == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (ds.Tables[0].Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //导出此检索成果到Excel按钮可点击
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
this.txt_achievement_name = this.dgvData.Rows[0].Cells[0].Value.ToString().Trim();
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
#endregion
}
private void btnDelete_Click(object sender, EventArgs e) //删除此项成果
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
DialogResult result = MessageBox.Show("是否确定删除此项成果？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)//确定
{
if (txt_achievement_name != string.Empty)
{
string sql_1 = string.Format("delete from datainfo where datainfo.AchievementName='{0}';", txt_achievement_name); //sql语句
if (MyDatabase.UpdateDataBySql(sql_1)) //删除成功
{
#region 更新DataGridView控件内的内容
try
{
DataTable dt = MyDatabase.GetDataTableBySql(sql_backup);
if (dt == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (dt.Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = true; //导出此检索成果到文件按钮可点击
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt; //DataGridView控件显示数据
this.txt_achievement_name = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[0].Value.ToString().Trim());
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToFile.Text = "导出此检索成果到文件"; //导出此检索成果到文件按钮显示内容
btnOutToFile.Enabled = false; //导出此检索成果到文件按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
return;
}
#endregion
}
}
MessageBox.Show("删除失败！", "提示");
return;
}
else//取消
{
return;
}
}
private void btnSearch_Click(object sender, EventArgs e) //检索
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (cbAchievementMoney.Checked == false) //支撑基金不限制未选中
{
if (txtAchievementMoney.Text.ToString().Trim() == string.Empty) //支撑基金为空
{
MessageBox.Show("如果检索不打算限制支撑基金，请将支撑基金后面的不限制选中，否则请输入支撑基金，支撑基金不能为空！", "提示");
return;
}
try//对支撑基金数据格式进行校检
{
Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim());
}
catch (Exception ex)
{
MessageBox.Show("支撑基金信息输入格式有误，请重新检查之后再重试！", "提示");
//MessageBox.Show(ex.Message);
//throw new Exception(ex.Message);
return;
}
}
if (cbAchievementDate.Checked == false) //成果时间不限制未选中
{
try //对成果时间数据格式进行校检
{
Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()).ToString("yyyy-MM-dd"); //最后加入ToString("yyyy-MM-dd")是为了去除时间只留下日期
}
catch (Exception ex)
{
MessageBox.Show("时间信息输入格式有误，请重新检查之后再重试！", "提示");
//MessageBox.Show(ex.Message);
//throw new Exception(ex.Message);
return;
}
}
string sql_result = string.Empty; //最终搜索的sql语句
string sql_temp = string.Empty; //生成最终搜索sql语句过程中的临时sql语句字符串变量
sql_result = "select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where";
sql_temp = string.Format(" AchievementName like '%{0}%' ", this.txtAchievementName.Text.ToString().Trim());
sql_result = sql_result + sql_temp + this.cboLogic.SelectedItem.ToString().Trim();
sql_temp = string.Format(" AchievementType like '%{0}%' ", this.txtAchievementType.Text.ToString().Trim());
sql_result = sql_result + sql_temp + this.cboLogic.SelectedItem.ToString().Trim();
if (cbAchievementDate.Checked == false)//成果时间不限制未选中
{
sql_temp = string.Format(" AchievementDate like '%{0}%' ", Convert.ToDateTime(this.dtpAchievementDate.Text.ToString().Trim()).ToString("yyyy-MM-dd"));
sql_result = sql_result + sql_temp + this.cboLogic.SelectedItem.ToString().Trim();
}
sql_temp = string.Format(" AchievementAuthor like '%{0}%' ", this.txtAchievementAuthor.Text.ToString().Trim());
sql_result = sql_result + sql_temp + this.cboLogic.SelectedItem.ToString().Trim();
sql_temp = string.Format(" AchievementCompany like '%{0}%'", this.txtAchievementCompany.Text.ToString().Trim());
sql_result = sql_result + sql_temp;
if (cbAchievementMoney.Checked == false)//支撑基金不限制未选中
{
sql_result = sql_result + " " + this.cboLogic.SelectedItem.ToString().Trim();
sql_temp = string.Format(" (AchievementMoney between {0} and {1})", Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim()) - 0.01, Convert.ToDouble(this.txtAchievementMoney.Text.ToString().Trim()) + 0.01);
sql_result = sql_result + sql_temp;
}
sql_result = sql_result + ";";
try
{
DataSet ds = MyDatabase.GetDataSetBySql(sql_result);
if (ds == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (ds.Tables[0].Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("未检索到符合要求的成果信息！", "提示");
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //导出此检索成果到Excel按钮可点击
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
this.txt_achievement_name = this.dgvData.Rows[0].Cells[0].Value.ToString().Trim();
sql_backup = sql_result;
MessageBox.Show("检索成功！", "提示");
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
sql_backup = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("检索失败！", "提示");
return;
}
}
private void btnSearch_Click(object sender, EventArgs e) //检索
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try//防止将所有成果类型均删除后，成果类型为空时检索，会导致未引用的异常
{
if (this.cboAchievementType.SelectedItem.ToString() == string.Empty)
{
MessageBox.Show("成果类型选择有误", "提示");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("无任何待检索成果类型，检索失败！", "提示");
return;
}
try
{
string sql = string.Format("select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementType='{0}';", this.cboAchievementType.SelectedItem.ToString().Trim());
DataSet ds = MyDatabase.GetDataSetBySql(sql);
if (ds == null) //数据库连接失败
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (ds.Tables[0].Rows.Count == 0) //未检索到符合要求的成果信息
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("未检索到符合要求的成果信息！", "提示");
return;
}
btnDelete.Enabled = true; //删除此项成果按钮可点击
btnUpdate.Enabled = true; //更新此项成果按钮可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = true; //导出此检索成果到Excel按钮可点击
this.dgvData.AutoGenerateColumns = true; //自动
this.dgvData.DataSource = ds.Tables[0]; //DataGridView控件显示数据
this.dgvData.Columns[0].ReadOnly = true; //设为只读
this.dgvData.Columns[1].ReadOnly = true; //设为只读
this.dgvData.Columns[2].ReadOnly = true; //设为只读
this.dgvData.Columns[3].ReadOnly = true; //设为只读
this.dgvData.Columns[4].ReadOnly = true; //设为只读
this.dgvData.Columns[5].ReadOnly = true; //设为只读
this.txt_achievement_name = this.dgvData.Rows[0].Cells[0].Value.ToString().Trim();
this.txt_achievement_type = this.dgvData.Rows[0].Cells[1].Value.ToString().Trim();
MessageBox.Show("检索成功！", "提示");
return;
}
catch (Exception ex)
{
btnDelete.Enabled = false; //删除此项成果按钮不可点击
btnUpdate.Enabled = false; //更新此项成果按钮不可点击
btnOutToExcel.Text = "导出此检索成果到Excel"; //导出此检索成果到Excel按钮显示内容
btnOutToExcel.Enabled = false; //导出此检索成果到Excel按钮不可点击
txt_achievement_name = string.Empty; //记录数据库内主键的值为空
txt_achievement_type = string.Empty;
this.dgvData.DataSource = null; //DataGridView控件显示数据
MessageBox.Show("检索失败！", "提示");
return;
}
}
private void btnAdd_Click(object sender, EventArgs e) //开始导入
{
if (MyDatabase.TestMyDatabaseConnect() == false) //数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
if (txtFilePath.Text == string.Empty)//判断文件路径是否为空
{
MessageBox.Show("文件路径不能为空！", "提示");
return;
}
if (System.IO.File.Exists(txtFilePath.Text) == false)//判断文件是否存在
{
MessageBox.Show("文件不存在！", "提示");
return;
}
try
{
btnAdd.Text = "正在导入中。。。"; //开始导入按钮显示内容
btnAdd.Enabled = false; //开始导入按钮不可点击
System.Data.DataTable dt = new System.Data.DataTable();
if (MyData.GetFileExtByFileName(txtFilePath.Text) == "xlsx" || MyData.GetFileExtByFileName(txtFilePath.Text) == "xls") //从Excel文件导入
{
if (MyExcel.CheckExcelFileData(txtFilePath.Text, 6) == false)
{
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
MessageBox.Show("文件数据格式有误，导入失败！", "提示");
return;
}
dt = MyExcel.LoadDataFromExcel(txtFilePath.Text);
if (MyExcel.DateTimeConvert(dt) == false) //从Excel文件中读取OA数据格式的日期文件后转换为字符串
{
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
MessageBox.Show("文件数据格式有误，导入失败！", "提示");
return;
}
}
else if (MyData.GetFileExtByFileName(txtFilePath.Text) == "txt")//从txt文件导入
{
if (MyTxt.CheckTxtFileData(txtFilePath.Text, 6) == false)
{
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
MessageBox.Show("文件数据格式有误，导入失败！", "提示");
return;
}
dt = MyTxt.LoadDataFromTxt(txtFilePath.Text);
}
else//扩展名有误
{
btnAdd.Text = "开始导入";//开始导入按钮显示内容
btnAdd.Enabled = true;//开始导入按钮可点击
MessageBox.Show("文件类型为不支持的导入类型，导入失败！", "提示");
return;
}
#region 将从文件导入的数据添加到数据库中
System.Data.DataTable dt_success = new System.Data.DataTable();
System.Data.DataTable dt_fail = new System.Data.DataTable();
int success_num = 0;
int fail_num = 0;
for (int i = 0; i < dt.Columns.Count; i++)//写入表头信息
{
dt_success.Columns.Add(dt.Columns[i].ColumnName);
dt_fail.Columns.Add(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
try
{
string sql = string.Empty;
sql = string.Format("insert into datainfo values('{0}', '{1}', '{2}', '{3}', '{4}', {5});", dt.Rows[i][0].ToString().Trim(), dt.Rows[i][1].ToString().Trim(), Convert.ToDateTime(dt.Rows[i][2].ToString().Trim()).ToString("yyyy-MM-dd"), dt.Rows[i][3].ToString().Trim(), dt.Rows[i][4].ToString().Trim(), Convert.ToDouble(dt.Rows[i][5].ToString().Trim())); //sql语句
if (MyDatabase.UpdateDataBySql(sql)) //添加成功
{
success_num++;
DataRow dr = dt_success.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
if (j == 2)//对成果时间的数据格式的单独转化
{
dr[j] = Convert.ToDateTime(dt.Rows[i][j].ToString().Trim()).ToString("yyyy-MM-dd");
}
else if (j == 5)//对成果支撑基金的格式的单独转化
{
dr[j] = Convert.ToDouble(dt.Rows[i][j].ToString().Trim());
}
else//其他字段格式不需要转化
{
dr[j] = dt.Rows[i][j].ToString();
}
}
catch (Exception ex_2)//格式转化出错
{
dr[j] = "(数据格式有误)".ToString();
}
}
dt_success.Rows.Add(dr);
}
else//添加失败
{
fail_num++;
DataRow dr = dt_fail.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
if (j == 2)//对成果时间的数据格式的单独转化
{
dr[j] = Convert.ToDateTime(dt.Rows[i][j].ToString().Trim()).ToString("yyyy-MM-dd");
}
else if (j == 5)//对成果支撑基金的格式的单独转化
{
dr[j] = Convert.ToDouble(dt.Rows[i][j].ToString().Trim());
}
else//其他字段格式不需要转化
{
dr[j] = dt.Rows[i][j].ToString();
}
}
catch (Exception ex_2)//格式转化出错
{
dr[j] = "(数据格式有误)".ToString();
}
}
dt_fail.Rows.Add(dr);
}
}
catch (Exception ex)//添加时发生异常
{
fail_num++;
DataRow dr = dt_fail.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
if (j == 2)//对成果时间的数据格式的单独转化
{
dr[j] = Convert.ToDateTime(dt.Rows[i][j].ToString().Trim()).ToString("yyyy-MM-dd");
}
else if (j == 5)//对成果支撑基金的格式的单独转化
{
dr[j] = Convert.ToDouble(dt.Rows[i][j].ToString().Trim());
}
else//其他字段格式不需要转化
{
dr[j] = dt.Rows[i][j].ToString();
}
}
catch (Exception ex_2)//格式转化出错
{
dr[j] = "(数据格式有误)".ToString();
}
}
dt_fail.Rows.Add(dr);
}
}
txtResultSuccess.Text = success_num.ToString(); //导入成功成果数目
txtResultFail.Text = fail_num.ToString(); //导入失败成果数目
if (success_num == 0)
{
btnOutToFileSuccess.Text = "将导入成功的成果导出到文件"; //将导入成功的成果导出到文件按钮显示内容
btnOutToFileSuccess.Enabled = false; //将导入成功的成果导出到文件按钮不可点击
this.dgvDataSuccess.DataSource = null; //导入成功的信息显示DataGridView控件显示数据
}
else
{
btnOutToFileSuccess.Text = "将导入成功的成果导出到文件"; //将导入成功的成果导出到文件按钮显示内容
btnOutToFileSuccess.Enabled = true; //将导入成功的成果导出到文件按钮可点击
dgvDataSuccess.DataSource = dt_success; //导入成功的信息显示DataGridView控件显示数据
}
if (fail_num == 0)
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = false; //将导入失败的成果导出到文件按钮不可点击
this.dgvDataFail.DataSource = null; //导入失败的信息显示DataGridView控件显示数据
}
else
{
btnOutToFileFail.Text = "将导入失败的成果导出到文件"; //将导入失败的成果导出到文件按钮显示内容
btnOutToFileFail.Enabled = true; //将导入失败的成果导出到文件按钮可点击
dgvDataFail.DataSource = dt_fail; //导入失败的信息显示DataGridView控件显示数据
}
#endregion
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
MessageBox.Show("文件导入完成！", "提示");
return;
}
catch (Exception ex_1)
{
btnAdd.Text = "开始导入"; //开始导入按钮显示内容
btnAdd.Enabled = true; //开始导入按钮可点击
MessageBox.Show("文件导入失败！", "提示");
return;
}
}
private void frmMechanicalDrawing_Load(object sender, EventArgs e) //窗体载入时初始化
{
txtAchievementName.Text = string.Empty; //成果名称显示内容
txtSaveDirectory.Text = string.Empty; //成果保存目录显示内容
txtPartNum.Text = "0"; //零件图个数显示内容
txtAssemblyNum.Text = "0"; //装配图个数显示内容
txtDrawingNum.Text = "0"; //工程图纸个数显示内容
this.dgvData.DataSource = null; //机械图信息显示DataGridView控件显示数据
this.dgvData.AllowUserToAddRows = false; //不允许用户添加新行
btnCount.Text = "开始统计个数信息"; //设置开始统计个数信息按钮显示内容
btnCount.Enabled = true; //开始统计个数信息按钮可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = false; //设置导出此视图内容到文件按钮不可点击
btnUpdate.Enabled = false; //更新机械图信息按钮不可点击
#region 所用软件组合框初始化
this.cboSoftware.Items.Clear();//清空combox内所有选项
this.cboSoftware.Items.Add("CAD");
this.cboSoftware.Items.Add("UG");
this.cboSoftware.Items.Add("Solidworks");
this.cboSoftware.Items.Add("Inventor");
this.cboSoftware.Items.Add("ProE");
this.cboSoftware.Items.Add("Catia");
this.cboSoftware.Items.Add("(其他)");
this.cboSoftware.Items.Add("(Unknown)"); //所用软件未知，数据库内该字段的默认值
this.cboSoftware.SelectedIndex = 7; //选中默认值"(Unknown)"
#endregion
if (MyDatabase.TestMyDatabaseConnect() == false)//数据库连接失败
{
MessageBox.Show("数据库连接失败！", "提示");
return;
}
try
{
string sql_1 = "select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金' from datainfo where AchievementType='机械图';";
DataTable dt_1 = MyDatabase.GetDataTableBySql(sql_1);
if (dt_1.Rows.Count == 0) //未检索到任何机械图成果
{
this.dgvData.DataSource = null; //机械图信息显示DataGridView控件显示数据
btnUpdate.Enabled = false; //更新机械图信息按钮不可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = false; //设置导出此视图内容到文件按钮不可点击
MessageBox.Show("目前未有任何机械图成果，请先添加机械图成果！", "提示");
return;
}
for (int i = 0; i < dt_1.Rows.Count; i++)//对所有在mechanicaldrawing表中没有的机械图成果自动添加所有值为默认值的记录
{
string sql_2 = string.Format("select mechanicaldrawing.AchievementName as '成果名称', mechanicaldrawing.SaveDirectory as '成果保存目录', mechanicaldrawing.PartNum as '零件图个数', mechanicaldrawing.AssemblyNum as '装配图个数', mechanicaldrawing.DrawingNum as '工程图纸个数' from mechanicaldrawing where mechanicaldrawing.AchievementName='{0}';", MyDatabase.CharFilter(dt_1.Rows[i][0].ToString().Trim()));
DataTable dt_2 = MyDatabase.GetDataTableBySql(sql_2);
if (dt_2.Rows.Count == 0)//此机械图成果在mechanicaldrawing表无记录，添加所有值为默认值的记录
{
string sql_3 = string.Format("insert into mechanicaldrawing values('{0}', '(Unknown)', '(Null)', 0, 0, 0);", MyDatabase.CharFilter(dt_1.Rows[i][0].ToString().Trim()));
if (MyDatabase.UpdateDataBySql(sql_3) == false) //添加失败
{
this.dgvData.DataSource = null; //机械图信息显示DataGridView控件显示数据
btnUpdate.Enabled = false; //更新机械图信息按钮不可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = false; //设置导出此视图内容到文件按钮不可点击
MessageBox.Show("获取数据初始化时失败！", "提示");
return;
}
}
}
string sql_4 = "select datainfo.AchievementName as '成果名称', datainfo.AchievementType as '成果类型', datainfo.AchievementDate as '时间', datainfo.AchievementAuthor as '作者', datainfo.AchievementCompany as '单位', datainfo.AchievementMoney as '支撑基金', mechanicaldrawing.Software as '所用软件', mechanicaldrawing.SaveDirectory as '成果保存目录', mechanicaldrawing.PartNum as '零件图个数', mechanicaldrawing.AssemblyNum as '装配图个数', mechanicaldrawing.DrawingNum as '工程图纸个数' from datainfo join mechanicaldrawing on datainfo.AchievementName=mechanicaldrawing.AchievementName;";
DataTable dt_4 = MyDatabase.GetDataTableBySql(sql_4);
this.dgvData.DataSource = null; //先置为null，否则在右键删除DataGridView后再次检索后显示的字段顺序会有误
this.dgvData.DataSource = dt_4; //机械图信息显示DataGridView控件显示数据
btnUpdate.Enabled = true; //更新机械图信息按钮可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = true; //设置导出此视图内容到文件按钮可点击
this.txtAchievementName.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[0].Value.ToString().Trim());
this.cboSoftware.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[6].Value.ToString().Trim());
this.txtSaveDirectory.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[7].Value.ToString().Trim());
this.txtPartNum.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[8].Value.ToString().Trim());
this.txtAssemblyNum.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[9].Value.ToString().Trim());
this.txtDrawingNum.Text = MyDatabase.CharFilter(this.dgvData.Rows[0].Cells[10].Value.ToString().Trim());
return;
}
catch (Exception ex)
{
this.dgvData.DataSource = null; //机械图信息显示DataGridView控件显示数据
btnUpdate.Enabled = false; //更新机械图信息按钮不可点击
btnOutToFile.Text = "导出此视图内容到文件"; //设置导出此视图内容到文件按钮显示内容
btnOutToFile.Enabled = false; //设置导出此视图内容到文件按钮不可点击
MessageBox.Show("机械图管理界面初始化失败！", "提示");
return;
}
}