//添加学生
public static bool AddStudent(Student stu)
{
string sql = "insert Student values(@StuNo,@Pwd,@StuName,@Gender,@GradeId,@Phone,@Address,@BornDate,@Email)";
SqlParameter[] paras =
{
new SqlParameter("@StuNo", stu.StudentNo),
new SqlParameter("@Pwd", stu.LoginPwd),
new SqlParameter("@StuName", stu.StudentName),
new SqlParameter("@Gender", stu.Gender),
new SqlParameter("@GradeId", stu.Grade.GradeId),
new SqlParameter("@Phone", stu.Phone),
new SqlParameter("@Address", stu.Address),
new SqlParameter("@BornDate", stu.BornDate),
new SqlParameter("@Email", stu.Email)
};
try
{
return(SQLDBHelper.ExecuteNonQueryByDBHelper(sql, paras));
}
catch (Exception)
{
throw;
}
}
//修改学生信息
public static bool UpdateStudent(Student stu)
{
string sql = "Update Student set LoginPwd = @Pwd,StudentName = @StuName,Gender = @Gender,GradeId = @GradeId,Phone = @Phone,Address = @Address,BornDate = @BornDate, Email = @Email where StudentNo = @StudentNo";
SqlParameter[] paras =
{
new SqlParameter("@Pwd", stu.LoginPwd),
new SqlParameter("@StuName", stu.StudentName),
new SqlParameter("@Gender", stu.Gender),
new SqlParameter("@GradeId", stu.Grade.GradeId),
new SqlParameter("@Phone", stu.Phone),
new SqlParameter("@Address", stu.Address),
new SqlParameter("@BornDate", stu.BornDate),
new SqlParameter("@Email", stu.Email),
new SqlParameter("@StudentNo", stu.StudentNo)
};
try
{
return(SQLDBHelper.ExecuteNonQueryByDBHelper(sql, paras));
}
catch (Exception)
{
throw;
}
}
//使用实体扩展类显示ListView
public static List <ResultSeach> SeachResultByStu(string stuNo)
{
string sql = "select r.*,s.StudentName,sub.SubjectName from Result as r inner join Student as s on s.StudentNo = r.StudentNo inner join Subject as sub on r.SubjectId = sub.SubjectId where r.StudentNo = @StuNo";
SqlParameter[] paras =
{
new SqlParameter("@StuNo", stuNo)
};
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql, paras);
List <ResultSeach> results = new List <ResultSeach>();
while (reader.Read())
{
results.Add(
new ResultSeach(
reader["StudentName"].ToString(),
reader["SubjectName"].ToString(),
Convert.ToInt32(reader["StudentResult"]),
Convert.ToDateTime(reader["ExamDate"])
)
);
}
reader.Close();
return(results);
}
catch (Exception)
{
throw;
}
}
//通过性别及年级Id获取学生
public static List <Student> GetAllStudentByStuIdAndGender(int?id = null, string gender = null)
{
string sql = "select s.*,g.GradeName from Student as s inner join Grade as g on s.GradeId = g.GradeId where 1=1 ";
SqlParameter[] paras = null;
if (id != null)
{
sql += " and s.GradeId = @GradeId ";
paras = new SqlParameter[] {
new SqlParameter("@GradeId", id)
};
}
if (gender != null)
{
sql += " and s.Gender = @Gender";
paras = new SqlParameter[] {
new SqlParameter("@GradeId", id),
new SqlParameter("@Gender", gender)
};
}
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql, paras);
List <Student> stus = new List <Student>();
while (reader.Read())
{
stus.Add(
new Student(
reader["StudentNo"].ToString(),
reader["LoginPwd"].ToString(),
reader["StudentName"].ToString(),
reader["Gender"].ToString(),
new Grade(
Convert.ToInt32(reader["GradeId"]),
reader["GradeName"].ToString()
),
reader["Phone"].ToString(),
reader["Address"].ToString(),
Convert.ToDateTime(reader["BornDate"]),
reader["Email"].ToString()
)
);
}
reader.Close();
return(stus);
}
catch (Exception)
{
throw;
}
}
//删除成绩
public static bool DelResultByStuNo(string StuNo)
{
string sql = "delete Result where StudentNo = @StudentNo";
SqlParameter[] paras =
{
new SqlParameter("@StudentNo", StuNo)
};
try
{
return(SQLDBHelper.ExecuteNonQueryByShiWu(sql, paras));
}
catch (Exception)
{
throw;
}
}
public static bool EditLoginPwd(Admin admin)
{
string sql = "Update Admin set LoginPwd = @NewPwd where LoginId = @Id";
SqlParameter[] paras = {
new SqlParameter("@NewPwd",admin.LoginPwd),
new SqlParameter("@Id",admin.LoginId)
};
try
{
return SQLDBHelper.ExecuteNonQueryByDBHelper(sql,paras);
}
catch (Exception)
{
throw;
}
}
public static bool CheckLogin(Admin admin)
{
string sql = "select * from Admin where LoginId = @Id and LoginPwd = @Pwd";
SqlParameter[] paras = {
new SqlParameter("@Id",admin.LoginId),
new SqlParameter("@Pwd",admin.LoginPwd)
};
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql,paras);
bool result = reader.Read();
reader.Close();
return result;
}
catch (Exception)
{
throw;
}
}
//新增成绩
public static bool AddResult(ResultInsert result)
{
string sql = "insert Result Values(@StuNo,@SubNo,@Result,@ExamDate)";
SqlParameter [] paras =
{
new SqlParameter("@StuNo", result.StudentNo),
new SqlParameter("@SubNo", result.SubjectNo),
new SqlParameter("@Result", result.StudentResult),
new SqlParameter("@ExamDate", result.ExamDate)
};
try
{
return(SQLDBHelper.ExecuteNonQueryByDBHelper(sql, paras));
}
catch (Exception)
{
throw;
}
}
//根据学生姓名获取学生信息
public static List <StudentBuss> GetAllStudentBuss(string name)
{
string sql = "select s.*,g.GradeName from Student as s inner join Grade as g on s.GradeId = g.GradeId where StudentName like +'%'+@Name+'%'";
SqlParameter[] paras =
{
new SqlParameter("@Name", name)
};
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql, paras);
List <StudentBuss> stus = new List <StudentBuss>();
while (reader.Read())
{
stus.Add(
new StudentBuss(
reader["StudentNo"].ToString(),
reader["LoginPwd"].ToString(),
reader["StudentName"].ToString(),
reader["Gender"].ToString(),
new Grade(
Convert.ToInt32(reader["GradeId"]),
reader["GradeName"].ToString()
),
reader["Phone"].ToString(),
reader["Address"].ToString(),
Convert.ToDateTime(reader["BornDate"]),
reader["Email"].ToString()
)
);
}
reader.Close();
return(stus);
}
catch (Exception)
{
throw;
}
}
public static List <Grade> GetAllGrade()
{
string sql = "select * from Grade";
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql);
List <Grade> grades = new List <Grade>();
while (reader.Read())
{
grades.Add(
new Grade(Convert.ToInt32(reader["GradeId"]), reader["GradeName"].ToString())
);
}
reader.Close();
return(grades);
}
catch (Exception)
{
throw;
}
}
//获取科目信息
public static List <Subject> GetSubject(int?gradeId = null)
{
string sql = "select s.*,g.GradeName from Subject as s inner join Grade as g on s.GradeId = g.GradeId where 1=1 ";
SqlParameter[] paras = null;
if (gradeId != null)
{
sql += " and g.GradeId = @GradeId";
paras = new SqlParameter[] {
new SqlParameter("@GradeId", gradeId)
};
}
try
{
SqlDataReader reader = SQLDBHelper.GetReader(sql, paras);
List <Subject> subs = new List <Subject>();
while (reader.Read())
{
subs.Add(
new Subject(
Convert.ToInt32(reader["SubjectId"]),
reader["SubjectName"].ToString(),
Convert.ToInt32(reader["ClassHour"]),
new Grade(
Convert.ToInt32(reader["GradeId"]),
reader["GradeName"].ToString()
)
)
);
}
reader.Close();
return(subs);
}
catch (Exception)
{
throw;
}
}
