public int AddCollege(College model)
{
string sql = "insert into CollegeInfo(Name,Submitter_id) values(@Name,@Submitter_id)";
System.Data.SqlClient.SqlParameter[] ps =
{
new System.Data.SqlClient.SqlParameter("@Name", model.Name),
new System.Data.SqlClient.SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddSpeciality(Speciality model)
{
string sql = "insert into SpecialityInfo(Name,College_id,Remark,Submitter_id) values(@Name,@College_id,@Remark,@Submitter_id)";
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@College_id", model.College_id),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddCourse(Course model)
{
string sql = "INSERT INTO COURSEINFO([Name],[Room_name],[Semester],[Speciality_id],[Submitter_id]) VALUES(@Name,@Room_name,@Semester,@Speciality_id,@Submitter_id) ";
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@Room_name", model.Room_name),
new SqlParameter("@Semester", model.Semester),
new SqlParameter("@Speciality_id", model.Speciality_id),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int UpdateUserByUserId(User model)
{
string sql = "Update UserInfo set Name=@Name,Role_id=@Role_id,Pwd=@Pwd,Remark=@Remark,Submitter_id=@Submitter_id where Id=" + model.Id;
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@Role_id", model.Role_id),
new SqlParameter("@Pwd", model.Pwd),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddUser(User model)
{
string sql = "INSERT INTO [dbo].[UserInfo]([Role_id],[Name],[Pwd],[Remark],[DelFlag],[Submitter_id]) VALUES(@Role_id,@Name,@Pwd,@Remark,@DelFlag,@Submitter_id)";
SqlParameter[] ps =
{
new SqlParameter("Role_id", model.Role_id),
new SqlParameter("Name", model.Name),
new SqlParameter("Pwd", model.Pwd),
new SqlParameter("Remark", model.Remark),
new SqlParameter("DelFlag", model.DelFlag),
new SqlParameter("Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int UpdateClassInfoByDelFlag(ClassInfo model)
{
string sql = " UPDATE [dbo].[ClassInfo] SET[Speciality_id] = @Speciality_id,[Name] = @Name,[Room_name] =@Room_name,[School_reform] =@School_reform,[Headteacher] =@Headteacher,[Enrolment_time] =@Enrolment_time,[Submitter_id] = @Submitter_id WHERE [Id]=" + model.Id;
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@Room_name", model.Room_name),
new SqlParameter("@School_reform", model.School_reform),
new SqlParameter("@Specility_id", model.Specility_id),
new SqlParameter("@Enrolment_time", model.Enrolment_time),
new SqlParameter("@Submitter_id", model.Submitter_id),
new SqlParameter("@Headteacher", model.Headteacher)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddClassInfo(ClassInfo model)
{
string sql = "INSERT INTO[dbo].[ClassInfo]([Name],[Speciality_id],[Room_name],[School_reform],[Headteacher],[Enrolment_time],[Submitter_id]) VALUES(@Name,@Speciality_id,@Room_name,@School_reform,@Headteacher,@Enrolment_time,@Submitter_id)";
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@Speciality_id", model.Specility_id),
new SqlParameter("@Room_name", model.Room_name),
new SqlParameter("@School_reform", model.School_reform),
new SqlParameter("@Headteacher", model.Headteacher),
new SqlParameter("@Enrolment_time", model.Enrolment_time),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddRole(Role model)
{
string sql = "INSERT INTO [dbo].[RoleInfo]([Name],[System_manage],[Speciality_manage],[Class_manage],[Course_manage],[Score_manage],[Student_manage],[Remark],[Submitter_id]) VALUES(@Name,@System_manage,@Speciality_manage,@Class_manage,@Course_manage,@Score_manage,@Student_manage,@Remark,@Submitter_id)";
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@System_manage", model.System_manage),
new SqlParameter("@Speciality_manage", model.Speciality_manage),
new SqlParameter("@Class_manage", model.Class_manage),
new SqlParameter("@Course_manage", model.Course_manage),
new SqlParameter("@Score_manage", model.Score_manage),
new SqlParameter("@Student_manage", model.Student_manage),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int UpdateRoleByRoleId(Role model_role)
{
string sql = "UPDATE [dbo].[RoleInfo] SET[Name] =@Name,[System_manage] =@System_manage,[Speciality_manage] =@Speciality_manage,[Class_manage] = @Class_manage,[Course_manage] =@Course_manage,[Score_manage] =@Score_manage,[Student_manage] =@Student_manage,[Remark] =@Remark,[Submitter_id] = @Submitter_id where [DelFlag]=0 and [Id]=" + model_role.Id;
SqlParameter[] ps =
{
new SqlParameter("@Name", model_role.Name),
new SqlParameter("@System_manage", model_role.System_manage),
new SqlParameter("@Speciality_manage", model_role.Speciality_manage),
new SqlParameter("@Class_manage", model_role.Class_manage),
new SqlParameter("@Course_manage", model_role.Course_manage),
new SqlParameter("@Score_manage", model_role.Score_manage),
new SqlParameter("@Student_manage", model_role.Student_manage),
new SqlParameter("@Remark", model_role.Remark),
new SqlParameter("@Submitter_id", model_role.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int UpdateSpecialityBySpecialityId(Speciality model)
{
//[Name] = <Name, varchar(50),>
//[Remark] = <Remark, text,>
//[Submitter_id] = <Submitter_id, int,>
//[Submit_datetime] = <Submit_datetime, datetime,> 存的是新建时间，只在添加时赋值一次
//[College_id] = <College_id, int,>
//[DelFlag] = <DelFlag, numeric(1,0),>
string sql = "Update SpecialityInfo set [Name]=@Name,[Remark]=@Remark,[Submitter_id]=@Submitter_id,[College_id]=@College_id,[Submit_datetime]=getdate() where [Id]=" + model.Id + " and [DelFlag]=0";
SqlParameter[] ps =
{
new SqlParameter("@Name", model.Name),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Submitter_id", model.Submitter_id),
new SqlParameter("@College_id", model.College_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int AddStudent(Student model)
{
string sql = "INSERT INTO [dbo].[StudentInfo]([Class_id],[Speciality_id],[Name],[ID_Card],[Sex],[Nationality],[Address],[Phone],[Political_role],[Posecode],[Remark],[Photograph],[Submitter_id]) VALUES(@Class_id,@Speciality_id,@Name,@ID_Card,@Sex,@Nationality,@Address,@Phone,@Political_role,@Posecode,@Remark,@Photograph,@Submitter_id)";
SqlParameter[] ps =
{
new SqlParameter("@Class_id", model.Class_id),
new SqlParameter("@Speciality_id", model.Speciality_id),
new SqlParameter("@Name", model.Name),
new SqlParameter("@ID_Card", model.ID_Card),
new SqlParameter("@Sex", model.Sex),
new SqlParameter("@Nationality", model.Nationality),
new SqlParameter("@Address", model.Address),
new SqlParameter("@Phone", model.Phone),
new SqlParameter("@Political_role", model.Political_role),
new SqlParameter("@Posecode", model.Posecode),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Photograph", model.Photograph),
new SqlParameter("@Submitter_id", model.Submitter_id)
};
return(SqlserverHelper.ExecuteNonQuery(sql, ps));
}
public int UpdateScore(int score_id, double score_value, int submitter_id)
{
string sql = "UPDATE ScoreInfo set Score=" + score_value + ",Submitter_id=" + submitter_id + " where Id=" + score_id;
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteRoleByRoleId(int role_id, int submitter_id, int delFlag = 1)
{
string sql = "Update RoleInfo set [DelFlag]=" + delFlag + " , Submitter_id=" + submitter_id + " where Id=" + role_id;
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteAllDeletedRoles()
{
string sql = "update RoleInfo set [DelFlag]=2 where [DelFlag]=1";
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int SoftDeleteById(int course_id, int submitter_id, int delFlag)
{
string sql = "Update CourseInfo set [DelFlag]=" + delFlag + " ,[Submitter_id]=" + submitter_id + " where [Id]=" + course_id;
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteCollegeByCollegeId(int college_id, int submitter_id, int delFlag = 0)
{
string sql = "Update CollegeInfo set [DelFlag]=" + delFlag + " ,[Submitter_id]=" + submitter_id + " where Id=" + college_id;
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteAllDeletedUsers(int submitter_id)
{
string sql = "update UserInfo set [DelFlag]=2 where [DelFlag]=1";
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteSpecialityBySpecialityId(int specialityId, int submitterId, int delFlag = 1)
{
string sql = "Update SpecialityInfo set [DelFlag]=" + delFlag + ",[Submitter_id]=" + submitterId + ",[Submit_datetime]=getdate() where [Id]=" + specialityId;
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int DeleteAllDeletedSpeciality(int submitter_id)
{
string sql = "Update SpecialityInfo set [DelFlag]=2 ,[Submitter_id]=" + submitter_id + ",[Submit_datetime]=getdate()";
return(SqlserverHelper.ExecuteNonQuery(sql));
}
public int SoftDeleteByStudentId(int student_id, int id)
{
string sql = "UPDATE STUDENTINFO SET [DelFlag]=1,[Submitter_id]=" + id + " where [Id]=" + student_id;
return(SqlserverHelper.ExecuteNonQuery(sql));
}