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));
}
