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

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

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

4、作，這些操作按是否改變數(shù)據(jù)庫數(shù)據(jù)可分為兩類：查詢操作，只讀取數(shù)據(jù)庫信息，不對信息做修改；刪除、更新和添加操作，需要對數(shù)據(jù)庫中的數(shù)據(jù)進行讀寫操作。因此只要寫兩部分代碼，查詢部分 :deptinfo_Query 進行部門信息查詢，查詢數(shù)據(jù)庫信息；信息管理部分： deptinfo_Manage，對信息進行更新、增加和刪除。二 數(shù)據(jù)庫設(shè)計1. 數(shù)據(jù)需求人事工資管理系統(tǒng)需要完成的主要功能有：1 員工的基本信息錄入2.工資信息表的錄入。3.部門信息表的查詢、插入、修改、刪除等操作。2. 相關(guān)表表 1 職工表： Employee_Info第2頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計表 2 工資表： Sala

5、ry第3頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計表 3 部門表： Dept_Info表 4系統(tǒng)用戶表： User_Info3. 數(shù)據(jù)流程圖第4頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計人事工資管理系統(tǒng)登錄系統(tǒng)管理員職工部工資門表表表圖數(shù)據(jù)流程圖三員工工資相關(guān)操作1. 查詢部分相關(guān)代碼：dateGrid1 數(shù)據(jù)綁定privatevoid BindData(Stringsql)SqlConnection connection =new SqlConnection(connString);SqlDataAdapter dataAdapter =new SqlDataAdapter(sql, conne

6、ction);DataSet dataSet =new DataSet();dataAdapter.Fill(dataSet);dataGridView1.DataSource = dataSet.Tables0;dataGridView1.Columns0.DataPropertyName =" 職?工 ?è號?" ;dataGridView1.Columns1.DataPropertyName ="年份" ;dataGridView1.Columns2.DataPropertyName ="月份" ;dataGridVi

7、ew1.Columns3.DataPropertyName =" 工資 o" ;部門組合框的數(shù)據(jù)綁定第5頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計privatevoid BindDeptComBox()stringsql ="SELECT * FROM Dept_Info"SqlConnection connection =new SqlConnection(connString);SqlDataAdapter dataAdapter =new SqlDataAdapter(sql, connection);DataSet dataSet =new Dat

8、aSet();dataAdapter.Fill(dataSet);DeptcomBox.DataSource = dataSet.Tables0;DeptcomBox.DisplayMember ="DeptName"DeptcomBox.ValueMember ="DeptID" ;DeptcomBox.SelectedIndex = 0;按部門查詢privatevoid check1_CheckedChanged( objectsender,EventArgs e)if(check1.Checked)btnYes.Enabled =true ;Dep

9、tcomBox.Enabled =true ;check4.Checked =false ;check4.Enabled =false ;elseDeptcomBox.Enabled =false ;check4.Enabled =true ;按年份查詢privatevoid check2_CheckedChanged( objectsender,EventArgs e)btnYes.Enabled =true ;if(check2.Checked)YearComBox.Enabled =true ;elseYearComBox.Enabled =false ;YearComBox.Selec

10、tedIndex = 0;按月份 Y查詢privatevoid check3_CheckedChanged( objectsender,EventArgs e)第6頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計if(check3.Checked)btnYes.Enabled =true ;MonthComBox.Enabled =true ;elseMonthComBox.Enabled =false ;僅查詢自己的工資privatevoid check4_CheckedChanged( objectsender,EventArgs e)if(check4.Checked)btnYes.Enable

11、d =true ;check1.Enabled =false ;DeptcomBox.Enabled =false ;elsecheck1.Enabled =true ;check1.Checked =false ;privatevoid btnYes_Click(objectsender,EventArgs e)count = 0;StringYearStr;intMonthStr;StringDeptStr;StringsqlStr ="" ;sql ="" ;DeptStr =部門號if(check1.Checked)DeptStr =sqlStr

