C#人事工资管理系统实验报告

1、武汉学院数据库应用系统设计实验报告计算机091学号:姓名: 班级: 指导教师:090501102毕波人事工资管理系统设计总说明：在当今社会，工资管理是一项必要而且很关键的工作。现在随着企业数量 的急剧增加，处理人们的工资数据变的越来越烦琐艰巨。如今，计算机已经普及到了几乎每 个学校、家庭，我们的学习和生活已经处处离不开计算机的存在。本系统依据开发要求主要应用于企业人事系统，完成对日常的工资增删查改的数字化管 理。比较系统地对员工的信息和工资进行管理，查询、增添、修改、删除都变的非常简便， 节省了大量的工作量。本课程设计是在学习了数据库应用系统和相关开发软件课程之后，让学生通过实际 项目的设计、

2、开发，培养学生独立进行数据库软件的建模、在计算机中进行数据库设计、并 通过相关软件开发系统的能力。本系统的基本功能包括：部门信息的管理（查询、添加、修改、删除学生部门等）、职员信息的管理（录入、查询、修改、删除员工的信息等）、工资信息的管理（录入、查询、修改 员工的工资等）。本系统主要用于对员工工资进行管理，能够进行插入、删除、修改、查询和显示员工的 信息。登录该系统时，用户需要输入口令和密码，以确保数据的安全性，成功登录的用户， 可以插入员工的信息和工资，并对员工的信息和工资进行增、删、改操作。基于上述想法，我们将员工数据保存到数据库中。我们要求系统能够高效快速的处理数 据，并且要保证数据的

3、正确性、相容性和安全性。所以在数据库中需要定义很多触发器，比 如删除了某个员工的信息则删除对应的所有工资信息、活着删除了某部门则删除该部门所有 的信息等。所以我们要从数据库中读取数据，并且和界面联系起来，同时也能将用户界面上的数据 存储到数据库中。以上是设计此系统应该注意的地方和设计原则，以下就是遵循这些原则和 标准设计出的一套完整的管理系统。一系统需求：根据题目需求，可以把系统分为三个部分：部门信息管理部分、职员信息管理部分和工 资信息管理部分。本次实验中，我主要负责部门信息管理模块，题中需要对部门信息进行查 询、添加、修改、删除操作，这些操作按是否改变数据库数据可分为两类：查询操作，只读

4、取数据库信息，不对信息做修改；删除、更新和添加操作，需要对数据库中的数据进行读写 操作。因此只要写两部分代码，查询部分：de ptin fo_Query 进行部门信息查询，查询数据库 信息；信息管理部分：deptinfo_Manage，对信息进行更新、增加和删除。数据库设计1. 数据需求人事工资管理系统需要完成的主要功能有：1员工的基本信息录入2. 工资信息表的录入。3. 部门信息表的查询、插入、修改、删除等操作。2.相关表表 1 职工表：Empioyee_lnfo列名馥据类型允许Nd值 口 口 団口vardiar®EmpNameuarchar(lO)hlatioiialityvar

5、charCaDSexchar(Z)A护smallintTelephonevarcharC12)DeptIDvardiar(S)Positionvardiar(lO)Manager工资表:SalaryamplSfiy勻HD匕反出睜57血1虹1里451759戲越1逍虹血35C0：2lO攵反坯湃？W3虹醵弹蚩出护57血3虹2：342卿M：1虹Xi期鯛如观W尹国口炳7g虹lO攵1K?jWM昵哺i?7區皿炳7002虹lfl.1i嘲违¥區加胸m贬阮临ElOlJ蛋1 1刃3孵了005耐J13 図：0£37coollfl：S3&251孙诩;Scm;虹LINGKERONG-PC- d

6、bo.UerJnfo '' LINGKERONG-卩 III Il4il llh-i llHlll- IIIM-IIIII-HIII III ll-ri-ll lll-i II ll+ll 勒据粪型用 i EmpIDi vardiar(5)轻 MonthSalarvsmallirtfloatEmpIDYearMonthSalarySI200944000oooo200953400oooo200962344ooooZOlO52300oooo201255600woo30071300010002D0931200LOOO如in3120010002010545001000MH412000w

7、oo20125010002012730001001300733600100120074轨ainniWilntvin? Yearchar (4)V DeptiDDeplNafneOfficeTelephoneManager表3部门表：Dept_lnfo数据类型VdEr varchdr(20) uarchar(3O) vflfchar(lZ) varchar5)DeptID10000lOOOl00020004100050003IQ009DeptNarreASS 拽朮生产 宣传部 行政部 计划营梢部 市场调查部 保卫部 后勤部officeT&lephone0010人事部办公室 枝朮生产办魁

