mainform.cs

《数据库原理及应用》-刘玉宝主编-源代码

			}
		}

		private void btnUpdate_Click(object sender, System.EventArgs e)
		{
			this.AddUpdateTask(this.tTask.Rows[this.dataGrid.CurrentRowIndex]);
		}

		private void btnSearch_Click(object sender, System.EventArgs e)
		{
			SearchForm searchForm = new SearchForm();
			searchForm.Connection = this.sqlConnection;
			searchForm.ShowDialog();

			if(searchForm.QueryCondition != null)
			{
				this.tTask.Rows.Clear();
				this.queryCondition = searchForm.QueryCondition;
				this.QueryTask(this.queryCondition);
			}
		}

		private void btnEduce_Click(object sender, System.EventArgs e)
		{
			//导出到 Excel 表
			KTTableName KTtn = new KTTableName();
			KTtn.ShowDialog();
			string KTTblName = KTtn.GetKTTblName();
			if(KTTblName == null)
			{
				return;
			}

			Excel.ApplicationClass appClass = new Microsoft.Office.Interop.Excel.ApplicationClass();
			if(appClass == null)
			{
				MessageBox.Show("不能打开Excel!");
				return;
			}
			appClass.Visible = true;
			Excel.Workbooks workbooks = appClass.Workbooks;
			Excel.Workbook workbook = workbooks.Add("");
			Excel.Sheets sheets = workbook.Worksheets;
			Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
			if (worksheet == null) 
			{ 
				Console.WriteLine ("ERROR: worksheet == null"); 
				return;
			}
			int row = 0;
			if(KTTblName!=null)
			{
				worksheet.Cells[1,1] = KTTblName;
				Microsoft.Office.Interop.Excel.Range r = worksheet.get_Range("A1",Missing.Value);
				r.HorizontalAlignment = 2;
				r = worksheet.get_Range("A1","M1");
				r.Select();
				r.Merge(true);
				row = 1;
			}
			worksheet.Cells[1+row,1] = "课题编号";
			Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 10;

			worksheet.Cells[1+row,2] = "课题名称";
			range = worksheet.get_Range("B"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 50;

			worksheet.Cells[1+row,3] = "院系名称";
			range = worksheet.get_Range("C"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 20;

			worksheet.Cells[1+row,4] = "负责人及主要合作者";
			range = worksheet.get_Range("D"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 50;

			worksheet.Cells[1+row,5] = "立项日期";
			range = worksheet.get_Range("E"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 15;

			worksheet.Cells[1+row,6] = "拟结项日期";
			range = worksheet.get_Range("F"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 15;

			worksheet.Cells[1+row,7] = "结项日期";
			range = worksheet.get_Range("G"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 15;

			worksheet.Cells[1+row,8] = "经费标准";
			range = worksheet.get_Range("H"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 10;

			worksheet.Cells[1+row,9] = "已拨经费";
			range = worksheet.get_Range("I"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 10;

			worksheet.Cells[1+row,10] = "剩余经费";
			range = worksheet.get_Range("J"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 10;

			worksheet.Cells[1+row,11] = "联系电话";
			range = worksheet.get_Range("K"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 15;

			worksheet.Cells[1+row,12] = "Email";
			range = worksheet.get_Range("L"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 30;

			worksheet.Cells[1+row,13] = "备注";
			range = worksheet.get_Range("M"+(row+1),Missing.Value);
			range.HorizontalAlignment = 2;
			range.ColumnWidth = 50;

			DataTable dt = this.tTask;
			DataRow dataRow;

			range = worksheet.get_Range("A"+(row+1),"M"+(dt.Rows.Count+row+1));
			range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
			range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
			range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
			range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
			range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
			range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;

			for(int i = 0;i<dt.Rows.Count;i++)
			{
				dataRow = dt.Rows[i];

				worksheet.Cells[2+row+i,1] = dataRow["课题编号"];
				range = worksheet.get_Range("A"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;
				
				worksheet.Cells[2+row+i,2] = dataRow["课题名称"];
				range = worksheet.get_Range("B"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,3] = dataRow["院系名称"];
				range = worksheet.get_Range("C"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,4] = dataRow["负责人及主要合作者"];
				range = worksheet.get_Range("D"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,5] = dataRow["立项日期"];
				range = worksheet.get_Range("E"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,6] = dataRow["拟结项日期"];
				range = worksheet.get_Range("F"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,7] = dataRow["结项日期"];
				range = worksheet.get_Range("G"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,8] = dataRow["经费标准"];
				range = worksheet.get_Range("H"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,9] = dataRow["已拨经费"];
				range = worksheet.get_Range("I"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,10] = dataRow["剩余经费"];
				range = worksheet.get_Range("J"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,11] = dataRow["联系电话"];
				range = worksheet.get_Range("K"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,12] = dataRow["Email"];
				range = worksheet.get_Range("L"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;

				worksheet.Cells[2+row+i,13] = dataRow["备注"];
				range = worksheet.get_Range("M"+(row+2+i),Missing.Value);
				range.HorizontalAlignment = 2;
			}
		}

		private void btnS_Manage_Click(object sender, System.EventArgs e)
		{
			Sort sortForm = new Sort();
			sortForm.Connection = this.sqlConnection;
			sortForm.ShowDialog();

			this.AfterManage();
		}

		private void btnL_Manage_Click(object sender, System.EventArgs e)
		{
			Level levelForm = new Level();
			levelForm.Connection = this.sqlConnection;
			levelForm.ShowDialog();

			this.AfterManage();
		}

		private void btnC_Manage_Click(object sender, System.EventArgs e)
		{
			college collegeForm = new college();
			collegeForm.Connection = this.sqlConnection;
			collegeForm.ShowDialog();

			this.AfterManage();
		}

		private void btnM_Manage_Click(object sender, System.EventArgs e)
		{
			Member memberForm = new Member();
			memberForm.Connection = this.sqlConnection;
			memberForm.ShowDialog();

			this.AfterManage();
		}

		private void btnAbout_Click(object sender, System.EventArgs e)
		{
			//
		}

		private void btnHelp_Click(object sender, System.EventArgs e)
		{
			//
		}

		/// <summary>
		/// 添加、修改课题。
		/// </summary>
		/// <param name="dataRow">当修改课题时，用于传递数据。</param>
		private void AddUpdateTask(System.Data.DataRow dataRow)
		{
			AddUpdateForm addUpdateForm = new AddUpdateForm();
			addUpdateForm.Connection = this.sqlConnection;
			addUpdateForm.DataRow = dataRow;
			addUpdateForm.ShowForm();

			ArrayList array = addUpdateForm.NewTaskArrayList;
			if(array != null)
			{
				string queryCondition;
				for(int i = 0; i < array.Count; i++)
				{
					queryCondition = " where TaskID = " + array[i];
					this.QueryTask(queryCondition);
				}
			}
		}

		/// <summary>
		/// 删除课题。
		/// </summary>
		private void DeleteTask()
		{
			try
			{
				this.sqlConnection.Open();

				this.storedProcedure.CommandText = "DeleteTask";
				this.storedProcedure.Parameters.Clear();

				this.storedProcedure.Parameters.Add("@TaskID", System.Data.SqlDbType.Int);
				this.storedProcedure.Parameters["@TaskID"].Value = this.tTask.Rows[this.dataGrid.CurrentRowIndex][0];

				this.storedProcedure.ExecuteNonQuery();

				this.tTask.Rows.Remove(this.tTask.Rows[this.dataGrid.CurrentRowIndex]);

				this.dataGrid_CurrentCellChanged(null, null);
			}
			catch(Exception caught)
			{
				MessageBox.Show(caught.Message);
			}
			finally
			{
				this.sqlConnection.Close();
			}
		}

		/// <summary>
		/// 查询课题。
		/// </summary>
		/// <param name="queryCondition">SQL 查询语句中的 WHERE 子句。</param>
		private void QueryTask(string queryCondition)
		{
			try
			{
				this.sqlConnection.Open();

				this.sqlCommand.CommandText = "select TaskID, SortCo, LevelCo, Year, Serial, TaskNa, CollegeCo, CollegeNa, MemberName, MainMember";
				this.sqlCommand.CommandText += ", StartDate, PlanDate, EndDate, StandardMoney, AlreadyMoney, SurplusMoney";
				this.sqlCommand.CommandText += ", Tel, Email, Remark, Member1, Member2, Member3, Member4, Member5, SortPY, LevelPY";
				this.sqlCommand.CommandText += " from viewT" + queryCondition;
				this.sqlDataReader = this.sqlCommand.ExecuteReader();
				DataRow dataRow;
				while(this.sqlDataReader.Read())
				{
					dataRow = this.tTask.NewRow();

					dataRow["课题ID"] = this.sqlDataReader["TaskID"];
					dataRow["分类编号"] =this.sqlDataReader["SortCo"];
					dataRow["级别编号"] = this.sqlDataReader["LevelCo"];
					dataRow["年份"] = this.sqlDataReader["Year"];
					dataRow["序号"] = this.sqlDataReader["Serial"];
					dataRow["课题名称"] = this.sqlDataReader["TaskNa"];
					dataRow["所属院系"] = this.sqlDataReader["CollegeCo"];
					dataRow["院系名称"] = this.sqlDataReader["CollegeNa"];
					dataRow["负责人及主要合作者"] = this.sqlDataReader["MemberName"];
					dataRow["立项日期"] = this.sqlDataReader["StartDate"];
					dataRow["拟结项日期"] = this.sqlDataReader["PlanDate"];
					dataRow["结项日期"] = this.sqlDataReader["EndDate"];
					dataRow["经费标准"] = this.sqlDataReader["StandardMoney"];
					dataRow["已拨经费"] = this.sqlDataReader["AlreadyMoney"];
					dataRow["剩余经费"] = this.sqlDataReader["SurplusMoney"];
					dataRow["联系电话"] = this.sqlDataReader["Tel"];
					dataRow["Email"] = this.sqlDataReader["Email"];
					dataRow["备注"] = this.sqlDataReader["Remark"];
					dataRow["负责人"] = this.sqlDataReader["MainMember"];
					dataRow["参研人1"] = this.sqlDataReader["Member1"];
					dataRow["参研人2"] = this.sqlDataReader["Member2"];
					dataRow["参研人3"] = this.sqlDataReader["Member3"];
					dataRow["参研人4"] = this.sqlDataReader["Member4"];
					dataRow["参研人5"] = this.sqlDataReader["Member5"];
					dataRow["分类拼音"] = this.sqlDataReader["SortPY"];
					dataRow["级别拼音"] = this.sqlDataReader["LevelPY"];

					dataRow["课题编号"] = dataRow["分类拼音"].ToString();
					dataRow["课题编号"] += dataRow["级别拼音"].ToString();
					dataRow["课题编号"] += Convert.ToDateTime(dataRow["年份"]).ToString().Substring(2, 2);
					dataRow["课题编号"] += "-";
					dataRow["课题编号"] += dataRow["序号"].ToString().PadLeft(2, '0');

					this.tTask.Rows.Add(dataRow);
				}
				this.sqlDataReader.Close();
			}
			catch(Exception caught)
			{
				MessageBox.Show(caught.Message);
			}
			finally
			{
				this.sqlConnection.Close();
			}

			this.dataGrid_CurrentCellChanged(null, null);
		}

		private void AfterManage()
		{
			if(this.tTask.Rows.Count != 0)
			{
				this.tTask.Rows.Clear();
				this.QueryTask(this.queryCondition);
			}
		}

		private void dataGrid_CurrentCellChanged(object sender, System.EventArgs e)
		{
			if(this.tTask.Rows.Count == 0)
			{
				this.btnDelete.Enabled = false;
				this.btnUpdate.Enabled = false;
				this.btnEduce.Enabled = false;

				return;
			}
			else
			{
				this.btnDelete.Enabled = true;
				this.btnUpdate.Enabled = true;
				this.btnEduce.Enabled = true;
			}

			if(this.dataGrid[this.dataGrid.CurrentCell.RowNumber, 0] == Convert.DBNull)
			{
				this.btnDelete.Enabled = false;
				this.btnUpdate.Enabled = false;
			}
		}

		private void btnExit_Click(object sender, System.EventArgs e)
		{
			DialogResult dialogResult = MessageBox.Show("是否确定要退出本系统？", "系统提问", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
			if(dialogResult == DialogResult.Yes)
			{
				this.Close();
			}
		}

		private void timer_DateTime_Tick(object sender, System.EventArgs e)
		{
			DateTime dateTime = DateTime.Now;

			this.sbpDate.Text = dateTime.Year.ToString() + "年" + dateTime.Month.ToString() + "月" + dateTime.Day.ToString() + "日";
			this.sbpTime.Text = dateTime.ToShortTimeString();
		}		
	}
}