C#人事工資管理系統(tǒng)實(shí)驗(yàn)報(bào)告

1、武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計(jì)實(shí)驗(yàn)報(bào)告學(xué)號： 090501102姓名：畢波班級:計(jì)算機(jī)091指導(dǎo)教師：人事工資管理系統(tǒng)設(shè)計(jì)總說明：在當(dāng)今社會，工資管理是一項(xiàng)必要而且很關(guān)鍵的工作?，F(xiàn)在隨著企業(yè)數(shù)量 的急劇增加，處理人們的工資數(shù)據(jù)變的越來越煩瑣艱巨。如今，計(jì)算機(jī)已經(jīng)普及到了幾乎每 個學(xué)校、家庭，我們的學(xué)習(xí)和生活已經(jīng)處處離不開計(jì)算機(jī)的存在。本系統(tǒng)依據(jù)開發(fā)要求主要應(yīng)用于企業(yè)人事系統(tǒng)，完成對日常的工資增刪查改的數(shù)字化管 理。比較系統(tǒng)地對員工的信息和工資進(jìn)行管理，查詢、增添、修改、刪除都變的非常簡便， 節(jié)省了大量的工作量。本課程設(shè)計(jì)是在學(xué)習(xí)了 數(shù)據(jù)庫應(yīng)用系統(tǒng) 和相關(guān)開發(fā)軟件課程之后，讓學(xué)生通過實(shí)際 項(xiàng)目的

2、設(shè)計(jì)、開發(fā)，培養(yǎng)學(xué)生獨(dú)立進(jìn)行數(shù)據(jù)庫軟件的建模、在計(jì)算機(jī)中進(jìn)行數(shù)據(jù)庫設(shè)計(jì)、并 通過相關(guān)軟件開發(fā)系統(tǒng)的能力。本系統(tǒng)的基本功能包括：部門信息的管理（查詢、添加、修改、刪除學(xué)生部門等） 、職員 信息的管理（錄入、查詢、修改、刪除員工的信息等） 、工資信息的管理（錄入、查詢、修改 員工的工資等）。本系統(tǒng)主要用于對員工工資進(jìn)行管理，能夠進(jìn)行插入、刪除、修改、查詢和顯示員工的 信息。登錄該系統(tǒng)時，用戶需要輸入口令和密碼，以確保數(shù)據(jù)的安全性，成功登錄的用戶， 可以插入員工的信息和工資，并對員工的信息和工資進(jìn)行增、刪、改操作。基于上述想法，我們將員工數(shù)據(jù)保存到數(shù)據(jù)庫中。我們要求系統(tǒng)能夠高效快速的處理數(shù) 據(jù)，并且

3、要保證數(shù)據(jù)的正確性、相容性和安全性。所以在數(shù)據(jù)庫中需要定義很多觸發(fā)器，比 如刪除了某個員工的信息則刪除對應(yīng)的所有工資信息、活著刪除了某部門則刪除該部門所有 的信息等。所以我們要從數(shù)據(jù)庫中讀取數(shù)據(jù)，并且和界面聯(lián)系起來，同時也能將用戶界面上的數(shù)據(jù) 存儲到數(shù)據(jù)庫中。以上是設(shè)計(jì)此系統(tǒng)應(yīng)該注意的地方和設(shè)計(jì)原則，以下就是遵循這些原則和 標(biāo)準(zhǔn)設(shè)計(jì)出的一套完整的管理系統(tǒng)。一系統(tǒng)需求：根據(jù)題目需求，可以把系統(tǒng)分為三個部分：部門信息管理部分、職員信息管理部分和工 資信息管理部分。本次實(shí)驗(yàn)中，我主要負(fù)責(zé)部門信息管理模塊，題中需要對部門信息進(jìn)行查 詢、添加、修改、刪除操作，這些操作按是否改變數(shù)據(jù)庫數(shù)據(jù)可分為兩類：查詢

