/// <summary> /// 返回学生所有数据 /// </summary> /// <param name="id"></param> /// <returns></returns> public IEnumerable <StudentFullClassScore> GetStudentJsonResult(int page, int limit, out int count) { //var students = _msSqlContext.StudentsInfos // .Join(_msSqlContext.LastClassScores, stu => stu.Id, score => score.StudentsInfo, (stu, score) => // new StudentFullClassScore() // { // Id = stu.Id, Name = stu.Name, Age = stu.Age, Gender = stu.Gender, Phone = stu.Phone, // Grade = stu.Grade, ClassName = stu.ClassName, Remark = stu.Remark, Geography = score.Geography, // Chinese = score.Chinese, Physics = score.Physics, Biology = score.Biology, // English = score.English, // Math = score.Math, History = score.History, Politics = score.Politics, // Chemistry = score.Chemistry // }).SelectMany(combination=>combination.Id.DefaultIfEmpty(), (score, c) => new{}).ToList(); var students = (from stu in _msSqlContext.StudentsInfos join score in _msSqlContext.LastClassScores on stu.Id equals score.StudentsInfoId into t from score in t.DefaultIfEmpty() select new StudentFullClassScore() { StudentInfoId = stu.Id, Name = stu.Name, Grade = stu.Grade.ToString(), ClassName = stu.ClassName, Remark = stu.Remark, id = score.id, ClassInfoId = score.ClassInfoId, Geography = score.Geography, Chinese = score.Chinese, Physics = score.Physics, Biology = score.Biology, English = score.English, Math = score.Math, History = score.History, Politics = score.Politics, Chemistry = score.Chemistry, TotalScore = (score.Chinese + score.Math + score.English + score.Geography + score.Biology + score.Chemistry + score.History + score.Politics + score.Physics) }).OrderBy(key => key.id).Skip(limit * (page - 1)).Take(limit).ToList(); count = _msSqlContext.LastClassScores.Count(); _loggerHelper.WriteInfoLog(students); return(students); }
public void UpdateScore(string studentInfoId, string classInfoId, int newScore, string course) { SqlParameter[] sql = new [] { new SqlParameter("course", course), new SqlParameter("stuInfo", studentInfoId), new SqlParameter("classInfo", classInfoId), new SqlParameter("newScore", newScore) { Direction = ParameterDirection.Output }, }; _loggerHelper.WriteInfoLog(studentInfoId + "," + classInfoId + "," + newScore + "," + course); //没搞明白为什么ExecuteSqlInterpolated可以执行却不能变更,暂时先用ExecuteSqlRaw var update = _msSqlContext.Database .ExecuteSqlRaw($"update LastClassScores set {course} = {newScore} where ClassInfoId = '{classInfoId}' and StudentsInfoId = '{studentInfoId}'"); _msSqlContext.SaveChanges(); _loggerHelper.WriteInfoLog(update); }
/// <summary> /// 获取学生查询数据 /// </summary> /// <param name="page"></param> /// <param name="limit"></param> /// <param name="name"></param> /// <returns></returns> public string SutudentSearch(int page, int limit, string searchName, int grade, string className) { //if (!string.IsNullOrEmpty(searchName) && !string.IsNullOrEmpty(grade.ToString()) && !string.IsNullOrEmpty(className)) //{ _loggerHelper.WriteInfoLog(searchName + grade + className); var data = _queryDbData.GetStudentJsonResult(page, limit, searchName, grade, className); StuAndSocreToJsonResult <StudentFullClassScore> result = new StuAndSocreToJsonResult <StudentFullClassScore>(); result.count = data.Count(); result.code = 0; result.msg = ""; result.data = data; var json = JsonConvert.SerializeObject(result); return(json); }