private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
String cmdText = "select * from employee where ename=@ename";
SqlParameter enamesprm = new SqlParameter("@ename", comboBox1.Text);
SqlParameter[] sprm = new SqlParameter[] { enamesprm };
SqlDataReader r = DBhelper.GetDR(cmdText, sprm);
if (!r.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
else
{
r.Read();
num.Text = r["eid"].ToString();
name.Text = r["ename"].ToString();
age.Text = r["eage"].ToString();
tel.Text = r["etel"].ToString();
date.Text = r["ecreatedate"].ToString();
//根据did在dept表中找到部门名称
if (r["did"] == null || r["did"].ToString().Equals(""))
{
dept.Text = "";
}
else
{
dept.Text = getDeptName((int)r["did"]);
}
}
}
private void addbtn_Click(object sender, EventArgs e)
{
//判断该员工号是否已经存在
String cmdText = "select * from employee where eid=@num";
SqlParameter numsprm = new SqlParameter("@num", num.Text.Trim());
SqlParameter[] sprms = new SqlParameter[] { numsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprms);
if (!dr.HasRows)
{
//进行添加操作
DataRow newrow = dt.NewRow();
id = id + 1;
//把最后一项的id+1作为该员工的员工id
newrow["员工ID"] = id;
newrow["员工工号"] = num.Text.Trim();
newrow["员工姓名"] = name.Text.Trim();
newrow["年龄"] = age.Text.Trim();
newrow["联系方式"] = tel.Text.Trim();
newrow["入职日期"] = date.Text.Trim();
dt.Rows.Add(newrow);
sda.Update(dt);
MessageBox.Show("添加成功！！");
}
else
{
MessageBox.Show("工号是员工的唯一标识，该员工已经存在！" + "\n" + "请重新填写员工信息！！");
}
dr.Close();
}
//设置部门名称
void setDeptName()
{
String cmdText = "select dname from dept";
SqlParameter[] sprm = new SqlParameter[] { };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
while (dr.Read())
{
dept.Items.Add(dr["dname"].ToString());
}
dr.Close();
}
void setFirstName()
{
String cmdText = "select ename from employee";
SqlParameter[] sprm = new SqlParameter[] { };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (dr.Read())
{
lastName = dr["ename"].ToString();
}
dr.Close();
}
//设置发布公告的时间
void setDate()
{
String cmdText = "select distinct date from ad";
SqlParameter[] sprm = new SqlParameter[] { };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
while (dr.Read())
{
comboBox1.Items.Add(dr["date"].ToString());
}
dr.Close();
}
private void signRecord_Load(object sender, EventArgs e)
{
getMouthDate();
daycount.Text = mCount.ToString();
zcqd.Text = getCount("True").ToString();
cd.Text = getCount("False").ToString();
wqd.Text = (mCount - getCount("True") - getCount("False")).ToString();
//获取该月
System.DateTime currentTime = new System.DateTime();
currentTime = System.DateTime.Now;
String strYM = currentTime.ToString("y");
for (int i = 1; i < mCount; i++)
{
String cmdText = "select * from sign where eid=@eid and sdate='" + strYM + i + "日'";
//MessageBox.Show(cmdText);
SqlParameter eidsprm = new SqlParameter("@eid", numStr);
// SqlParameter dsprm = new SqlParameter("@date", strYM+"%");
SqlParameter[] sprms = new SqlParameter[] { eidsprm };
SqlDataReader r = DBhelper.GetDR(cmdText, sprms);
if (!r.HasRows)
{
//没找到该数据，说明该天未签到
String sdate = strYM + i + "日";
String stime = "";
String sflag = "未签到";
String[] strs = { sdate, stime, sflag };
ListViewItem item = new ListViewItem(strs, 0);
listView1.Items.Add(item);
}
while (r.Read())
{
String sdate = r["sdate"].ToString();
String stime = r["stime"].ToString();
//MessageBox.Show(r["sflag"].ToString());
String f = r["sflag"].ToString();
String sflag;
if (f == "True")
{
sflag = "签到";
}
else
{
sflag = "迟到";
}
String[] strs = { sdate, stime, sflag };
ListViewItem item = new ListViewItem(strs, 0);
listView1.Items.Add(item);
}
}
}
//设置所有员工姓名
void setPersonName()
{
String cmdText = "select ename from employee";
SqlParameter[] sprm = new SqlParameter[] { };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
while (dr.Read())
{
lastName = dr["ename"].ToString();
comboBox1.Items.Add(dr["ename"].ToString());
}
dr.Close();
}
//根据部门id获取部门名称
public String getDeptName(int id)
{
String cmdText = "select dname from dept where id=@d";
SqlParameter idsprm = new SqlParameter("@d", id);
SqlParameter[] sprm = new SqlParameter[] { idsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (!dr.HasRows)
{
MessageBox.Show("没有找到该数据");
}
dr.Read();
return(dr["dname"].ToString());
}
//根据员工ID获取员工姓名
public String getEmpName(String num)
{
String cmdText = "select ename from employee where eid=@n";
SqlParameter namesprm = new SqlParameter("@n", num);
SqlParameter[] sprm = new SqlParameter[] { namesprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (!dr.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
dr.Read();
return(dr["ename"].ToString());
}
//根据部门名称获取部门id
public int getDeptId(String name)
{
String cmdText = "select id from dept where dname=@n";
SqlParameter namesprm = new SqlParameter("@n", name);
SqlParameter[] sprm = new SqlParameter[] { namesprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (!dr.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
dr.Read();
return((int)dr["id"]);
}
//获取最后一项的员工id
public void getLastId()
{
String cmdText = "select id from employee";
SqlParameter[] sprm = new SqlParameter[] {};
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (!dr.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
while (dr.Read())
{
id = (int)dr["id"];
}
}
//根据ID找到用户名
public String setFindName(String str)
{
String name = "";
//去重处理
String cmdText = "select ename from employee where eid=@eid";
SqlParameter eidsprm = new SqlParameter("@eid", str);
SqlParameter[] sprm = new SqlParameter[] { eidsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
while (dr.Read())
{
name = dr["ename"].ToString();
}
return(name);
}
private void updatebtn_Click(object sender, EventArgs e)
{
int id = getEmpId(comboBox1.Text);
String cmdText = "select id from employee where eid=@num";
SqlParameter numsprm = new SqlParameter("@num", num.Text.Trim());
SqlParameter[] sprms = new SqlParameter[] { numsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprms);
dr.Read();
if (!dr.HasRows || (int)dr["id"] == id)
{
//进行更新数据库操作操作
String cmdText1 = "update employee set eid=@eid,ename=@name,eage=@age,etel=@tel,ecreatedate=@cd,did=@did where id=@id";
SqlParameter eidsprm = new SqlParameter("@eid", num.Text.Trim());
SqlParameter nasprm = new SqlParameter("@name", name.Text.Trim());
SqlParameter agesprm = new SqlParameter("@age", age.Text.Trim());
SqlParameter telsprm = new SqlParameter("@tel", tel.Text.Trim());
SqlParameter cdsprm = new SqlParameter("@cd", date.Value);
SqlParameter idsprm = new SqlParameter("@id", id);
//根据输入的部门名获取部门id
int did = getDeptId(dept.Text);
SqlParameter didsprm = new SqlParameter("@did", did);
SqlParameter[] sprms1 = new SqlParameter[] { eidsprm, nasprm, agesprm, telsprm, cdsprm, didsprm, idsprm };
int count = DBhelper.Add(cmdText1, sprms1);
if (count > 0)
{
MessageBox.Show("更新成功！");
//更新之前要clear一下，否则重复
comboBox1.Items.Clear();
//更新comboBox列表
setPersonName();
}
else
{
MessageBox.Show("更新失败！");
}
//更新修改后的员工名字
comboBox1.Text = name.Text;
}
else
{
MessageBox.Show("该员工工号已经存在" + "\n" + "请再次填写");
}
}
private void myInformation_Load(object sender, EventArgs e)
{
setDept();
//把信息，填入对应的textbox中
int deptid;
String cmdText = "select * from employee where eid=@enum";
SqlParameter numsprm = new SqlParameter("@enum", strText);
SqlParameter[] sprm = new SqlParameter[] { numsprm };
SqlDataReader r = DBhelper.GetDR(cmdText, sprm);
if (!r.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
else
{
r.Read();
id = (int)r["id"];
deptid = (int)r["did"];
num.Text = r["eid"].ToString();
name.Text = r["ename"].ToString();
age.Text = r["eage"].ToString();
tel.Text = r["etel"].ToString();
createdate.Text = r["ecreatedate"].ToString();
if (r["img"].ToString().Equals("") || r["img"] == null)
{
img.Image = null;
}
else
{
MemoryStream MStream = new MemoryStream((byte[])r["img"]);
imgname = (byte[])r["img"];
img.Image = Image.FromStream(MStream);
}
//根据did在dept表中找到部门名称
dept.Text = getDeptName(deptid);
}
r.Close();
}
//判断今天有没有签到过
public int getDate()
{
int dayflag = 0;
String cmdText = "select * from sign where eid=@n and sdate=@d";
SqlParameter nsprm = new SqlParameter("@n", numStr);
SqlParameter dsprm = new SqlParameter("@d", strYMD);
SqlParameter[] sprm = new SqlParameter[] { nsprm, dsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprm);
if (!dr.HasRows)
{
dayflag = 0;
}
else
{
dr.Read();
dayflag = 1;
}
return(dayflag);
}
private void deptinfo_Load(object sender, EventArgs e)
{
alldept.Text = getDeptCount().ToString();
String cmdText = "select * from dept";
SqlParameter[] sprms = new SqlParameter[] { };
SqlDataReader r = DBhelper.GetDR(cmdText, sprms);
if (!r.HasRows)
{
MessageBox.Show("没有找到该数据！");
}
while (r.Read())
{
depid = (int)r["id"];
depcount = getDeptCountPerson(depid);
depname = r["dname"].ToString();
deptel = r["dtel"].ToString();
String[] strs = { depname, deptel, depcount.ToString() };
ListViewItem item = new ListViewItem(strs, 0);
listView1.Items.Add(item);
}
}
private void alterbtn_Click(object sender, EventArgs e)
{
//点击按钮编辑信息
if (this.alterbtn.Text == "编辑资料")
{
num.ReadOnly = false;
name.ReadOnly = false;
age.Enabled = true;
tel.ReadOnly = false;
createdate.Enabled = true;
dept.Enabled = true;
lookbtn.Enabled = true;
this.alterbtn.Text = "保存资料";
}
//点击按钮保存信息
else if (this.alterbtn.Text == "保存资料")
{
num.ReadOnly = true;
name.ReadOnly = true;
age.Enabled = false;
tel.ReadOnly = false;
createdate.Enabled = false;
dept.Enabled = false;
lookbtn.Enabled = false;
MessageBox.Show("save");
this.alterbtn.Text = "编辑资料";
//查询修改后的部门id
int deid = getDeptId(dept.Text.Trim());
//要保证员工号的唯一性，
//两种情况，第一种员工号未修改的情况，保证查找id是对应原来的id
//第二种员工号修改了，保证查找的id是不存在
String cmdText = "select id from employee where eid=@num";
SqlParameter numsprm = new SqlParameter("@num", num.Text.Trim());
SqlParameter[] sprms = new SqlParameter[] { numsprm };
SqlDataReader dr = DBhelper.GetDR(cmdText, sprms);
dr.Read();
if (!dr.HasRows || (int)dr["id"] == id)
{
this.numtext.ForeColor = Color.Green;
this.numtext.Text = "OK";
//进行更新操作
MessageBox.Show("更新中。。。。");
byte[] byteImage;
if (strImage == null)
{
byteImage = imgname;
}
else
{
FileStream FStream = new FileStream(strImage, FileMode.Open, FileAccess.Read);
BinaryReader BReader = new BinaryReader(FStream);
byteImage = BReader.ReadBytes((int)FStream.Length);
}
String cmdText1 = "update employee set eid=@eid,ename=@name,eage=@age,etel=@tel,ecreatedate=@cd,did=@did,img=@img where id=@id";
SqlParameter imgsprm = new SqlParameter("@img", byteImage);
SqlParameter eidsprm = new SqlParameter("@eid", num.Text.Trim());
SqlParameter nasprm = new SqlParameter("@name", name.Text.Trim());
SqlParameter agesprm = new SqlParameter("@age", age.Text.Trim());
SqlParameter telsprm = new SqlParameter("@tel", tel.Text.Trim());
SqlParameter cdsprm = new SqlParameter("@cd", createdate.Value);
SqlParameter idsprm = new SqlParameter("@id", id);
SqlParameter didsprm = new SqlParameter("@did", deid);
SqlParameter[] sprms1 = new SqlParameter[] { eidsprm, nasprm, agesprm, telsprm, cdsprm, didsprm, imgsprm, idsprm };
int count = DBhelper.Add(cmdText1, sprms1);
if (count > 0)
{
MessageBox.Show("更新成功！");
}
else
{
MessageBox.Show("更新失败！");
}
}
else
{
this.numtext.ForeColor = Color.Red;
this.numtext.Text = "已存在";
MessageBox.Show("该工号属于他人，请检查是否填错");
}
dr.Close();
}
else
{
}
}