12、 =String .Format( "select Salary.EmpID as '職工號 ', Year as '年份 ', Month as '月份 ', Salary as '工資 o' from Salary,Employee_Info where DeptID='0'andSalary.EmpID=Employee_Info.EmpID", DeptStr);if(count = 0)第7頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計sql += sqlStr;count = 1;else

13、sql +=" intersect "+ sqlStr;check1.Checked =false ;年份if(check2.Checked)YearStr =if ( this .mainForm.operatorRight=2)sqlStr =String .Format( "select EmpID as '職工號 ', Year as '年份 ', Month as '月份 ',Salary as '工資 ' from Salary where Year='0'",

14、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'andSalary.EmpID=Employee_Info.EmpID and Employee_Info.

15、DeptID=Dept_Info.DeptID and Dept_Info.DeptID in(select DeptID from Employee_Info where EmpID='1')", YearStr,elsesqlStr =String .Format( "select EmpID as '職工號 ', Year as '年份 ', Month as '月份', Salary as '工資 á' from Salary where Year='0'and

16、 EmpID='1'",YearStr,if(count = 0)sql += sqlStr;count = 1;elsesql +=" intersect "+ sqlStr;check2.Checked =false ;月份第8頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計if(check3.Checked)MonthStr =if( this .mainForm.operatorRight = 2)sqlStr =String .Format( "select EmpID as '職工號 ', Year as '年

17、份 ', 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 Salar

18、y,Employee_Info,Dept_Info where Month='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')", MonthStr,elsesqlStr =String .Format( "select EmpID as '職工號 ', Year as &

19、#39;年份 ', Month as '月份 ',Salary as '工' from Salary where Month='0' and EmpID='1'",if(count = 0)sql += sqlStr;count = 1;elsesql +=" intersect "+ sqlStr;check3.Checked =false ;自己工資if(check4.Checked)sql =String .Format( "select EmpID as '職工號 &

20、#39;, Year as '年份 ', Month as '月份 ', Salaryas ' 工資 ' from Salary where EmpID='0'",if(count = 0)sql += sqlStr;count = 1;else第9頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計sql +=" intersect "+ sqlStr;check4.Checked =false ;SqlCommand command = new SqlCommand(sql, connection); try

21、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");elseif( this .mainFor

22、m.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 (

23、select DeptID from Employee_Infowhere EmpID='0')",elsesql =String .Format( "select EmpID as '職工號 ', Year as '年份 ', Month as '月份', Salary as '工資 ' from Salary where EmpID='0'",BindData(sql);MessageBox.Show( " 查詢失敗 , 沒有符合要求的工資信息 "

24、; , " 查找失敗 ?" , MessageBoxButtons.OK, MessageBoxIcon.Exclamation);catch ( Exception ex)MessageBox.Show(ex.Message," 數(shù)據(jù)庫操作失敗 1" , MessageBoxButtons.OK,MessageBoxIcon.Exclamation);finally第10頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計connection.Close();privatevoid LoadOrCancel()mainForm = (MainForm)this .

25、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 '

26、;年份 ', Month as '月份 ', Salaryas ' 工資 ' 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 ' 工資e

