//public int AddTea(Model.Teacher.Teacher objTea, out string strErr)为添加的方法
//增加教师信息,int iRent为1添加信息成功,同时添加用户成功,为0则返回错误提示,并将错误赋给strErr
public int AddTea(Model.Teacher.Teacher objTea, out string strErr) //增加教师信息同时增加账号
{
int iRent = 0;
strErr = "";
SqlCommand sqlCom = new SqlCommand();
string strSql = "insert into teacher(teano,teaname,sex,age,degree,rank,teatel,teaaddress,remark,type)";
strSql += "values('" + objTea.TeaNo + "','" + objTea.TeaName + "','" + objTea.Sex + "','" + objTea.Age + "',";
strSql += "'" + objTea.Degree + "','" + objTea.Rank + "','" + objTea.TeaTel + "','" + objTea.TeaAddress + "','" + objTea.Remark + "',1) ";
strSql += "insert into users (userno,username,password,rights,type) values ('" + objTea.TeaNo + "','" + objTea.TeaName + "','" + objTea.TeaNo + "',2,1) ";
sqlCom.CommandText = strSql;
try
{
sqlCon.Open();
sqlCom.Connection = sqlCon;
sqlCom.ExecuteNonQuery();
iRent = 1;
}
catch (Exception exErr)
{
strErr = exErr.ToString();
iRent = 0;
}
finally
{
sqlCon.Close();
sqlCom.Dispose();
}
return(iRent);
}
private void btnSave_Click(object sender, EventArgs e)
{
string strErr = "";
Model.Teacher.Teacher objTeacher = new Model.Teacher.Teacher();
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
Manage.Teacher.Teacher objTea = new Manage.Teacher.Teacher(strConnection);
objTeacher.TeaNo = txtTeaNo.Text.Replace("'", "''");
objTeacher.TeaNo = objTeacher.TeaNo.Trim();
objTeacher.TeaName = txtTeaName.Text.Replace("'", "''");
objTeacher.TeaName = objTeacher.TeaName.Trim();
if (objTeacher.TeaNo == "" || objTeacher.TeaName == "")
{
MessageBox.Show("请将信息填写完整！");
}
else
{
if (rabMan.Checked == true)
{
objTeacher.Sex = "男";
}
else
{
objTeacher.Sex = "女";
}
objTeacher.Age = int.Parse(nudAge.Value.ToString());
objTeacher.Rank = cmbRank.SelectedItem.ToString();
objTeacher.Degree = cmbDegree.SelectedItem.ToString();
objTeacher.TeaAddress = txtAddress.Text.Replace("'", "''");
objTeacher.TeaTel = txtTel.Text.Replace("'", "''");
objTeacher.Remark = txtRemark.Text.Replace("'", "''");
int iTeaNo = objTea.JudgeTeaNo(objTeacher.TeaNo); //判断教师编号间是否重复
int iR = objTea.JudgeTeaNoLikeStuNo(objTeacher.TeaNo); //判断教师编号与学生编号是否重复
if (iTeaNo == 0)
{
MessageBox.Show("此编号的教师已经存在，添加失败");
}
else if (iR == 0)
{
MessageBox.Show("存在一个此编号的学生，教师编号不能和学生编号相同");
}
else
{
int iRent = objTea.AddTea(objTeacher, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
MessageBox.Show("添加成功");
this.Visible = false;
}
}
}
}
public void Data_Load()
{
string strErr = "";
Model.Teacher.Teacher objTeacher = new Model.Teacher.Teacher();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
string strSql = "select * from teacher where teaNo ='" + strTeaNo.Replace("'", "''") + "'";
SqlConnection sqlCon = new SqlConnection();
sqlCon.ConnectionString = strConnection;
sqlCon.Open();
SqlCommand sqlcomd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(strSql, sqlCon);
try
{
da.Fill(ds);
}
catch (Exception exce)
{
strErr = exce.ToString();
}
finally
{
sqlCon.Close();
}
DataRow dr = ds.Tables[0].Rows[0];
txtTeaNo.Text = dr["teaNo"].ToString();
strTeaName = txtTeaName.Text = dr["teaName"].ToString();
nudAge.Text = dr["age"].ToString();
cmbRank.SelectedItem = dr["rank"].ToString();
cmbDegree.SelectedItem = dr["Degree"].ToString();
txtAddress.Text = dr["TeaAddress"].ToString();
txtRemark.Text = dr["Remark"].ToString();
txtTel.Text = dr["TeaTel"].ToString();
string woman = dr["sex"].ToString();
if (woman == "女")
{
rdbWoman.Checked = true;
}
}
//修改教师信息的方法,public int ModifyTea(string strTeaNo, Model.Teacher.Teacher objTea, out string strErr)
//int iTeaId是传入的教师ID， string strOld是传入的旧的教师编号，
//Model.teacher.teacher objTea是模型类， out string strErr定义捕获错误的变量
//int 为1则修改成功，同时修改用户成功，模型类存储教师的相关信息，为0则返回错误提示，将错误赋给strErr。
public int ModifyTea(string strTeaNo, Model.Teacher.Teacher objTea, out string strErr) //修改教师信息同时修改账号信息
{
int iRent = 0;
strErr = "";
SqlCommand sqlCom = new SqlCommand();
objTea.TeaNo = objTea.TeaNo.Replace("'", "''");
objTea.TeaNo.Trim();
objTea.TeaName = objTea.TeaName.Replace("'", "''");
objTea.TeaName.Trim();
objTea.TeaTel = objTea.TeaTel.Replace("'", "''");
objTea.TeaTel.Trim();
objTea.TeaAddress = objTea.TeaAddress.Replace("'", "''");
objTea.TeaAddress.Trim();
objTea.Remark = objTea.Remark.Replace("'", "''");
objTea.Remark.Trim();
strTeaNo = strTeaNo.Replace("'", "''");
strTeaNo = strTeaNo.Trim();
string strSql = "update teacher ";
strSql += "set teano='" + objTea.TeaNo + "',teaname='" + objTea.TeaName + "',sex='" + objTea.Sex + "',age = '" + objTea.Age + "',";
strSql += "degree='" + objTea.Degree + "',rank='" + objTea.Rank + "',teatel='" + objTea.TeaTel + "',teaaddress='" + objTea.TeaAddress + "',remark='" + objTea.Remark + "' where teaNo ='" + strTeaNo.ToString() + "' and type =1 ";
strSql += " update users set username='******',password='******' where username='******'", "''").ToString() + "'and type=1 ";
sqlCom.CommandText = strSql;
try
{
sqlCon.Open();
sqlCom.Connection = sqlCon;
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.Teacher.Teacher objTeacher = new Model.Teacher.Teacher();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
string strSql = "select distinct b.courseid ,a.classid from class_teacher a,teacher_course b where a.teaid=(select teaid from teacher where teano='" + strTeaNo + "'and type=1) ";
strSql += " and a.teaid =b.teaid and a.type =1 and b.type =1 and a.classid =(select classid from class where classname ='" + strClassName + "' and type =1) ";
strSql += " and b.courseid =(select courseid from course where coursename='" + strCourseName + "' and type =1)";
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];
OldClassId = int.Parse(dr["classid"].ToString());
OldCourseId = int.Parse(dr["courseid"].ToString());
cmbClassName.SelectedValue = dr["classid"].ToString();
cmbCourseName.SelectedValue = dr["courseid"].ToString();
OldClass = cmbClassName.SelectedValue.ToString();
OldCourse = cmbCourseName.SelectedValue.ToString();
}
}//管理员按课程查询该课程学生成绩
public int SearchScoreTeaByAdmin(string strTeaNo, out DataTable objDataTable, out string strErr)
{
int iReturn = 0;
strErr = "";
SqlCommand sqlCom = new SqlCommand();
string strCom;
objDataTable = new DataTable();
Model.Teacher.Teacher objTeacher = new Model.Teacher.Teacher();
//strCom = strCom = "select distinct teacher.teaNo as 教师编号,teacher.teaName as 教师名称,student.stuNo as 学生编号,course.courseName as 课程名称, Course.Points as 得分,student.stuName as 学生姓名 from student,score,teacher_course,teacher,course where teacher.TeaNo like '%" + strTeaNo + "%'escape '\\' and Score.StuId = Student.stuId and Course.CourseId = Score.CourseId and Course.CourseId=Teacher_Course.CourseId and Student.Type = 1 and Teacher.Type = 1 ";
strCom = "select distinct teacher.teaNo as 教师编号,teacher.teaName as 教师名称,student.stuNo as 学生编号,course.courseName as 课程名称, score.Score as 得分,student.stuName as 学生姓名 from student,score,teacher_course,teacher,course where teacher.teaNo like '%";
strCom += strTeaNo + "%'escape '\\' and score.stuId = student.stuId and course.courseId = score.courseId and course.courseid=teacher_course.courseid and teacher.teaId = teacher_Course.teaId and score.Type = 1 and student.Type = 1 and teacher.Type = 1 ";
//strCom = "select student.stuNo 学生编号,student.stuName 姓名,course.courseNo 课程编号,course.courseName 课程名称,score.score 得分 from student,score,course where student.StuName like '%";
//strCom += StuName.ToString() + "%'escape '\\'and score.stuId = student.stuId and course.courseId = score.courseId and score.Type = 1 and student.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);
}//管理员按教师编号查询所有学生的成绩。
private void btnSave_Click(object sender, EventArgs e)
{
string strErr;
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["StudentManager"].ConnectionString;
Model.Teacher.Teacher objTea = new Model.Teacher.Teacher();
Manage.Teacher.Teacher objTeaManage = new Manage.Teacher.Teacher(strConnection);
objTea.TeaNo = txtTeaNo.Text.Replace("'", "''");
objTea.TeaNo = txtTeaNo.Text.Trim();
objTea.TeaName = txtTeaName.Text.Replace("'", "''");
objTea.TeaName = txtTeaName.Text.Trim();
objTea.Age = int.Parse(nudAge.Value.ToString());
objTea.Rank = cmbRank.Text;
objTea.TeaTel = txtTel.Text.Replace("'", "''");
objTea.TeaTel = txtTel.Text.Trim();
objTea.TeaAddress = txtAddress.Text.Replace("'", "''");
objTea.TeaAddress = txtAddress.Text.Trim();
objTea.Degree = cmbDegree.Text;
objTea.Remark = txtRemark.Text.Replace("'", "''");
objTea.Remark = txtRemark.Text.Trim();
if (rdbMan.Checked == true)
{
objTea.Sex = "男";
}
else
{
objTea.Sex = "女";
}
if (txtTeaNo.Text == "" || txtTeaName.Text == "")
{
MessageBox.Show("教师编号和姓名不能为空");
txtTeaNo.Text = strTeaNo;
txtTeaName.Text = strTeaName;
}
else if (objTea.TeaNo == strTeaNo)
{
int iRent = objTeaManage.ModifyTea(strTeaNo, objTea, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
MessageBox.Show("修改成功");
this.Visible = false;
}
}
else
{
int j = objTeaManage.JudgeTeaNo(objTea.TeaNo);
int i = objTeaManage.JudgeTeaNoLikeStuNo(objTea.TeaNo);
if (i == 1 && j == 1)
{
int iRent = objTeaManage.ModifyTea(strTeaNo, objTea, out strErr);
if (iRent == 0)
{
MessageBox.Show(strErr);
return;
}
else
{
MessageBox.Show("修改成功");
this.Visible = false;
}
}
else
{
MessageBox.Show("已经存在一个编号相同的教师或者学生，修改失败");
txtTeaNo.Text = strTeaNo;
}
}
}
