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 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 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 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 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 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 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 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;
}
}
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 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;
}
}
