public List <Student> GetAllStudentByClassInfoId(int classInfo_id, int delFlag) { // SELECT[Student_GUID] //,[Id] //,[Class_id] //,[Speciality_id] //,[Name] //,[ID_Card] //,[Sex] //,[Nationality] //,[Address] //,[Phone] //,[Political_role] //,[Posecode] //,[Remark] //,[Photograph] //,[Submitter_id] //,[DelFlag] // FROM[dbo].[StudentInfo] string sql = "SELECT [Id],[Speciality_id],[Name],[ID_Card],[Phone],[Posecode] FROM[dbo].[StudentInfo] WHERE [DelFlag]=" + delFlag + " And [Class_Id]=" + classInfo_id; DataTable dt = SqlserverHelper.AdapterDataTable(sql); List <Student> list = new List <Student>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(RowToStudent(dr)); } } return(list); }
public object GetAllUsersByDelFlag(int delFlag) { //一张表的内连接 //SELECT UserInfo.Id 编号, RoleInfo.Name 角色, UserInfo.Name 名字, UserInfo.Remark 备注, U1.Name 提交人 FROM[dbo].[UserInfo] //inner join RoleInfo on UserInfo.Role_id = RoleInfo.Id //inner join UserInfo U1 on U1.Id = UserInfo.Submitter_id string sql = "SELECT UserInfo.Id 编号,UserInfo.Name 名字,RoleInfo.Name 角色,UserInfo.Remark 备注,U1.Name 提交人 FROM[dbo].[UserInfo] inner join RoleInfo on UserInfo.Role_id = RoleInfo.Id inner join UserInfo U1 on U1.Id = UserInfo.Submitter_id where UserInfo.DelFlag=" + delFlag; return(SqlserverHelper.AdapterDataTable(sql)); }
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 Speciality GetSpecialityBySpecialityId(int speciality_id) { string sql = "SELECT [Id],[Name],[Remark],[Submitter_id],[Submit_datetime] FROM[dbo].[SpecialityInfo] WHERE [DelFlag]=0 AND [Id]=" + speciality_id; DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { return(RowToSpeciality(dt.Rows[0])); } else { return(null); } }
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 ClassInfo GetClassInfoByClassInfoId(int class_id) { string sql = "SELECT [Id],[Name],[Speciality_id],[Room_name],[School_reform],[Headteacher],[Enrolment_time],[Submitter_id] FROM[dbo].[ClassInfo] where [DelFlag]=0 AND [Id]=" + class_id; DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { return(RowToClassInfo(dt.Rows[0])); } else { return(null); } }
public List <Speciality> GetAllSpecialityByDelFlagAndCollegeId(int delFlag, int college_id) { string sql = "SELECT [Id],[Name],[Remark],[Submitter_id],[Submit_datetime] FROM[dbo].[SpecialityInfo] where [College_id]=" + college_id + " and [DelFlag]=" + delFlag; List <Speciality> list = new List <Speciality>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(RowToSpeciality(dr)); } } return(list); }
public Dictionary <int, string> GetAllSpecialityIdAndNameByCollegeId(int college_id) { string sql = "select [Id],[Name] from SpecialityInfo where [DelFlag]=0 AND [College_id]=" + college_id; Dictionary <int, string> dictionary = new Dictionary <int, string>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dictionary.Add(Convert.ToInt32(dr[0]), dr[1].ToString()); } } return(dictionary); }
public List <College> GetAllCollegeByDelFlag(int delFlag) { string sql = "SELECT [Id],[Name],[Submitter_id],[Submit_datetime] FROM[dbo].[CollegeInfo] where [DelFlag]=" + delFlag; List <College> list = new List <College>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(RowToCollege(dr)); } } return(list); }
public Dictionary <int, string> GetAllClassInfoIdAndNameBySpecialityId(int speciality_id) { string sql = "SELECT [Id],[Name] FROM[dbo].[ClassInfo] where [DelFlag]=0 and [Speciality_id]=" + speciality_id; Dictionary <int, string> dictonary = new Dictionary <int, string>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dictonary.Add(Convert.ToInt32(dr[0]), dr[1].ToString()); } } return(dictonary); }
public List <Course> GetCoursesBySpecialityId(int speciality_id, int delFlag) { string sql = "SELECT [Id],[Speciality_id],[Name],[Room_name],[Semester],[Submitter_id] FROM [dbo].[CourseInfo] WHERE [DelFlag]=" + delFlag + " AND [Speciality_id]=" + speciality_id; List <Course> list = new List <Course>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(RowToCourse(dr)); } } return(list); }
public List <ClassInfo> GetClassInfosBySpecialityId(int id, int delFlag) { string sql = "SELECT [Id],[Name],[Speciality_id],[Room_name],[School_reform],[Headteacher],[Enrolment_time],[Submitter_id] FROM[dbo].[ClassInfo] where [DelFlag]=" + delFlag + " and [Speciality_id]=" + id; DataTable dt = SqlserverHelper.AdapterDataTable(sql); List <ClassInfo> list = new List <ClassInfo>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(RowToClassInfo(dr)); } } return(list); }
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 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 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 List <Role> GetRolesByRoleIdAndDelFlag(int role_id, int delFlag = 0) { string sql = "SELECT [Id] ,[Name],[System_manage],[Speciality_manage],[Class_manage],[Course_manage],[Score_manage],[Student_manage],[Remark],[Submitter_id],[DelFlag] FROM [dbo].[RoleInfo] where [DelFlag]=" + delFlag + " and Id like '%" + role_id + "%'"; using (SqlDataReader reader = SqlserverHelper.ExecuteReader(sql)) { List <Role> list = new List <Role>(); if (reader.HasRows) { while (reader.Read()) { list.Add(ReaderToRole(reader)); } } return(list); } }
public List <Speciality> GetAllSpecialityByDelFlag(int delFlag) { string sql = "SELECT [dbo].[SpecialityInfo].[Id],[dbo].[SpecialityInfo].[Name],[dbo].[SpecialityInfo].[Remark],[dbo].[SpecialityInfo].[Submitter_id],[dbo].[SpecialityInfo].[Submit_datetime], CollegeInfo.Name FROM[dbo].[SpecialityInfo] inner join CollegeInfo on CollegeInfo.Id=SpecialityInfo.College_id where [dbo].[SpecialityInfo].[DelFlag]=" + delFlag; List <Speciality> list = new List <Speciality>(); DataTable dt = SqlserverHelper.AdapterDataTable(sql); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { var model = RowToSpeciality(dr); model.Model_college.Name = dr[5].ToString(); list.Add(model); } } return(list); }
public List <Role> GetAllRoleInfoByDelFlag(int delflag) { //"select * from RoleInfo"; string sql = "SELECT [Id] ,[Name],[System_manage],[Speciality_manage],[Class_manage],[Course_manage],[Score_manage],[Student_manage],[Remark],[Submitter_id],[DelFlag] FROM [dbo].[RoleInfo] where [DelFlag]=" + delflag; using (SqlDataReader reader = SqlserverHelper.ExecuteReader(sql)) { List <Role> list = new List <Role>(); if (reader.HasRows) { while (reader.Read()) { list.Add(ReaderToRole(reader)); } } return(list); } }
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 User GetUserByUserId(int user_id) { string sql = "SELECT [Id], [Role_id],[Name],[Pwd],[Remark],[Submitter_id] FROM [dbo].[UserInfo] where [DelFlag]=0 and [Id]=" + user_id; DataTable dt = SqlserverHelper.AdapterDataTable(sql); User model = null; if (dt.Rows.Count > 0) { model = new User(); model.Id = Convert.ToInt32(dt.Rows[0][0]); model.Role_id = Convert.ToInt32(dt.Rows[0][1]); model.Name = dt.Rows[0][2].ToString(); model.Pwd = dt.Rows[0][3].ToString(); model.Remark = dt.Rows[0][4].ToString(); model.Submitter_id = Convert.ToInt32(dt.Rows[0][5]); } return(model); }
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 List <Speciality> GetSpecialityBySpecialityNameIdAndDelFlag(string specialityName, int delFlag) { List <Speciality> list = new List <Speciality>(); DataTable[] dts = new DataTable[2]; string splitName = ""; for (int i = specialityName.Length; i > 0; i--) //查找算法,拆分要查询的string, { splitName = specialityName.Substring(0, i); string sql = "SELECT [Id],[Name],[Remark],[Submitter_id],[Submit_datetime] FROM[dbo].[SpecialityInfo] where [DelFlag]=" + delFlag + " and Name like '%" + splitName + "%'"; dts[0] = SqlserverHelper.AdapterDataTable(sql); //颠倒 splitName = specialityName.Substring(i, specialityName.Length - i); dts[1] = SqlserverHelper.AdapterDataTable(sql); //将搜索到的数据加到List中 foreach (DataTable dt in dts) { if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { int id = Convert.ToInt32(dr[0]); IEnumerable <Speciality> Ien = from model in list //linq查询,检查重复项 where model.Id == id select model; if (Ien.Count() > 0) { continue; } list.Add(RowToSpeciality(dr)); } } } } return(list); }
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 object GetAllCollegeByDelFlag(int delFlag, bool isSpecial) { string sql = "select CollegeInfo.Id 编号 ,CollegeInfo.Name 名称,CollegeInfo.Submitter_id 提交人编号,UserInfo.Name 提交人姓名,CollegeInfo.Submit_datetime 提交时间 from CollegeInfo inner join UserInfo on CollegeInfo.Submitter_id=UserInfo.Id where CollegeInfo.delflag=" + delFlag; return(SqlserverHelper.AdapterDataTable(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)); }