使用Dictionary<>泛型集合读取和返回多个结果集
返回有多个结果集时
使用reader.NextResult()读取下一个结果集
/// <summary>
/// 根据班级统计考试成绩相关信息(或全校考试成绩统计)
/// </summary>
/// <param name="classId">班级编号</param>
/// <returns>返回包含统计结果的泛型集合</returns>
public Dictionary<string,string> QueryScoreInfo(string classId)
{
//查询考试总人数、C#和DB平均分
string sql = "select stuCount=COUNT(*),avgCSharp=AVG(CSharp),avgDB=avg(SQLServerDB) from ScoreList ";
sql += "inner join Students on Students.StudentId = ScoreList.StudentId";
if (classId != null && classId.Length != 0)
{
sql += string.Format(" where ClassId={0}", classId);
}
//查询缺考总人数
sql += "; select absentCount=COUNT(*) from Students where StudentId not in ";
sql += "(select StudentId from ScoreList)";
if (classId != null && classId.Length != 0)
{
sql += string.Format(" and ClassId={0}", classId);
}
SqlDataReader reader = SQLHelper.GetReader(sql);
Dictionary<string, string> scoreInfo = null;
if (reader.Read())
{
scoreInfo = new Dictionary<string, string>();
scoreInfo.Add("stuCount", reader["stuCount"].ToString());
scoreInfo.Add("avgCSharp", reader["avgCSharp"].ToString());
scoreInfo.Add("avgDB", reader["avgDB"].ToString());
}
if (reader.NextResult())
{
if (reader.Read())
{
scoreInfo.Add("absentCount", reader["absentCount"].ToString());
}
}
reader.Close();
return scoreInfo;
}