27、lsecheck1.Visible =false ;check4.Visible =false ;DeptcomBox.Visible =false ;sql =String .Format( "select EmpID as '職工號 ', Year as '年份 ', Month as '月份 ', Salaryas ' 工資 ' from Salary where EmpID='0'",BindData(sql);count = 0;privatevoid groupBox2_Enter(

28、objectsender,EventArgs e)第11頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計2. 對表的增刪改部分：ataGrid 數(shù)據(jù)綁定privatevoid BindData()sql =String .Format( "select EmpID as '職工號 ', Year as '年份 ', Month as '月份 ', Salary as '工資 ' from Salary");SqlConnection connection =new SqlConnection(connString);S

29、qlDataAdapter dataAdapter =new SqlDataAdapter(sql, connection);DataSet dataSet =new DataSet();dataAdapter.Fill(dataSet);dataGridView1.DataSource = dataSet.Tables0;dataGridView1.Columns0.DataPropertyName =" 職工號 " ;dataGridView1.Columns1.DataPropertyName =" 年份 " ;dataGridView1.Colu

30、mns2.DataPropertyName =" 月份 " ;dataGridView1.Columns3.DataPropertyName =" 工資 " ;刪除privatevoid btn3_Click(objectsender,EventArgs e)cleartext();textreadwrite();btnOkOrCancel();combMonth.Enabled =true ;combYear.Enabled =true ;txt4.ReadOnly =true ;opterate ="delete"修改或更新職工工

31、資信息privatevoid btnOk_Click(objectsender,EventArgs e)btnOkOrCancel();btnOk.Enabled =false ;EmpID = txt1.Text;Year =Month =Stringsalary = txt4.Text;if(salary ="" )salary ="0" ;第12頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計Salary = (float) Convert .ToDouble(salary.Trim();intnum = 0;tryif(EmpID !="&qu

32、ot; )sql =String .Format( "select count (*) from Employee_Info where EmpID='0'",EmpID);SqlCommand command =new SqlCommand(sql, connection);connection.Open();intc = (int )command.ExecuteScalar();connection.Close();if(c > 0)connection =new SqlConnection(connString);connection.Open

33、();sql =String .Format( "select count (*) from Salary where EmpID='0'andYear='1' and Month='2'", EmpID, Year, Month);SqlCommand mycmd =new SqlCommand(sql, connection);num = (int )mycmd.ExecuteScalar();connection.Close();職工的工資信息存在if(num > 0)if(opterate ="inse

34、rt")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 Mont

35、h='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第13頁共18頁武漢

36、工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計connection.Open();sql =String .Format( "delete from Salary where EmpID='0'andYear='1' and Month='2'", EmpID, Year, Month);command =new SqlCommand(sql, connection);command.ExecuteNonQuery();connection.Close();BindData();MessageBox.Show(" 刪除成功！ "

37、; ," 刪除成功 " , MessageBoxButtons.OK,MessageBoxIcon.Exclamation);textreadonly();elseif(opterate ="insert")sql =String .Format( "insert intoSalary(EmpID,Year,Month,Salary)values ('0','1','2','3')", EmpID, Year, Month, Salary);command =new Sq

38、lCommand(sql, connection);connection.Open();command.ExecuteNonQuery();connection.Close();BindData();MessageBox.Show(" 錄入成功！ " ," 錄入成功 " , MessageBoxButtons.OK,MessageBoxIcon.Exclamation);textreadonly();elseif(opterate ="update" )MessageBox.Show(" 修改失敗，不存在要修改的工資信息！&

39、quot; ," 修T改失敗 " ,MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show(" 刪除失敗，不存在要刪除的工資信息！" ," 刪除失敗 " ,MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();else第14頁共18頁武漢工業(yè)學(xué)院數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計MessageBox.Show(" 不存在要管理的員工工資信息！"

40、 ," 操作失敗 " ,MessageBoxButtons.OK, MessageBoxIcon.Exclamation);textreadonly();elseMessageBox.Show(" 操作失敗，請?zhí)钊雴T工號！" ," 操作失敗 " , MessageBoxButtons.OK,MessageBoxIcon.Exclamation);textreadonly();btnModi();btnCancel.Enabled =true ;catch ( Exception ex)MessageBox.Show(ex.Messag

41、e," 數(shù)據(jù)庫操作失敗 1" , MessageBoxButtons.OK,MessageBoxIcon.Exclamation);finallyconnection.Close();3. 關(guān)于界面錄入和修改鍵和關(guān)閉鍵能用，確定和取消鍵不能用privatevoid btnModi()btn1.Enabled =true ;btn2.Enabled =true;btn3.Enabled =true;btnOk.Enabled =false ;btnCancel.Enabled =false ;錄入和修改鍵不能用，確定和取消鍵和關(guān)閉鍵能用privatevoid btnOkOrCancel()btn1.Enabled =false;btn2.Enabled =false;btn3.Enabled =false;btnCancel.Enabled =true ;btn