C#窗口实现

使用C#窗口实现数据库显示
注意设置：窗体显示位置，窗体是否可伸缩，列表视图显示细节，

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    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 table = db.getBySql(@"select * from [EMPLOYEE]");
            listView1.BeginUpdate(); // 数据更新，UI暂时挂起，知道EndUpdate 绘制控件，可以有效避免闪烁并大大提高加载速度
            for (int i = 0; i < table.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = table.Rows[i][j] + "";
                    } else
                    {
                        listViewItem.SubItems.Add(table.Rows[i][j] + "");
                    }
                }
                listView1.Items.Add(listViewItem);
            }
            listView1.EndUpdate(); // 结束数据处理，UI界面一次性绘制

            // 查询 3-1
            // 加载现有的员工号
            table = db.getBySql(@"select [EmpNo] from [EMPLOYEE]");
            for (int i = 0; i < table.Rows.Count ; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    comboBox1.Items.Add(table.Rows[i][j] + "");

                }
            }
            comboBox1.SelectedIndex = 0;

            // 加载现有员工名
            table = db.getBySql(@"select [EmpName] from [EMPLOYEE]");
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    comboBox2.Items.Add(table.Rows[i][j] + "");
                }
            }
            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);

            // 表的内容
            table = 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(); // 数据更新 
            for (int i = 0; i < table.Rows.Count; i++ )
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = table.Rows[i][j] + "";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(table.Rows[i][j] + "");
                    }
                }
                listView3.Items.Add(listViewItem);
            }
            listView3.EndUpdate();

            // 表3-3
            // 查询至少具有两份工作员工的姓名和其公司名
            listView4.Columns.Add("员工姓名", listView1.Width / 2 - 2, HorizontalAlignment.Left);
            listView4.Columns.Add("公司名", listView1.Width / 2 - 2, HorizontalAlignment.Left);

            table = db.getBySql(@"select [EMPLOYEE].[EmpName],[COMPANY].[CmpName] from [EMPLOYEE],[COMPANY],[WORKS],(" +
                " select [EmpName],count([CmpName]) as 'CmpNum'" +
                " from [EMPLOYEE],[WORKS],[COMPANY]" +
                " 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();
            for (int i = 0; i < table.Rows.Count ; i++ )
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < table.Columns.Count; j++) {
                    if (j <= 0)
                    {
                        listViewItem.Text = table.Rows[i][j] + "";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(table.Rows[i][j] + "");
                    }
                }
                listView4.Items.Add(listViewItem);
            }
            listView4.EndUpdate();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            Intent.dict["form1_text"] = this.Text;
            Intent.dict["form1_flag"] = 0; // 表示这是添加
            if (form2.ShowDialog() == DialogResult.OK)
            {
                // 这个判断，将会等到 form2 被关闭之后才执行，如果 form2 返回一个ok
                bool canAdd = true;
                foreach (ListViewItem item in this.listView1.Items)
                {
                    if (Intent.dict["form2_textbox1_text"] + "" == 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"] + ""))
                {
                    canAdd = false;
                    MessageBox.Show("年龄不为正数！", this.Text);
                }
                if (canAdd)
                {
                    ListViewItem listViewItem = new ListViewItem();
                    listViewItem.Text = Intent.dict["form2_textbox1_text"] + "";
                    listViewItem.SubItems.Add(Intent.dict["form2_textbox2_text"] + "");
                    listViewItem.SubItems.Add(Intent.dict["form2_radioButton"] + "");
                    listViewItem.SubItems.Add(Intent.dict["form2_textbox3_text"] + "");
                    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();
            Intent.dict["form1_text"] = this.Text;
            Intent.dict["form1_flag"] = 1; // 表示这是修改
            if (listView1.SelectedItems.Count == 0)
            {
                MessageBox.Show("请先选择一个修改项！");
                return;
            }
            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;
            
            if (form2.ShowDialog() == DialogResult.OK)
            {
                bool canUpdate = true;
                if (!((Intent.dict["form1_selectedItems0"] + "") == (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;
                        }
                    }
                }

                if (canUpdate)
                {
                    ListViewItem listViewItem = new ListViewItem();
                    listView1.SelectedItems[0].SubItems[0].Text = Intent.dict["form2_textbox1_text"] + "";
                    listView1.SelectedItems[0].SubItems[1].Text = Intent.dict["form2_textbox2_text"] + "";
                    listView1.SelectedItems[0].SubItems[2].Text = Intent.dict["form2_radioButton"] + "";
                    listView1.SelectedItems[0].SubItems[3].Text = Intent.dict["form2_textbox3_text"] + "";

                    db.setBySql("update [EMPLOYEE] set [EmpNo]='" + Intent.dict["form2_textbox1_text"] + "' where [EmpNo]='" + Intent.dict["form1_selectedItems0"] + "';");
                    db.setBySql("update [EMPLOYEE] set [EmpName]='" + Intent.dict["form2_textbox2_text"] + "' where [EmpName]='" + Intent.dict["form1_selectedItems1"] + "';");
                    db.setBySql("update [EMPLOYEE] set [EmpSex]='" + Intent.dict["form2_radioButton"] + "' where [EmpSex]='" + Intent.dict["form1_selectedItems2"] + "';");
                    db.setBySql("update [EMPLOYEE] set [EmpAge]='" + Intent.dict["form2_textbox3_text"] + "' where [EmpAge]='" + Intent.dict["form1_selectedItems3"] + "';");


                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listView1.SelectedItems.Count == 0)
            {
                MessageBox.Show("请先选择一个修改项！");
                return;
            }
            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("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("员工姓名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("性别", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("年龄", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            // 表的内容
            DataTable table = 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();
            for (int i = 0;  i < table.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = table.Rows[i][j].ToString();
                    }
                    else
                    {
                        listViewItem.SubItems.Add(table.Rows[i][j].ToString());
                    }
                }
                listView2.Items.Add(listViewItem);
            }
            listView2.EndUpdate();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            listView2.Clear();
            listView2.Columns.Add("员工号", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("员工姓名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("性别", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("年龄", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            // 表的内容
            DataTable table = 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();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = table.Rows[i][j].ToString();
                    }
                    else
                    {
                        listViewItem.SubItems.Add(table.Rows[i][j].ToString());
                    }
                }
                listView2.Items.Add(listViewItem);
            }
            listView2.EndUpdate();
        }
    }
}