标签：insert Text System CS Intent EMPLOYEE 2015 using

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();
        }
    }
}

标签：insert,Text,System,CS,Intent,EMPLOYEE,2015,using
From： https://www.cnblogs.com/benbenlzw/p/17244872.html

2015-CS

2015-CS

相关文章

赞助商

阅读排行