private void BtnSrhClick(object sender, EventArgs e)
{
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入学生学号！");
return;
}
if (inf1.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
string cmd = $"select ryear,score from register where sid='{inf0.Text}' and cid='{inf1.Text}';";
Dictionary <string, string> dic = DBO.Search(cmd);
foreach (KeyValuePair <string, string> kv in dic)
{
switch (kv.Key)
{
case "ryear":
inf2.Text = kv.Value;
break;
case "score":
inf3.Text = kv.Value;
break;
}
}
}
private void BtnAddClick(object sender, EventArgs e)
{
foreach (Control c in gbox.Controls)
{
if (c is TextBox && c.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
if (c == inf3 && ((ComboBox)c).SelectedIndex == -1)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
if (c == inf4 && (!int.TryParse(c.Text, out int age) || age < 10 || age > 50))
{
MessageBox.Show("学生年龄无效，请重新输入！");
c.Text = string.Empty;
return;
}
}
string cmd = $"insert into students(sid,sname,gender,enage,grade,class) values('{inf1.Text}','{inf2.Text}','{inf3.SelectedItem}',{inf4.Text},{inf5.Text},'{inf6.Text}');";
DBO.Insert(cmd);
MessageBox.Show("插入成功！");
}
private void StuBnSrhClick(object sender, EventArgs e)
{
if (stuTxtSrh.Text == string.Empty)
{
MessageBox.Show("请输入学生学号或姓名！");
return;
}
string cmd;
switch (int.Parse((string)stuGbxInf.Tag))
{
case 1:
cmd = stuCbxSrh.SelectedIndex == 0
? $"select * from students where sid='{stuTxtSrh.Text}';"
: $"select * from students where sname='{stuTxtSrh.Text}';";
DBO.Search(cmd, stuLtvTab);
break;
case 2:
cmd = stuCbxSrh.SelectedIndex == 0
? $"select * from register where sid='{stuTxtSrh.Text}';"
: $"select * from register where sid in(select sid from students where sname='{stuTxtSrh.Text}');";
DBO.Search(cmd, stuLtvTab);
break;
case 3:
cmd = stuCbxSrh.SelectedIndex == 0
? $"select avg(score) as avgScore from register where sid='{stuTxtSrh.Text}';"
: $"select avg(score) as avgScore from register where sid in(select sid from students where sname='{stuTxtSrh.Text}');";
DBO.Search(cmd, stuLtvTab);
break;
}
}
private void BtnUpdClick(object sender, EventArgs e)
{
List <string> list = new List <string>();
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
if (inf1.Enabled)
{
if (inf1.Text == string.Empty)
{
MessageBox.Show("请输入课程的完整信息！");
return;
}
list.Add($"cname='{inf1.Text}'");
}
if (inf2.Enabled)
{
if (inf2.Text == string.Empty)
{
MessageBox.Show("请输入课程的完整信息！");
return;
}
list.Add($"tname='{inf2.Text}'");
}
if (inf3.Enabled)
{
if (inf3.Text == string.Empty)
{
MessageBox.Show("请输入课程的完整信息！");
return;
}
list.Add($"credit={inf3.Text}");
}
if (inf4.Enabled)
{
if (inf4.Text == string.Empty)
{
MessageBox.Show("请输入课程的完整信息！");
return;
}
list.Add($"grade={inf4.Text}");
}
if (inf5.Enabled)
{
list.Add($"cyear={(inf5.Text == string.Empty ? "null" : inf5.Text)}");
}
if (list.Count > 0)
{
string cmd = $"update courses set {string.Join(",", list.ToArray())} where cid='{inf0.Text}';";
DBO.Update(cmd);
MessageBox.Show("修改成功");
}
}
private void SelBnClaClick(object sender, EventArgs e)
{
if (selTbInf0.Text == string.Empty)
{
MessageBox.Show("请输入班级！");
return;
}
string cmd = $"select avg(score) as avgScore from register where sid in(select sid from students where class='{selTbInf0.Text}')";
DBO.Search(cmd, selLtvTab);
}
private void BtnDelClick(object sender, EventArgs e)
{
if (idtb.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
string cmd = $"delete from courses where cid='{idtb.Text}';";
DBO.Delete(cmd);
MessageBox.Show("删除成功！");
}
private void BtnAddClick(object sender, EventArgs e)
{
foreach (Control c in gbox.Controls)
{
if (c is TextBox && c.Text == string.Empty)
{
MessageBox.Show("请输入选课的完整信息！");
return;
}
}
string cmd = $"insert into register(sid,cid,ryear,score) values('{inf1.Text}','{inf2.Text}',{inf3.Text},{inf4.Text});";
DBO.Insert(cmd);
MessageBox.Show("插入成功！");
}
private void BtnAddClick(object sender, EventArgs e)
{
foreach (Control c in gbox.Controls)
{
if (c is TextBox && c != inf6 && c.Text == string.Empty)
{
MessageBox.Show("请输入课程的完整信息！");
return;
}
}
string cmd = $"insert into courses(cid,cname,tname,credit,grade,cyear) values('{inf1.Text}','{inf2.Text}','{inf3.Text}',{inf4.Text},{inf5.Text},{(inf6.Text == string.Empty ? "null" : inf6.Text)});";
DBO.Insert(cmd);
MessageBox.Show("插入成功！");
}
private void BtnDelClick(object sender, EventArgs e)
{
if (tbxs.Text == string.Empty)
{
MessageBox.Show("请输入学生学号！");
return;
}
if (tbxc.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
string cmd = $"delete from register where sid='{tbxs.Text}' and cid='{tbxc.Text}';";
DBO.Delete(cmd);
MessageBox.Show("删除成功");
}
private void CouBnSrhClick(object sender, EventArgs e)
{
if (couTxtSrh.Text == string.Empty)
{
MessageBox.Show("请输入课程编号或名称！");
return;
}
string cmd;
switch (int.Parse((string)couGbxInf.Tag))
{
case 1:
cmd = couCbxSrh.SelectedIndex == 0
? $"select * from courses where cid='{couTxtSrh.Text}';"
: $"select * from courses where cname='{couTxtSrh.Text}';";
DBO.Search(cmd, couLtvTab);
break;
case 2:
cmd = couCbxSrh.SelectedIndex == 0
? $"select * from register where cid='{couTxtSrh.Text}';"
: $"select * from register where cid in(select cid from courses where cname='{couTxtSrh.Text}');";
DBO.Search(cmd, couLtvTab);
break;
case 3:
cmd = couCbxSrh.SelectedIndex == 0
? $"select elt(interval(score,0,60,70,80,90,100),'fail','60-69','70-79','80-89','90-99','full') as level,count(*) as count from register where cid='{couTxtSrh.Text}' group by interval(score, 0, 60, 70, 80, 90, 100) order by interval(score, 0, 60, 70, 80, 90, 100) desc; "
: $"select elt(interval(score,0,60,70,80,90,100),'fail','60-69','70-79','80-89','90-99','full') as level,count(*) as count from register where cid in(select cid from courses where cname='{couTxtSrh.Text}') group by interval(score, 0, 60, 70, 80, 90, 100) order by interval(score, 0, 60, 70, 80, 90, 100) desc; ";
DBO.Search(cmd, couLtvTab);
break;
case 4:
cmd = couCbxSrh.SelectedIndex == 0
? $"select avg(score) as avgScore from register where cid='{couTxtSrh.Text}';"
: $"select avg(score) as avgScore from register where cid in(select cid from courses where cname='{couTxtSrh.Text}');";
DBO.Search(cmd, couLtvTab);
break;
}
}
private void BtnUpdClick(object sender, EventArgs e)
{
List <string> list = new List <string>();
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入学生学号！");
return;
}
if (inf1.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
if (inf2.Enabled)
{
if (inf2.Text == string.Empty)
{
MessageBox.Show("请输入选课的完整信息！");
return;
}
list.Add($"ryear={inf2.Text}");
}
if (inf3.Enabled)
{
if (inf3.Text == string.Empty)
{
MessageBox.Show("请输入选课的完整信息！");
return;
}
list.Add($"score={inf3.Text}");
}
if (list.Count > 0)
{
string cmd = $"update register set {string.Join(",", list.ToArray())} where sid='{inf0.Text}' and cid='{inf1.Text}';";
DBO.Update(cmd);
MessageBox.Show("修改成功！");
}
}
private void SelBnStuClick(object sender, EventArgs e)
{
if (selTbInf1.Text == string.Empty)
{
MessageBox.Show("请输入学生学号或姓名！");
return;
}
if (selTbInf2.Text == string.Empty)
{
MessageBox.Show("请输入课程编号或名称！");
return;
}
string cmd;
switch (selCbxSrh.SelectedIndex)
{
case 0:
cmd = $"select score from register where sid='{selTbInf1.Text}' and cid='{selTbInf2.Text}';";
DBO.Search(cmd, selLtvTab);
break;
case 1:
cmd = $"select score from register where sid='{selTbInf1.Text}' and cid in(select cid from courses where cname='{selTbInf2.Text}');";
DBO.Search(cmd, selLtvTab);
break;
case 2:
cmd = $"select score from register where sid in(select sid from students where sname='{selTbInf1.Text}') and cid='{selTbInf2.Text}';";
DBO.Search(cmd, selLtvTab);
break;
case 3:
cmd = $"select score from register where sid in(select sid from students where sname='{selTbInf1.Text}') and cid in(select cid from courses where cname='{selTbInf2.Text}');";
DBO.Search(cmd, selLtvTab);
break;
}
}
private void BtnSrhClick(object sender, EventArgs e)
{
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入学生学号！");
return;
}
string cmd = $"select sname,gender,enage,grade,class from students where sid='{inf0.Text}';";
Dictionary <string, string> dic = DBO.Search(cmd);
foreach (KeyValuePair <string, string> kv in dic)
{
switch (kv.Key)
{
case "sname":
inf1.Text = kv.Value;
break;
case "gender":
inf2.SelectedIndex = kv.Value == "女" ? 1 : 0;
break;
case "enage":
inf3.Text = kv.Value;
break;
case "grade":
inf4.Text = kv.Value;
break;
case "class":
inf5.Text = kv.Value;
break;
}
}
}
private void BtnSrhClick(object sender, EventArgs e)
{
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入课程编号！");
return;
}
string cmd = $"select cname,tname,credit,grade,cyear from courses where cid='{inf0.Text}';";
Dictionary <string, string> dic = DBO.Search(cmd);
foreach (KeyValuePair <string, string> kv in dic)
{
switch (kv.Key)
{
case "cname":
inf1.Text = kv.Value;
break;
case "tname":
inf2.Text = kv.Value;
break;
case "credit":
inf3.Text = kv.Value;
break;
case "grade":
inf4.Text = kv.Value;
break;
case "cyear":
inf5.Text = kv.Value;
break;
}
}
}
private void BtnUpdClick(object sender, EventArgs e)
{
List <string> list = new List <string>();
if (inf0.Text == string.Empty)
{
MessageBox.Show("请输入学生学号！");
return;
}
if (inf1.Enabled)
{
if (inf1.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
list.Add($"sname='{inf1.Text}'");
}
if (inf2.Enabled)
{
if (inf2.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
list.Add($"gender='{inf2.SelectedItem}'");
}
if (inf3.Enabled)
{
if (inf3.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
if (!int.TryParse(inf3.Text, out int age) || age < 10 || age > 50)
{
MessageBox.Show("学生年龄无效，请重新输入！");
inf3.Text = string.Empty;
return;
}
list.Add($"enage={inf3.Text}");
}
if (inf4.Enabled)
{
if (inf4.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
list.Add($"grade={inf4.Text}");
}
if (inf5.Enabled)
{
if (inf5.Text == string.Empty)
{
MessageBox.Show("请输入学生的完整信息！");
return;
}
list.Add($"class='{inf5.Text}'");
}
if (list.Count > 0)
{
string cmd = $"update students set {string.Join(",", list.ToArray())} where sid='{inf0.Text}';";
DBO.Update(cmd);
MessageBox.Show("修改成功");
}
}
