//获取所有学生姓名的集合
public static DataSet FillByAllStudentName(string className)
{
string strSql = "select StudentName from Student where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", className)));
}
//获取所有课程名称的集合
public static DataSet FillByAllSubjectName()
{
string strSql = "select SubjectName from Subject";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.CreaDataSet(strSql, null));
}
//添加用户
public static int CreaUser(string userName, bool level, bool status)
{
string strSql = "insert into UserInfo(UserName,Level,Status) values(@userName,@level,@status)";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@userName", userName), new SqlParameter("@level", level), new SqlParameter("@status", status)));
}
//获取所有教师名称的集合
public static DataSet FillByAllTeacherName()
{
string strSql = "select TeacherName from Teacher";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.CreaDataSet(strSql, null));
}
public static SqlDataReader CheckByUserNameAndPwd(string userName, string password)
{
string strSql = "select userName from UserInfo where [userName] = @userName and [password] = @password";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteReader(strSql, new SqlParameter("@userName", userName), new SqlParameter("@password", password)));
}
public static SqlDataReader CheckStatus(string userName)
{
string strSql = "select * from UserInfo where [userName] = @userName and status = '1'";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteReader(strSql, new SqlParameter("@userName", userName)));
}
public static SqlDataReader GetReaderByUserName(string userName)
{
string strSql = "select * from UserInfo where [UserName] = @userName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteReader(strSql, new SqlParameter("@userName", userName)));
}
//得到所有用户的记录
public static DataSet FillByAllUser()
{
string strSql = "select UserName,Level,Status from UserInfo";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.CreaDataSet(strSql, null));
}
//删除用户
public int Delete()
{
string strSql = "delete from UserInfo where [userName] = @userName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@userName", _userName)));
}
//更新班级
public int Update()
{
string strSql = "update Class set [Director] = @director,[EntranceDate] = @entranceDate,[Remark] = @remark where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@Director", this._director), new SqlParameter("@EntranceDate", this._entranceDate), new SqlParameter("@Remark", this._remark), new SqlParameter("@className", _className)));
}
//获取所有教师的集合
public static DataSet FillByAllTeacher()
{
string strSql = "select TeacherName,Gender,Birthday,IDCard,University,Specialfield,Diploma,Picture,Remark from Teacher";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.CreaDataSet(strSql, null));
}
//删除分数
public int Delete()
{
string strSql = "delete from Score where [StudentNo] = @studentNo and CourseID = @courseID";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@studentNo", _studentNo), new SqlParameter("@courseID", _courseID)));
}
//添加科目
public static int Add(string subjectName, int period, string remark)
{
string strSql = "insert into Subject(SubjectName,Period,Remark) values(@subjectName,@period,@remark)";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@subjectName", subjectName), new SqlParameter("@period", period), new SqlParameter("@remark", remark)));
}
//修改科目
public int Update()
{
string strSql = "update Subject set [Period] = @period, Remark = @remark where [SubjectName] = @subjectName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@period", _period), new SqlParameter("@remark", _remark), new SqlParameter("@subjectName", _subjectName)));
}
//通过科目名称返回一条记录,以判断科目是否存在
public static SqlDataReader GetReaderBySubjectName(string subjectName)
{
string strSql = "select SubjectName from Subject where [SubjectName] = @subjectName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteReader(strSql, new SqlParameter("@subjectName", subjectName)));
}
//判断用户是否为管理员
public SqlDataReader CheckAdmin()
{
string strSql = "select level from UserInfo where [userName] = @userName and [level] = '1'";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteReader(strSql, new SqlParameter("@userName", _userName)));
}
//得到所有班级名
public static DataTable GetAllClassName()
{
string strSql = "select ClassName from Class";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, null);
return(ds.Tables[0]);
}
public DataTable GetAllStudentNo()
{
string strSql = "select StudentNo from Student where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", _className));
return(ds.Tables[0]);
}
//得到所有班级的记录
public static DataSet FillByAllClass()
{
string strSql = "select * from Class";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, null);
return(ds);
}
//检查是否有班
public SqlDataReader IsStudentExists()
{
string strSql = "select * from Class where [ClassName] = @className and [CurrentCount] > 0 ";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@className", _className));
return(reader);
}
//检查是否有班
public static SqlDataReader IsClassExists()
{
string strSql = "select * from Class ";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, null);
return(reader);
}
//判断教师是否已存在
public static SqlDataReader GetReaderByTeacherName(string teacherName)
{
string strSql = "select TeacherName from Teacher where [TeacherName] = @teacherName";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@teacherName", teacherName));
return(reader);
}
//检查班级名是否已存在
public static SqlDataReader CheckExists(string className)
{
string strSql = "select * from Class where [className] = @className";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@className", className));
return(reader);
}
//判断学号是否已存在
public static SqlDataReader CheckExist(int studentNo)
{
string strSql = "select * from Student where [StudentNo] = @studentNo";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@studentNo", studentNo));
return(reader);
}
//获取班级所有学生的集合
public DataSet FillByAllStudent()
{
string strSql = "select StudentNo,StudentName,Gender,Birthday,Picture,Remark from Student where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", _className));
return(ds);
}
//得到指定班级所有课程名称的记录
public DataSet FillByAllSubjectName()
{
string strSql = "select SubjectName from Course where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", _className));
return(ds);
}
//得到指定班级所有课程信息的记录
public DataSet FillByAllCourse()
{
string strSql = "select SubjectName,Teacher,BeginDate,FinishDate,Remark from Course where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", _className));
return(ds);
}
//得到该班所有学生的成绩
public static DataSet FillByAllScoreInfo(string className)
{
string strSql = "select * from ScoreView where StudentNo in (select StudentNo from Student where ClassName = @className)";
SqlCommon sqlcomm = new SqlCommon();
DataSet ds = sqlcomm.CreaDataSet(strSql, new SqlParameter("@className", className));
return(ds);
}
//判断该班是否已存在相应课程
public SqlDataReader GetDataReaderBySubjectName(string subjectName)
{
string strSql = "select * from Course where [ClassName] = @className and [SubjectName] = @subjectName ";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@className", _className), new SqlParameter("@subjectName", subjectName));
return(reader);
}
//判断该学生是否已存在相应课程成绩
public static SqlDataReader GetReader(int studentNo, int courseID)
{
string strSql = "select * from Score where [StudentNo] = @studentNo and [CourseID] = @courseID";
SqlCommon sqlcomm = new SqlCommon();
SqlDataReader reader = sqlcomm.ExecuteReader(strSql, new SqlParameter("@studentNo", studentNo), new SqlParameter("@courseID", courseID));
return(reader);
}