}//学生按教师编号查询全班学生成绩 public int SearchScoreStu(string userNo, out DataTable objDataTable, out string strErr) { int iReturn = 0; strErr = ""; objDataTable = new DataTable(); SqlCommand sqlCom = new SqlCommand(); string strCom; Model.Score.Score objScore = new Model.Score.Score(); strCom = "select stuNo 学生编号,stuName 学生名称,courseName 课程名称,Class.className 班级名称,score 得分 from student,class,score,course,class_course where class.classid =(select class.classid from class,student where stuNo = '"; strCom += userNo + "'and student.classId=class.classId )and score.stuId = student.stuId and course.courseId = score.courseId and class.classid =class_course.classId and class_course.courseid = course.courseId and score.Type = 1 and student.Type = 1 and course.Type =1"; sqlCom.CommandText = strCom; sqlCom.Connection = sqlCon; SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCom); try { sqlDa.Fill(objDataTable); iReturn = 1; } catch (Exception ex) { strErr = ex.ToString(); iReturn = 0; } finally { sqlDa.Dispose(); sqlCom.Dispose(); } return(iReturn); }//学生登陆后,绑定表里显示的数据
public int ModifyScore(Model.Score.Score objScore, out string strErr) { int iReturn = 0; strErr = ""; SqlCommand sqlCom = new SqlCommand(); sqlCom.Connection = sqlCon; string strSql = "update score set score = " + objScore.score.ToString(); strSql += "where CourseId = " + objScore.CourseId.ToString() + "and StuId = " + objScore.StuId.ToString(); sqlCom.CommandText = strSql; try { sqlCon.Open(); sqlCom.ExecuteNonQuery(); iReturn = 1; } catch (Exception exErr) { strErr = exErr.ToString(); iReturn = 0; } finally { sqlCon.Close(); sqlCom.Dispose(); } return(iReturn); }
}//管理员按教师编号查询所有学生的成绩。 public int SearchScore(out DataTable objDataTable, out string strErr) { int iReturn = 0; strErr = ""; objDataTable = new DataTable(); SqlCommand sqlCom = new SqlCommand(); string strCom; Model.Score.Score objScore = new Model.Score.Score(); strCom = "select distinct student.stuNo 学号,student.stuName 学生姓名,course.courseName 课程名称,course.courseno 课程编号, score.Score 得分 from student,score,course where score.stuId = student.stuId and course.courseId = score.courseId and score.Type = 1 and student.Type = 1 and course.Type =1"; sqlCom.CommandText = strCom; sqlCom.Connection = sqlCon; SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCom); try { sqlDa.Fill(objDataTable); iReturn = 1; } catch (Exception ex) { strErr = ex.ToString(); iReturn = 0; } finally { sqlDa.Dispose(); sqlCom.Dispose(); } return(iReturn); }//管理员登陆后,查询所有成绩表里的记录。
public int AddScore(Model.Score.Score objScore, out string strErr) { int iReturn = 0; strErr = ""; SqlCommand sqlCom = new SqlCommand(); sqlCom.Connection = sqlCon; string strSql = "insert into score(CourseId,StuId,Score,Type)"; strSql += "values (" + objScore.CourseId.ToString() + "," + objScore.StuId.ToString() + "," + objScore.score.ToString() + ",1)"; sqlCom.CommandText = strSql; try { sqlCon.Open(); sqlCom.ExecuteNonQuery(); iReturn = 1; } catch (Exception exErr) { strErr = exErr.ToString(); iReturn = 0; } finally { sqlCon.Close(); sqlCom.Dispose(); } return(iReturn); }
//添加成绩信息 private void btnSave_Click(object sender, EventArgs e) { string strErr = ""; Model.Class.Class objClass1 = new Model.Class.Class(); Model.Student.Student objStudent1 = new Model.Student.Student(); Model.Course.Course objCourse1 = new Model.Course.Course(); objClass1.ClassNo = cmbClassName.SelectedValue.ToString(); objStudent1.StuId = int.Parse(cmbStuNo.SelectedValue.ToString()); objCourse1.CourseId = int.Parse(cmbCourseName.SelectedValue.ToString()); string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString; Manage.Score.Score objSco = new Manage.Score.Score(strConnection); int i = objSco.Judge(objStudent1, objCourse1); // 判断是否存在所选的成绩,如果为0,表示不存在记录,可以增加。为1表示存在记录,不能重复插入成绩。 if (i == 0) { Model.Score.Score objScore = new Model.Score.Score(); Model.Student.Student objStudent = new Model.Student.Student(); Model.Course.Course objCourse = new Model.Course.Course(); objScore.CourseId = int.Parse(cmbCourseName.SelectedValue.ToString()); objScore.StuId = int.Parse(cmbStuNo.SelectedValue.ToString()); objScore.score = float.Parse(nudScore.Value.ToString()); objScore.StuName = txtStuName.Text; int iRent = objSco.AddScore(objScore, out strErr); if (iRent == 0) { MessageBox.Show(strErr); return; } else { MessageBox.Show("添加成功"); this.Visible = false; BindClassId(); BindStuId(); BindCourseId(); } } else { MessageBox.Show("不能重复插入分数"); } }
//修改成绩信息 private void btnSave_Click(object sender, EventArgs e) { string strErr = ""; Model.Student.Student objStudent1 = new Model.Student.Student(); Model.Course.Course objCourse1 = new Model.Course.Course(); objStudent1.StuId = int.Parse(txtStuNo.ToString()); objCourse1.CourseId = int.Parse(txtCourseNo.ToString()); string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString; Manage.Score.Score objSco = new Manage.Score.Score(strConnection); int i = objSco.Judge(objStudent1, objCourse1); //返回为0,表示不存在该成绩记录,不能修改。返回为1,表示存在记录,可以修改。 if (i == 1) { if (DialogResult.Yes == MessageBox.Show("真的要修改该成绩吗?", "确认修改", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { string strEr = ""; Model.Score.Score objScore = new Model.Score.Score(); Model.Student.Student objStudent = new Model.Student.Student(); Model.Course.Course objCourse = new Model.Course.Course(); objScore.CourseId = int.Parse(txtCourseNo.ToString()); objScore.StuId = int.Parse(txtStuNo.ToString()); objScore.score = float.Parse(nudScore.Value.ToString()); int iRent = objSco.ModifyScore(objScore, out strErr); if (iRent == 0) { MessageBox.Show(strEr); return; } else { MessageBox.Show("修改成功"); //BindDataGridView(); this.Visible = false; } } } else if (i == 0) { MessageBox.Show("当前要修改的成绩不存在,请确定后再修改"); } }
}//学生登陆后,绑定表里显示的数据 public int Judge(Model.Student.Student objStuId, Model.Course.Course objCourseId) { string strErr = ""; Model.Score.Score objScore = new Model.Score.Score(); DataSet ds = new DataSet(); string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString; string strSql = "select Score from score where stuid='" + objStuId.StuId.ToString() + "'and CourseId='" + objCourseId.CourseId + "'and type =1"; SqlConnection sqlCon = new SqlConnection(); sqlCon.ConnectionString = strConnection; sqlCon.Open(); SqlCommand sqlCom = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(strSql, sqlCon); try { da.Fill(ds); } catch (Exception exce) { strErr = exce.ToString(); } finally { sqlCon.Close(); } if (ds.Tables[0].Rows.Count != 0) { return(1); } else { return(0); } }