app.config
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<connectionStrings>
<add name="connStr" connectionString="Server=.;Database=employerdb1;integrated security=true"/>
<add name="connStr2" connectionString="Server=.;Database=employerdb;user=sa;password=xiaobao"/>
<add name="connStr3" connectionString="Server=.;Database=employerdb1;integrated security=true"/>
</connectionStrings>
</configuration>
login.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EmployerProject
{
class Login
{
private int tid;
private int id;
private string password;
private string lastTime;
}
}
pagedata.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace EmployerProject
{
class PageData
{
public int PageSize { set; get; } //每页记录大小
public int CurrentPageNo { set; get; } //当前页的页码
public int PageCount { set; get; } //总页数
public int TotalRecords { set; get; } //总的记录数
public DataSet RecordsOfCurrentPage { set; get; } //当前页的数据
public PageData()
{
}
public PageData(int pageSize,int currentPageNo)
{
this.PageSize = pageSize;
this.CurrentPageNo = currentPageNo;
this.PageCount = 0;
this.TotalRecords = 0;
this.RecordsOfCurrentPage = null;
}
}
}
pageutil.cs
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EmployerProject
{
class PageUtil
{
public static void getDataofCurrentPage(string sql,string sql2,PageData pageData)
{
DbUtil dbUtil = new DbUtil();
pageData.TotalRecords=(int)(dbUtil.executeScalar(sql, null));
pageData.PageCount = pageData.TotalRecords % pageData.PageSize == 0 ?
pageData.TotalRecords / pageData.PageSize :
pageData.TotalRecords / pageData.PageSize + 1;
pageData.RecordsOfCurrentPage = dbUtil.executeDataSet(sql2, "staff", null);
string s = "1111";
}
}
}
staff.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EmployerProject
{
class Staff
{
private int id;
private string staffName;
private string folk;
private string birthday;
private string sex;
private string workdate;
private Login login;
private EmployeeGenre genre;
public int Id { set; get; }
public string StaffName { set; get; }
public string Folk { set; get; }
public string Birthday { set; get; }
public string Sex { set; get; }
public string Workdate { set; get; }
public Login Login {
set { this.login = value; }
get { return this.login; }
}
public EmployeeGenre Genre {
set { this.genre = value; }
get { return this.genre; } }
}
}
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace EmployerProject
{
public partial class WAddStaff : Form
{
DbUtil util = new DbUtil();
public WAddStaff()
{
InitializeComponent();
}
private void WAddStaff_Load(object sender, EventArgs e)
{
//读取数据库绑定到部门控件中
DataSet ds=util.executeDataSet("select * from tb_EmployeeGenre", "EmployeeGenre", null);
this.cbDepartment.DataSource = ds.Tables["EmployeeGenre"];
this.cbDepartment.ValueMember = "id";
this.cbDepartment.DisplayMember = "employeename";
//this.dtWorkDate.Value = DateTime.Parse("2010-01-01");
}
private void btnAdd_Click(object sender, EventArgs e)
{
//MessageBox.Show(this.cbDepartment.SelectedValue.ToString());
string sql = @"insert into tb_Staffbbasic
(id, staffName, folk, birthday, sex, workdate, temployeeId)
values(@id, @staffName, @folk, @birthday, @sex, @workdate, @temployeeId)";
SqlParameter[] paras= new SqlParameter[] {
new SqlParameter("@id",this.tbId.Text),
new SqlParameter("@staffName",this.tbStaffName.Text),
new SqlParameter("folk",this.cbFolk.Text),
new SqlParameter("@birthday",this.dtBirthday.Value),
new SqlParameter("@sex",rbMale.Checked?"男":"女"),
new SqlParameter("@workdate",this.dtWorkDate.Value),
new SqlParameter("@temployeeId",Convert.ToInt32(this.cbDepartment.SelectedValue.ToString()))
};
if(util.executeUpdate(sql,paras))
{
MessageBox.Show("添加员工信息成功");
}
else
{
MessageBox.Show("添加员工信息失败");
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace EmployerProject
{
public partial class WLogin : Form
{
public WLogin()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = @"select * from tb_Staffbbasic s,tb_login l
where l.id = s.id
and((s.id = @name and l.pass = @pass)
or(s.staffName = @name and l.pass =@pass))";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@name",this.tbName.Text),
new SqlParameter("@pass",this.tbPassword.Text)
};
DbUtil util = new DbUtil();
DataSet ds=util.executeDataSet(sql, "staff", paras);
if(ds.Tables["staff"].Rows.Count>0)
{
WMainForm mainform = new WMainForm();
mainform.UserName = this.tbName.Text;
mainform.Show();
this.Hide();
}
else
{
MessageBox.Show("failed!");
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace EmployerProject
{
public partial class WSearchStaff : Form
{
private PageData pageData;
public WSearchStaff()
{
InitializeComponent();
}
private void WSearchStaff_Load(object sender, EventArgs e)
{
this.pageData = new PageData(10,1);
DbUtil util = new DbUtil();
DataSet ds = util.executeDataSet("select * from tb_EmployeeGenre", "EmployeeGenre", null);
this.cbDepartment.DataSource = ds.Tables["EmployeeGenre"];
this.cbDepartment.ValueMember = "id";
this.cbDepartment.DisplayMember = "employeename";
}
private void searchGetPageData()
{
string condition = "";
获取页数的
if (this.tbStaffName.Text.Trim() != "")
{
//Trim()去掉空格
condition += " and staffName like '%" + this.tbStaffName.Text.Trim() + "%'";
//模糊查询
}
condition += " and employeeName='" + this.cbDepartment.Text + "'";
DbUtil db = new DbUtil();
string sql = @"select count(1)
from tb_Staffbbasic s,tb_EmployeeGenre e
where e.id=s.temployeeId";
//select count(1)计算一共有多少符合条件的行当两个表中的id相等的时候,所以说是查数据搞页数的;
sql += condition;
string sql2 = string.Format(@"select top {0} s.id as id,staffName,folk,birthday,sex,workdate,e.employeeName
from tb_Staffbbasic s,tb_EmployeeGenre e
where e.id=s.temployeeId {1}
and s.id not in
(
select top {2} s.id from tb_Staffbbasic s,tb_EmployeeGenre e where e.id=s.temployeeId {3}
)", pageData.PageSize, condition, (pageData.CurrentPageNo - 1) * pageData.PageSize, condition);
PageUtil.getDataofCurrentPage(sql, sql2, pageData);
//getDataofCurrentPage获取当前页数的方法
this.dgvStaff.DataSource = pageData.RecordsOfCurrentPage.Tables[0];
this.linfoNav.Text = string.Format("当前页面第{0}页,总共{1}页,总记录数:{2}条",
pageData.CurrentPageNo, pageData.PageCount, pageData.TotalRecords);
this.tbCurrentpageNo.Text = pageData.CurrentPageNo.ToString();
}
private void btnSeach_Click(object sender, EventArgs e)
{
this.pageData.CurrentPageNo = 1;
searchGetPageData();
/*
string condition = "";
if(this.tbStaffName.Text.Trim()!="")
{
condition += " and staffName like '%"+this.tbStaffName.Text.Trim()+"%'";
}
condition += " and employeeName='"+this.cbDepartment.Text+"'";
DbUtil db = new DbUtil();
string sql= @"select count(1)
from tb_Staffbbasic s,tb_EmployeeGenre e
where e.id=s.temployeeId";
sql += condition;
string sql2 =string.Format( @"select top {0} s.id as id,staffName,folk,birthday,sex,workdate,e.employeeName
from tb_Staffbbasic s,tb_EmployeeGenre e
where e.id=s.temployeeId {1}
and s.id not in
(
select top {2} s.id from tb_Staffbbasic s,tb_EmployeeGenre e where e.id=s.temployeeId {3}
)",pageData.PageSize, condition, (pageData.CurrentPageNo - 1) * pageData.PageSize,condition);
PageUtil.getDataofCurrentPage(sql,sql2, pageData);
this.dgvStaff.DataSource = pageData.RecordsOfCurrentPage.Tables[0];
*/
}
private void llFirst_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.pageData.CurrentPageNo = 1;
searchGetPageData();
}
private void llPre_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
if(this.pageData.CurrentPageNo==1)
{
MessageBox.Show("现在已经是在第一页");
}else
{
this.pageData.CurrentPageNo = this.pageData.CurrentPageNo - 1;
searchGetPageData();
}
}
private void llnext_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.pageData.CurrentPageNo = this.pageData.CurrentPageNo + 1;
searchGetPageData();
}
private void llLast_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.pageData.CurrentPageNo = this.pageData.PageCount;
searchGetPageData();
}
private void lljump_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
this.pageData.CurrentPageNo = Convert.ToInt32(this.tbCurrentpageNo.Text);
searchGetPageData();
}
private void button1_Click(object sender, EventArgs e)
{
if(MessageBox.Show("你确定要删除吗?","删除数据",
MessageBoxButtons.OKCancel)==DialogResult.OK)
{
String id=this.dgvStaff.CurrentRow.Cells[0].Value.ToString();
DbUtil db = new DbUtil();
String sql = string.Format("delete from tb_Staffbbasic where id='{0}' ",id);
if(db.executeUpdate(sql,null))
{
MessageBox.Show("删除数据成功");
searchGetPageData();
}
else
{
MessageBox.Show("删除数据失败");
}
}
else
{
//MessageBox.Show("取消");
}
}
private void button2_Click(object sender, EventArgs e)
{
WUpdateStaff ws = new WUpdateStaff();
ws.Id = this.dgvStaff.CurrentRow.Cells[0].Value.ToString();
ws.ShowDialog();
}
private void button3_Click(object sender, EventArgs e)
{
DataTable dt=(DataTable)this.dgvStaff.DataSource;
//MessageBox.Show(dt.Rows.Count.ToString());
XSSFWorkbook book = new XSSFWorkbook();
ISheet sheet=book.CreateSheet("成绩单");
for(int i=0;i< dt.Rows.Count;i++)
{
IRow row = sheet.CreateRow(i);
for(int j=0;j<dt.Columns.Count;j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//IRow row=sheet.CreateRow(0);
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "xlsx文件|.xlsx";
string path="";
if(dialog.ShowDialog()==DialogResult.OK)
{
path = dialog.FileName;
FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);
book.Write(fs);
fs.Close();
book.Close();
}
//string path = @"E:\xiaobao.xlsx";
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace EmployerProject
{
public partial class WUpdateStaff : Form
{
public WUpdateStaff()
{
InitializeComponent();
}
public WUpdateStaff(string id)
{
InitializeComponent();
}
public string Id { set; get; }
private void WUpdateStaff_Load(object sender, EventArgs e)
{
DbUtil db = new DbUtil();
DataSet ds = db.executeDataSet("select * from tb_EmployeeGenre", "EmployeeGenre", null);
this.cbDepartment.DataSource = ds.Tables["EmployeeGenre"];
this.cbDepartment.ValueMember = "id";
this.cbDepartment.DisplayMember = "employeename";
string sql = string.Format("select * from tb_Staffbbasic where id = '{0}'",Id);
ds= db.executeDataSet(sql,"staff",null);
DataTable dt= ds.Tables["staff"];
if(dt.Rows.Count==1)
{
DataRow row=dt.Rows[0];
this.tbId.Text=row["id"].ToString();
this.tbStaffName.Text = row["staffName"].ToString();
this.cbFolk.Text = row["folk"].ToString();
this.dtBirthday.Value = DateTime.Parse( row["birthday"].ToString());
this.dtWorkDate.Value= DateTime.Parse(row["workdate"].ToString());
if(row["sex"].ToString().Equals("男"))
{
this.rbMale.Checked = true;
}
else
{
this.rbFeMale.Checked = true;
}
this.cbDepartment.SelectedValue = row["temployeeId"];
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
string sql = @"update tb_Staffbbasic
set staffName=@staffName,
folk=@folk,
birthday=@birthday,
sex= @sex,
workdate=@workdate,
temployeeId=@temployeeId
where id=@id";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@id",this.tbId.Text),
new SqlParameter("@staffName",this.tbStaffName.Text),
new SqlParameter("folk",this.cbFolk.Text),
new SqlParameter("@birthday",this.dtBirthday.Value),
new SqlParameter("@sex",rbMale.Checked?"男":"女"),
new SqlParameter("@workdate",this.dtWorkDate.Value),
new SqlParameter("@temployeeId",Convert.ToInt32(this.cbDepartment.SelectedValue.ToString()))
};
DbUtil db = new DbUtil();
if (db.executeUpdate(sql, paras))
{
MessageBox.Show("更新员工信息成功");
}
else
{
MessageBox.Show("更新员工信息失败");
}
}
}
}