8、宣倩部办处室 行B如公室 计划营硝办公 市场调查部办 ffl卫frii室 后勤办公室 )h«3办公室027-12345678027-29337456027-23539565Q27-09SS9765027-25473765027-12J12452027-2-75426027-21314234027-12345673Atn10 L4lOQOML101110IJ1007ALE/1004|l = 0；-.“；_ = .=.?i UserID':PasswordI UserIDPassword允许MJ值O系统用户表：User_l nfo数据捷型*i varchar(S)vardiar5

9、)000010000mnilirwi00001D0200001M3DODO1004nil100600001007nil100300001011nil1012OODO3.数据流程图数据流程图员工工资相关操作1.查询部分相关代码：dateGrid1数据绑定Private void BindData( Stringsql)SqlC onn ecti on conn ecti on = SqlDataAda pter dataAda pter = DataSet dataSet = dataAda pter.Fill(dataSet);n ewSqlC onn ecti on(conn Stri ng

10、);n ewSqlDataAda pter(sql, conn ecti on); newDataSetO;dataGridView1.DataSource = dataSet.TablesO; dataGridView1.Colum ns0.Data Prop ertyName dataGridView1.Colum ns1.Data Prop ertyName dataGridView1.Colum ns2.Data Prop ertyName dataGridView1.Colum ns3.Data Prop ertyName"职？ ？工？ e号？"；"年份

11、"；"月份"；"工资0 "部门组合框的数据绑定Privatevoid BindDeptBox()stri ng sql = "SELECT * FROM De pt_ln fo"SqlC onn ecti on conn ecti on =n ewSqlC onn ecti on(conn Stri ng);SqlDataAda pter dataAda pter =n ewSqlDataAda pter(sql, conn ecti on);DataSet dataSet =n ewDataSet();dataAda pt

12、er.Fill(dataSet);Dep tBox.DataSource = dataSet.TablesO;"De ptName""DeptID"De ptBox.Dis playMember = Dep tBox.ValueMember = De ptBox.Selectedl ndex = 0;按部门查询private void check1_CheckedChanged( objectif (check1.Checked)btnYes.Enabled = DeptBox.Enabled = check4.Checked = check4.Enab

13、led =true ;true ; false ; false ;elseDeptBox.Enabled = check4.Enabled =false ;true ;按年份查询private void check2_CheckedChanged( objectbtnYes.Enabled =true ;if (check2.Checked)YearBox.Enabled =true ;sender,sender,EventArgs e)EventArgs e)elseYearBox.Enabled =false ;YearBox.SelectedIndex = 0;按月份Y査询private

