private void Form1_Load(object sender, EventArgs e)
{
dgv_LoadData();
cb_LoadData();
lv2_LoadData();
lv3_LoadData();
}
//将数据加载到dgv上
public void dgv_LoadData()
{
DataTable dt = new DataTable();
//注意连接字符串别写错!!!
string str = "server = .;database = scut15;integrated security = true";
string sql = "select * from employee;";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
dgv.DataSource = dt;
}
//用于增加记录
private void btn1_Click(object sender, EventArgs e)
{
string empno = textBox1.Text.Trim();
string empname = textBox2.Text.Trim();
int empage = Convert.ToInt32(textBox3.Text.Trim());
string empsex;
if (radioButton1.Checked)//注意empsex的写法
{
empsex = "男";
}
else if (radioButton2.Checked)
{
empsex = "女";
}
else
{
MessageBox.Show("请输入性别");
return;
}
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
string sql1 = string.Format("select count(*)from employee where empno = '{0}';",empno);
//注意string.format即带参数的sql语句的写法的正确写法!!!
string sql2 = string.Format("insert into employee values('{0}','{1}','{2}','{3}')",empno,empname,empsex,empage);
using (SqlCommand cmd1 = new SqlCommand(sql1,con))
{
con.Open();
//一定要考虑工号重复的问题!!!
int r = Convert.ToInt32(cmd1.ExecuteScalar());
if (r > 0)
{
MessageBox.Show("工号重复");
}
else
{
using (SqlCommand cmd2 = new SqlCommand(sql2, con))
{
cmd2.ExecuteNonQuery();
}
}
}
}
dgv_LoadData();
cb_LoadData();
}
//选择dgv的某一行将数据加载到前端,以便于删除和修改
private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
{
//记住选定dgv的某一个格子单元的数据的写法,且用于修改删除的工号用的是label
label12.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[0].Value);
textBox5.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[1].Value);
textBox4.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[3].Value);
}
//用于删除记录
private void btn2_Click(object sender, EventArgs e)
{
string empno = label12.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
string sql = string.Format("delete from employee where empno = '{0}'",empno);
using (SqlCommand cmd = new SqlCommand(sql,con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}
dgv_LoadData();
cb_LoadData();
}
//用于修改记录
private void btn3_Click(object sender, EventArgs e)
{
string empno = label12.Text.Trim();
string empname = textBox5.Text.Trim();
int empage = Convert.ToInt32(textBox4.Text.Trim());
string empsex;//注意empsex的写法!!!
if (radioButton3.Checked)
{
empsex = "男";
}
else if (radioButton4.Checked)
{
empsex = "女";
}
else
{
MessageBox.Show("请输入性别");
return;
}
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
//注意update的写法!!!!
string sql = string.Format("update employee set empname = '{1}',empsex = '{2}',empage = '{3}'where empno = '{0}';",empno,empname, empsex, empage);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}
dgv_LoadData();
cb_LoadData();
}
//将工号和姓名分别加载到cb1和cb2两个下拉菜单
public void cb_LoadData()
{
DataTable dt = new DataTable();
//注意将要显示的列与主键一起查出来!!!
string sql = "select empno,empname from employee;";
string str = "server = .;database = scut15;integrated security = true";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
//注意要先写绑定再写valuemember即主键,再写要在cb上显示的字段!!!
cb1.DataSource = dt;
cb1.ValueMember = "empno";
cb1.DisplayMember = "empno";
cb2.DataSource = dt;
cb2.ValueMember = "empno";
cb2.DisplayMember = "empname";
}
//根据员工号查询
private void btn4_Click(object sender, EventArgs e)
{
//要加上这一句!!!
lv1.Clear();
DataTable dt = new DataTable();
string empno = cb1.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
string sql = string.Format("select cmpname,salary from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empno = '{0}'",empno);
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
lv1.Columns.Add("公司名");
lv1.Columns.Add("工资");
//i小于的是dt的行数!!!和dt的一个单元的数据的写法,记住要new lv的行对象!!!
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv1.Items.Add(lvi);
}
}
//根据员工姓名查询
private void btn5_Click(object sender, EventArgs e)
{
lv1.Clear();
DataTable dt = new DataTable();
string empname = cb2.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
string sql = string.Format("select cmpname,salary from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empname = '{0}'",empname);
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
//要为lv1添加列!!!然后再加行
lv1.Columns.Add("公司名");
lv1.Columns.Add("工资");
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv1.Items.Add(lvi);
}
}
//将查询结果加载到lv2
public void lv2_LoadData()
{
DataTable dt = new DataTable();
string str = "server = .;database = scut15;integrated security = true";
string sql = "select e.empno'工号',e.empname'姓名',sum(salary)'总工资' from employee\"e\" join works\"w\" on e.empno = w.empno where empage>40 group by e.empno,empname order by sum(salary) desc";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
lv2.Columns.Add("工号");
lv2.Columns.Add("姓名");
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv2.Items.Add(lvi);
}
}
//将查询结果加载到lv3
public void lv3_LoadData()
{
DataTable dt = new DataTable();
string str = "server = .;database = scut15;integrated security = true";
string sql = "select empname,cmpname from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empno in (select empno from works group by empno having count(empno)>1)";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
lv3.Columns.Add("员工名");
lv3.Columns.Add("公司名");
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv3.Items.Add(lvi);
}
}
}
}
{
dgv_LoadData();
cb_LoadData();
lv2_LoadData();
lv3_LoadData();
}
//将数据加载到dgv上
public void dgv_LoadData()
{
DataTable dt = new DataTable();
//注意连接字符串别写错!!!
string str = "server = .;database = scut15;integrated security = true";
string sql = "select * from employee;";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
dgv.DataSource = dt;
}
//用于增加记录
private void btn1_Click(object sender, EventArgs e)
{
string empno = textBox1.Text.Trim();
string empname = textBox2.Text.Trim();
int empage = Convert.ToInt32(textBox3.Text.Trim());
string empsex;
if (radioButton1.Checked)//注意empsex的写法
{
empsex = "男";
}
else if (radioButton2.Checked)
{
empsex = "女";
}
else
{
MessageBox.Show("请输入性别");
return;
}
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
string sql1 = string.Format("select count(*)from employee where empno = '{0}';",empno);
//注意string.format即带参数的sql语句的写法的正确写法!!!
string sql2 = string.Format("insert into employee values('{0}','{1}','{2}','{3}')",empno,empname,empsex,empage);
using (SqlCommand cmd1 = new SqlCommand(sql1,con))
{
con.Open();
//一定要考虑工号重复的问题!!!
int r = Convert.ToInt32(cmd1.ExecuteScalar());
if (r > 0)
{
MessageBox.Show("工号重复");
}
else
{
using (SqlCommand cmd2 = new SqlCommand(sql2, con))
{
cmd2.ExecuteNonQuery();
}
}
}
}
dgv_LoadData();
cb_LoadData();
}
//选择dgv的某一行将数据加载到前端,以便于删除和修改
private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
{
//记住选定dgv的某一个格子单元的数据的写法,且用于修改删除的工号用的是label
label12.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[0].Value);
textBox5.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[1].Value);
textBox4.Text = Convert.ToString(dgv.Rows[e.RowIndex].Cells[3].Value);
}
//用于删除记录
private void btn2_Click(object sender, EventArgs e)
{
string empno = label12.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
string sql = string.Format("delete from employee where empno = '{0}'",empno);
using (SqlCommand cmd = new SqlCommand(sql,con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}
dgv_LoadData();
cb_LoadData();
}
//用于修改记录
private void btn3_Click(object sender, EventArgs e)
{
string empno = label12.Text.Trim();
string empname = textBox5.Text.Trim();
int empage = Convert.ToInt32(textBox4.Text.Trim());
string empsex;//注意empsex的写法!!!
if (radioButton3.Checked)
{
empsex = "男";
}
else if (radioButton4.Checked)
{
empsex = "女";
}
else
{
MessageBox.Show("请输入性别");
return;
}
string str = "server = .;database = scut15;integrated security = true";
using (SqlConnection con = new SqlConnection(str))
{
//注意update的写法!!!!
string sql = string.Format("update employee set empname = '{1}',empsex = '{2}',empage = '{3}'where empno = '{0}';",empno,empname, empsex, empage);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}
dgv_LoadData();
cb_LoadData();
}
//将工号和姓名分别加载到cb1和cb2两个下拉菜单
public void cb_LoadData()
{
DataTable dt = new DataTable();
//注意将要显示的列与主键一起查出来!!!
string sql = "select empno,empname from employee;";
string str = "server = .;database = scut15;integrated security = true";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
//注意要先写绑定再写valuemember即主键,再写要在cb上显示的字段!!!
cb1.DataSource = dt;
cb1.ValueMember = "empno";
cb1.DisplayMember = "empno";
cb2.DataSource = dt;
cb2.ValueMember = "empno";
cb2.DisplayMember = "empname";
}
//根据员工号查询
private void btn4_Click(object sender, EventArgs e)
{
//要加上这一句!!!
lv1.Clear();
DataTable dt = new DataTable();
string empno = cb1.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
string sql = string.Format("select cmpname,salary from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empno = '{0}'",empno);
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
lv1.Columns.Add("公司名");
lv1.Columns.Add("工资");
//i小于的是dt的行数!!!和dt的一个单元的数据的写法,记住要new lv的行对象!!!
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv1.Items.Add(lvi);
}
}
//根据员工姓名查询
private void btn5_Click(object sender, EventArgs e)
{
lv1.Clear();
DataTable dt = new DataTable();
string empname = cb2.Text.Trim();
string str = "server = .;database = scut15;integrated security = true";
string sql = string.Format("select cmpname,salary from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empname = '{0}'",empname);
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
//要为lv1添加列!!!然后再加行
lv1.Columns.Add("公司名");
lv1.Columns.Add("工资");
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv1.Items.Add(lvi);
}
}
//将查询结果加载到lv2
public void lv2_LoadData()
{
DataTable dt = new DataTable();
string str = "server = .;database = scut15;integrated security = true";
string sql = "select e.empno'工号',e.empname'姓名',sum(salary)'总工资' from employee\"e\" join works\"w\" on e.empno = w.empno where empage>40 group by e.empno,empname order by sum(salary) desc";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql,str))
{
adapter.Fill(dt);
}
lv2.Columns.Add("工号");
lv2.Columns.Add("姓名");
lv2.Columns.Add("总工资");
//lv1.Columns.Add("工号", lv1.Width / 4 - 1, HorizontalAlignment.Left);
//lv1.Columns.Add("姓名", lv1.Width / 4 - 1, HorizontalAlignment.Left);
//lv1.Columns.Add("年龄", lv1.Width / 4 - 1, HorizontalAlignment.Left);
//lv1.Columns.Add("总工资", lv1.Width / 4 - 1, HorizontalAlignment.Left);
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv2.Items.Add(lvi);
}
}
//将查询结果加载到lv3
public void lv3_LoadData()
{
DataTable dt = new DataTable();
string str = "server = .;database = scut15;integrated security = true";
string sql = "select empname,cmpname from employee\"e\" join works\"w\" on e.empno = w.empno join company\"c\" on w.cmpno = c.cmpno where e.empno in (select empno from works group by empno having count(empno)>1)";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str))
{
adapter.Fill(dt);
}
lv3.Columns.Add("员工名");
lv3.Columns.Add("公司名");
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem lvi = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 0)
{
lvi.Text = Convert.ToString(dt.Rows[i][j]);
}
else
{
lvi.SubItems.Add(Convert.ToString(dt.Rows[i][j]));
}
}
lv3.Items.Add(lvi);
}
}
}
}