searchform.cs

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

		private void cbTask_CheckedChanged(object sender, System.EventArgs e)
		{
			this.tbTaskName.Enabled = this.cbTask.Checked;
		}

		private void cbCollege_CheckedChanged(object sender, System.EventArgs e)
		{
			this.cmbCollege.Enabled = this.cbCollege.Checked;
		}

		private void cbCollegeMain_CheckedChanged(object sender, System.EventArgs e)
		{
			this.cmbCollegeMain.Enabled = this.cbCollegeMain.Checked;
		}

		private void cbMemberMain_CheckedChanged(object sender, System.EventArgs e)
		{
			this.cmbMemberMain.Enabled = this.cbMemberMain.Checked;
		}

		private void cbCollegeNa_CheckedChanged(object sender, System.EventArgs e)
		{
			this.cmbCollegeNa.Enabled = this.cbCollegeNa.Checked;
		}

		private void cbMemberNa_CheckedChanged(object sender, System.EventArgs e)
		{
			this.cmbMemberNa.Enabled = this.cbMemberNa.Checked;
		}
		#endregion

		#region /* 从数据库表中读取“分类信息”、“级别信息”、“院系信息”、“成员信息” */
		/// <summary>
		/// 读取分类信息。
		/// </summary>
		private void ReadSort()
		{
			string selectStr = "select SortCo, SortNa from Sort order by SortNa";

			this.ResetLC(this.lbSortCo, this.cmbSortNa, "请选择分类");
			this.ReadLC(this.lbSortCo, this.cmbSortNa, selectStr);
		}

		/// <summary>
		/// 读取级别信息。
		/// </summary>
		private void ReadLevel()
		{
			string selectStr = "select LevelCo, LevelNa from Level order by LevelNa";

			this.ResetLC(this.lbLevelCo, this.cmbLevelNa, "请选择级别");
			this.ReadLC(this.lbLevelCo, this.cmbLevelNa, selectStr);
		}

		/// <summary>
		/// 读取院系信息。
		/// </summary>
		private void ReadCollege()
		{
			string selectStr = "select CollegeCo, CollegeNa from College order by CollegeNa";

			this.ResetLC(this.lbCollegeCo, this.cmbCollege, "请选择院系");
			this.ReadLC(this.lbCollegeCo, this.cmbCollege, selectStr);

			this.cmbCollegeMain.Items.Clear();
			this.cmbCollegeNa.Items.Clear();
			for(int i = 0; i < this.cmbCollege.Items.Count; i++)
			{
				this.cmbCollegeMain.Items.Add(this.cmbCollege.Items[i]);
				this.cmbCollegeNa.Items.Add(this.cmbCollege.Items[i]);
			}
			this.cmbCollegeMain.SelectedIndex = 0;
			this.cmbCollegeNa.SelectedIndex = 0;
		}

		/// <summary>
		/// 读取成员信息。
		/// </summary>
		private void ReadMember()
		{
			int collegeCo = Convert.ToInt32(this.lbCollegeCo.Items[this.cmbC.SelectedIndex]);
			string selectStr = "select MemberCo, MemberNa from Member where CollegeCo = " + collegeCo + " order by MemberNa";

			this.ResetLC(this.lbM, this.cmbM, "请选择成员");
			this.ReadLC(this.lbM, this.cmbM, selectStr);
		}

		private void cmbSortNa_SelectionChangeCommitted(object sender, System.EventArgs e)
		{
			this.cmbSortNa.Tag = this.lbSortCo.Items[this.cmbSortNa.SelectedIndex];
		}

		private void cmbLevelNa_SelectionChangeCommitted(object sender, System.EventArgs e)
		{
			this.cmbLevelNa.Tag = this.lbLevelCo.Items[this.cmbLevelNa.SelectedIndex];
		}

		private void cmbCollegeNa_SelectionChangeCommitted(object sender, System.EventArgs e)
		{
			int tag = Convert.ToInt32(((ComboBox)sender).Parent.Tag);
			this.SetTools(tag);

			if(this.cmbC.SelectedIndex == 0)	//选择“请选择院系”
			{
				this.cbM.Enabled = false;
				this.cmbM.Enabled = false;

				this.ResetLC(this.lbM, this.cmbM, "请选择成员");
			}
			else	//选择某一院系
			{                
				this.ReadMember();

				this.cbM.Enabled = true;
				this.cmbM.Enabled = this.cbM.Checked;
			}

			this.cmbC.Tag = this.lbCollegeCo.Items[this.cmbC.SelectedIndex];
		}

		private void cmbMemberNa_SelectionChangeCommitted(object sender, System.EventArgs e)
		{
			int tag = Convert.ToInt32(((ComboBox)sender).Parent.Tag);
			this.SetTools(tag);

			this.cmbM.Tag = this.lbM.Items[this.cmbM.SelectedIndex];
		}

		private void cmbCollege_SelectionChangeCommitted(object sender, System.EventArgs e)
		{
			this.cmbCollege.Tag = this.lbCollegeCo.Items[this.cmbCollege.SelectedIndex];
		}

		/// <summary>
		/// 重新设置相关控件。
		/// </summary>
		/// <param name="listBox">设置哪个 ListBox 。</param>
		/// <param name="comboBox">设置哪个 ComboBox 。</param>
		/// <param name="chooseStr">选择字段。</param>
		private void ResetLC(ListBox listBox, ComboBox comboBox, string chooseStr)
		{
			listBox.Items.Clear();
			comboBox.Items.Clear();

			listBox.Items.Insert(0, 0);
			comboBox.Items.Insert(0, chooseStr);

			comboBox.SelectedIndex = 0;
		}

		/// <summary>
		/// 将读取的信息绑定到相关控件中。
		/// </summary>
		/// <param name="listBox">绑定到哪个 ListBox 。</param>
		/// <param name="comboBox">绑定到哪个 ComboBox 。</param>
		/// <param name="selectStr">SQL 查询语句。</param>
		private void ReadLC(ListBox listBox, ComboBox comboBox, string selectStr)
		{
			try
			{
				this.sqlConnection.Open();

				this.sqlCommand.CommandText = selectStr;
				this.sqlDataReader = this.sqlCommand.ExecuteReader();
				while(this.sqlDataReader.Read())
				{
					listBox.Items.Insert(1, this.sqlDataReader[0]);
					comboBox.Items.Insert(1, this.sqlDataReader[1]);
				}
				this.sqlDataReader.Close();
			}
			catch(Exception caught)
			{
				MessageBox.Show(caught.Message);
			}
			finally
			{
				this.sqlConnection.Close();
			}
		}
		#endregion

		#region /* 公有属性 */
		/// <summary>
		/// 设置该窗体所使用的 SqlConnection。
		/// </summary>
		public System.Data.SqlClient.SqlConnection Connection
		{
			set
			{
				this.sqlConnection = value;
				this.sqlCommand.Connection = this.sqlConnection;
			}
		}

		/// <summary>
		/// 获取 SQL 查询语句的 WHERE 子句。
		/// </summary>
		public string QueryCondition
		{
			get
			{
				return this.queryCondition;
			}
		}
		#endregion

		private void SetTools(int tag)
		{
			switch(tag)
			{
				case 0:
				{
					this.cmbC = this.cmbCollegeMain;
					this.cmbM = this.cmbMemberMain;
					this.cbM = this.cbMemberMain;
					this.lbM = this.lbMemberMain;
					break;
				}
				case 1:
				{
					this.cmbC = this.cmbCollegeNa;
					this.cmbM = this.cmbMemberNa;
					this.cbM = this.cbMemberNa;
					this.lbM = this.lbMember;
					break;
				}
				default:
				{
					break;
				}
			}
		}

		/// <summary>
		/// 生成 SQL 查询语句的 WHERE 子句。
		/// </summary>
		private void CreateQueryCondition()
		{
			this.queryCondition = " where ";

			if(this.cbSort.Checked && this.cmbSortNa.SelectedIndex != 0)
			{
				this.queryCondition += "SortCo = " + this.cmbSortNa.Tag + " and ";
			}

			if(this.cbLevel.Checked && this.cmbLevelNa.SelectedIndex != 0)
			{
				this.queryCondition += "LevelCo = " + this.cmbLevelNa.Tag + " and ";
			}

			if(this.cbYear.Checked)
			{
				this.queryCondition += "year(Year) = " + this.dtpYear.Value.Year + " and ";
			}

			if(this.cbStart.Checked)
			{
				this.queryCondition += "StartDate = \'" + this.tbStart.Text.Trim() + "\' and ";
			}

			if(this.cbPlan.Checked)
			{
				this.queryCondition += "PlanDate = \'" + this.tbPlan.Text.Trim() + "\' and ";
			}

			if(this.cbEnd.Checked)
			{
				this.queryCondition += "EndDate = \'" + this.tbEnd.Text.Trim() + "\' and ";
			}

			if(this.cbStandard.Checked)
			{
				this.queryCondition += "StandardMoney = " + this.nudStandard.Value + " and ";
			}

			if(this.cbAlready.Checked)
			{
				this.queryCondition += "AlreadyMoney = " + this.nudAlready.Value + " and ";
			}

			if(this.cbSurplus.Checked)
			{
				this.queryCondition += "SurplusMoney = " + this.nudSurplus.Value + " and ";
			}

			if(this.cbTask.Checked)
			{
				this.queryCondition += "TaskNa = \'" + this.tbTaskName.Text.Trim() + "\' and ";
			}

			if(this.cbCollege.Checked && this.cmbCollege.SelectedIndex != 0)
			{
				this.queryCondition += "CollegeCo = " + this.cmbCollege.Tag + " and ";
			}

			if(this.cbCollegeMain.Checked)
			{
				if(this.cbMemberMain.Checked && this.cmbMemberMain.SelectedIndex != 0)
				{
					this.queryCondition += "MainMember = " + this.cmbMemberMain.Tag + " and ";
				}
				else
				{
					this.queryCondition += "MainMember in(" + "select MemberCo from Member where CollegeCo = ";
					this.queryCondition += this.cmbCollegeMain.Tag + ") and ";
				}
			}

			if(this.cbCollegeNa.Checked)
			{
				if(this.cbMemberNa.Checked && this.cmbMemberNa.SelectedIndex != 0)
				{
					this.queryCondition += this.QueryByMC(true, this.cmbMemberNa.Tag) + " and ";
				}
				else
				{
					this.queryCondition += this.QueryByMC(false, this.cmbCollegeNa.Tag) + " and ";
				}
			}

			if(this.queryCondition.Length > 7)
			{
				this.queryCondition = this.queryCondition.Remove((this.queryCondition.Length - 5), 5);
			}
			else
			{
				this.queryCondition = "";
			}
		}

		/// <summary>
		/// 通过参研人信息查询课题。
		/// </summary>
		/// <param name="byMemberCo">是否根据成员编号（还是根据成员所在院系编号）。</param>
		/// <param name="mc">成员编号或成员所在院系编号。</param>
		/// <returns>SQL 查询语句的部分 WHERE 子句。</returns>
		private string QueryByMC(bool byMemberCo, object mc)
		{
			string str;
			string queryByMC = "(";

			if(byMemberCo)
			{
				str = " in(select MemberCo from Member where CollegeCo = ";
			}
			else
			{
				str = " = (";
			}

			for(int i = 1; i < 6; i++)
			{
				queryByMC += "Member" + i.ToString() + str + mc + ") or ";
			}
			queryByMC = queryByMC.Remove((queryByMC.Length - 4), 4);
			queryByMC += ")";

			return queryByMC;
		}
	}
}