自用MySqlHelper

create table [EMPLOYEE](
	[EmpNo] varchar(8) not null primary key,
	[EmpName] varchar(50) not null,
	[EmpSex] varchar(2) check([EmpSex]='男' or [EmpSex]='女'),
	[EmpAge] int check([EmpAge]>0)
)
create table [COMPANY](
	[CmpNo] varchar(8) not null primary key,
	[CmpName] varchar(50) not null
)
create table [WORKS](
	[EmpNo] varchar(8) references [EMPLOYEE]([EmpNo]),
	[CmpNo] varchar(8) 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);



create table [TEACHERS](
	[tNo] varchar(8) not null primary key,
	[tName] varchar(50) not null,
	[tSex] varchar(2) check([tSex]='男' or [tSex]='女')
)
create table [COURSES](
	[CNo] varchar(8) not null primary key,
	[CName] varchar(50) not null,
	[CTime] varchar(8) check([CTime]>0)
)
create table [WORKS](
	[tNo] varchar(8) references [TEACHERS]([tNo]),
	[CNo] varchar(8) references [COURSES]([CNo]),
	[Year] int check([Year]>0),
	[Term] varchar(8) not null
)

select * from TEACHERS
select * from COURSES
select * from WORKS

insert into [TEACHERS] values('T01','张三','男');
insert into [TEACHERS] values('T02','李四','女');
insert into [TEACHERS] values('T03','王五','男');
insert into [TEACHERS] values('T04','赵六','女');
 
insert into [COURSES] values('C01','数据结构',64);
insert into [COURSES] values('C02','操作系统',64);
insert into [COURSES] values('C03','网络原理',48);
insert into [COURSES] values('C04','数据库',48);
 
insert into [WORKS] values('T01','C01',2010,'春');
insert into [WORKS] values('T01','C02',2010,'秋');
insert into [WORKS] values('T02','C01',2011,'春');
insert into [WORKS] values('T03','C01',2011,'秋');
insert into [WORKS] values('T04','C02',2011,'春');
insert into [WORKS] values('T04','C03',2010,'春');
insert into [WORKS] values('T04','C04',2012,'秋');


create table [Department](
	[deptNo] varchar(8) not null primary key,
	[deptName] varchar(50) not null
)
create table [CLASS](
	[classNo] varchar(8) not null primary key,
	[specName] varchar(50) not null,
	[deptNo] varchar(8) references [Department]([deptNo]),
	[year] int not null,
	[num] int not null
)
create table [Student](
	[SNo] varchar(8) not null primary key,
	[SName] varchar(50) not null,
	[Date] date not null,
	[classNo] varchar(8) references [class]([classNo]) on delete cascade
)
select * from Student
select * from CLASS
select * from Department
 
insert into [Department] values('1','数学');
insert into [Department] values('2','计算机');
insert into [Department] values('3','化学');
 
insert into [CLASS] values('101','软件','2',2012,25);
insert into [CLASS] values('102','微电子','2',2013,28);
insert into [CLASS] values('111','无机化学','3',2012,20);
insert into [CLASS] values('112','高分子化学','3',2013,19);
insert into [CLASS] values('121','统计数学','1',2013,11);

insert into [Student] values('8101','张三','1996-7-6',101);
insert into [Student] values('8102','钱四','1996-2-6',121);
insert into [Student] values('8105','李飞','1994-1-21',102);
insert into [Student] values('8201','张飞','1996-2-25',111);
insert into [Student] values('8302','周瑜','1996-6-15',112);
insert into [Student] values('8203','王亮','1997-5-27',111);
insert into [Student] values('8305','董庆','1996-4-2',102);
insert into [Student] values('8409','赵龙','1995-6-1',101);

    SqlConnection con = new SqlConnection(@"server=.;database=自己填;User ID=sa;Password=1234567890");

    void initLV(ListView l)//初始化listview
    {
        l.MultiSelect = false;
        l.GridLines = true;
        l.View = View.Details;
        l.FullRowSelect = true;
    }

    void loadLV(ListView l,string sql)//将数据导入listview
    {
        try
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            l.Items.Clear();
            l.Columns.Clear();
            int i, j;
            for (i = 0; i <dt.Columns.Count; i++)
            {
                l.Columns.Add(dt.Columns[i].Caption, l.Width / dt.Columns.Count);
            }
            for (i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem li = new ListViewItem();
                li.SubItems[0].Text = dt.Rows[i][0].ToString().Trim();
                for (j = 1; j < dt.Columns.Count; j++)
                    li.SubItems.Add(dt.Rows[i][j].ToString().Trim());
                l.Items.Add(li);
            }
        }
        catch (Exception ex){ MessageBox.Show(ex.Message); }
        finally { con.Close(); }
    }

    void loadCB(ComboBox c,string sql)//将数据导入combox
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataReader rd = cmd.ExecuteReader();
            c.Items.Clear();
            while (rd.Read())
                c.Items.Add(rd.GetValue(0).ToString().Trim());
        }
        catch { }
        finally { con.Close(); }
    }


    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            con.Open();
        }
        catch (Exception ex) { MessageBox.Show(ex.Message); }
        finally { con.Close(); }
        initLV(listView1);
        loadCB(comboBox1, "sql语句");

        this.Text = "请命名";
        this.FormBorderStyle = FormBorderStyle.FixedSingle;
        this.MaximizeBox = false;
    }

    int execute(string sql,string error) //增删改共用
    {
        try 
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            int i = cmd.ExecuteNonQuery();
            con.Close();
            return i;
        }
        catch { MessageBox.Show(error); con.Close(); return -1; }
    }





            void autoinquire() //自动执行查询功能，复选框，文本框或下拉列表有变化时自动查询
    {
        try
        {
            listView1.Items.Clear();
            if (!checkBox1.Checked && !checkBox2.Checked && !checkBox3.Checked) return;

            string s = "select department.deptname as 系名,class.specname as 班名,";
            s += "student.sname as 学生名 from department,class,student ";
            s += "where student.classno=class.classno ";
            s += "and department.deptno=class.deptno ";
            if (checkBox1.Checked)
            {
                if (comboBox3.Text == "") return;
                else s += "and department.deptname='" + comboBox3.Text + "' ";
            }
            if (checkBox2.Checked)
            {
                if (comboBox4.Text == "") return;
                else s += "and class.specname='" + comboBox4.Text + "' ";
            }
            if (checkBox3.Checked)
            {
                if (!checkBox1.Checked && !checkBox2.Checked && textBox3.Text == "") return;
                else s += "and student.sname like '%" + textBox3.Text + "%' ";//模糊查询
            }
            s += "order by department.deptname desc,class.specname,student.sname desc";
            loadLV(listView1, s);
        }
        catch { }
    }

    private void checkBox_CheckedChanged(object sender, EventArgs e)
    {
        autoinquire();
    }
    private void textBox1_TextChanged(object sender, EventArgs e)//textBox文本变化时自动查询
    {
        autoinquire();
    }
    
    //文本框不能输入单引号，防止直接调用到sql语句导致出错或其他情况
    private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
    {
        if (e.KeyChar == '\'') e.Handled = true;
    }