//查看公告
public List <JXGG> LookJXGG(int CourseId)
{
string sql = "select Row_Number() over(order by ID) as xh ,Id ,GGHead,GGContent,TeaId,Time,TeaName from V_LookJXGG where CourseId = @CourseId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId)
};
List <JXGG> gglist = new List <JXGG>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
gglist.Add(new JXGG()
{
xh = Convert.ToInt32(result["xh"]),
Id = Convert.ToInt32(result["id"]),
GGHead = result["GGHead"].ToString(),
GGContent = result["GGContent"].ToString(),
TeaId = Convert.ToInt32(result["TeaId"]),
Time = Convert.ToDateTime(result["Time"]),
TeaName = result["TeaName"].ToString()
});
}
return(gglist);
}
/// <summary>
/// 通过学号查找学生
/// </summary>
/// <param name="StudentId"></param>
/// <returns>学生信息</returns>
public StudentsExt queryStudentByStudentId(int StudentId)
{
string sql = "Select Students.StudentId,StudentName,Gender,Birthday,StudentIdNo,CardNo,PhoneNumber,StudentAddress,Students.ClassId,ClassName,SQLServerDB,CSharp"
+ " from Students join StudentClass on Students.ClassId=StudentClass.ClassId "
+ "join ScoreList on ScoreList.StudentId=Students.StudentId "
+ " where Students.StudentId=@StudentId";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@StudentId", StudentId)
};
StudentsExt stu = null;
SqlDataReader result = new Helper.SQLHelper().QueryAll(sql, param, false);
while (result.Read())
{
stu = new StudentsExt()
{
StudentId = Convert.ToInt32(result["StudentId"]),
StudentName = result["StudentName"].ToString(),
Gender = result["Gender"].ToString(),
Birthday = Convert.ToDateTime(result["Birthday"]),
StudentIdNo = result["StudentIdNo"].ToString(),
CardNo = result["CardNo"].ToString(),
PhoneNumber = result["PhoneNumber"].ToString(),
StudentAddress = result["StudentAddress"].ToString(),
ClassId = Convert.ToInt32(result["ClassId"]),
ClassName = result["ClassName"].ToString(),
SQLServerDB = Convert.ToInt32(result["SQLServerDB"]),
CSharp = Convert.ToInt32(result["CSharp"])
};
}
result.Close();
return(stu);
}
//查询教师的所有课程
public List <CourseMes> queryCourseInfoByTeaId(int TeaId)
{
string sql = "SELECT CourseID, courseproperty, Xuefen, CourseName, CollegeName,Time FROM V_TeaCourse where TeaId=@TeaId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@TeaId", TeaId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <CourseMes> courselist = new List <CourseMes>();
while (result.Read())
{
courselist.Add(new CourseMes()
{
CourseID = Convert.ToInt32(result["CourseID"]),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
courseproperty = result["courseproperty"].ToString(),
CollegeName = result["CollegeName"].ToString(),
Time = result["Time"].ToString()
});
}
return(courselist);
}
/// <summary>
/// 登录
/// </summary>
/// <param name="user"></param>
/// <returns>返回Admins</returns>
public Admins Login(Admins user)
{
string sql = "Select AdminName from Admins where LoginId=@LoginId and LoginPwd=@LoginPwd";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@LoginId", user.LoginId),
new SqlParameter("@LoginPwd", user.LoginPwd)
};
try
{
SqlDataReader result = new Helper.SQLHelper().QueryAll(sql, param, false);
while (result.Read())
{
user.LoginName = result["AdminName"].ToString();
if (user.LoginName == null)
{
user = null;
}
}
result.Close();
}
catch (Exception ex)
{
throw new Exception("系统异常！" + ex.Message);
}
return(user);
}
/// <summary>
/// 查看作业细节
/// </summary>
/// <param name="HwId"></param>
/// <returns></returns>
public Homework queryHkByHkId(int HwId)
{
string sql = "select HwId,StartTime,EndTime,HwContent,CourseId,HwHead from Homework where HwId = @HwId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@HwId", HwId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
Homework hw = null;
while (result.Read())
{
hw = new Homework()
{
HwId = HwId,
HwContent = result["HwContent"].ToString(),
HwHead = result["HwHead"].ToString(),
StartTime = Convert.ToDateTime(result["StartTime"]),
EndTime = Convert.ToDateTime(result["EndTime"]),
CourseId = Convert.ToInt32(result["CourseId"])
};
}
return(hw);
}
/// <summary>
/// 查看未完成的作业
/// </summary>
/// <param name="CourseId"></param>
/// <param name="StuId"></param>
/// <returns></returns>
public List <Homework> queryUnfinishedHw(int CourseId, int StuId)
{
string sql = "select HwId,StartTime,EndTime,HwContent,CourseId,HwHead from Homework where CourseId = @CourseId and HwId not in (Select distinct HwId from V_Hw where StuId = @StuId )";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId),
new SqlParameter("@StuId", StuId)
};
List <Homework> hklist = new List <Homework>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
hklist.Add(new Homework()
{
HwId = Convert.ToInt32(result["HwId"]),
StartTime = Convert.ToDateTime(result["StartTime"]),
EndTime = Convert.ToDateTime(result["EndTime"]),
HwContent = result["HwContent"].ToString(),
CourseId = CourseId,
HwHead = result["HwHead"].ToString()
});
}
return(hklist);
}
/// <summary>
/// 通过学号查询学生信息
/// </summary>
/// <param name="StuId"></param>
/// <returns></returns>
public Students queryStuById(int StuId)
{
string sql = "select StuId,StuName, StuSex, StuBirth, StuNoId, StuPhoneNum, StuAdd, ClassId, StuHonor, Students.Major, Students.College,StuState,Punish,PoliticalStatus, CollegeName,MajorName" +
" from Students join XY on Students.College=XY.College " +
" join ZY on Students.Major=ZY.Major where StuId=@StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@StuId", StuId)
};
Students stu = new Students();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
stu.StuId = Convert.ToInt32(result["StuId"]);
stu.StuName = result["StuName"].ToString();
stu.StuSex = result["StuSex"].ToString();
stu.StuBirth = Convert.ToDateTime(result["StuBirth"]).ToString("yyyy-MM-dd");
stu.StuNoId = result["StuNoId"].ToString();
stu.StuPhoneNum = result["StuPhoneNum"].ToString();
stu.StuAdd = result["StuAdd"].ToString();
stu.ClassId = result["ClassId"].ToString();
stu.StuHonor = result["StuHonor"].ToString();
stu.College = result["College"].ToString();
stu.Major = result["Major"].ToString();
stu.MajorName = result["MajorName"].ToString();
stu.CollegeName = result["CollegeName"].ToString();
stu.StuState = result["StuState"].ToString();
stu.Punish = result["Punish"].ToString();
stu.PoliticalStatus = result["PoliticalStatus"].ToString();
}
return(stu);
}
// 查询已未审批的作业
public List <Homework> querycheckedHw(int CourseId, int StuId)
{
string sql = "select Homework.HwId,StartTime,EndTime,HwContent,CourseId,HwHead,Answer,Time from Homework join Answer_Stu on Homework.HwId = Answer_Stu.HwId where HwState = 'F' and CourseId = @CourseId and StuId = @StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId),
new SqlParameter("@StuId", StuId)
};
List <Homework> hklist = new List <Homework>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
hklist.Add(new Homework()
{
HwId = Convert.ToInt32(result["HwId"]),
StartTime = Convert.ToDateTime(result["StartTime"]),
EndTime = Convert.ToDateTime(result["EndTime"]),
HwContent = result["HwContent"].ToString(),
CourseId = CourseId,
HwHead = result["HwHead"].ToString()
});
}
return(hklist);
}
//查看已选课程（全）
public List <CourseMana> queryCourseInfoByStuId(int StuId)
{
string sql = "SELECT StuId,TeaName, CourseID, Season, courseproperty, Xuefen, CourseName, CollegeName,CourseAdd,Time FROM V_course2 where StuId=@StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@StuId", StuId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <CourseMana> courselist = new List <CourseMana>();
while (result.Read())
{
courselist.Add(new CourseMana()
{
CourseID = Convert.ToInt32(result["CourseID"]),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
TeaName = result["TeaName"].ToString(),
courseproperty = result["courseproperty"].ToString(),
Season = result["Season"].ToString(),
CollegeName = result["CollegeName"].ToString(),
CourseAdd = result["CourseAdd"].ToString(),
Time = result["Time"].ToString()
});
}
return(courselist);
}
//查询某一课程的考情记录
public List <KQ> queryAllKq(int CourseId)
{
string sql = "select Row_Number() over(order by KQId) as '序号',KQId,CourseId,KqTime,EndTime,StuNum from KQ where CourseId = @CourseId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId)
};
List <KQ> kqList = new List <KQ>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
kqList.Add(new KQ()
{
KQXh = Convert.ToInt32(result["序号"]),
KQId = Convert.ToInt32(result["KQId"]),
CourseId = Convert.ToInt32(result["CourseId"]),
KqTime = Convert.ToDateTime(result["KqTime"]),
EndTime = Convert.ToDateTime(result["EndTime"]),
StuNum = Convert.ToInt32(result["StuNum"])
});
}
return(kqList);
}
//学生查看成绩
public List <Score> queryScore(int StuId)
{
string sql = "SELECT StuId, CourseId, ClassScore, MatchScore, FinalScore, SCID, CourseName, Xuefen, CourseNum, courseproperty, CollegeName, TeaId, TeaName,Season from V_StuScore where StuId = @StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@StuId", StuId)
};
List <Score> sclist = new List <Score>();
SqlDataReader res = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (res.Read())
{
sclist.Add(new Score()
{
StuId = Convert.ToInt32(res["StuId"]),
CourseId = Convert.ToInt32(res["CourseId"]),
SCID = Convert.ToInt32(res["SCID"]),
CourseNum = Convert.ToInt32(res["CourseNum"]),
TeaId = Convert.ToInt32(res["TeaId"]),
ClassScore = float.Parse(res["ClassScore"].ToString()),
MatchScore = float.Parse(res["MatchScore"].ToString()),
FinalScore = float.Parse(res["FinalScore"].ToString()),
Xuefen = float.Parse(res["Xuefen"].ToString()),
CourseName = res["CourseName"].ToString(),
courseproperty = res["courseproperty"].ToString(),
CollegeName = res["CollegeName"].ToString(),
TeaName = res["TeaName"].ToString(),
Season = res["Season"].ToString()
});
}
return(sclist);
}
//通过课程编号查看课程信息，
public CourseMes queryCourseById(int courseId)
{
string sql = "select CourseName,Xuefen,CourseNum,courseproperty,CollegeName,TeaId from CoursesMes where CourseID=@CourseId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", courseId),
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
CourseMes course = null;
while (result.Read())
{
course = new CourseMes()
{
CourseID = courseId,
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
CourseNum = Convert.ToInt32(result["CourseNum"]),
courseproperty = result["courseproperty"].ToString(),
CollegeName = result["CollegeName"].ToString(),
TeaId = Convert.ToInt32(result["TeaId"])
};
}
return(course);
}
//教师查看某个班级的成绩
public List <ScoreExt> queryScorebyClassId(int CourseId, string ClassId)
{
string sql = "SELECT CourseId,StuId, StuName, College, ClassScore, MatchScore, FinalScore, ClassId,CourseName, Xuefen, CollegeName from V_tea_score where CourseId = @CourseId and ClassId = @ClassId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId),
new SqlParameter("@ClassId", ClassId)
};
List <ScoreExt> sclist = new List <ScoreExt>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
sclist.Add(new ScoreExt()
{
CourseId = Convert.ToInt32(result["CourseId"]),
StuId = Convert.ToInt32(result["StuId"]),
StuName = result["StuName"].ToString(),
College = result["College"].ToString(),
MatchScore = float.Parse(result["MatchScore"].ToString()),
ClassScore = float.Parse(result["ClassScore"].ToString()),
FinalScore = float.Parse(result["FinalScore"].ToString()),
ClassId = result["ClassId"].ToString(),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
CollegeName = result["CollegeName"].ToString(),
});
}
return(sclist);
}
/// <summary>
/// 查询已提交作业的学生信息
/// </summary>
/// <param name="HwId"></param>
/// <returns></returns>
public List <Answer_Stu> querySubmitedStu(int HwId)
{
string sql = "select StuId,StuName,Answer,Grade,Resist,Time,ClassId,HwState,HwId from V_SubmitHw where HwId=@HwId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@HwId", HwId),
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <Answer_Stu> StuList = new List <Answer_Stu>();
while (result.Read())
{
StuList.Add(new Answer_Stu()
{
StuId = Convert.ToInt32(result["StuId"]),
StuName = result["StuName"].ToString(),
Answer = result["Answer"].ToString(),
Grade = result["Grade"].ToString(),
Resist = result["Resist"].ToString(),
Time = Convert.ToDateTime(result["Time"]),
ClassId = result["ClassId"].ToString(),
HwState = result["HwState"].ToString(),
HwId = Convert.ToInt32(result["HwId"])
});
}
return(StuList);
}
//查看辅导员的班级
public List <Class> queryFDYClass(int FDYID)
{
string sql = "select classId,Major,College,fudaoyuan,MajorName,CollegeName from V_Class where fudaoyuan = @FDYID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@FDYID", FDYID)
};
List <Class> clalist = new List <Class>();
SqlDataReader res = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (res.Read())
{
clalist.Add(new Class()
{
ClassId = res["classId"].ToString(),
majorId = res["Major"].ToString(),
collegId = res["College"].ToString(),
fudaoyuan = Convert.ToInt32(res["fudaoyuan"]),
MajorName = res["MajorName"].ToString(),
CollegeName = res["CollegeName"].ToString()
});
}
return(clalist);
}
/// <summary>
/// 通过专业名称查询学生信息
/// </summary>
/// <param name="Major"></param>
/// <returns></returns>
public List <Students> queryStuByMajor(string Major)
{
string sql = "select StuId,StuName, StuSex, StuBirth, StuNoId, StuPhoneNum, StuAdd, ClassId, StuHonor, Students.Major, Students.College,StuState,Punish,PoliticalStatus, CollegeName,MajorName" +
" from Students join XY on Students.College=XY.College " +
" join ZY on Students.Major=ZY.Major where MajorName=@Major";
List <Students> stulist = new List <Students>();
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@Major", Major)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
stulist.Add(new Students()
{
StuId = Convert.ToInt32(result["StuId"]),
StuName = result["StuName"].ToString(),
StuSex = result["StuSex"].ToString(),
StuBirth = Convert.ToDateTime(result["StuBirth"]).ToString("yyyy-MM-dd"),
StuNoId = result["StuNoId"].ToString(),
StuPhoneNum = result["StuPhoneNum"].ToString(),
StuAdd = result["StuAdd"].ToString(),
ClassId = result["ClassId"].ToString(),
StuHonor = result["StuHonor"].ToString(),
Major = result["Major"].ToString(),
College = result["College"].ToString(),
StuState = result["StuState"].ToString(),
Punish = result["Punish"].ToString(),
PoliticalStatus = result["PoliticalStatus"].ToString()
});
}
return(stulist);
}
//查询教师信息
public Teacher queryTeainfoById(int TeaId)
{
string sql = "select TeaId,TeaName, TeaSex, TeaBirth, TeaNoId, TeaPhoneNum, TeaAdd, TeaHonor, Teachers.College,PoliticalStatus, Education,job,office,CollegeName " +
" from Teachers join XY on Teachers.College = XY.College " +
" where TeaId = @TeaId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@TeaId", TeaId)
};
Teacher tea = new Teacher();
SqlDataReader res = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (res.Read())
{
tea.TeaId = Convert.ToInt32(res["TeaId"]);
tea.TeaName = res["TeaName"].ToString();
tea.TeaSex = res["TeaSex"].ToString();
tea.TeaBirth = Convert.ToDateTime(res["TeaBirth"]);
tea.TeaNoId = res["TeaNoId"].ToString();
tea.Phone = res["TeaPhoneNum"].ToString();
tea.TeaAdd = res["TeaAdd"].ToString();
tea.TeaHonor = res["TeaHonor"].ToString();
tea.College = res["College"].ToString();
tea.PoliticalStatus = res["PoliticalStatus"].ToString();
tea.Education = res["Education"].ToString();
tea.Job = res["job"].ToString();
tea.Office = res["office"].ToString();
tea.CollegeName = res["CollegeName"].ToString();
}
return(tea);
}
/// <summary>
/// 查询班级
/// </summary>
/// <param name="collegeId"></param>
/// <param name="majorId"></param>
/// <returns></returns>
public List <Class> queryAllClass(string collegeId, string majorId)
{
string sql = "select classId,Major,college from Class where 1=1";
List <Class> clalist = new List <Class>();
if (collegeId != " ")
{
sql += " and collegeId='" + collegeId + "'";
}
if (majorId != " ")
{
sql += " and majorId='" + majorId + "'";
}
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
while (result.Read())
{
clalist.Add(new Class()
{
ClassId = result["classId"].ToString(),
collegId = result["college"].ToString(),
majorId = result["Major"].ToString()
});
}
return(clalist);
}
/// <summary>
/// 按学号查询课程
/// </summary>
/// <param name="StuId"></param>
/// <returns></returns>
public List <CourseMana> queryCourseByStuId(int StuId)
{
string sql = "select Selected_courseid,CourseName,TeaName,Xuefen,courseproperty,Season,CollegeName,CourseAdd from course1 where StuId=@StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@StuId", StuId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <CourseMana> courselist = new List <CourseMana>();
while (result.Read())
{
courselist.Add(new CourseMana()
{
Selected_courseid = result["Selected_courseid"].ToString(),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
TeaName = result["TeaName"].ToString(),
courseproperty = result["courseproperty"].ToString(),
Season = result["Season"].ToString(),
CollegeName = result["CollegeName"].ToString(),
CourseAdd = result["CourseAdd"].ToString()
});
}
return(courselist);
}
/// <summary>
/// 综合查询
/// </summary>
/// <param name="StuId"></param>
/// <param name="StuName"></param>
/// <param name="ClassId"></param>
/// <param name="College"></param>
/// <param name="Major"></param>
/// <returns></returns>
public List <Students> queryStu(int StuId, string StuName, string ClassId, string College, string Major)
{
string sql = "select StuId,StuName, StuSex, StuBirth, StuNoId, StuPhoneNum, StuAdd, ClassId, StuHonor, Students.Major, Students.College,StuState,Punish,PoliticalStatus, CollegeName,MajorName" +
" from Students join XY on Students.College=XY.College " +
" join ZY on Students.Major=ZY.Major where 1=1";
if (StuId != 0)
{
sql += " and StuId LIKE '%" + StuId + "%'";
}
if (StuName != "")
{
sql += " and StuName LIKE '%" + StuName + "%'";
}
if (ClassId != "")
{
sql += " and Students.ClassId='" + ClassId + "'";
}
if (College != "")
{
sql += " and Students.College='" + College + "'";
}
if (Major != "")
{
sql += " and Students.Major='" + Major + "'";
}
List <Students> stulist = new List <Students>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
while (result.Read())
{
stulist.Add(new Students()
{
StuId = Convert.ToInt32(result["StuId"]),
StuName = result["StuName"].ToString(),
StuSex = result["StuSex"].ToString(),
StuBirth = Convert.ToDateTime(result["StuBirth"]).ToString("yyyy-MM-dd"),
StuNoId = result["StuNoId"].ToString(),
StuPhoneNum = result["StuPhoneNum"].ToString(),
StuAdd = result["StuAdd"].ToString(),
ClassId = result["ClassId"].ToString(),
StuHonor = result["StuHonor"].ToString(),
Major = result["Major"].ToString(),
College = result["College"].ToString(),
StuState = result["StuState"].ToString(),
MajorName = result["MajorName"].ToString(),
CollegeName = result["CollegeName"].ToString(),
Punish = result["Punish"].ToString(),
PoliticalStatus = result["PoliticalStatus"].ToString()
});
}
return(stulist);
}
/// <summary>
/// 查询学院信息
/// </summary>
/// <returns>所有学院信息</returns>
public List <College> queryAllCollege()
{
string sql = "select college,collegeName from XY";
List <College> collist = new List <College>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
while (result.Read())
{
collist.Add(new College()
{
collegeId = result["college"].ToString(),
collegeName = result["collegeName"].ToString()
});
}
return(collist);
}
public Login UserLogin(Login user)
{
string sql = "Select StuName,type from Login where UserName=@UserName and PassWord=@PassWord";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@UserName", user.UserName),
new SqlParameter("@PassWord", user.PassWord)
};
SqlDataReader result = null;
result = new Helper.SQLHelper().queryAllResult(sql, param, false);
if (result.Read())
{
user.StuName = result["StuName"].ToString();
user.type = Convert.ToInt32(result["type"]);
return(user);
}
return(null);
}
//查询该课程的所有班级
public List <Model.Class> queryClassByCourseId(int CourseId)
{
string sql = "select distinct ClassId from V_CourseTea where CourseId=@CourseId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@CourseId", CourseId)
};
List <Model.Class> classList = new List <Model.Class>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
while (result.Read())
{
classList.Add(new Model.Class()
{
ClassId = result["ClassId"].ToString()
});
}
return(classList);
}
/// <summary>
/// 查看通过审核的课程
/// </summary>
/// <returns></returns>
public List <CourseMes> showChecked()
{
string sql = "select CourseId,CourseName,Xuefen,CourseNum,courseproperty,collegeName from CoursesMes where SStatus='Y'";
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
List <CourseMes> courselist = new List <CourseMes>();
while (result.Read())
{
courselist.Add(new CourseMes()
{
CourseID = Convert.ToInt32(result["CourseID"]),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
CourseNum = Convert.ToInt32(result["CourseNum"]),
courseproperty = result["courseproperty"].ToString(),
CollegeName = result["collegeName"].ToString()
});
}
return(courselist);
}
//查询所有学期
public List <CourseMana> querySeason(int StuId)
{
string sql = "select distinct Season from Courses_Stu where StuId=@StuId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@StuId", StuId)
};
SqlDataReader res = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <CourseMana> SeasonList = new List <CourseMana>();
while (res.Read())
{
SeasonList.Add(new CourseMana()
{
Season = res["Season"].ToString()
});
}
return(SeasonList);
}
//查看系统公告
public List <XTGG> LookXTGG()
{
string sql = "select Row_Number() over(order by ID) as xh ,Id ,GGHead,GGContent,GGauthor,GGdateTime from XTGG";
List <XTGG> gglist = new List <XTGG>();
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
while (result.Read())
{
gglist.Add(new XTGG()
{
xh = Convert.ToInt32(result["xh"]),
GGId = Convert.ToInt32(result["Id"]),
GGHead = result["GGHead"].ToString(),
GGcontent = result["GGContent"].ToString(),
GGauthor = result["GGauthor"].ToString(),
GGdateTime = Convert.ToDateTime(result["GGdateTime"])
});
}
return(gglist);
}
//按姓名模糊查找
public List <CourseMana> queryCourseByName(string Name)
{
string sql = "select CourseId,CourseName,TeaName,Xuefen,courseproperty,Season,CollegeName from V_xuanke where CourseName LIKE '%" + Name + "%'";
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
List <CourseMana> courselist = new List <CourseMana>();
while (result.Read())
{
courselist.Add(new CourseMana()
{
CourseID = Convert.ToInt32(result["CourseID"]),
CourseName = result["CourseName"].ToString(),
Xuefen = float.Parse(result["Xuefen"].ToString()),
TeaName = result["TeaName"].ToString(),
courseproperty = result["courseproperty"].ToString(),
Season = result["Season"].ToString(),
CollegeName = result["CollegeName"].ToString()
});
}
return(courselist);
}
//查询未签到学生的学号
public List <Students> queryUnCheckStuId(int KQId, int CourseId)
{
string sql = "select distinct StuId from Courses_Stu where StuId not in (select distinct StuId from qiandao where KQId = @KQId )and courseId = @CourseId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@KQId", KQId),
new SqlParameter("@CourseId", CourseId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <Students> StuList = new List <Students>();
while (result.Read())
{
StuList.Add(new Students()
{
StuId = Convert.ToInt32(result["StuId"])
});
}
return(StuList);
}
/// <summary>
/// 查询所有专业
/// </summary>
/// <param name="collegeId">学院ID</param>
/// <returns>该学院的所有专业</returns>
public List <Major> queryAllMajor(string collegeId)
{
string sql = "select major,MajorName,College from ZY where 1=1";
List <Major> majlist = new List <Major>();
if (collegeId != " ")
{
sql += " and college='" + collegeId + "'";
}
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, false);
while (result.Read())
{
majlist.Add(new Major()
{
majorId = result["Major"].ToString(),
majorName = result["MajorName"].ToString(),
collegeId = result["College"].ToString()
});
}
return(majlist);
}
//查询已签到的学生学号姓名
public List <Students> queryCheckedStu(int KQId)
{
string sql = "select qiandao.StuId,StuName,Time from qiandao join Students on qiandao.StuId = Students.StuId where KQId = @KQId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@KQId", KQId)
};
SqlDataReader result = new Helper.SQLHelper().queryAllResult(sql, param, false);
List <Students> StuList = new List <Students>();
while (result.Read())
{
StuList.Add(new Students()
{
StuId = Convert.ToInt32(result["StuId"]),
StuName = result["StuName"].ToString(),
Time = Convert.ToDateTime(result["Time"])
});
}
return(StuList);
}