以下是学习笔记：
效果：
1，统计全校考试信息
2，班级考试信息
实现：
1，后台代码部分
DAL--ScoreListService中添加
namespace DAL
{
public class ScoreListService
{
#region 按照班级成绩查询列表
/// <summary>
/// 根据班级查询考试成绩列表
/// </summary>
/// <param name="className">班级名称</param>
/// <returns></returns>
public List<StudentExt> GetScoreList(string className)
{
string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB from Students ";
sql += "inner join StudentClass on StudentClass.ClassId=Students.ClassId ";//内连接 条件：StudentClass.ClassId=Students.ClassId
sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId";
if (className != null && className.Length != 0)
{
sql += string.Format(" where ClassName='{0}'", className);
}
SqlDataReader objReader = SQLHelper.GetReader(sql);
List<StudentExt> list = new List<StudentExt>();//StudentExt扩展实体类
while (objReader.Read())
{
list.Add(new StudentExt()//对象初始化器
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
ClassName = objReader["ClassName"].ToString(),
CSharp = Convert.ToInt32(objReader["CSharp"]),
SQLServerDB = Convert.ToInt32(objReader["SQLServerDB"])
});
}
objReader.Close();
return list;
}
#endregion
#region 全校考试成绩统计
/// <summary>
/// 获取全部考试的统计信息
/// </summary>
/// <returns></returns>
public Dictionary<string, string> GetScoreInfo()
{
string sql = "select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList;";// 总数：stuCount=count(*)，平均：avgCSharp=avg(CSharp)
sql += "select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList)";//学号不在成绩表里面
Dictionary<string, string> scoreInfo = null;
SqlDataReader objReader = SQLHelper.GetReader(sql);
if (objReader.Read())
{
scoreInfo = new Dictionary<string, string>();
scoreInfo.Add("stuCount", objReader["stuCount"].ToString());
scoreInfo.Add("avgCSharp", objReader["avgCSharp"].ToString());
scoreInfo.Add("avgDB", objReader["avgDB"].ToString());
}
if (objReader.NextResult())//另外的一个结果集，上面有2个sql语句
{
if (objReader.Read())
{
scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
}
}
objReader.Close();
return scoreInfo;
}
/// <summary>
/// 获取所有未参考考试的学员名单
/// </summary>
/// <returns></returns>
public List<string> GetAbsentList()
{
string sql = "select StudentName from Students where StudentId not in(select StudentId from ScoreList)";//学号不在成绩表里面
SqlDataReader objReader = SQLHelper.GetReader(sql);
List<string> list = new List<string>();
while (objReader.Read())
{
list.Add(objReader["StudentName"].ToString());
}
objReader.Close();
return list;
}
#endregion
#region 按照班级统计考试信息
public Dictionary<string, string> GetScoreInfoByClassId(string classId)
{
string sql = "select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList ";
sql += "inner join Students on Students.StudentId=ScoreList.StudentId where ClassId={0};";
sql += "select absentCount=count(*) from Students where StudentId not in";
sql += "(select StudentId from ScoreList) and ClassId={1}";
sql = string.Format(sql, classId, classId);
SqlDataReader objReader = SQLHelper.GetReader(sql);
Dictionary<string, string> scoreInfo = null;
if (objReader.Read())//读取考试成绩统计结果
{
scoreInfo = new Dictionary<string, string>();
scoreInfo.Add("stuCount", objReader["stucount"].ToString());
scoreInfo.Add("avgCSharp", objReader["avgCSharp"].ToString());
scoreInfo.Add("avgDB", objReader["avgDB"].ToString());
}
if (objReader.NextResult())//读取缺考人数列表
{
if (objReader.Read())
{
scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
}
}
objReader.Close();
return scoreInfo;
}
/// <summary>
/// 查询未参加考试的学生名单
/// </summary>
/// <returns></returns>
public List<string> GetAbsentListByClassId(string classId)
{
string sql = "select StudentName from Students where StudentId not in ";
sql += "(select StudentId from ScoreList) and ClassId={0}";
sql = string.Format(sql, classId);
SqlDataReader objReader = SQLHelper.GetReader(sql);
List<string> list = new List<string>();
while (objReader.Read())
{
list.Add(objReader["StudentName"].ToString());
}
objReader.Close();
return list;
}
#endregion
#region 基于数据集DataSet的数据查询
/// <summary>
/// 获取所有的考试信息（存储在DataSet中）
/// </summary>
/// <returns></returns>
public DataSet GetAllScoreList()
{
string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB";
sql += " from Students ";
sql += " inner join StudentClass on StudentClass.ClassId=Students.ClassId";
sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId";
return SQLHelper.GetDataSet(sql);
}
#endregion
}
2，UI部分：
统计全校成绩代码：
//统计全校考试成绩
private void btnStat_Click(object sender, EventArgs e)
{
this.gbStat.Text = "全校考试成绩统计";
//查询成绩列表
this.dgvScoreList.AutoGenerateColumns = false;//禁止生成不需要的数据
this.dgvScoreList.DataSource = objScoreService.GetScoreList("");
//查询并显示成绩统计
Dictionary<string, string> dic = objScoreService.GetScoreInfo();
this.lblAttendCount.Text = dic["stuCount"];
this.lblDBAvg.Text = dic["avgDB"];
this.lblCSharpAvg.Text = dic["avgCSharp"];
this.lblCount.Text = dic["absentCount"];
//显示缺考的人员姓名
List<string> list = objScoreService.GetAbsentList();
this.lblList.Items.Clear();//每次显示要把前面的清除掉
this.lblList.Items.AddRange(list.ToArray());//AddRange 一次性添加
}
统计班级成绩代码：
//根据班级查询
private void cboClass_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.cboClass.SelectedIndex == -1)
{
MessageBox.Show("请首先选择要查询的班级", "查询提示");
return;
}
this.dgvScoreList.AutoGenerateColumns = false;
this.dgvScoreList.DataSource = objScoreService.GetScoreList(this.cboClass.Text.Trim());
//同步显示班级考试信息
this.gbStat.Text = "[" + this.cboClass.Text.Trim() + "]考试成绩统计";
Dictionary<string, string> dic =
objScoreService.GetScoreInfoByClassId(this.cboClass.SelectedValue.ToString());
this.lblAttendCount.Text = dic["stuCount"];
this.lblCSharpAvg.Text = dic["avgCSharp"];
this.lblDBAvg.Text = dic["avgDB"];
this.lblCount.Text = dic["absentCount"];
//显示缺考人员姓名
List<string> list =
objScoreService.GetAbsentListByClassId(this.cboClass.SelectedValue.ToString());
this.lblList.Items.Clear();
if (list.Count == 0) this.lblList.Items.Add("没有缺考");
else lblList.Items.AddRange(list.ToArray());
}