武汉工业学院
数据库应用系统设计
试验汇报
学号：
姓名： 毕 波
班级: 计算机091
指导老师：
人事工资管理系统
设计总说明：在当今社会，工资管理是一项必需而且很关键工作。现在伴随企业数量急剧增加，处理大家工资数据变越来越烦琐艰巨。现在，计算机已经普及到了几乎每个学校、家庭，我们学习和生活已经四处离不开计算机存在。
本系统依据开发要求关键应用于企业人事系统，完成对日常工资增删查改数字化管理。比较系统地对职员信息和工资进行管理，查询、增添、修改、删除全部变很简便，节省了大量工作量。
本课程设计是在学习了《数据库应用系统》和相关开发软件课程以后，让学生经过实际项目标设计、开发，培养学生独立进行数据库软件建模、在计算机中进行数据库设计、并经过相关软件开发系统能力。
本系统基础功效包含：部门信息管理（查询、添加、修改、删除学生部门等）、职员信息管理（录入、查询、修改、删除职员信息等）、工资信息管理（录入、查询、修改职员工资等）。
本系统关键用于对职员工资进行管理，能够进行插入、删除、修改、查询和显示职员信息。登录该系统时，用户需要输入口令和密码，以确保数据安全性，成功登录用户，能够插入职员信息和工资，并对职员信息和工资进行增、删、改操作。
基于上述想法，我们将职员数据保留到数据库中。我们要求系统能够高效快速处理数据，而且要确保数据正确性、相容性和安全性。所以在数据库中需要定义很多触发器，比如删除了某个职员信息则删除对应全部工资信息、活着删除了某部门则删除该部门全部信息等。
所以我们要从数据库中读取数据，而且和界面联络起来，同时也能将用户界面上数据存放到数据库中。以上是设计此系统应该注意地方和设计标准，以下就是遵照这些标准和标准设计出一套完整管理系统。
一 系统需求：
依据题目需求，能够把系统分为三个部分：部门信息管理部分、职员信息管理部分和工资信息管理部分。此次试验中，我关键负责部门信息管理模块，题中需要对部门信息进行查询、添加、修改、删除操作，这些操作按是否改变数据库数据可分为两类：查询操作，只读取数据库信息，不对信息做修改；删除、更新和添加操作，需要对数据库中数据进行读写操作。所以只要写两部分代码，查询部分:deptinfo_Query进行部门信息查询，查询数据库信息；信息管理部分：deptinfo_Manage，对信息进行更新、增加和删除。
二 数据库设计
1.数据需求
人事工资管理系统需要完成关键功效有：
1职员基础信息录入
2.工资信息表录入。
3.部门信息表查询、插入、修改、删除等操作。
2.相关表
表1 职员表：Employee_Info
表2 工资表：Salary
表3 部门表：Dept_Info
表4 系统用户表：User_Info
3.数据步骤图
人事工资管理系统
人事工资管理系统
登录
登录
系统管理员
系统管理员
职员表
职员表
部门表工资表
部门表
工资表
图 数据步骤图
三 职员工资相关操作
1.查询部分
相关代码：
dateGrid1数据绑定
private void 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 = "工资o";
}
部门组合框数据绑定
private void 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;
}
按部门查询
private void 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;
}
}
按年份查询
private void check2_CheckedChanged(object sender, EventArgs e)
{
btnYes.Enabled = true;
if (check2.Checked)
{
YearComBox.Enabled = true;
}
else
{
YearComBox.Enabled = false;
}
YearComBox.SelectedIndex = 0;
}
按月份Y查询
private void check3_CheckedChanged(object sender, EventArgs e)
{
if (check3.Checked)
{
btnYes.Enabled = true;
MonthComBox.Enabled = true;
}
else
{
MonthComBox.Enabled = false;
}
}
仅查询自己工资
private void 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;
}
}
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 = 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 (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);
}
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 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);
}
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}'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");
}
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.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, "数据库操作失败1", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
connection.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.operatorRight == 2)
{
sql = String.Format("select EmpID as '职员号', Year as '年份', Month as '月份', Salary as '工资' from Salary");
}
else if (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;
}
private void 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 = 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 = "工资";
}
删除
private void btn3_Click(object sender, EventArgs e)
{
cleartext();
textreadwrite();
btnOkOrCancel();
combMonth.Enabled = true;
combYear.Enabled = true;
txt4.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 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();
}
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();
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();
}
else if (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, "数据库操作失败1", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
connection.Close();
}
}
3. 相关界面
录入和修改键和关闭键能用，确定和取消键不能用
private void btnModi()
{
btn1.Enabled = true;
btn2.Enabled = true;
btn3.Enabled = true;
btnOk.Enabled = false;
btnCancel.Enabled = false;
}
录入和修改键不能用，确定和取消键和关闭键能用
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 = false;
combYear.Enabled = false;
textreadonly();
btnModi();
BindData();
}
录入按钮
private void btn1_Click(object sender, EventArgs e)
{
cleartext();
textreadwrite();
btnOkOrCancel();
combMonth.Enabled = true;
combYear.Enabled = true;
opterate = "insert";
}
修改按钮
private void btn2_Click(object sender, EventArgs e)
{
cleartext();
textreadwrite();
btnOkOrCancel();
opterate = "update";
combMonth.Enabled = true;
combYear.Enabled = true;
}
四 职员工资界面：
五 课程设计总结：
此次课程设计，和另外两个同学一起，完成了人事工资管理系统设计，经过此次课程设计，提升了我们团体协作能力，加强了我们动手、思索和处理问题能力，检验了我所学习知识，在设计过程中，和同学分工设计，和同学们相互探讨，相互学习，相互监督。经过完成系统设计，使自己对C#编写C/S系统有了深入认识