工业学院数据库应用系统设计实验报告 __090501102 ： 毕 波 班级: 计算机091 指导 人事工资管理系统设计总说明：在当今社会，工资管理是一项必要而且很关键的工作现在随着企业数量的急剧增加，处理人们的工资数据变的越来越烦琐艰巨如今，计算机已经普与到了几乎每个学校、家庭，我们的学习和生活已经处处离不开计算机的存在本系统依据开发要求主要应用于企业人事系统，完成对日常的工资增删查改的数字化管理比较系统地对员工的信息和工资进行管理，查询、增添、修改、删除都变的非常简便，节省了大量的工作量本课程设计是在学习了《数据库应用系统》和相关开发软件课程之后，让学生通过实际项目的设计、开发，培养学生独立进行数据库软件的建模、在计算机中进行数据库设计、并通过相关软件开发系统的能力本系统的基本功能包括：部门信息的管理（查询、添加、修改、删除学生部门等）、职员信息的管理（录入、查询、修改、删除员工的信息等）、工资信息的管理（录入、查询、修改员工的工资等）本系统主要用于对员工工资进行管理，能够进行插入、删除、修改、查询和显示员工的信息。
登录该系统时，用户需要输入口令和密码，以确保数据的安全性，成功登录的用户，可以插入员工的信息和工资，并对员工的信息和工资进行增、删、改操作基于上述想法，我们将员工数据保存到数据库中我们要求系统能够高效快速的处理数据，并且要保证数据的正确性、相容性和安全性所以在数据库中需要定义很多触发器，比如删除了某个员工的信息则删除对应的所有工资信息、活着删除了某部门则删除该部门所有的信息等所以我们要从数据库中读取数据，并且和界面联系起来，同时也能将用户界面上的数据存储到数据库中以上是设计此系统应该注意的地方和设计原则，以下就是遵循这些原则和标准设计出的一套完整的管理系统一 系统需求： 根据题目需求，可以把系统分为三个部分：部门信息管理部分、职员信息管理部分和工资信息管理部分本次实验中，我主要负责部门信息管理模块，题中需要对部门信息进行查询、添加、修改、删除操作，这些操作按是否改变数据库数据可分为两类：查询操作，只读取数据库信息，不对信息做修改；删除、更新和添加操作，需要对数据库中的数据进行读写操作因此只要写两部分代码，查询部分:deptinfo_Query进行部门信息查询，查询数据库信息；信息管理部分：deptinfo_Manage，对信息进行更新、增加和删除。
二 数据库设计1.数据需求人事工资管理系统需要完成的主要功能有：1员工的基本信息录入2.工资信息表的录入3.部门信息表的查询、插入、修改、删除等操作2.相关表表1 职工表：Employee_Info表2 工资表：Salary表3 部门表：Dept_Info表4 系统用户表：User_Info3.数据流程图人事工资管理系统登录 系统管理员职工表部门表工资表图 数据流程图三 员工工资相关操作1.查询部分 相关代码：dateGrid1数据绑定privatevoid BindData(String sql) { SqlConnection connection = new SqlConnection(connString); SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); dataGridView1.DataSource = dataSet.Tables[0]; dataGridView1.Columns[0].DataPropertyName = "职¡ã工¡è号?"; dataGridView1.Columns[1].DataPropertyName = "年份"; dataGridView1.Columns[2].DataPropertyName = "月份"; dataGridView1.Columns[3].DataPropertyName = "工资º"; }部门组合框的数据绑定privatevoid BindDeptComBox() {string sql = "SELECT * FROM [Dept_Info]"; SqlConnection connection = new SqlConnection(connString); SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); DeptcomBox.DataSource = dataSet.Tables[0]; DeptcomBox.DisplayMember = "DeptName"; DeptcomBox.ValueMember = "DeptID"; DeptcomBox.SelectedIndex = 0; }按部门查询privatevoid check1_CheckedChanged(object sender, EventArgs e) {if (check1.Checked) { btnYes.Enabled = true; DeptcomBox.Enabled = true; check4.Checked = false; check4.Enabled = false; }else { DeptcomBox.Enabled = false; check4.Enabled = true; } }按年份查询privatevoid check2_CheckedChanged(object sender, EventArgs e) { btnYes.Enabled = true;if (check2.Checked) { YearComBox.Enabled = true; }else { YearComBox.Enabled = false; } YearComBox.SelectedIndex = 0; }按月份Y查询privatevoid check3_CheckedChanged(object sender, EventArgs e) {if (check3.Checked) { btnYes.Enabled = true; MonthComBox.Enabled = true; }else { MonthComBox.Enabled = false; } }仅查询自己的工资privatevoid check4_CheckedChanged(object sender, EventArgs e) {if (check4.Checked) { btnYes.Enabled = true; check1.Enabled = false; DeptcomBox.Enabled = false; }else { check1.Enabled = true; check1.Checked = false; } }privatevoid btnYes_Click(object sender, EventArgs e) { count = 0;String YearStr;int MonthStr;String DeptStr;String sqlStr = ""; sql = ""; DeptStr = Convert.ToString(DeptcomBox.SelectedValue.ToString()); 部门号if (check1.Checked) { DeptStr = Convert.ToString(DeptcomBox.SelectedValue.ToString()); sqlStr = String.Format("select [Salary].EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资º' from [Salary],[Employee_Info] where DeptID='{0}'and Salary.EmpID=Employee_Info.EmpID", DeptStr);if (count == 0) { sql += sqlStr; count = 1; }else { sql += " intersect " + sqlStr; } check1.Checked = false; }年份if (check2.Checked) { YearStr = Convert.ToString(YearComBox.SelectedItem.ToString());if(this.mainForm.operatorRight==2) { sqlStr = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from [Salary] where Year='{0}'", YearStr); }elseif(this.mainForm.operatorRight==1) { sqlStr = String.Format("select Salary.EmpID as '职工号', Year as '年份', Month as '月', Salary as '工资' from Salary,Employee_Info,Dept_Info where Year='{0}'and Salary.EmpID=Employee_Info.EmpID and Employee_Info.DeptID=Dept_Info.DeptID and Dept_Info.DeptID in (select DeptID from Employee_Info where EmpID='{1}')", YearStr,this.mainForm.person.userName); }else { sqlStr = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资Á' from [Salary] where Year='{0}'and EmpID='{1}'", YearStr,this.mainForm.person.userName); }if (count == 0) { sql += sqlStr; count = 1; }else { sql += " intersect " + sqlStr; } check2.Checked = false; }月份if (check3.Checked) { MonthStr = Convert.ToInt32(MonthComBox.SelectedItem.ToString());if (this.mainForm.operatorRight == 2) { sqlStr = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from [Salary] where Month='{0}'", MonthStr); }elseif (this.mainForm.operatorRight == 1) { sqlStr = String.Format("select Salary.EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary,Employee_Info,Dept_Info where Month='{0}'and Salary.EmpID=Employee_Info.EmpID and Employee_Info.DeptID=Dept_Info.DeptID and Dept_Info.DeptID in (select DeptID from Employee_Info where EmpID='{1}')", MonthStr, this.mainForm.person.userName); }else { sqlStr = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工' from [Salary] where Month='{0}' and EmpID='{1}'", MonthStr,this.mainForm.person.userName); }if (count == 0) { sql += sqlStr; count = 1; }else { sql += " intersect " + sqlStr; } check3.Checked = false; }自己工资if (check4.Checked) { sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary where EmpID='{0}'", this.mainForm.person.userName);if (count == 0) { sql += sqlStr; count = 1; }else { sql += " intersect " + sqlStr; } check4.Checked = false; } SqlCommand command = new SqlCommand(sql, connection);try { connection.Open(); SqlDataReader data = command.ExecuteReader();if (data.Read()) { BindData(sql); }else {if(this.mainForm.operatorRight==2) { sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary"); }elseif (this.mainForm.operatorRight == 1) { sql = String.Format("select Salary.EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary,Employee_Info,Dept_Info where Salary.EmpID=Employee_Info.EmpID and Employee_Info.DeptID=Dept_Info.DeptID and Dept_Info.DeptID in (select DeptID from Employee_Info where EmpID='{0}')", this.mainForm.person.userName); }else { sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary where EmpID='{0}'", this.mainForm.person.userName); } BindData(sql); MessageBox.Show("查询失败,没有符合要求的工资信息", "查找失败ã", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }catch (Exception ex) { MessageBox.Show(ex.Message, "数据库操作失败¹", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }finally { connection.Close(); } }privatevoid LoadOrCancel() { mainForm = (MainForm)this.ParentForm; YearComBox.SelectedIndex = 0; MonthComBox.SelectedIndex = 0; YearComBox.Enabled = false; MonthComBox.Enabled = false; DeptcomBox.Enabled = false; btnYes.Enabled = false; BindDeptComBox();if (this.mainForm.operatorRight == 2) { sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary"); }elseif (this.mainForm.operatorRight == 1) { DeptcomBox.Visible = false; check1.Visible = false; sql = String.Format("select Salary.EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary,Employee_Info,Dept_Info where Salary.EmpID=Employee_Info.EmpID and Employee_Info.DeptID=Dept_Info.DeptID and Dept_Info.DeptID in (select DeptID from Employee_Info where EmpID='{0}')", this.mainForm.person.userName); }else { check1.Visible = false; check4.Visible = false; DeptcomBox.Visible = false; sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary where EmpID='{0}'", this.mainForm.person.userName); } BindData(sql); count = 0; }privatevoid groupBox2_Enter(object sender, EventArgs e) { }2.对表的增删改部分：ataGrid数据绑定privatevoid BindData() { sql = String.Format("select EmpID as '职工号', Year as '年份', Month as '月份', Salary as '工资' from Salary"); SqlConnection connection = new SqlConnection(connString); SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); dataGridView1.DataSource = dataSet.Tables[0]; dataGridView1.Columns[0].DataPropertyName = "职工号"; dataGridView1.Columns[1].DataPropertyName = "年份"; dataGridView1.Columns[2].DataPropertyName = "月份"; dataGridView1.Columns[3].DataPropertyName = "工资"; }删除privatevoid btn3_Click(object sender, EventArgs e) { cleartext(); textreadwrite(); btnOkOrCancel(); combMonth.Enabled = true; combYear.Enabled = true; txt4.ReadOnly = true; opterate = "delete"; }修改或更新职工工资信息privatevoid btnOk_Click(object sender, EventArgs e) { btnOkOrCancel(); btnOk.Enabled = false; EmpID = txt1.Text; Year = Convert.ToString(combYear.SelectedItem.ToString()); Month = Convert.ToInt32(combMonth.SelectedItem.ToString());String salary = txt4.Text;if (salary == "") { salary = "0"; } Salary = (float)Convert.ToDouble(salary.Trim());int num = 0;try {if (EmpID != "") { sql = String.Format("select count (*) from [Employee_Info] where EmpID='{0}'", EmpID); SqlCommand command = new SqlCommand(sql, connection); connection.Open();int c = (int)command.ExecuteScalar(); connection.Close();if (c > 0) { connection = new SqlConnection(connString); connection.Open(); sql = String.Format("select count (*) from [Salary] where EmpID='{0}'and Year='{1}' and Month='{2}'", EmpID, Year, Month); SqlCommand mycmd = new SqlCommand(sql, connection); num = (int)mycmd.ExecuteScalar(); connection.Close();职工的工资信息存在if (num > 0) {if (opterate == "insert") { MessageBox.Show("录入不成功，您要录入的员工的工资信息已存在！", "录入失败", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); }elseif (opterate == "update") { sql = String.Format("update [Salary] set Salary='{0}'where EmpID='{1}' and Year='{2}' and Month='{3}'", Salary, EmpID, Year, Month); connection.Open(); command = new SqlCommand(sql, connection); command.ExecuteNonQuery(); connection.Close(); BindData(); textreadonly(); MessageBox.Show("更新成功！ ", "更新成功", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }else { connection.Open(); sql = String.Format("delete from [Salary] where EmpID='{0}'and Year='{1}' and Month='{2}'", EmpID, Year, Month); command = new SqlCommand(sql, connection); command.ExecuteNonQuery(); connection.Close(); BindData(); MessageBox.Show("删除成功！ ", "删除成功", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); } }else {if (opterate == "insert") { sql = String.Format("insert into [Salary](EmpID,Year,Month,Salary)values ('{0}','{1}','{2}','{3}')", EmpID, Year, Month, Salary); command = new SqlCommand(sql, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); BindData(); MessageBox.Show("录入成功！ ", "录入成功", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); }elseif (opterate == "update") { MessageBox.Show("修改失败，不存在要修改的工资信息！ ", "修T改失败", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); }else { MessageBox.Show("删除失败，不存在要删除的工资信息！", "删除失败", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); } } }else { MessageBox.Show("不存在要管理的员工工资信息！ ", "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); } }else { MessageBox.Show("操作失败，请填入员工号！", "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); textreadonly(); } btnModi(); btnCancel.Enabled = true; }catch (Exception ex) { MessageBox.Show(ex.Message, "数据库操作失败¹", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }finally { connection.Close(); } }3. 关于界面录入和修改键和关闭键能用，确定和取消键不能用privatevoid btnModi() { btn1.Enabled = true; btn2.Enabled = true; btn3.Enabled = true; btnOk.Enabled = false; btnCancel.Enabled = false; }录入和修改键不能用，确定和取消键和关闭键能用privatevoid btnOkOrCancel() { btn1.Enabled = false; btn2.Enabled = false; btn3.Enabled = false; btnCancel.Enabled = true; btnOk.Enabled = true; }将文本框清空privatevoid cleartext() { txt1.Text = ""; txt4.Text = ""; }取消按钮privatevoid btnCancel_Click(object sender, EventArgs e) { combYear.SelectedIndex = 0; combMonth.SelectedIndex = 0; combMonth.Enabled = false; combYear.Enabled = false; textreadonly(); btnModi(); BindData(); }录入按钮privatevoid btn1_Click(object sender, EventArgs e) { cleartext(); textreadwrite(); btnOkOrCancel(); combMonth.Enabled = true; combYear.Enabled = true; opterate = "insert"; }修改按钮privatevoid btn2_Click(object sender, EventArgs e) { cleartext(); textreadwrite(); btnOkOrCancel(); opterate = "update"; combMonth.Enabled = true; combYear.Enabled = true; }四 员工工资界面：五 课程设计总结：本次课程设计，和另外两个同学一起，完成了人事工资管理系统的设计，通过本次课程设计，提升了我们的团队协作能力，加强了我们动手、思考和解决问题的能力，检验了我所学习的知识，在设计过程中，与同学分工设计，和同学们相互探讨，相互学习，相互监督。
通过完成系统的设计，使自己对C#编写C/S系统有了进一步的认识，同时也提高了C#编程能力本次设计中用到了数据库、软件设计等，使得自己对所学的容更加清晰明了并使自己在各种工具的集成开发有了全新体会本次课程设计最大的收获是了解到了团队合作以与任务规划的重要性，对于目前的自己来说想要独立完成一个软件的设计是不现实的，分工合作可以很好地解决这个问题，但合作中也会出现很多问题，由于每个人独立设计一个模块，单独运行时没有问题，但最后系统功能综合运行时，由于每个人各个部分的设计思路不尽相同，会出很多错误，需要跟同组人员进行沟通，然后各自对自己的模块进行必要的修改，才能综合形成一个完整的可用的系统，由此可见在软件设计的过程中，合作是很重要的，并且每个成员间必须有很好的沟通，才能避免设计中出现的不兼容的错误，才能使工程做得完美在实验中，明白了C#的反馈机制：一个代理对象包括了访问一个特定对象的特定方法所需的信息.只要把它当成一个聪明的方法指针就行了代理对象可以被移动到另一个地方，然后可以通过访问它来对已存在的方法进行类型安全的调用.一个反馈方法是代理的特例对C#的强类型特点有了更深的理解本次实验对我的帮助非常之大。