//添加科目
public static int Add(string subjectName, int period, string remark)
{
string strSql = "insert into Subject(SubjectName,Period,Remark) values(@subjectName,@period,@remark)";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@subjectName", subjectName), new SqlParameter("@period", period), new SqlParameter("@remark", remark)));
}
//更新班级
public int Update()
{
string strSql = "update Class set [Director] = @director,[EntranceDate] = @entranceDate,[Remark] = @remark where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@Director", this._director), new SqlParameter("@EntranceDate", this._entranceDate), new SqlParameter("@Remark", this._remark), new SqlParameter("@className", _className)));
}
//修改科目
public int Update()
{
string strSql = "update Subject set [Period] = @period, Remark = @remark where [SubjectName] = @subjectName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@period", _period), new SqlParameter("@remark", _remark), new SqlParameter("@subjectName", _subjectName)));
}
//删除用户
public int Delete()
{
string strSql = "delete from UserInfo where [userName] = @userName";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@userName", _userName)));
}
//添加用户
public static int CreaUser(string userName, bool level, bool status)
{
string strSql = "insert into UserInfo(UserName,Level,Status) values(@userName,@level,@status)";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@userName", userName), new SqlParameter("@level", level), new SqlParameter("@status", status)));
}
//删除分数
public int Delete()
{
string strSql = "delete from Score where [StudentNo] = @studentNo and CourseID = @courseID";
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, new SqlParameter("@studentNo", _studentNo), new SqlParameter("@courseID", _courseID)));
}
//改变密码
public int ChangePassword()
{
string strSql = "update UserInfo set [password] = @password where [userName] = @userName";
SqlParameter[] para = { new SqlParameter("@password", _password), new SqlParameter("@userName", _userName) };
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql, para));
}
//修改分数
public int Update()
{
string strSql = "update Score set [Score] = @score where [StudentNo] = @studentNo and [CourseID] = @courseID";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[3];
paras[0] = new SqlParameter("@studentNo", _studentNo);
paras[1] = new SqlParameter("@courseID", _courseID);
paras[2] = new SqlParameter("@score", _scoreCount);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//改变用户权限
public int ChangeLevel()
{
string strSql = "update UserInfo set [Level] = @level ,Status = @status where [UserName] = @userName";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] para = new SqlParameter[3];
para[0] = new SqlParameter("@level", _level);
para[1] = new SqlParameter("@status", _status);
para[2] = new SqlParameter("@userName", _userName);
return(sqlcomm.ExecuteNonQuery(strSql, para));
}
//添加分数
public static int Add(int studentNo, int courseID, double score)
{
string strSql = "insert into Score values(@studentNo,@courseID,@score)";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[3];
paras[0] = new SqlParameter("@studentNo", studentNo);
paras[1] = new SqlParameter("@courseID", courseID);
paras[2] = new SqlParameter("@score", score);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//删除科目
public int Delete()
{
//删除科目的同时级联删除课程、成绩信息
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Score where [CourseID] in (select CourseID from Course where [SubjectName] = @subjectName) ;");
strSql.Append("delete from Course where [SubjectName] = @subjectName ;");
strSql.Append("delete from Subject where [SubjectName] = @subjectName");
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql.ToString(), true, false, new SqlParameter("@subjectName", _subjectName)));
}
//删除教师
public int Delete()
{
//删除教师的同时将同时删除你相应的课程以及相应课程成绩的信息
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Score where [CourseID] in (select CourseID from Course where [Teacher] = @teacherName);");
strSql.Append("delete from Course where [Teacher] = @teacherName ;");
strSql.Append("delete from Teacher where [TeacherName] = @teacherName ;");
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql.ToString(), true, false, new SqlParameter("@teacherName", _teacherName)));
}
//删除课程
public int Delete()
{
int courseID = GetCourseID();
//删除课程的同时将删除与其对应的所有成绩记录
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Score where [CourseID] = @courseID ;");
strSql.Append("delete from Course where [CourseID] = @courseID;");
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql.ToString(), true, false, new SqlParameter("@courseID", courseID)));
}
//新建班级
public static int CreateClass(string className, string director, DateTime entranceDate, string remark)
{
string strSql = "insert into Class(ClassName,Director,EntranceDate,Remark) values(@className,@director,@entranceDate,@remark);";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] pa = new SqlParameter[4];
pa[0] = new SqlParameter("@ClassName", className);
pa[1] = new SqlParameter("@Director", director);
pa[2] = new SqlParameter("@EntranceDate", entranceDate);
pa[3] = new SqlParameter("@Remark", remark);
return(sqlcomm.ExecuteNonQuery(strSql, pa));
}
public int Delete()
{
//删除学生资料时，同时删除该生成绩
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Score where [StudentNo] = @studentNo ;");
strSql.Append("delete from Student where [StudentNo] = @studentNo;");
strSql.Append("update Class set [currentCount] = [currentCount]-1 where [ClassName] = @className ;");
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql.ToString(), true, false, new SqlParameter("@studentNo", _studentNo), new SqlParameter("@className", _className)));
}
//删除班级
public int Delete()
{
//删除班级的同时删除该班所有学生记录、课程记录、成绩记录
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Score where [StudentNo] in (Select [StudentNo] from Student where [ClassName] = @className) ;");
strSql.Append("delete from Course where [ClassName] = @className ;");
strSql.Append("delete from Student where [ClassName] = @className ;");
strSql.Append("delete from Class where [ClassName] = @className ;");
SqlCommon sqlcomm = new SqlCommon();
return(sqlcomm.ExecuteNonQuery(strSql.ToString(), true, false, new SqlParameter("@className", _className)));
}
//添加课程
public static int Add(string className, string subjectName, string teacher, DateTime beginDate, DateTime finishDate, string remark)
{
string strSql = "insert into Course(className,subjectName,teacher,beginDate,finishDate,remark) values(@className,@subjectName,@teacher,@beginDate,@finishDate,@remark)";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[6];
paras[0] = new SqlParameter("@className", className);
paras[1] = new SqlParameter("@subjectName", subjectName);
paras[2] = new SqlParameter("@teacher", teacher);
paras[3] = new SqlParameter("@beginDate", beginDate);
paras[4] = new SqlParameter("@finishDate", finishDate);
paras[5] = new SqlParameter("@remark", remark);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//修改课程
public int Update()
{
string strSql = "update Course set Teacher = @teacher,[BeginDate] = @beginDate, [FinishDate] = @finishDate ,[Remark] = @remark where [ClassName] = @className and [SubjectName] = @subjectName";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[6];
paras[0] = new SqlParameter("@teacher", _teacher);
paras[1] = new SqlParameter("@beginDate", _beginDate);
paras[2] = new SqlParameter("@finishDate", _finishDate);
paras[3] = new SqlParameter("@Remark", _remark);
paras[4] = new SqlParameter("@className", _className);
paras[5] = new SqlParameter("@subjectName", _subjectName);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//修改老师资料
public int Update()
{
string strSql = "update Teacher set Gender =@gender,Birthday = @birthday,IDCard = @idCard,University =@university,Specialfield = @specialfield,Diploma = @diploma ,Picture = @picturePath,Remark = @remark where TeacherName = @teacherName";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[9];
paras[0] = new SqlParameter("@teacherName", _teacherName);
paras[1] = new SqlParameter("@gender", _gender);
paras[2] = new SqlParameter("@birthday", _birthday);
paras[3] = new SqlParameter("@idCard", _idCard);
paras[4] = new SqlParameter("@university", _university);
paras[5] = new SqlParameter("@specialfield", _specialfield);
paras[6] = new SqlParameter("@diploma", _diploma);
paras[7] = new SqlParameter("@picturePath", _picture);
paras[8] = new SqlParameter("@remark", _remark);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//添加学生
public static int AddTeacher(string teacherName, string gender, DateTime birthday, string idCard, string university, string specialfield, string diploma, string picturePath, string remark)
{
string strSql = "insert into Teacher values(@teacherName,@gender,@birthday,@idCard,@university,@specialfield,@diploma,@picturePath,@remark)";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] paras = new SqlParameter[9];
paras[0] = new SqlParameter("@teacherName", teacherName);
paras[1] = new SqlParameter("@gender", gender);
paras[2] = new SqlParameter("@birthday", birthday);
paras[3] = new SqlParameter("@idCard", idCard);
paras[4] = new SqlParameter("@university", university);
paras[5] = new SqlParameter("@specialfield", specialfield);
paras[6] = new SqlParameter("@diploma", diploma);
paras[7] = new SqlParameter("@picturePath", picturePath);
paras[8] = new SqlParameter("@remark", remark);
return(sqlcomm.ExecuteNonQuery(strSql, paras));
}
//添加学生
public static int CreateStudent(int studentNo, string studentName, string gender, DateTime birthday, string className, string imagePath, string remark)
{
string strSql = "insert into Student values(@studentNo,@studentName,@gender,@birthday,@className,@picture,@remark);";
strSql += "update Class set [currentCount] = [currentCount]+1 where [ClassName] = @className";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] pa = new SqlParameter[7];
pa[0] = new SqlParameter("@studentNo", studentNo);
pa[1] = new SqlParameter("@studentName", studentName);
pa[2] = new SqlParameter("@gender", gender);
pa[3] = new SqlParameter("@birthday", birthday);
pa[4] = new SqlParameter("@className", className);
pa[5] = new SqlParameter("@picture", imagePath);
pa[6] = new SqlParameter("@remark", remark);
return(sqlcomm.ExecuteNonQuery(strSql, pa));
}
//修改学生资料（不修改学生所在班级）
public int Update()
{
string strSql = "update Student set [StudentName] = @studentName,[Gender] = @gender,[Birthday] = @birthday,[ClassName] = @className,[Picture] = @picture,[Remark] = @remark where [StudentNo] = @studentNo;";
strSql += "Update Class set [CurrentCount] = [CurrentCount] + 1 where [ClassName] = @className ";
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] pa = new SqlParameter[7];
pa[0] = new SqlParameter("@studentNo", _studentNo);
pa[1] = new SqlParameter("@studentName", _studentName);
pa[2] = new SqlParameter("@gender", _gender);
pa[3] = new SqlParameter("@birthday", _birthday);
pa[4] = new SqlParameter("@className", _className);
pa[5] = new SqlParameter("@picture", _picture);
pa[6] = new SqlParameter("@remark", _remark);
return(sqlcomm.ExecuteNonQuery(strSql, true, false, pa));
}
//修改学生资料（修改学生所在班级）
public int Update(string oldClassName)
{
StringBuilder str = new StringBuilder();
str.Append("update Student set [StudentName] = @studentName,[Gender] = @gender,[Birthday] = @birthday,[ClassName] = @className,[Picture] = @picture,[Remark] = @remark where [StudentNo] = @studentNo;");
str.Append("Update Class set [CurrentCount] = [CurrentCount] - 1 where ClassName = @oldClassName ;");
str.Append("Update Class set [CurrentCount] = [CurrentCount] + 1 where ClassName = @className ");
SqlCommon sqlcomm = new SqlCommon();
SqlParameter[] pa = new SqlParameter[8];
pa[0] = new SqlParameter("@studentNo", _studentNo);
pa[1] = new SqlParameter("@studentName", _studentName);
pa[2] = new SqlParameter("@gender", _gender);
pa[3] = new SqlParameter("@birthday", _birthday);
pa[4] = new SqlParameter("@className", _className);
pa[5] = new SqlParameter("@picture", _picture);
pa[6] = new SqlParameter("@remark", _remark);
pa[7] = new SqlParameter("@oldClassName", oldClassName);
return(sqlcomm.ExecuteNonQuery(str.ToString(), true, false, pa));
}