一、数据库创建
(1)新建查询
第一步建数据库和表
create database test2015
use test2015
create table EMPLOYEE(
EmpNo varchar(20) primary key,
EmpName varchar(20) not null,
EmpSex varchar(10),
EmpAge int
)
create table COMPANY(
CmpNo varchar(20) primary key,
CmpName varchar(20)
)
create table WORKS(
EmpNo varchar(20),
CmpNo varchar(20),
Salary int,
foreign key (EmpNo) references EMPLOYEE (EmpNo),
foreign key (CmpNo) references COMPANY (CmpNo),
primary key (EmpNo,CmpNo)
)
第二步,插入数据
--向员工表中插入数据
insert into EMPLOYEE values ('E01','张三','女',32);
insert into EMPLOYEE values ('E02','李四','男',28);
insert into EMPLOYEE values ('E03','王五','女',42);
insert into EMPLOYEE values ('E04','赵六','男',37);
insert into EMPLOYEE values ('E05','陈七','男',51);
--向公司表中插入数据
insert into COMPANY values ('C01','阳光科技');
insert into COMPANY values ('C02','晨光科技');
insert into COMPANY values ('C03','未来科技');
--向工作表中插入数据
insert into WORKS values ('E01','C01',3000);
insert into WORKS values ('E01','C02',4000);
insert into WORKS values ('E02','C02',5000);
insert into WORKS values ('E02','C03',2500);
insert into WORKS values ('E03','C01',3500);
insert into WORKS values ('E04','C02',3000);
insert into WORKS values ('E05','C03',2000);
二、编写窗体程序
(1)先编写一个通用dao类
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace test2015
{
class SqlManage
{
//这是另一种登录方法,一般Windows登录更方便
//string str = @"Persist Security Info=False;Initial Catalog=test2013;Data Source=DESKTOP-8MOGREQ;User ID=sa;Password=123456";
public static string cnnstring = @"Data Source=DESKTOP-8MOGREQ;Initial Catalog=test2015;Integrated Security=True";
public static SqlConnection cnn = null;
public static void TableChange(string sql)
{
try
{
SqlCommand cmd = new SqlCommand(sql, SqlManage.cnn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("操作成功!");
}
else
{
MessageBox.Show("操作失败!\n检查数据库是否有该记录");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static DataTable TableSelect(string sql)
{
try
{
DataTable table = new DataTable();
SqlDataAdapter sdp = new SqlDataAdapter(sql, SqlManage.cnn);
sdp.Fill(table);
return table;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
}
}
}
(2)主窗口改为FormMain.cs,并将program.cs改为以下:(记得修改namespace名称)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace test2015
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
try
{
SqlManage.cnn = new SqlConnection(SqlManage.cnnstring);
SqlManage.cnn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Application.Exit();
return;
}
Application.Run(new FormMain());
SqlManage.cnn.Close();
}
}
}
(3)加入两个窗体,分别为FormEmploy、FormSearch
在FormMain代码中加入以下:
private void button1_Click(object sender, EventArgs e)
{
FormEmploy fe = new FormEmploy();
this.Hide();
fe.ShowDialog();
this.Show();
}
private void button2_Click(object sender, EventArgs e)
{
FormSearch fs = new FormSearch();
this.Hide();
fs.ShowDialog();
this.Show();
}
private void button3_Click(object sender, EventArgs e)
{
Application.Exit();
}
(4)编写员工管理窗口
//显示Table函数
public void Table()
{
this.dataGridView1.ReadOnly = true;
this.dataGridView1.MultiSelect = false;//不能选择多行,只能选择一行
this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//不是选择一个字段,而是一整行
this.dataGridView1.RowHeadersVisible = false;//取消最左侧一栏
this.dataGridView1.AllowUserToAddRows = false;//不允许用户自己添加一行
this.dataGridView1.AllowUserToDeleteRows = false;//禁止用户自己删除一行
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
string sql = "select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE";
DataTable table = SqlManage.TableSelect(sql);
this.dataGridView1.DataSource = table;
label2.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
}
//加载函数
private void FormEmploy_Load(object sender, EventArgs e)
{
Table();
}
新建两个窗体FormEmployAdd和FormEmployUpdate(之后再设计)
以下是员工管理具体操作:
//浏览员工信息函数
private void button1_Click(object sender, EventArgs e)
{
string sql = "select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE";
DataTable table = SqlManage.TableSelect(sql);
this.dataGridView1.DataSource = table;
}
//添加员工信息
private void button2_Click(object sender, EventArgs e)
{
FormEmployAdd formEmployAdd = new FormEmployAdd();
this.Hide();
formEmployAdd.ShowDialog();
Table();
this.Show();
}
//删除员工
private void button3_Click(object sender, EventArgs e)
{
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
DialogResult dr = MessageBox.Show("确认删除吗?", "信息提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if(dr == DialogResult.OK)
{
string sql = $"delete from EMPLOYEE where EmpNo='{id}'";
SqlManage.TableChange(sql);
Table();
}
}
//修改员工信息
private void button4_Click(object sender, EventArgs e)
{
string EmpNo = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string EmpName = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string EmpSex = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
string EmpAge = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
FormEmployUpdate formEmployUpdate = new FormEmployUpdate(EmpNo, EmpName, EmpSex, EmpAge);
this.Hide();
formEmployUpdate.ShowDialog();
Table();
this.Show();
}
员工管理全部代码如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace test2015
{
public partial class FormEmploy : Form
{
public FormEmploy()
{
InitializeComponent();
}
private void FormEmploy_Load(object sender, EventArgs e)
{
Table();
}
public void Table()
{
this.dataGridView1.ReadOnly = true;
this.dataGridView1.MultiSelect = false;//不能选择多行,只能选择一行
this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//不是选择一个字段,而是一整行
this.dataGridView1.RowHeadersVisible = false;//取消最左侧一栏
this.dataGridView1.AllowUserToAddRows = false;//不允许用户自己添加一行
this.dataGridView1.AllowUserToDeleteRows = false;//禁止用户自己删除一行
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
string sql = "select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE";
DataTable table = SqlManage.TableSelect(sql);
this.dataGridView1.DataSource = table;
label2.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString()+ dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
}
private void button1_Click(object sender, EventArgs e)
{
string sql = "select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE";
DataTable table = SqlManage.TableSelect(sql);
this.dataGridView1.DataSource = table;
}
private void button2_Click(object sender, EventArgs e)
{
FormEmployAdd formEmployAdd = new FormEmployAdd();
this.Hide();
formEmployAdd.ShowDialog();
Table();
this.Show();
}
private void button3_Click(object sender, EventArgs e)
{
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取书号
DialogResult dr = MessageBox.Show("确认删除吗?", "信息提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if(dr == DialogResult.OK)
{
string sql = $"delete from EMPLOYEE where EmpNo='{id}'";
SqlManage.TableChange(sql);
Table();
}
}
private void button4_Click(object sender, EventArgs e)
{
string EmpNo = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string EmpName = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
string EmpSex = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
string EmpAge = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
FormEmployUpdate formEmployUpdate = new FormEmployUpdate(EmpNo, EmpName, EmpSex, EmpAge);
this.Hide();
formEmployUpdate.ShowDialog();
Table();
this.Show();
}
private void button5_Click(object sender, EventArgs e)
{
this.Close();
}
private void dataGridView1_Click(object sender, EventArgs e)
{
label2.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
}
}
}
(5)添加员工模块代码
private void button1_Click(object sender, EventArgs e)
{
string sex = "";
if (this.radioButton1.Checked)
{
sex = "男";
}
else
{
sex = "女";
}
string sql = string.Format("insert into EMPLOYEE values('{0}','{1}','{2}','{3}')",
this.textBox1.Text, this.textBox2.Text, sex, this.textBox3.Text);
SqlManage.TableChange(sql);
}
//退出该页
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
(6)修改员工模块代码
//建立一个新的有参构造函数
//前面已经在管理页面初始化绑定数据了
public FormEmployUpdate(string EmpNo,string EmpName,string EmpSex,string EmpAge)
{
InitializeComponent();
textBox1.Text = EmpNo;
textBox2.Text = EmpName;
if(EmpSex == "男")
{
this.radioButton1.Checked = true;
}
else
{
this.radioButton2.Checked = true;
}
textBox3.Text = EmpAge;
}
//点击确认修改后要做的操作
private void button1_Click(object sender, EventArgs e)
{
string sex = this.radioButton1.Checked ? "男" : "女";
string sql = string.Format("update EMPLOYEE set EmpName = '{1}',EmpSex = '{2}',EmpAge = '{3}' where EmpNo = '{0}'",
this.textBox1.Text, this.textBox2.Text, sex, this.textBox3.Text);
SqlManage.TableChange(sql);
}
//退出该页面
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
(7)查询与统计模块代码
记得要在每个下拉菜单,把行为>>enabled改为false,第一个改为true
把每个单选框,把外观>>checked改为false,第一个改为true
//初始化radiobutton和combobox,在radiobutton改变时,修改combobox可用状态。
//事件里的CheckedChanged绑定此类函数
private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
if (this.radioButton1.Checked)
{
this.comboBox1.Enabled = true;
}
else
{
this.comboBox1.Enabled = false;
}
}
private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
if (this.radioButton2.Checked)
{
this.comboBox2.Enabled = true;
}
else
{
this.comboBox2.Enabled = false;
}
}
private void radioButton3_CheckedChanged(object sender, EventArgs e)
{
if (this.radioButton3.Checked)
{
this.comboBox3.Enabled = true;
}
else
{
this.comboBox3.Enabled = false;
}
}
private void radioButton4_CheckedChanged(object sender, EventArgs e)
{
if (this.radioButton4.Checked)
{
this.comboBox4.Enabled = true;
}
else
{
this.comboBox4.Enabled = false;
}
}
//加载函数,绑定下拉菜单comboBox的数据,有些可以编辑列完成
private void FormSearch_Load(object sender, EventArgs e)
{
this.dataGridView1.ReadOnly = true;
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.RowHeadersVisible = false;
this.dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
//初始化员工编号
string sql = "select EmpNo from EMPLOYEE";
DataTable table = SqlManage.TableSelect(sql);
string tno;
foreach (DataRow row in table.Rows)
{
tno = row["EmpNo"].ToString();
this.comboBox1.Items.Add(tno);
}
if (table.Rows.Count > 0)
{
this.comboBox1.SelectedIndex = 0;
}
//初始化员工姓名
string sql_name = "select EmpName from EMPLOYEE";
table.Clear();
table = SqlManage.TableSelect(sql_name);
string tname;
foreach (DataRow row in table.Rows)
{
tname = row["EmpName"].ToString();
this.comboBox2.Items.Add(tname);
}
if (table.Rows.Count > 0)
{
this.comboBox2.SelectedIndex = 0;
}
//初始化课程数
if (this.comboBox4.Items.Count > 0)
{
this.comboBox4.SelectedIndex = 0;
}
}
(8)最重要的查询条件
private void button1_Click(object sender, EventArgs e)
{
string sql = "";
if (this.radioButton1.Checked)
{
sql = string.Format("select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE where EmpNO = '{0}'",
this.comboBox1.Text);
}
else if (this.radioButton2.Checked)
{
sql = string.Format("select EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄 from EMPLOYEE where EmpName = '{0}'",
this.comboBox2.Text);
}
else if (this.radioButton3.Checked)
{
if(this.comboBox3.Text == "工资降序")
{
sql = "select EMPLOYEE.EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄,Salary as 工资 from EMPLOYEE,WORKS where EMPLOYEE.EmpNo = WORKS.EmpNo and EmpAge >= 40 ORDER BY EmpAge";
}
else if(this.comboBox3.Text == "总工资")
{
sql = "select sum(Salary) as 总工资 from EMPLOYEE,WORKS where EMPLOYEE.EmpNo = WORKS.EmpNo and EmpAge >= 40";
}
else
{
MessageBox.Show("没有相关内容");
}
//sql = string.Format("select EMPLOYEE.EmpNo as 员工编号,EmpNAME as 员工姓名,EmpSEX as 性别,EmpAge as 年龄,Salary as 工资 from EMPLOYEE,WORKS where EMPLOYEE.EmpNo = WORKS.EmpNo and EmpAge >= 40 ORDER BY EmpAge ",
//this.comboBox3.Text);
}
else
{
sql = "select EmpNAME as 员工名,CmpName as 公司名 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo = WORKS.EmpNo and WORKS.CmpNo = COMPANY.CmpNo and WORKS.EmpNo in(select EmpNo from WORKS group by EmpNo having COUNT(CmpNo) >=2)";
//sql = "select EmpNAME as 员工名,CNAME as 课程名 from TEACHERS,COURSES,WORKS";
//sql += " where TEACHERS.TNO = WORKS.TNO and WORKS.CNO = COURSES.CNO and WORKS.TNO in";
//sql += " (select TNO from WORKS group by TNO having COUNT(CNO) >=2);";
}
DataTable table = SqlManage.TableSelect(sql);
if (table.Rows.Count > 0)
{
this.dataGridView1.DataSource = table;
}
else
{
MessageBox.Show("没有相关内容");
}
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}