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