原文章:https://blog.csdn.net/yongh701/article/details/50467129
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text; `//using System.Collections.Generic;
//using System.ComponentModel;
//using System.Drawing;
//using System.Linq;
//using System.Text;
//using System.Threading.Tasks;
using System;
using System.Windows.Forms;
using System.Data.SqlClient;// 不加会出现 未能找到类型或命名空间sqlConnection
using System.Data;// 不加会出现 当前上下文不存在名称connectionstate
//using System.Diagnostics;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
// 默认选中第一项
comboBox1.Text = "请选择员工号";
comboBox1.SelectedIndex = 0;
comboBox2.Text = "请选择员工名字";
comboBox2.SelectedIndex = 0;
//必须先创建表头
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);
listView2.Columns.Add("员工号", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("员工姓名", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("性别", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("年龄", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView2.Columns.Add("总工资", listView1.Width / 5 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("员工姓名", listView1.Width / 2 - 1, HorizontalAlignment.Left);
listView3.Columns.Add("公司名", listView1.Width / 2 - 1, HorizontalAlignment.Left);
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
//listView1.Items.Clear(); //清空
//ListViewItem item1 = new ListViewItem();
//listView1.Columns.Add("员工号", listView1.Width / 4 - 1,HorizontalAlignment.Left);
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//string view = (sender as ComboBox).SelectedItem as string;
//从字符串中还原枚举值
//View v = (View)Enum.Parse(typeof(View), view);
//listView1.View = v; //设置ListView的呈现方式
}
private void button1_Click(object sender, EventArgs e)
{
string connStr = "Data Source=DESKTOP-CHKF0KA;" +
"Initial Catalog=temp;Integrated Security=True";
//DESKTOP-CHKF0KA 连接的数据库服务器名
//temp 连接的数据库名
SqlConnection conn = new SqlConnection(connStr);
//SqlConnection 变量名 = new SqlConnection(参数字符串或存储了参数字符串的变量);
//测试数据
conn.Open();
if (conn.State == ConnectionState.Open)
{
//MessageBox.Show("数据库连接成功!");//winform中可以用
Console.WriteLine("连接成功");//控制台用
}
//conn.Close();//关闭数据库链接,用完就关,别挂着
//试试根据输入查询数据 + 两个form传递数据?
string t1 = comboBox1.Text;//EmpNo
string sql = String.Format("select * from works w join employee e on w.EmpNo = e.EmpNo " +
"join company c on w.CmpNo = c.CmpNo " +
"where e.EmpNo = '{0}'", t1);//EmpName = '{0}' and t1,
SqlCommand cmd = new SqlCommand(sql, conn);
listView1.Items.Clear();
try
{
SqlDataReader re = cmd.ExecuteReader();//.ExecuteNonQuery();
//1.使用ExecuteReader()操作数据库 https://www.jianshu.com/p/7b35cbc7df75
//2.使用ExecuteNonQuery()操作数据库
//3.使用ExecuteScalar()操作数据库
//4.使用DataSet数据集插入记录,更新数据
Console.WriteLine("修改成功");
if (!re.HasRows)//判断是否为空
{
comboBox1.Text = "不存在该员工";
return;
}
//re.Read();
//textBox1.Text = re["EmpName"].ToString();
this.listView1.BeginUpdate(); //数据更新,UI暂时挂起,直到EndUpdate绘制控件,可以有效避免闪烁并大大提高加载速度
//this.listView1.Columns.Add("查询结果", 120, HorizontalAlignment.Left); //一步添加
for (int i = 0; re.Read(); i++) //添加10行数据
{
ListViewItem lvi = new ListViewItem();
// **方法4(最好理解) * *
// ListViewItem LVI = new ListViewItem("11"); //11是String型,为第1列内容
// LVI.SubItems.Add("22");//第二列
// LVI.SubItems.Add("33");
// listView1.Items.Add(LVI);
//lvi.ImageIndex = i; //通过与imageList绑定,显示imageList中第i项图标
lvi.Text = re["EmpNo"].ToString();
lvi.SubItems.Add(re["EmpName"].ToString());
lvi.SubItems.Add(re["EmpSex"].ToString());
lvi.SubItems.Add(re["EmpAge"].ToString());
this.listView1.Items.Add(lvi);
//listView1.Text += re["EmpNo"].ToString();
}
this.listView1.EndUpdate(); //结束数据处理,UI界面一次性绘制。
}
catch (SqlException ae)
{
Console.WriteLine("修改失败");
Console.WriteLine(ae.Message);
MessageBox.Show("error");
}
finally
{
conn.Close();//对数据库操作完成后,需要关闭数据库,释放内存
}
}
private void tabPage1_Click(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void label2_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button2_Click(object sender, EventArgs e)
{
string connStr = "Data Source=DESKTOP-CHKF0KA;" +
"Initial Catalog=temp;Integrated Security=True";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
if (conn.State == ConnectionState.Open)
{
Console.WriteLine("连接成功");//控制台用
}
string t2 = comboBox2.Text;//选项框本来就可以输入文本
//string t2 = textBox1.Text;//不用文本框
string sql = String.Format("select * from works w join employee e on w.EmpNo = e.EmpNo " +
"join company c on w.CmpNo = c.CmpNo " +
"where e.EmpName = '{0}'", t2);//EmpName = '{0}' and t1,
SqlCommand cmd = new SqlCommand(sql, conn);
listView1.Items.Clear();
try
{
SqlDataReader re = cmd.ExecuteReader();//.ExecuteNonQuery();
Console.WriteLine("修改成功");
if (!re.HasRows)//判断是否为空
{
comboBox1.Text = "不存在该员工"; return;
}
this.listView1.BeginUpdate(); //数据更新,UI暂时挂起,直到EndUpdate绘制控件,可以有效避免闪烁并大大提高加载速度
for (int i = 0; re.Read(); i++) //添加10行数据
{
ListViewItem lvi = new ListViewItem();
lvi.Text = re["EmpNo"].ToString();
lvi.SubItems.Add(re["EmpName"].ToString());
lvi.SubItems.Add(re["EmpSex"].ToString());
lvi.SubItems.Add(re["EmpAge"].ToString());
this.listView1.Items.Add(lvi);
}
this.listView1.EndUpdate(); //结束数据处理,UI界面一次性绘制。
}
catch (SqlException ae)
{
Console.WriteLine("修改失败");
Console.WriteLine(ae.Message);
MessageBox.Show("error");
}
finally
{
conn.Close();//对数据库操作完成后,需要关闭数据库,释放内存
}
}
private void button3_Click(object sender, EventArgs e)
{//统计40岁以上员工的总工资,由大到小排列
//控制 page2
string connStr = "Data Source=DESKTOP-CHKF0KA;" +
"Initial Catalog=temp;Integrated Security=True";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
if (conn.State == ConnectionState.Open)
{
Console.WriteLine("连接成功");//控制台用
}
string sql = String.Format("select e.EmpNo,e.EmpAge,e.EmpName,e.EmpSex,sum(Salary) as '总工资' " +
"from employee e join works as w on e.EmpNo = w.EmpNo " +
"where e.EmpAge >= 40 " +
"group by e.EmpNo, e.EmpAge, e.EmpName, e.EmpSex " +
"order by sum(salary) desc; ");
SqlCommand cmd = new SqlCommand(sql, conn);
listView2.Items.Clear();
try
{
SqlDataReader re = cmd.ExecuteReader();//.ExecuteNonQuery();
Console.WriteLine("修改成功");
if (!re.HasRows)//判断是否为空
{ MessageBox.Show("结果为空"); return; }
this.listView2.BeginUpdate(); //数据更新,UI暂时挂起,直到EndUpdate绘制控件,可以有效避免闪烁并大大提高加载速度
for (int i = 0; re.Read(); i++) //添加10行数据
{
ListViewItem lvi = new ListViewItem();
lvi.Text = re["EmpNo"].ToString();
lvi.SubItems.Add(re["EmpName"].ToString());
lvi.SubItems.Add(re["EmpSex"].ToString());
lvi.SubItems.Add(re["EmpAge"].ToString());
lvi.SubItems.Add(re["总工资"].ToString());
this.listView2.Items.Add(lvi);
}
this.listView2.EndUpdate(); //结束数据处理,UI界面一次性绘制。
}
catch (SqlException ae)
{
Console.WriteLine("修改失败");
Console.WriteLine(ae.Message);
MessageBox.Show("error");
}
finally
{
conn.Close();//对数据库操作完成后,需要关闭数据库,释放内存
}
}
private void label3_Click(object sender, EventArgs e)
{
}
private void listView2_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{//查询至少具有两份工作的员工姓名与公司名
// select e.EmpName,c.CmpName from employee as e join works as w on e.EmpNo = w.EmpNo join company as c on w.CmpNo = c.CmpNo where e.EmpNo in (select e.EmpNo
//from employee as e join works as w on e.EmpNo = w.EmpNo
// group by e.EmpNo
// having count(e.EmpName) >= 2
string connStr = "Data Source=DESKTOP-CHKF0KA;" +
"Initial Catalog=temp;Integrated Security=True";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
if (conn.State == ConnectionState.Open)
{
Console.WriteLine("连接成功");//控制台用
}
//select e.EmpName,c.CmpName
//from employee as e join works as w on e.EmpNo = w.EmpNo join company as c on w.CmpNo = c.CmpNo
//where e.EmpNo in (
//select e.EmpNo
//from employee as e join works as w on e.EmpNo = w.EmpNo
//group by e.EmpNo
//having count(e.EmpName) >= 2
//)
string sql = String.Format(
"select e.EmpName,c.CmpName " +
"from employee as e " +
"join works as w on e.EmpNo = w.EmpNo join [company] as c on w.CmpNo = c.CmpNo " +
"where e.EmpNo in (" +
"select e.EmpNo " +
"from employee as e " +
"join works as w on e.EmpNo = w.EmpNo " +
"group by e.EmpNo " +
"having count(e.EmpName) >= 2" +
")");
SqlCommand cmd = new SqlCommand(sql, conn);
listView3.Items.Clear();
try
{
SqlDataReader re = cmd.ExecuteReader();//.ExecuteNonQuery(); 好像改的话不能用Reader()
Console.WriteLine("修改成功");
if (!re.HasRows)//判断是否为空
{ MessageBox.Show("结果为空"); return; }
this.listView3.BeginUpdate(); //数据更新,UI暂时挂起,直到EndUpdate绘制控件,可以有效避免闪烁并大大提高加载速度
for (int i = 0; re.Read(); i++) //添加10行数据
{
ListViewItem lvi = new ListViewItem();
lvi.Text = re["EmpName"].ToString();
lvi.SubItems.Add(re["CmpName"].ToString());
this.listView3.Items.Add(lvi);
}
this.listView3.EndUpdate(); //结束数据处理,UI界面一次性绘制。
}
catch (SqlException ae)
{
Console.WriteLine("修改失败");
Console.WriteLine(ae.Message);
MessageBox.Show("sql error");
}
finally//即使异常,finally也会被执行
{
conn.Close();//对数据库操作完成后,需要关闭数据库,释放内存
}
}
private void textBox1_TextChanged_1(object sender, EventArgs e)
{
}
private void label6_Click(object sender, EventArgs e)
{
}
private void button5_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged_2(object sender, EventArgs e)
{
}
}
}
`