public class StudentDAL
{
public List<Grade> SelectGradeName()
{
string str = SqlHelper.Constr;
string sql = "select * from grade";
List<Grade> glist = new List<Grade>();
using (SqlConnection con = new SqlConnection(str))
{
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Grade item = new Grade();
item.GradeId = Convert.ToInt32(dr["gradeid"]);
item.GradeName = dr["gradename"].ToString();
glist.Add(item);
}
dr.Close();
return glist;
}
}
public List<Subject> SelectSubjectName(string name)
{
string str = SqlHelper.Constr;
string sql = "select * from subject,grade where grade.gradeid=subject.gradeid and gradename='"+name+"'";
List<Subject> slist = new List<Subject>();
using (SqlConnection con = new SqlConnection(str))
{
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Subject item = new Subject();
item.SubjectId = Convert.ToInt32(dr["subjectid"]);
item.SubjectName = dr["subjectname"].ToString();
item.ClassHour = Convert.ToInt32(dr["classhour"]);
item.GradeId = Convert.ToInt32(dr["gradeid"]);
slist.Add(item);
}
dr.Close();
return slist;
}
}
public List<Subject> SelectSubjectName()
{
string str = SqlHelper.Constr;
string sql = "select * from subject";
List<Subject> slist = new List<Subject>();
using (SqlConnection con = new SqlConnection(str))
{
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Subject item = new Subject();
item.SubjectId = Convert.ToInt32(dr["subjectid"]);
item.SubjectName = dr["subjectname"].ToString();
item.ClassHour = Convert.ToInt32(dr["classhour"]);
item.GradeId = Convert.ToInt32(dr["gradeid"]);
slist.Add(item);
}
dr.Close();
return slist;
}
}
public DataTable FillListView(string name,string grade,string subject)
{
string str = SqlHelper.Constr;
using(SqlConnection con = new SqlConnection(str)){
string sql = "select studentname as '学生姓名',subjectname as '科目',studentresult as '成绩',examdate as '考试时间' from result inner join student on student.studentno=result.studentno inner join subject on subject.subjectid=result.subjectid inner join grade on grade.gradeid=student.gradeid where studentname like '%" + name + "%' and gradename='"+grade+"' and subjectname='"+subject+"'";
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, con);
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Student");
con.Close();
return ds.Tables["Student"];
}
}
}
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- using System.Windows.Forms;
- using MySchoolDAL;
- using MySchoolModels;
-
- namespace MySchoolUI
- {
- public partial class StudentResult : Form
- {
- public StudentResult()
- {
- InitializeComponent();
- }
-
- private void btnSearch_Click(object sender, EventArgs e)
- {
- StudentDAL sd = new StudentDAL();
- string s1 = this.comboBox1.Text;
- string s2 = this.comboBox2.Text;
- string name = this.txtStudentName.Text;
- DataTable dt = sd.FillListView(name,s1,s2);
- this.dataGridView1.DataSource = dt;
- }
- StudentDAL sd = new StudentDAL();
- private void StudentResult_Load(object sender, EventArgs e)
- {
- List<Grade> lg = sd.SelectGradeName();
- foreach (Grade item in lg)
- {
- this.comboBox1.Items.Add(item.GradeName);
- }
- List<Subject> ls = sd.SelectSubjectName();
- foreach (Subject item in ls)
- {
- this.comboBox2.Items.Add(item.SubjectName);
- }
- }
-
- private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
- {
- string s = this.comboBox1.Text;
- this.comboBox2.Items.Clear();
- List<Subject> ls = sd.SelectSubjectName(s);
- foreach (Subject item in ls)
- {
- this.comboBox2.Items.Add(item.SubjectName);
- }
- }
- }
- }