14、 void check3_CheckedChanged( objectif (check3.Checked)btnYes.Enabled =MonthBox.Enabled =true ;true ;elseMonthBox.Enabled =false ;仅查询自己的工资private void check4_CheckedChanged( objectif (check4.Checked)btnYes.Enabled =check1.Enabled = DeptBox.Enabled =true ; false ;false ;elsecheck1.Enabled = check1.Che

15、cked =true ; false ;sender,sender,EventArgs e)EventArgs e)private void btnYes_Click( object sender, EventArgs e) count = 0;String YearStr;int MonthStr;StringStringsql = DeptStr =部门号DeptStr; sqlStr =""HH .Convert .ToString(DeptBox.SelectedValue.ToString();if (check1.Checked)DeptStr =sqlStr

16、=月份', Salary as 'Salary.EmpID=Employee_Info.EmpID" , DeptStr);if (count = 0)Convert .ToString(DeptBox.SelectedValue.ToString();String .Format( "select Salary.EmpID as '职工号 ', Year as ' 年份 ', Month as '工资 o ' from Salary,Employee_Info where DeptID='0'

17、andsql += sqlStr; count = 1;elsesql +=" intersect " + sqlStr;check1.Checked =false ;年份if (check2.Checked)YearStr = Convert .ToString(YearBox.SelectedItem.ToString(); if (this .mainForm.operatorRight=2)sqlStr = String .Format( "select EmpID as ' 职工号 ', Year as '年份', Mon

18、th as ' 月份 ',Salary as '工资' from Salary where Year='0'", YearStr);else if (this .mainForm.operatorRight=1)sqlStr =String .Format( "select Salary.EmpID as '职工号 ', Year as ' 年份 ', Month as '月 ', Salary as '工资 ' from Salary,Employee_Info

19、,Dept_Info where Year='0'andSalary.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', Salary as 'YearStr, this .mainForm.person.u

20、serName); sqlStr = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份 ', Month as ' 月份 工资 a ' from Salary where Year='0'and EmpID='1'",if (count = 0)sql += sqlStr; count = 1;elsesql +=check2.Checked =" intersect " + sqlStr;false ;月份if (

21、check3.Checked)MonthStr =Convert .ToInt32(MonthBox.SelectedItem.ToString();if ( this .mainForm.operatorRight = 2)Salary as 'sqlStr = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份', Month as ' 月份 ', 工资' from Salary where Month='0'", MonthStr)

22、;else if ( 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'andSalary.EmpID=Employee_Info.EmpID and Employee_Info.De

23、ptID=Dept_Info.DeptID and Dept_Info.DeptID in (select DeptID from Employee_Info where EmpID='1')", MonthStr, this .mainForm.person.userName);elsesqlStr = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份', Month as ' 月份 ', 工 ' from Salary where Mont

24、h='0' and EmpID='1'" ,Salary as 'MonthStr, this .mainForm.person.userName);if (count = 0)sql += sqlStr; count = 1;elsesql +=" intersect " + sqlStr;check3.Checked =false ;自己工资if (check4.Checked)职工号', Year as ' 年份', Month as ' 月份', Salary , this .

25、mainForm.person.userName);sql = String .Format( "select EmpID as ' as ' 工资' from Salary where EmpID='0'" if (count = 0)sql += sqlStr; count = 1;elsesql +=check4.Checked =" intersect "+ sqlStr;false ;new Sqlmand(sql, connection);Sqlmand mand =tryconnection.Open

26、();SqlDataReader data = mand.ExecuteReader(); if (data.Read()BindData(sql);elseif (this .mainForm.operatorRight=2)sql =String .Format( "select EmpID as ' 职工号 ', Year as '年份 ', Month as ' 月份', Salary as '工资' from Salary" );else if ( this .mainForm.operatorRig

27、ht = 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 DeptI

28、D from Employee_Info where EmpID='0')" , this .mainForm.person.userName);elsesql = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份 ', Month as ' 月份 ', Salary as '工资 ' from Salary where EmpID='0'" , this .mainForm.person.userName);

29、BindData(sql);MessageBox.Show("查询失败 , 没有符合要求的工资信息 " , "查找失败 ? ",MessageBoxButtons.OK, MessageBoxIcon.Exclamation);catch ( Exception ex)MessageBox.Show(ex.Message, MessageBoxIcon.Exclamation);" 数据库操作失败 1 ", MessageBoxButtons.OK,finallyconnection.Close();private void Load

30、OrCancel()mainForm = (MainForm)this .ParentForm;YearBox.SelectedIndex = 0;false ;false ;false ;false ;MonthBox.SelectedIndex = 0; YearBox.Enabled = MonthBox.Enabled = DeptBox.Enabled = btnYes.Enabled = BindDeptBox();if ( this .mainForm.operatorRight = 2)sql = String .Format( "select EmpID as &#

31、39; 职工号 ', Year as ' 年份 ', Month as ' 月份 ', Salary as ' 工资 ' from Salary" );else if ( this .mainForm.operatorRight = 1)DeptBox.Visible = false ; check1.Visible = false ;sql = String .Format( "select Salary.EmpID as '职工号 ', Year as ' 年份 ', Month a

32、s ' 月份 ',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);elsefalse ;false ;

33、false ;check1.Visible =check4.Visible =DeptBox.Visible =sql = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份 ', Month as ' 月份 ', Salaryas ' 工资 ' from Salary where EmpID='0'", this .mainForm.person.userName);BindData(sql);count = 0;private voi

34、d groupBox2_Enter( object sender, EventArgs e)2. 对表的增删改部分：ataGrid 数据绑定private void BindData()sql = String .Format( "select EmpID as ' 职工号 ', Year as ' 年份 ', Month as ' 月份 ', Salary as ' 工资' from Salary" );SqlConnection connection = newSqlConnection(connStrin

35、g);SqlDataAdapter dataAdapter = newSqlDataAdapter(sql, connection);DataSet dataSet =newDataSet();"职工号""年份" "月份""工资"dataAdapter.Fill(dataSet); dataGridView1.DataSource = dataSet.Tables0; dataGridView1.Columns0.DataPropertyName dataGridView1.Columns1.DataPropert

36、yName dataGridView1.Columns2.DataPropertyName dataGridView1.Columns3.DataPropertyName删除private void btn3_Click( object sender, EventArgs e)true ; true ; true ;"delete" ;cleartext(); textreadwrite(); btnOkOrCancel(); bMonth.Enabled = bYear.Enabled = txt4.ReadOnly = opterate =修改或更新职工工资信息priv

37、ate void btnOk_Click( object sender, EventArgs e) btnOkOrCancel(); btnOk.Enabled =false ;EmpID = txt1.Text;Year = Month =String salary = txt4.Text; if (salary = "" )Convert .ToString(bYear.SelectedItem.ToString();Convert .ToInt32(bMonth.SelectedItem.ToString();HHsalary = Salary = ( int try

38、"0" ;float ) Convert .ToDouble(salary.Trim(); num = 0;if (EmpID != "" )sql =String.Format( "select count (*) from Employee_Info where EmpID='0'"EmpID);Sqlmand mand = connection.Open();int c = ( connection.Close();if (c > 0)intnewSqlmand(sql, connection);)mand

39、.ExecuteScalar();connection =connection.Open(); sql =if (opterate ="insert" )newSqlConnection(connString);String .Format( "select count (*) from Salary where EmpID='0'and Year='1' and Month='2'", EmpID, Year, Month);Sqlmand mycmd =newSqlmand(sql, connectio

40、n);num = ( int )mycmd.ExecuteScalar(); connection.Close();职工的工资信息存在 if (num > 0)MessageBox.Show("录入不成功，您要录入的员工的工资信息已存在！ ", " 录入 失败 ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();else if (opterate = "update" )sql = String .Format( "update Sal

41、ary set Salary='0'where EmpID='1' and Year='2' and Month='3'", Salary, EmpID, Year, Month);connection.Open();mand =newSqlmand(sql, connection);mand.ExecuteNonQuery(); connection.Close(); BindData(); textreadonly();"更新成功！ " , "更新成功 ", MessageBo

42、xButtons.OK,MessageBox.Show(MessageBoxIcon.Exclamation);else connection.Open();sql = String .Format( "delete from Salary where EmpID='0'and Year='1' and Month='2'" , EmpID, Year, Month);mand =newSqlmand(sql, connection);" 删除成功！ " , " 删除成功 ", Mess

43、ageBoxButtons.OK,mand.ExecuteNonQuery(); connection.Close(); BindData(); MessageBox.Show( MessageBoxIcon.Exclamation);textreadonly();elseif (opterate ="insert" )sql =" 录入成功！ " , " 录入成功 ", MessageBoxButtons.OK,else if (opterate ="update" )MessageBox.Show(Messag

44、eBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();"修改失败，不存在要修改的工资信息！"，“修T改失败",elseMessageBox.Show(MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();"删除失败，不存在要删除的工资信息！", "删除失败 ",elseMessageBox.Show(" 不存在要管理的员工工资信息！ " , " 操作失败 &

45、quot;,MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show(MessageBoxIcon.Exclamation);" 操作失败，请填入员工号！ ", " 操作失败 ", MessageBoxButtons.OK,String .Format( "insert into Salary(EmpID,Year,Month,Salary)values ('0','1','2',

46、9;3')" , EmpID, Year, Month, Salary); mand = newSqlmand(sql, connection); connection.Open(); mand.ExecuteNonQuery(); connection.Close();BindData(); MessageBox.Show(MessageBoxIcon.Exclamation); textreadonly();textreadonly();btnModi();true ;btnCancel.Enabled = catch ( Exception ex)MessageBox.

47、Show(ex.Message, MessageBoxIcon.Exclamation);" 数据库操作失败 1 ", MessageBoxButtons.OK,finallyconnection.Close();3. 关于界面 录入和修改键和关闭键能用，确定和取消键不能用 private void btnModi()btn1.Enabled =btn2.Enabled =btn3.Enabled =true ; true ; true ;btnOk.Enabled = btnCancel.Enabled =false ;false ;录入和修改键不能用，btnOkOrCancel()private voidbtn1.Enabled = btn2.Enabled = btn3.Enabled = btnCancel.Enabled = btnOk.Enabled =将文本框清空 private voidtxt1.Text =txt4