4、操作，只讀 取數(shù)據(jù)庫信息，不對信息做修改；刪除、更新和添加操作，需要對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行讀寫 操作。因此只要寫兩部分代碼，查詢部分：dept in fo_Query 進(jìn)行部門信息查詢，查詢數(shù)據(jù)庫 信息；信息管理部分：dept in fo_Ma nage，對信息進(jìn)行更新、增加和刪除。二數(shù)據(jù)庫設(shè)計(jì)1. 數(shù)據(jù)需求人事工資管理系統(tǒng)需要完成的主要功能有：1員工的基本信息錄入2. 工資信息表的錄入。3. 部門信息表的查詢、插入、修改、刪除等操作。2. 相關(guān)表表1 職工表：Employeenfo列堵數(shù)據(jù)類型值EmpriamevarcharlQ)Nationalitychar(2)smdlintTelepho

5、nevardiarjl?'DeptlDvardw Poibonvardiar(lO)【亦i"M1_JHIOIO輻wi«昵_馳-嫦禪IO輜ZSB敲觀1IB710B_翹_11urn啊一魅10】3時10J5售憾怖ECetDFW211779)7OCjI虹45L劇瀏我朗虹15OCXSB _虹1527067燈虹aKOI虹期KID窺2虹*阿附了譏152/m?虹何UTO燈鹽Z訶rawKj3虹Kj521MKK7OCjI23157155燈虹表2 工資表：SalaryLINGKERONG-PCPem - dbo.UserJnfo UNGKERONG-歹館數(shù)據(jù)奘型允許MJ值i i-fcil

6、 lirini liBi ilIn:-illiri-i il9i- lilii mill i-feil iiririi lid-: il iiilriiEmpIDvar±ar(5)H?Yearchar (斗)輕MonfrismaltnSalaryfloatHEmpIDYearMonthSalaryk跡200944000oaoo2009534000000200962344oooo201052300oooo201255600woa3007130001000200931200moosom312001000301054500woo3011斗12000woo201250100020127300

