2015-CS
数据库部分
create table [EMPLOYEE](
[EmpNo] varchar(10) not null primary key,
[EmpName] varchar(10) not null,
[EmpSex] varchar(5) check([EmpSex]='男' or [EmpSex]='女'),
[EmpAge] int check([EmpAge]>0)
)
create table [COMPANY](
[CmpNo] varchar(10) not null primary key,
[CmpName] varchar(10) not null
)
create table [WORKS](
[EmpNo] varchar(10) not null references [EMPLOYEE]([EmpNo]),
[CmpNo] varchar(10) not null references [COMPANY]([CmpNo]),
[Salary] int check([Salary]>0)
)
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);
DB.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data; //DataTable 用到
using System.Data.SqlClient; //一些列数据库操作类用到
namespace test0314
{
internal class DB:IDisposable
{
private SqlConnection sqlConnection;
public DB() //私有无参构造函数
{
//采用Windows身份验证，关键是Trusted_Connection=SSPI
sqlConnection = new SqlConnection(@"server=.;database=test0314;Trusted_Connection=SSPI;");
sqlConnection.Open();
}
//查询
public DataTable GetBySql(string sql)
{
SqlDataAdapter adapter = new SqlDataAdapter(new SqlCommand(sql,sqlConnection));
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
//修改
public void SetBySql(string sql)
{
new SqlCommand(sql).ExecuteNonQuery();
}
//相当于析构函数
public void Dispose()
{
sqlConnection.Close();
}
//在C#中关闭数据库连接不能在类的析构函数中关闭，否则会抛出“内部.Net Framework 数据提供程序错误 1”的异常
//通过实现C#中IDisposable接口中的Dispose() 方法主要用途是释放非托管资源。
}
}
Intent.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using test0314;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
namespace test0314
{
internal class Intent
{
//便于窗体间传送数据
//存数据的字典容器
public static Dictionary<string,Object> dict = new Dictionary<string,Object>();
}
}
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;//使用到了正则表达式
using System.Globalization;
namespace test0314
{
public partial class Form1 : Form
{
DB db;
public Form1()
{
InitializeComponent();
db = new DB();
}
private void Form1_Load(object sender, EventArgs e)
{
//第一张表增删改查的”查“
//生成表头
listView1.Columns.Add("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("员工名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("性别", listView1.Width / 4 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("年龄", listView1.Width / 4 - 1, HorizontalAlignment.Left);
//查数据库
DataTable dataTable = db.GetBySql(@"select * from [EMPLOYEE]");
//更新表的内容
listView1.BeginUpdate(); //数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
for(int i= 0; i < dataTable.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem();//生成每一行
for(int j = 0; j < dataTable.Columns.Count; j++)
{
if (j <= 0) //listView 每行第一项与其余子项的添加是不同的。
{
listViewItem.Text = dataTable.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dataTable.Rows[i][j].ToString());
}
}
listView1.Items.Add(listViewItem);
}
listView1.EndUpdate();//结束数据处理，UI界面一次性绘制
//查询3-1：根据员工号和员工名查询所在公司和工资
//初始时只需要更新下拉列表框的数据以便选择后进行查询
//加载现有的员工号到下拉列表框1
dataTable = db.GetBySql(@"select [EmpNo] from [EMPLOYEE]");
for(int i= 0;i < dataTable.Rows.Count;i++)
{
for(int j = 0; j < dataTable.Columns.Count; j++)
{
comboBox1.Items.Add(dataTable.Rows[i][j].ToString());
}
}
//下拉列表框1默认选中第一个数据显示出来
comboBox1.SelectedIndex = 0;
//加载现有员工名到下拉列表框2
dataTable = db.GetBySql(@"select [EmpName] from [EMPLOYEE]");
for (int i= 0;i< dataTable.Rows.Count; i++)
{
for (int j= 0; j < dataTable.Columns.Count; j++)
{
comboBox2.Items.Add(dataTable.Rows[i][j].ToString());
}
}
//下拉列表框2默认选中第一个数据显示出来
comboBox2.SelectedIndex = 0;
//查询3-2：查询年龄至少为40岁的员工的总工资，工资按从小到大排序
//生成表头
listView3.Columns.Add("员工号", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("员工名", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("性别", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("年龄", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("总工资", listView1.Width / 5 - 1, HorizontalAlignment.Left);
//查数据库
dataTable = db.GetBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],[EMPLOYEE].[EmpAge],sum([WORKS].[Salary]) as '总工资' from [EMPLOYEE],[WORKS] where [EMPLOYEE].[EmpAge]>=40 and [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo] group by [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[EMPLOYEE].[EmpSex],[EMPLOYEE].[EmpAge] order by '总工资' desc ");
//更新表的内容
listView3.BeginUpdate();//数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
for (int i=0;i<dataTable.Rows.Count;i++)
{
ListViewItem listViewItem = new ListViewItem();//生成每一行
for(int j=0;j<dataTable.Columns.Count;j++)
{
if (j <= 0)
{
listViewItem.Text = dataTable.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dataTable.Rows[i][j].ToString());
}
}
listView3.Items.Add(listViewItem);
}
listView3.EndUpdate();//结束数据处理，UI界面一次性绘制
//查询3-3 ：查询至少有两份工作员工的姓名和公司名
//生成表头
listView4.Columns.Add("员工名",listView1.Width/2 - 2, HorizontalAlignment.Left); //生成表头
listView4.Columns.Add("公司名",listView1.Width/2 - 2, HorizontalAlignment.Left);
//查数据库
dataTable = db.GetBySql(@"select [EMPLOYEE].[EmpName],[COMPANY].[CmpName] from [EMPLOYEE],[COMPANY],[WORKS],(select [EmpName],count([CmpName]) as 'CmpNum' from [EMPLOYEE],[COMPANY],[WORKS] where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo] and [COMPANY].[CmpNo]=[WORKS].[CmpNo] group by [EmpName] having count([CmpName])>1) as t1 where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo] and [COMPANY].[CmpNo]=[WORKS].[CmpNo] and [EMPLOYEE].[EmpName]=t1.[EmpName];");
//更新表的内容
listView4.BeginUpdate(); //数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
for(int i=0;i<dataTable.Rows.Count;i++)
{
ListViewItem listViewItem = new ListViewItem();//生成每一行
for(int j=0;j<dataTable.Columns.Count;j++)
{
if (j <= 0)
{
listViewItem.Text = dataTable.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dataTable.Rows[i][j].ToString());
}
}
listView4.Items.Add(listViewItem);
}
listView4.EndUpdate(); //结束数据处理，UI界面一次性绘制
}
//添加按钮
private void button1_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();//声明要使用form2窗体
//将form1当前的位置压入 Intent中的dict
Intent.dict["form1_text"] = this.Text;
Intent.dict["form1_flag"] = 0; //传个flag =0 表示这是”添加“
if(form2.ShowDialog() == DialogResult.OK)
{//这个判断，将会等到form2被关闭后才执行，如果返回一个OK值的话
bool canAdd = true;
foreach(ListViewItem item in this.listView1.Items)
{
if (Intent.dict["form2_textbox1_text"].ToString() == item.SubItems[0].Text)
{
canAdd = false;
MessageBox.Show("该员工已经存在！", this.Text);
break;
}
}
Regex regex = new Regex("^[0-9]*$");
//利用正则表达式判断是否输入的是数字
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"].ToString()))
{
canAdd = false;
MessageBox.Show("年龄不为正数！",this .Text);
}
if(canAdd)
{
//在listView中添加一项
ListViewItem listViewItem = new ListViewItem();
//listViewItem的第一项与其他项的添加不一样
listViewItem.Text = Intent.dict["form2_textbox1_text"].ToString();
listViewItem.SubItems.Add(Intent.dict["form2_textbox2_text"].ToString());
listViewItem.SubItems.Add(Intent.dict["form2_radioButton"].ToString());
listViewItem.SubItems.Add(Intent.dict["form2_textbox3_text"].ToString());
listView1.Items.Add(listViewItem);
db.SetBySql("insert into [EMPLOYEE] values('" + Intent.dict["form2_textbox1_text"] + "','" + Intent.dict["form2_textbox2_text"] + "','" + Intent.dict["form2_radioButton"] + "','" + Intent.dict["form2_textbox3_text"] + ")");
}
}
}
//修改按钮
private void button2_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2();//声明要使用form2窗体
//将form1当前的位置压入Intent中的dict
Intent.dict["form1_text"] = this.Text;
Intent.dict["form1_flag"] = 1; //传flag = 1 表示这是“修改”
Intent.dict["form1_selectedItems0"] = listView1.SelectedItems[0].SubItems[0].Text;
Intent.dict["form1_selectedItems1"] = listView1.SelectedItems[0].SubItems[1].Text;
Intent.dict["form1_selectedItems2"] = listView1.SelectedItems[0].SubItems[2].Text;
Intent.dict["form1_selectedItems3"] = listView1.SelectedItems[0].SubItems[3].Text;
Intent.dict["form1_flag"] = 1;
if(form2.ShowDialog() == DialogResult.OK)
{//这个判断，将会等到form2 被关闭之后才执行，如果form2返回一个OK值
bool canUpdate = true;
//仅仅当用户修改过员工号才进行遍历
if (!(Intent.dict["form1_selectedItems0"].ToString() == Intent.dict["form2_textbox1_text"]))
{
foreach(ListViewItem item in this.listView1.Items)
{
if (Intent.dict["form2_textbox1_text"] == item.SubItems[0].Text)
{
canUpdate = false;
MessageBox.Show("已经存在该员工号！", this.Text);
break;
}
}
}
Regex regex = new Regex("^[0-9]*$");
if (!regex.IsMatch(Intent.dict["form2_textbox3_text"].ToString()))//利用正则表达式判读输入的是否是数字
{
canUpdate = false;
MessageBox.Show("年龄不正确！", this.Text);
}
if(canUpdate)
{
//在listView中添加一项
ListViewItem listViewItem = new ListViewItem();
listView1.SelectedItems[0].SubItems[0].Text = Intent.dict["form2_textbox1_text"].ToString();
listView1.SelectedItems[0].SubItems[1].Text = Intent.dict["form2_textbox2_text"].ToString();
listView1.SelectedItems[0].SubItems[2].Text = Intent.dict["form2_radioButton"].ToString();
listView1.SelectedItems[0].SubItems[3].Text = Intent.dict["form2_textbox3_text"].ToString();
db.SetBySql("update [EMPLOYEE] set [EmpNo]='" + Intent.dict["form2_textbox1_text"].ToString() + "' where [EmpNo]='" + Intent.dict["form1_selectedItems0"] + "';");
db.SetBySql("update [EMPLOYEE] set [EmpName]='" + Intent.dict["form2_textbox2_text"].ToString() + "' where [EmpName]='" + Intent.dict["form1_selectedItems1"] + "';");
db.SetBySql("update [EMPLOYEE] set [EmpSex]='" + Intent.dict["form2_radioButton"].ToString() + "' where [EmpSex]='" + Intent.dict["form1_selectedItems2"] + "';");
db.SetBySql("update [EMPLOYEE] set [EmpAge]='" + Intent.dict["form2_textbox3_text"].ToString() + "' where [EmpAge]='" + Intent.dict["form1_selectedItems3"] + "';");
}
}
}
//删除按钮
private void button3_Click(object sender, EventArgs e)
{
//传sql删除指令
db.SetBySql("delete from [EMPLOYEE] where [EmpNo]='" + listView1.SelectedItems[0].SubItems[0].Text + "';");
//显示出来的表数据的删除一定要放在在数据库操作之后，否则选中项再也取不到了。
listView1.SelectedItems[0].Remove();
}
//根据员工 号 查所在公司和工资
private void button4_Click(object sender, EventArgs e)
{
listView2.Clear();
//生成表头
listView2.Columns.Add("员工号", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("员工名", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("公司名", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("工资数", listView2.Width / 4 - 1, HorizontalAlignment.Left);
//sql语句查询
DataTable dataTable = db.GetBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[COMPANY].[CmpName],[WORKS].[Salary] from [EMPLOYEE],[COMPANY],[WORKS] where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo] and [COMPANY].[CmpNo]=[WORKS].[CmpNo] and [EMPLOYEE].[EmpNo]='" + comboBox1.Text + "';");
listView2.BeginUpdate(); //数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem();//生成每一行
for (int j = 0; j < dataTable.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = dataTable.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dataTable.Rows[i][j].ToString());
}
}
listView2.Items.Add(listViewItem);
}
listView2.EndUpdate(); //结束数据处理，UI界面一次性绘制
}
//根据员工 名 查所在公司和工资
private void button5_Click(object sender, EventArgs e)
{
listView2.Clear();
//生成表头
listView2.Columns.Add("员工号", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("员工名", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("公司名", listView2.Width / 4 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("工资数", listView2.Width / 4 - 1, HorizontalAlignment.Left);
//sql语句查询
DataTable dataTable = db.GetBySql(@"select [EMPLOYEE].[EmpNo],[EMPLOYEE].[EmpName],[COMPANY].[CmpName],[WORKS].[Salary] from [EMPLOYEE],[COMPANY],[WORKS] where [EMPLOYEE].[EmpNo]=[WORKS].[EmpNo] and [COMPANY].[CmpNo]=[WORKS].[CmpNo] and [EMPLOYEE].[EmpName]='" + comboBox2.Text + "';");
listView2.BeginUpdate(); //数据更新，UI暂时挂起，直到EndUpdate绘制控件，可以有效避免闪烁并大大提高加载速度
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem();//生成每一行
for (int j = 0; j < dataTable.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = dataTable.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dataTable.Rows[i][j].ToString());
}
}
listView2.Items.Add(listViewItem);
}
listView2.EndUpdate(); //结束数据处理，UI界面一次性绘制
}
}
}
Form2.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace test0314
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
if((int)Intent.dict["form1_flag"] == 0) //flag=0 表示添加，对应添加按钮
{
this.Text = Intent.dict["form1_text"].ToString();
textBox1.Focus();//设置焦点停留在 textBox1中，即“员工号”
}
else //flag=1 表示修改，对应修改按钮
{
this.Text = Intent.dict["form1_text"]+"";
textBox1.Text = Intent.dict["form1_selectedItems0"].ToString();
textBox2.Text = Intent.dict["form1_selectedItems1"].ToString();
if(Intent.dict["form1_selectedItems2"].ToString() == "男")
{
radioButton1.Checked = true;
}
else
{
radioButton2.Checked = true;
}
textBox3.Text = Intent.dict["form1_selectedItems3"].ToString();
textBox1.Focus();//设置焦点停留在textBox1中
textBox1.SelectAll();//要现有焦点才能全选
}
}
//确认按钮
private void button1_Click(object sender, EventArgs e)
{
if(textBox1.Text=="" || textBox2.Text=="" || (!radioButton1 .Checked && !radioButton2.Checked) || textBox3.Text == "")
{
MessageBox.Show("没有填写完整！", this.Text);
}
else
{
//关闭form2之间，将要穿给form1 的值压入 Intent中的dict
Intent.dict["form2_textbox1_text"] = textBox1.Text;
Intent.dict["form2_textbox2_text"] = textBox2.Text;
if (radioButton1.Checked)
{
Intent.dict["form2_radioButton"] = "男";
}
else
{
Intent.dict["form2_radioButton"] = "女";
}
Intent.dict["form2_textbox3_text"] = textBox3.Text;
this.DialogResult = DialogResult.OK;//同时设置返回值为OK，如不设置的话，默认返回值为cancel
this.Close();
}
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
}
}