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