}//
/// <summary>
/// 根据条件获取教师信息列表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数</param>
/// <returns>教师信息列表</returns>
public static List <TeacherInfo> GetAllTeacherInfo(string sql, params SqlParameter[] param)
{
DataTable dt = new DataTable();
List <TeacherInfo> list = new List <TeacherInfo>();
dt = SqlHelper.ExecuteTable(sql, param);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
TeacherInfo teacher = new TeacherInfo();
teacher = RowToEntity(dr);
list.Add(teacher);
}
}
return(list);
}
/// <summary>
/// 【DataRow转TeacherInfo】行转实体
/// </summary>
/// <param name="dr">一行记录</param>
/// <returns>一个实体</returns>
public static TeacherInfo RowToEntity(DataRow dr)
{
TeacherInfo teacher = new TeacherInfo();
#region 填充教师信息
//教师编号
if (dr["TeacherNum"] is DBNull)
{
teacher.TeacherNum = "";
}
else
{
teacher.TeacherNum = dr["TeacherNum"].ToString();
}
//姓名
if (dr["TeacherName"] is DBNull)
{
teacher.TeacherName = "";
}
else
{
teacher.TeacherName = dr["TeacherName"].ToString();
}
//性别
if (dr["Sex"] is DBNull)
{
teacher.Sex = "";
}
else
{
teacher.Sex = dr["Sex"].ToString();
}
//身份证号码
if (dr["IdentityCard"] is DBNull)
{
teacher.IdentityCard = "";
}
else
{
teacher.IdentityCard = dr["IdentityCard"].ToString();
}
//学历
if (dr["Education"] is DBNull)
{
teacher.Education = "";
}
else
{
teacher.Education = dr["Education"].ToString();
}
//职业
if (dr["Position"] is DBNull)
{
teacher.Position = "";
}
else
{
teacher.Position = dr["Position"].ToString();
}
//专业
if (dr["Major"] is DBNull)
{
teacher.Major = "";
}
else
{
teacher.Major = dr["Major"].ToString();
}
//学院编码
if (dr["InstituteNum"] is DBNull)
{
teacher.InstituteNum = "";
}
else
{
teacher.InstituteNum = dr["InstituteNum"].ToString();
}
//学院名字
if (dr["InstituteName"] is DBNull)
{
teacher.InstituteName = "";
}
else
{
teacher.InstituteName = dr["InstituteName"].ToString();
}
//联系电话
if (dr["Phone"] is DBNull)
{
teacher.Phone = "";
}
else
{
teacher.Phone = dr["Phone"].ToString();
}
//邮箱
if (dr["Email"] is DBNull)
{
teacher.Email = "";
}
else
{
teacher.Email = dr["Email"].ToString();
}
//主讲课程一
if (dr["CourseName1"] is DBNull)
{
teacher.CourseName1 = "";
}
else
{
teacher.CourseName1 = dr["CourseName1"].ToString();
}
//主讲课程二
if (dr["CourseName2"] is DBNull)
{
teacher.CourseName2 = "";
}
else
{
teacher.CourseName2 = dr["CourseName2"].ToString();
}
//主讲课程三
if (dr["CourseName3"] is DBNull)
{
teacher.CourseName3 = "";
}
else
{
teacher.CourseName3 = dr["CourseName3"].ToString();
}
//状态
if (dr["State"] is DBNull)
{
teacher.State = "正常";
}
else
{
teacher.State = dr["State"].ToString();
}
//注册时间
if (dr["SubDate"] is DBNull)
{
teacher.SubDate = DateTime.Now;
}
else
{
teacher.SubDate = Convert.ToDateTime(dr["SubDate"]);
}
//删除标识
if (dr["DelFlag"] is DBNull)
{
teacher.DelFlag = 0;
}
else
{
teacher.DelFlag = Convert.ToInt32(dr["DelFlag"]);
}
//备注
if (dr["Remark"] is DBNull)
{
teacher.Remark = "";
}
else
{
teacher.Remark = dr["Remark"].ToString();
}
#endregion
return(teacher);
}
} //角色
/// <summary>
/// 【管理员】获取所有注册待验证的用户
/// </summary>
/// <param name="level">管理员级别</param>
/// <returns></returns>
public static List <AllUserInfo> GetAllRegisterUserInfo(string level)
{
List <AllUserInfo> allList = new List <AllUserInfo>();
int k = 1;
//①加载学生信息列表
//①--------1读取信息
string sql1 = "Select s.*,a.InstituteName from StuInfo s, InstituteInfo a where s.InstituteNum = a.InstituteNum and s.State = @State and s.DelFlag = 0";
SqlParameter[] p1 = { new SqlParameter("@State", "待检验") };
List <StuInfo> stuList = StuInfo.GetAllStuInfo(sql1, p1);
//①--------2添加
foreach (StuInfo stu in stuList)
{
AllUserInfo all = new AllUserInfo();
all.ListID = k;
all.UserNum = stu.StuNum;
all.UserName = stu.StuName;
all.Sex = stu.Sex;
all.IdentityCard = stu.IdentityCard;
all.Role = "学生";
allList.Add(all);
k++;
}
//②加载教师信息列表
//②-----------1读取信息
string sql2 = "Select t.*, a.InstituteName from TeacherInfo t, InstituteInfo a where t.State = @State and t.DelFlag = 0 and t.InstituteNum = a.InstituteNum";
SqlParameter p2 = new SqlParameter("@State", "待检验");
List <TeacherInfo> teaList = TeacherInfo.GetAllTeacherInfo(sql2, p2);
//②------------2添加
foreach (TeacherInfo teacher in teaList)
{
AllUserInfo all = new AllUserInfo();
all.ListID = k;
all.UserNum = teacher.TeacherNum;
all.UserName = teacher.TeacherName;
all.Sex = teacher.Sex;
all.IdentityCard = teacher.IdentityCard;
all.Role = "教师";
allList.Add(all);
k++;
}
//③加载管理员信息列表
if (level == "0")
{
//③-------------1读取信息
string sql3 = "Select * from AdminInfo where State = @State and DelFlag = 0";
SqlParameter p3 = new SqlParameter("@State", "待检验");
List <AdminInfo> adminList = new List <AdminInfo>();
adminList = AdminInfo.GetAllAdminInfo(sql3, p3);
//③-------------2添加
foreach (AdminInfo a in adminList)
{
AllUserInfo all = new AllUserInfo();
all.ListID = k;
all.UserNum = a.AdminNum;
all.UserName = a.AdminName;
all.Sex = a.Sex;
all.IdentityCard = "";
all.Role = "管理员";
allList.Add(all);
k++;
}
}
return(allList);
}
/// <summary>
/// 获取单个教师信息
/// </summary>
/// <param name="username">教师编号</param>
/// <returns></returns>
public static TeacherInfo GetTeacherInfo(string username)
{
TeacherInfo teacher = new TeacherInfo();
string sql = "Select * from TeacherInfo a,InstituteInfo b where a.InstituteNum = b.InstituteNum and a.TeacherNum = @TeacherNum and a.DelFlag = 0 ";
//string sql = "Select a.*, b.InstituteName,c.CourseName from TeacherInfo a,InstituteInfo b , CourseInfo c where a.InstituteNum = b.InstituteNum and a.TeacherNum =@TeacherNum and a.DelFlag = 0 and c.CourseNum in (a.CourseNum1,a.CourseNum2,a.CourseNum3)";
try
{
using (SqlConnection con = new SqlConnection(connStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@TeacherNum", username));
//cmd.Parameters.Add(new SqlParameter("@StuPwd", password));
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
string state = reader.GetString(reader.GetOrdinal("State"));
if (state == "正常")
{
teacher.IsLogin = "******";
#region 填充教师信息
//教师编号
teacher.TeacherNum = username;
//姓名
if (reader["TeacherName"] is DBNull)
{
teacher.TeacherName = "";
}
else
{
teacher.TeacherName = reader.GetString(reader.GetOrdinal("TeacherName"));
}
//性别
if (reader["Sex"] is DBNull)
{
teacher.Sex = "";
}
else
{
teacher.Sex = reader.GetString(reader.GetOrdinal("Sex"));
}
//身份证号码
if (reader["IdentityCard"] is DBNull)
{
teacher.IdentityCard = "";
}
else
{
teacher.IdentityCard = reader.GetString(reader.GetOrdinal("IdentityCard"));
}
//学历
if (reader["Education"] is DBNull)
{
teacher.Education = "";
}
else
{
teacher.Education = reader.GetString(reader.GetOrdinal("Education"));
}
//职业
if (reader["Position"] is DBNull)
{
teacher.Position = "";
}
else
{
teacher.Position = reader.GetString(reader.GetOrdinal("Position"));
}
//专业
if (reader["Major"] is DBNull)
{
teacher.Major = "";
}
else
{
teacher.Major = reader.GetString(reader.GetOrdinal("Major"));
}
//学院编码
if (reader["InstituteNum"] is DBNull)
{
teacher.InstituteNum = "";
}
else
{
teacher.InstituteNum = reader.GetString(reader.GetOrdinal("InstituteNum"));
}
//学院名字
if (reader["InstituteName"] is DBNull)
{
teacher.InstituteName = "";
}
else
{
teacher.InstituteName = reader.GetString(reader.GetOrdinal("InstituteName"));
}
//联系电话
if (reader["Phone"] is DBNull)
{
teacher.Phone = "";
}
else
{
teacher.Phone = reader.GetString(reader.GetOrdinal("Phone"));
}
//邮箱
if (reader["Email"] is DBNull)
{
teacher.Email = "";
}
else
{
teacher.Email = reader.GetString(reader.GetOrdinal("Email"));
}
//主讲课程一
if (reader["CourseName1"] is DBNull)
{
teacher.CourseName1 = "";
}
else
{
teacher.CourseName1 = reader.GetString(reader.GetOrdinal("CourseName1"));
}
//主讲课程二
if (reader["CourseName2"] is DBNull)
{
teacher.CourseName2 = "";
}
else
{
teacher.CourseName2 = reader.GetString(reader.GetOrdinal("CourseName2"));
}
//主讲课程三
if (reader["CourseName3"] is DBNull)
{
teacher.CourseName3 = "";
}
else
{
teacher.CourseName3 = reader.GetString(reader.GetOrdinal("CourseName3"));
}
//状态
if (reader["State"] is DBNull)
{
teacher.State = "正常";
}
else
{
teacher.State = reader.GetString(reader.GetOrdinal("State"));
}
//注册时间
if (reader["SubDate"] is DBNull)
{
teacher.SubDate = DateTime.Now;
}
else
{
teacher.SubDate = reader.GetDateTime(reader.GetOrdinal("SubDate"));
}
//删除标识
if (reader["DelFlag"] is DBNull)
{
teacher.DelFlag = 0;
}
else
{
teacher.DelFlag = reader.GetInt32(reader.GetOrdinal("DelFlag"));
}
//备注
if (reader["Remark"] is DBNull)
{
teacher.Remark = "";
}
else
{
teacher.Remark = reader.GetString(reader.GetOrdinal("Remark"));
}
#endregion
return(teacher);
}
else
{
teacher.IsLogin = "******";
return(teacher);
}
}
else//用户不存在
{
teacher.IsLogin = "******";
return(teacher);
}
}//
}
}
}
catch (Exception e)
{
throw e;
}
}//
/// <summary>
/// 【通过】注册用户
/// </summary>
/// <param name="userNum">用户账号</param>
/// <param name="role">角色</param>
/// <returns>成功与否的字符串</returns>
public static string PassRegisterUser(string userNum, string role)
{
if (!string.IsNullOrEmpty(role) && !string.IsNullOrEmpty(userNum))
{
if (role == "学生")
{
#region 执行删除学生
string sql = "Update StuInfo set State = @State where StuNum = @StuNum";
SqlParameter[] param =
{
new SqlParameter("@StuNum", userNum),
new SqlParameter("@State", "正常")
};
if (StuInfo.DeleteStuInfo(sql, param) > 0)
{
return("Success");
}
else
{
return("Error");
}
#endregion
}
else if (role == "教师")
{
#region 执行删除教师
string sql = "Update TeacherInfo set State = @State where TeacherNum = @TeacherNum";
SqlParameter[] param =
{
new SqlParameter("@TeacherNum", userNum),
new SqlParameter("@State", "正常")
};
if (TeacherInfo.DeleteTeacherInfo(sql, param) > 0)
{
return("Success");
}
else
{
return("Error");
}
#endregion
}
else if (role == "管理员")
{
#region 执行删除管理员
string sql = "Update AdminInfo set State = @State where AdminNum = @AdminNum";
SqlParameter[] param =
{
new SqlParameter("@AdminNum", userNum),
new SqlParameter("@State", "正常")
};
if (AdminInfo.DeleteAdminInfo(sql, param) > 0)
{
return("Success");
}
else
{
return("Error");
}
#endregion
}
else
{
return("Error");//【Role】的值不明确
}
}
else
{
return("Error");//【Role】或者【userNum】的值为空或者是null
}
}
