public int ModifyClass(string classNo, Model.Class.Class objClass, out string strErr)//修改班级信息
{
int iRent = 0;
strErr = "";
SqlCommand sqlCom = new SqlCommand();
string strSql = "update class ";
strSql += "set classname='" + objClass.ClassName + "',remark='" + objClass.Remark + "', classNo = '" + objClass.ClassNo + "'where classId in (select classId from class where classNo ='" + classNo.Replace("'", "''") + "')";
sqlCom.CommandText = strSql;
sqlCom.Connection = sqlCon;
try
{
sqlCon.Open();
sqlCom.ExecuteNonQuery();
iRent = 1;
}
catch (Exception exErr)
{
strErr = exErr.ToString();
iRent = 0;
}
finally
{
sqlCon.Close();
sqlCom.Dispose();
}
return(iRent);
}
public int AddClass(Model.Class.Class objClass, out string strErr)//增加班级信息
{
int iRent = 0;
strErr = "";
SqlCommand sqlCom = new SqlCommand();
string strSql = "insert into Class(classno,classname,remark,type)";
strSql += "values('" + objClass.ClassNo + "','" + objClass.ClassName + "','" + objClass.Remark + "',1)";
sqlCom.CommandText = strSql;
sqlCom.Connection = sqlCon;
try
{
sqlCon.Open();
sqlCom.ExecuteNonQuery();
iRent = 1;
}
catch (Exception exErr)
{
strErr = exErr.ToString();
iRent = 0;
}
finally
{
sqlCon.Close();
sqlCom.Dispose();
}
return(iRent);
}
public void LoadData()
{
string strErr = "";
Model.Class.Class objClass = new Model.Class.Class();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
string strSql = "select * from class where classNo ='" + classNo.Replace("'", "''") + "'";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = strConnection;
conn.Open();
SqlCommand sqlcomd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
try
{
da.Fill(ds);
}
catch (Exception exce)
{
strErr = exce.ToString();
}
finally
{
conn.Close();
}
DataRow dr = ds.Tables[0].Rows[0];
className = this.txtClassName.Text = dr["ClassName"].ToString();
this.txtClassNo.Text = dr["ClassNo"].ToString();
this.txtRemark.Text = dr["Remark"].ToString();
}
private void btnSave_Click(object sender, EventArgs e)
{
string strErr = "";
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
Model.Class.Class objClass = new Model.Class.Class();
Manage.Class.Class objclass = new Manage.Class.Class(strConnection);
objClass.ClassNo = txtClassNo.Text.Replace("'", "''");
objClass.ClassNo = objClass.ClassNo.Trim();
objClass.ClassName = txtClassName.Text.Replace("'", "''");
objClass.ClassName = objClass.ClassName.Trim();
objClass.Remark = txtRemark.Text.Replace("'", "''");
objClass.Remark = objClass.Remark.Trim();
if (objClass.ClassName == "" || objClass.ClassNo == "")
{
MessageBox.Show("请将信息填写完整！");
}
else
{
int i = objclass.JudgeClassNo(objClass);
int j = objclass.JudgeClassName(objClass);
if (i == 0 && j == 0)
{
int iRent = objclass.AddClass(objClass, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
MessageBox.Show("添加成功");
this.Visible = false;
}
}
else if (i == 1 && j == 0)
{
MessageBox.Show("当前班级已经存在，新建失败");
}
else if (i == 0 && j == 1)
{
MessageBox.Show("当前班级名称已经存在，新建失败");
}
else
{
MessageBox.Show("班级编号和班级名称都已经存在，新建失败");
}
}
}
//添加成绩信息
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("不能重复插入分数");
}
}
//输入学生编号、课程编号，将要删除的成绩表里的type值设为0，查询时只显示type为1的记录。
public int SearchScoreClass(string ClassNo, out DataTable objDataTable, out string strErr)
{
int iReturn = 0;
strErr = "";
objDataTable = new DataTable();
SqlCommand sqlCom = new SqlCommand();
string strCom;
Model.Class.Class objClass = new Model.Class.Class();
//此处有错误
//class.classNo
//strCom = "select StuNo 学生编号,stuName 学生姓名,className 班级名称,class.classNo 班级编号,CourseName 课程名称,courseNo 课程编号,Score 得分 from student,score,course,class where course.CourseNo like '%";
//strCom += ClassNo.ToString() + "%'escape '\\' and score.courseId = course.courseId and score.stuId = student.stuId and student.classId = class.classId and score.Type = 1";
//strCom= "select StuNo 学生编号,stuName 学生姓名,ClassName 班级名称,class.classNo 班级编号,CourseName 课程名称,courseNo 课程编号,Score 得分 from student,score,course,class where course.CourseNo like";
//strCom+= ClassNo.ToString()+"and score.courseId = course.courseId and score.stuId = student.stuId and student.classId = class.classId and score.Type = 1";
//strCom = "select StuNo 学生编号,stuName 学生姓名,ClassName 班级名称,class.classNo 班级编号,CourseName 课程名称,courseNo 课程编号,Score 得分 from student,score,course,class where class.ClassName like+ '%" + + "%' escape '\\'" + " and score.courseId = course.courseId and score.stuId = student.stuId and student.classId = class.classId and score.Type = 1";
strCom = "select StuNo 学生编号,stuName 学生姓名,classname 班级名称,class.classNo 班级编号,CourseName 课程名称,courseNo 课程编号,Score 得分 from student,score,course,class where class.classNo like '%";
strCom += ClassNo.ToString() + "%'escape '\\' and score.courseId = course.courseId and score.stuId = student.stuId and student.classId = class.classId and score.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 JudgeClassName(Model.Class.Class objClassName)//通过查询插入的班级名称是否存在来判断能不能添加当前班级。返回查询结果：0或非0
{
string strErr = "";
DataSet ds = new DataSet();
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
string strSql = "select classId from class where className = '" + objClassName.ClassName + "' 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(0);
}
else
{
return(1);
}
}
private void btnSave_Click(object sender, EventArgs e) //保存修改后的班级。如果修改后的班级编号和名称有相同的，修改失败
{
string strErr = "";
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
Model.Class.Class objClass = new Model.Class.Class();
Manage.Class.Class objClassMan = new Manage.Class.Class(strConnection);
objClass.ClassNo = txtClassNo.Text.Replace("'", "''");
objClass.ClassNo = objClass.ClassNo.Trim();
objClass.ClassName = txtClassName.Text.Replace("'", "''");
objClass.ClassName = objClass.ClassName.Trim();
objClass.Remark = txtRemark.Text.Replace("'", "''");
objClass.Remark = objClass.Remark.Trim();
string strClassNo = classNo.Replace("'", "''");
strClassNo = strClassNo.Trim();
string strClassName = className.Replace("'", "''");
strClassName = strClassName.Trim();
if (objClass.ClassName == "" || objClass.ClassNo == "")
{
MessageBox.Show("请将信息填写完整！");
}
else
{
int i = objClassMan.JudgeClassNo(objClass);
int j = objClassMan.JudgeClassName(objClass);
if (strClassNo == objClass.ClassNo && strClassName == objClass.ClassName)
{
int iRent = objClassMan.ModifyClass(classNo, objClass, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
this.Visible = false;
}
}
else if (strClassNo != objClass.ClassNo && strClassName == objClass.ClassName)
{
if (i == 0)
{
int iRent = objClassMan.ModifyClass(classNo, objClass, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
this.Visible = false;
}
}
else
{
MessageBox.Show("当前班级编号已经存在，修改失败");
txtClassNo.Text = classNo;
}
}
else if (strClassNo == objClass.ClassNo && strClassName != objClass.ClassName)
{
if (j == 0)
{
int iRent = objClassMan.ModifyClass(classNo, objClass, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
this.Visible = false;
}
}
else
{
MessageBox.Show("当前班级名称已经存在，修改失败");
txtClassName.Text = className;
}
}
else
{
if (i == 0 && j == 0)
{
int iRent = objClassMan.ModifyClass(classNo, objClass, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
this.Visible = false;
}
}
else
{
MessageBox.Show("当前班级编号和名称都已经存在，修改失败");
txtClassNo.Text = classNo;
}
}
}
}