7、0lOQl20Q733&Q01001300 743400mmTfMT斗表3部門表：Dept_lnfo數(shù)據(jù)類型允詫hLill值DepUDvarcher(5)ODeptNamevanhw(20Elofficevarchar(30Telephoneuarcthar(12)nManagervarchar(S)團(tuán)lV0DeptlDDeptNameOfficeTtlephoneManager0000人事部人事部辦處室ACEZ0001接術(shù)生產(chǎn)枝朮生產(chǎn)辦公一027933745610140002宣傳部宣倍部辦處室027-235 J956510000003行政部行it亦公UZ00

8、0計(jì)劃營梢部計(jì)期營體辦益宀Q27-234787651C110005市場調(diào)查部市場調(diào)查部亦1013coos保衛(wèi)部保衛(wèi)咖也室027-75410070009后勤部.后勤亦處室ALU0010樸聰卻辦公室027-1234567910041茁門AW表4 系統(tǒng)用戶表：Usernfo期S捷型允值?1 ll>M lllHIHIll»« lllkfalll Ilka .1111 laUaillikllBI&BIII 1MI 11UserIDuarc±i3r(5)Passwordvardldr(S)UserIDPasswordkrai0000iGOODIMO1111100

9、1000010020000OOCO1M41111100600001007mi10C800001011mi101200003. 數(shù)據(jù)流程圖圖數(shù)據(jù)流程圖三員工工資相關(guān)操作1.查詢部分相關(guān)代碼：dateGrid1數(shù)據(jù)綁定private void BindData( String SqlC onn ecti on conn ecti on =SqlDataAdapter dataAdapter =sql)n ewSqlC onn ecti on(conn Stri ng);n ewSqlDataAdapter(sql, conn ecti on);DataSet dataSet =n ewDataSe

10、t();"職? ?工? e 號 ?""年份""月份""工資o "dataAdapter.Fill(dataSet); dataGridViewl.DataSource = dataSet.TablesO; dataGridViewl.Colum ns0.DataPropertyName = dataGridView1.Colum ns1.DataPropertyName = dataGridView1.Colum ns2.DataPropertyName = dataGridView1.Colum ns3.Data

11、PropertyName =部門組合框的數(shù)據(jù)綁定private void BindDeptComBox()string sql = "SELECT * FROM Dept_Info" ;SqlConnection connection = newSqlConnection(connString);SqlDataAdapter dataAdapter =newSqlDataAdapter(sql, connection);DataSet dataSet = newDataSet();dataAdapter.Fill(dataSet);DeptcomBox.DataSource

12、 = dataSet.Tables0;DeptcomBox.DisplayMember = "DeptName"DeptcomBox.ValueMember = "DeptID" ;DeptcomBox.SelectedIndex = 0;按部門查詢private void check1_CheckedChanged( object sender, EventArgs e)if (check1.Checked)btnYes.Enabled =true ;DeptcomBox.Enabled =true ;check4.Checked =false ;ch

13、eck4.Enabled =false ;elseDeptcomBox.Enabled =false ;check4.Enabled =true ;按年份查詢private void check2_CheckedChanged( object sender, EventArgs e)btnYes.Enabled =true ;if (check2.Checked)YearComBox.Enabled =trueelseYearComBox.Enabled =falseYearComBox.SelectedIndex = 0;按月份Y査詢private void check3_CheckedCh

14、anged( object sender, EventArgs e)if (check3.Checked)btnYes.Enabled = true ;MonthComBox.Enabled = true ;elseMonthComBox.Enabled = false ; 僅查詢自己的工資private void check4_CheckedChanged( object sender, EventArgs e)if (check4.Checked)btnYes.Enabled =true ;check1.Enabled =false ;DeptcomBox.Enabled =false ;

15、elsecheck1.Enabled =true ;check1.Checked =false ; private void 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

16、= Convert .ToString(DeptcomBox.SelectedValue.ToString();sqlStr =String .Format( "select Salary.EmpID as '職工號 ', Year as ' 年份 ', Month as月份 ', Salary as ' 工資 o ' from Salary,Employee_Info where DeptID='0'and Salary.EmpID=Employee_Info.EmpID" , DeptStr);if

17、 (count = 0)sql += sqlStr;count = 1;elsesql += " 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 

18、9; 年份 ', Month 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

19、Salary,Employee_Info,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);elsesqlStr = String .Format( "se

20、lect EmpID as ' 職工號 ', Year as ' 年份 ', Month as ' 月份 ',Salary as ' 工資 a ' from Salary where Year='0'and EmplD='1"',YearStr, this .mainForm.person.userName);if (count = 0)sql += sqlStr;count = 1;elsesql +=" intersect "+ sqlStr; check2.Che

21、cked =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='

22、0'" , MonthStr);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.Em

23、pID 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);elsesqlStr =String .Format( "select EmpID as ' 職工號 ', Year as ' 年份 ', Month as ' 月份 ',Salary

24、as ' 工 ' from Salary where Month='0' and EmpID='1'",MonthStr, this .mainForm.person.userName);if (count = 0)sql += sqlStr;count = 1;elsesql += " intersect " + sqlStr;check3.Checked = false ;自己工資if (check4.Checked)sql =String .Format( "select EmpID as '

25、 職工號 ', Year as ' 年份 ', Month as ' 月份 ', Salaryas ' 工資 ' from Salary where EmpID='0'", this .mainForm.person.userName);if (count = 0)sql += sqlStr;count = 1;elsesql +=" intersect " + sqlStr;check4.Checked =false ;SqlCommand command =newSqlCommand(sq

26、l, connection);try connection.Open(); SqlDataReader data = command.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&quo

27、t; );else if ( 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.EmpIDand Employee_Info.DeptID=Dept_Info.DeptI

28、D and Dept_Info.DeptID in (select DeptID 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'&qu

29、ot; , this .mainForm.person.userName);BindData(sql);MessageBox.Show("查詢失敗 , 沒有符合要求的工資信息 " , "查找失敗 ? ",MessageBoxButtons.OK, MessageBoxIcon.Exclamation);catch ( Exception ex)MessageBox.Show(ex.Message," 數(shù)據(jù)庫操作失敗 1 ", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);finall

30、yconnection.Close(); private void 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.operatorR

31、ight = 2)sql =String .Format( "select EmpID as ' 職工號 ', Year as ' 年份 ', Month as ' 月份 ', Salaryas ' 工資 ' from Salary" );else if ( this .mainForm.operatorRight = 1)DeptcomBox.Visible =false ;check1.Visible =false ;sql =String .Format( "select Salary.EmpI

32、D 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')"

33、, this .mainForm.person.userName);elsecheck1.Visible =false ;check4.Visible =false ;DeptcomBox.Visible =false ;sql =String .Format( "select EmpID as ' 職工號 ', Year as ' 年份 ', Month as ' 月份 ', Salaryas ' 工資 ' from Salary where EmpID='0'", this .mainFor

34、m.person.userName);BindData(sql);count = 0;private void groupBox2_Enter( object sender, EventArgs e)2. 對表的增刪改部分：ataGrid 數(shù)據(jù)綁定private void BindData()sql = String .Format( "select EmpID as ' 職工號 ', Year as ' 年份 ', Month as ' 月份 ', Salary as 工資' from Salary" );SqlCo

