/// <summary>
/// 获取异动类型
/// </summary>
/// <returns></returns>
public DataTable GetChangeType()
{
string sql = "select * from ChangeTypes";
DataTable dt = DataBaseHelper.cmbSqlDataAdapter(sql);
return(dt);
}
public DataTable Get(string classID)
{
string sql = "select * from Classes cls where cls.Classes_ID=@0";
DataTable dt = DataBaseHelper.cmbSqlDataAdapter(sql, classID);
return(dt);
}
public DataTable GetInfoByClassid(string classid)
{
string sql = "select * from Colleges,Speciality,Classes,Teacher"
+ " where College_ID=Speciality_College"
+ " and Speciality_ID=Classes_Speciality"
+ " and ClassHeadTeacher=Teacher_ID and Classes_ID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, classid));
}
/// <summary>
/// 根据班级id获取专业id，专业名称
/// </summary>
/// <param name="specid"></param>
/// <returns></returns>
public DataTable GetSubjectGoSpecID(string specid)
{
string sql = "select sb.Subjects_Name,sb.Subjects_ID from Classes clas"
+ " join Sect_Subjects ss on clas.Classes_Speciality=ss.Sepc_ID"
+ " join Subject sb on sb.Subjects_ID=ss.Subjects_ID"
+ " where clas.Classes_ID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, specid));
}
/// <summary>
/// 通过班级id查询科目名称，班级id，班级名称，备注，老师
/// </summary>
/// <param name="classid"></param>
/// <returns></returns>
public DataTable GetTreeViewByClassid(string classid)
{
string sql = "select Speciality_Name 科目名称,Classes_ID 班级编号,Classes_Name 班级名称,Classes_PS 备注,Teacher_Name 教师姓名"
+ " from Colleges,Speciality,Classes,Teacher"
+ " where College_ID=Speciality_College"
+ " and Speciality_ID=Classes_Speciality"
+ " and ClassHeadTeacher=Teacher_ID and Classes_ID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, classid));
}
/// <summary>
/// 根据学生id，获取学籍异动信息
/// </summary>
/// <param name="studentid">学生id</param>
/// <returns></returns>
public DataTable GetChange(string studentid)
{
string sql = "select ctr.ChangeTypeRecode_ID 异动编号,s.Student_Name 学生姓名,ct.ChangeTypes_Name 异动类型,ctr.ChangeReason 异动原因,ctr.ChangeDate 异动时间 from ChangeTypeRecode ctr "
+ " join Students s on ctr.ChangeStudentID=s.Student_ID"
+ " join ChangeTypes ct on ctr.ChangeTypes_ID=ct.ChangeTypes_ID"
+ " where s.Student_ID=@0";
DataTable dt = DataBaseHelper.cmbSqlDataAdapter(sql, studentid);
return(dt);
}
/// <summary>
/// 模糊查询（教师）
/// </summary>
/// <param name="teachername">教师姓名</param>
/// <returns></returns>
public DataTable GetInfoLikeTeacherName(string teachername)
{
string sql = "select c.Classes_ID 班级名称,c.Classes_Name 班级编号,s.Speciality_Name 专业名称,t.Teacher_Name 教师姓名,c.Classes_PS 班级说明 "
+ " from Classes as c,Teacher as t,Speciality as s,Colleges"
+ " where c.ClassHeadTeacher=t.Teacher_ID "
+ " and c.Classes_Speciality=s.Speciality_ID"
+ " and s.Speciality_College=Colleges.College_ID"
+ " and t.Teacher_Name like '%'+@0+'%'";
return(DataBaseHelper.cmbSqlDataAdapter(sql, teachername));
}
public DataTable GetPunishment(string studentid)
{
string sql = "select par.PunshmentAwardsRecode_ID 处分编号,pat.PunishmentAwardTypes_Name 处分类型,"
+ " s.Student_Name 学生姓名,par.PunshmentAwardsContent 处分内容,"
+ " par.PunshmentAwardsReason 处分原因,par.PunshmentAwardsDate 处分时间 from PunshmentAwardsRecode par"
+ " join PunishmentAwardTypes pat on par.PunshmentAwardsType_ID=pat.PunishmentAwardTypes_ID"
+ " join Students s on s.Student_ID=par.PunshmentAwardStudentID"
+ " where PunshmentAwardsRecode_ID<0 and Student_ID=@0";
DataTable dt = DataBaseHelper.cmbSqlDataAdapter(sql, studentid);
return(dt);
}
public DataTable GetStudentInfoByID(string StudentID)
{
using (SqlConnection conn = new SqlConnection(strsql))
{
string sql = "select * from Students where Student_ID=@0";
/*SqlCommand cmd = new SqlCommand(sql,conn);
*
* SqlParameter sp = new SqlParameter("@studentid",StudentID);
* cmd.Parameters.Add(sp);
*
* SqlDataAdapter sda = new SqlDataAdapter(cmd);
* DataSet ds = new DataSet();
* sda.Fill(ds);
* return ds.Tables[0];*/
DataTable dt = DataBaseHelper.cmbSqlDataAdapter(sql, StudentID);
return(dt);
}
}
/// <summary>
/// 根据专业id获取成绩
/// </summary>
/// <param name="specid"></param>
/// <returns></returns>
public DataTable GetExamInationBySpecid(string specid)
{
string sql = "select * from Examination e join Sect_Subjects ss on e.SubjectsID=ss.Subjects_ID where ss.Sepc_ID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, specid));
}
/// <summary>
/// 根据班级id获取学生信息
/// </summary>
/// <param name="Classid"></param>
/// <returns></returns>
public DataTable GetStudentByClassid(string Classid)
{
string sql = "select * from Students where Student_Class=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, Classid));
}
public DataTable GetExamInationByClassid(string classid)
{
string sql = "select * from Examination e join Students s on e.StudentID=s.Student_ID where s.Student_Class=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, classid));
}
public DataTable GetStudentByClassid(string classid)
{
string sql = "select s.Student_ID,s.Student_Name,c.Classes_Name from Students s join Classes c on s.Student_Class=c.Classes_ID where s.Student_Class=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, classid));
}
/// <summary>
/// 获取班级
/// </summary>
/// <returns></returns>
public DataTable GetClasses()
{
string sql = "select * from Classes";
return(DataBaseHelper.cmbSqlDataAdapter(sql));
}
/// <summary>
/// 根据专业id获取科目
/// </summary>
/// <param name="specid"></param>
/// <returns></returns>
public DataTable GetSubjectToSpecID(string specid)
{
string sql = "select s.Subjects_Name,s.Subjects_ID from Subject s join Sect_Subjects ss on s.Subjects_ID=ss.Subjects_ID where ss.Sepc_ID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, specid));
}
/// <summary>
/// 根据学生id查询成绩
/// </summary>
/// <param name="studentid">学生id</param>
/// <returns></returns>
public DataTable GetScoreByStudentid(string studentid)
{
string sql = "select * from Examination where StudentID=@0";
return(DataBaseHelper.cmbSqlDataAdapter(sql, studentid));
}
public DataTable GetTeacher()
{
string sql = "select * from Teacher";
return(DataBaseHelper.cmbSqlDataAdapter(sql));
}
