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 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 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 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 <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 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 <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 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 <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 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 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 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 object GetUsersByUserNameUserDelFlag(string user_name, int delFlag) { 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 + " and UserInfo.Name like '%" + user_name + "%'"; return(SqlserverHelper.AdapterDataTable(sql)); }
public DataTable GetAllScoreByTeacherId(int teacher_id) { string sql = "select CourseInfo.Name as 课程名,CourseInfo.Id 课程编号, ScoreInfo.Student_id 学生编号,ScoreInfo.Score 分数 from ScoreInfo inner join CourseInfo on CourseInfo.Id = ScoreInfo.Course_id inner join StudentInfo on StudentInfo.Id = ScoreInfo.Student_id inner join UserInfo on UserInfo.Id = CourseInfo.Heather_id where UserInfo.Id = " + teacher_id; return(SqlserverHelper.AdapterDataTable(sql)); }