35、nnection connection = newSqlConnection(connString);SqlDataAdapter dataAdapter = newSqlDataAdapter(sql, connection);DataSet dataSet =newDataSet();dataAdapter.Fill(dataSet);dataGridView1.DataSource = dataSet.Tables0; dataGridView1.Columns0.DataPropertyName =" 職工號 "dataGridView1.Columns1.Data

36、PropertyName =" 年份 " ;dataGridView1.Columns2.DataPropertyName =" 月份 " ;dataGridView1.Columns3.DataPropertyName =" 工資 " ;刪除private void btn3_Click( object sender, EventArgs e)cleartext();textreadwrite();btnOkOrCancel();combMonth.Enabled =true ;combYear.Enabled = true ;tx

37、t4.ReadOnly = true ;opterate = "delete" ;修改或更新職工工資信息private void 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 salar

38、y = txt4.Text;if (salary = "" )salary = "0" ;Salary = (float ) Convert .ToDouble(salary.Trim();int num = 0;tryif (EmpID != "" )sql = String .Format( "select count (*) from Employee_Info where EmpID='0'",EmpID);SqlCommand command =newSqlCommand(sql, con

39、nection);connection.Open();int c = ( int )command.ExecuteScalar();connection.Close();if (c > 0)connection = newSqlConnection(connString);connection.Open();sql = String .Format( "select count (*) from Salary where EmpID='0'and Year='1' and Month='2'" , EmpID, Year

40、, Month);SqlCommand mycmd = newSqlCommand(sql, connection);num = ( int )mycmd.ExecuteScalar();connection.Close();職工的工資信息存在if (num > 0)if (opterate = "insert" )MessageBox.Show("錄入不成功，您要錄入的員工的工資信息已存在！ ", " 錄入失敗 ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textr

41、eadonly();else if (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();

42、connection.Close(); BindData();textreadonly();MessageBox.Show(" 更新成功！ " , " 更新成功 ", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);elseconnection.Open();sql = String .Format( "delete from Salary where EmpID='0'and Year='1' and Month='2'" , EmpI

43、D, Year, Month);command =new SqlCommand(sql, connection);command.ExecuteNonQuery();connection.Close();BindData();MessageBox.Show(" 刪除成功！ " , " 刪除成功 ", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);textreadonly();elseif (opterate = "insert" )sql = String .Format( &quo

44、t;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(" 錄入成功！ " , " 錄入

45、成功 ", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);textreadonly();else if (opterate = "update" )MessageBox.Show("修改失敗，不存在要修改的工資信息！"，“修T改失敗“MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show("刪除失敗，不存在要刪除的工資信息！", "刪除失敗"

46、,MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show("不存在要管理的員工工資信息！ " , "操作失敗 ",MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show(" 操作失敗，請?zhí)钊雴T工號！ ", " 操作失敗 ", MessageBoxButtons.OK,MessageBoxIc

47、on.Exclamation);textreadonly();btnModi();btnCancel.Enabled = true ;catch ( Exception ex)MessageBox.Show(ex.Message," 數(shù)據(jù)庫操作失敗 1 ", MessageBoxButtons.OK,MessageBoxIcon.Exclamation);finallyconnection.Close();3. 關(guān)于界面錄入和修改鍵和關(guān)閉鍵能用，確定和取消鍵不能用private void btnModi() btn1.Enabled =true;btn2.Enabled =

48、true;btn3.Enabled =true;btnOk.Enabled = false ; btnCancel.Enabled = false錄入和修改鍵不能用，確定和取消鍵和關(guān)閉鍵能用private void btnOkOrCancel()btn1.Enabled =false;btn2.Enabled =false;btn3.Enabled =false;btnCancel.Enabled = true ; btnOk.Enabled =true;將文本框清空 private void cleartext()txt1.Text ="" ;txt4.Text ="" ;取消按鈕private void btnCancel_Click( object sender, EventArgs e) combYear.SelectedIndex = 0;combMonth.SelectedIndex = 0;combMonth.Enabled =