//public string CheckLogin(string username, string passwrord)
//{
// try
// {
// string sql = "Select * from StuInfo where StuNum = @StuNum and StuPwd = @StuPwd and DelFlag = 0";
// using (SqlConnection con = new SqlConnection(connStr))
// {
// con.Open();
// using (SqlCommand cmd = con.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.Parameters.Add(new SqlParameter("@StuNum", username));
// cmd.Parameters.Add(new SqlParameter("@StuPwd", passwrord));
// using (SqlDataReader reader = cmd.ExecuteReader())
// {
// if (reader.Read())
// {
// string state = reader.GetString(reader.GetOrdinal("State"));
// if (state != "禁用")
// {
// return "Success";
// }
// else
// {
// return "Error_State";
// }
// }
// else
// {
// return "Error_UserNotExist";
// }
// }
// }
// }
// }
// catch
// {
// return "Error_Check";
// }
//}//
/// <summary>
/// 根据条件获取学生的列表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数</param>
/// <returns>学生信息表数组</returns>
public static List <StuInfo> GetAllStuInfo(string sql, SqlParameter[] param)
{
DataTable dt = SqlHelper.ExecuteTable(sql, param);
List <StuInfo> list = new List <StuInfo>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
StuInfo s = RowToEntity(dr);
list.Add(s);
}
}
return(list);
}
}//
/// <summary>
/// 【教师】获取学生的信息
/// </summary>
/// <returns></returns>
public static List <StuInfo> GetStuInfoByCourseNumAndTeacherNum(string courseNum, string teacherNum)
{
string sql = "select s.* from SelectClassInfo sc, StuInfo s, CourseInfo c where sc.StuNum = s.StuNum and sc.DelFlag = 0 and sc.CourseNum = c.CourseNum and c.IsEnd = 1 and sc.CourseNum = @CourseNum and sc.TeacherNum = @TeacherNum";
SqlParameter[] param =
{
new SqlParameter("@CourseNum", courseNum),
new SqlParameter("@TeacherNum", teacherNum)
}; //参数列表
DataTable dt = SqlHelper.ExecuteTable(sql, param); //执行数据库操作，读取学生信息
List <StuInfo> list = new List <StuInfo>(); //用来放学生信息的一个列表（容器）
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)//遍历每行数据
{
StuInfo s = new StuInfo();
#region 填充学生个人信息
//学号
if (dr["StuNum"] is DBNull)
{
s.StuNum = "";
}
else
{
s.StuNum = dr["StuNum"].ToString();
}
//学生名字
if (dr["StuName"] is DBNull)
{
s.StuName = "";
}
else
{
s.StuName = dr["StuName"].ToString();
}
//性别
if (dr["Sex"] is DBNull)
{
s.Sex = "";
}
else
{
s.Sex = dr["Sex"].ToString();
}
//身份证号码
if (dr["IdentityCard"] is DBNull)
{
s.IdentityCard = "";
}
else
{
s.IdentityCard = dr["IdentityCard"].ToString();
}
//专业
if (dr["Major"] is DBNull)
{
s.Major = "";
}
else
{
s.Major = dr["Major"].ToString();
}
//班级
if (dr["ClassName"] is DBNull)
{
s.ClassName = "";
}
else
{
s.ClassName = dr["ClassName"].ToString();
}
//学院编号
if (dr["InstituteNum"] is DBNull)
{
s.InstituteNum = "";
}
else
{
s.InstituteNum = dr["InstituteNum"].ToString();
}
//入学日期
if (dr["EnrollmentDate"] is DBNull)
{
s.EnrollmentDate = DateTime.Now;
}
else
{
s.EnrollmentDate = Convert.ToDateTime(dr["EnrollmentDate"]);
}
//民族
if (dr["Nation"] is DBNull)
{
s.Nation = "";
}
else
{
s.Nation = dr["Nation"].ToString();
}
//籍贯
if (dr["Native"] is DBNull)
{
s.Native = "";
}
else
{
s.Native = dr["Native"].ToString();
}
//联系电话
if (dr["Phone"] is DBNull)
{
s.Phone = "";
}
else
{
s.Phone = dr["Phone"].ToString();
}
//邮箱
if (dr["Email"] is DBNull)
{
s.Email = "";
}
else
{
s.Email = dr["Email"].ToString();
}
//状态
if (dr["State"] is DBNull)
{
s.State = "";
}
else
{
s.State = dr["State"].ToString();
}
////提交时间
//if (dr["SubDate"] is DBNull)
//{
// s.SubDate = DateTime.Now;
//}
//else
//{
// s.SubDate = Convert.ToDateTime(dr["SubDate"]);
//}
//备注
if (dr["Remark"] is DBNull)
{
s.Remark = "";
}
else
{
s.Remark = dr["Remark"].ToString();
}
#endregion
list.Add(s);
}
}
return(list);
}
}//
/// <summary>
///【 行转实体】
/// </summary>
/// <param name="dr">行记录</param>
/// <returns></returns>
public static StuInfo RowToEntity(DataRow dr)
{
StuInfo s = new StuInfo();
#region 数据填充
//学号
if (dr["StuNum"] is DBNull)
{
s.StuNum = "";
}
else
{
s.StuNum = dr["StuNum"].ToString();
}
//姓名
if (dr["StuName"] is DBNull)
{
s.StuName = "";
}
else
{
s.StuName = dr["StuName"].ToString();
}
//性别
if (dr["Sex"] is DBNull)
{
s.Sex = "";
}
else
{
s.Sex = dr["Sex"].ToString();
}
//身份证号码
if (dr["IdentityCard"] is DBNull)
{
s.IdentityCard = "";
}
else
{
s.IdentityCard = dr["IdentityCard"].ToString();
}
//专业
if (dr["Major"] is DBNull)
{
s.Major = "";
}
else
{
s.Major = dr["Major"].ToString();
}
//班级名字
if (dr["ClassName"] is DBNull)
{
s.ClassName = "";
}
else
{
s.ClassName = dr["ClassName"].ToString();
}
//学院编号
if (dr["InstituteNum"] is DBNull)
{
s.InstituteNum = "";
}
else
{
s.InstituteNum = dr["InstituteNum"].ToString();
}
//学院名字
if (dr["InstituteName"] is DBNull)
{
s.InstituteName = "";
}
else
{
s.InstituteName = dr["InstituteName"].ToString();
}
//入学时间
if (dr["EnrollmentDate"] is DBNull)
{
s.EnrollmentDate = DateTime.Now;
}
else
{
s.EnrollmentDate = Convert.ToDateTime(dr["EnrollmentDate"]);
}
//民族
if (dr["Nation"] is DBNull)
{
s.Nation = "";
}
else
{
s.Nation = dr["Nation"].ToString();
}
//籍贯
if (dr["Native"] is DBNull)
{
s.Native = "";
}
else
{
s.Native = dr["Native"].ToString();
}
//联系电话
if (dr["Phone"] is DBNull)
{
s.Phone = "";
}
else
{
s.Phone = dr["Phone"].ToString();
}
//邮箱
if (dr["Email"] is DBNull)
{
s.Email = "";
}
else
{
s.Email = dr["Email"].ToString();
}
//状态
if (dr["State"] is DBNull)
{
s.State = "";
}
else
{
s.State = dr["State"].ToString();
}
//备注
if (dr["Remark"] is DBNull)
{
s.Remark = "";
}
else
{
s.Remark = dr["Remark"].ToString();
}
#endregion
return(s);
}
/// <summary>
/// 登录验证
/// </summary>
/// <param name="username">用户名</param>
/// <param name="password">密码</param>
/// <returns>响应信息</returns>
public static StuInfo GetStuInfo(string username)
{
StuInfo stu = new StuInfo();
string sql = "Select * from StuInfo a,InstituteInfo b where a.InstituteNum = b.InstituteNum and StuNum = @StuNum and a.DelFlag = 0 ";
try
{
using (SqlConnection con = new SqlConnection(connStr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@StuNum", 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 == "正常")
{
stu.IsLogin = "******";
#region 填充学生信息
stu.StuNum = username;
//姓名
if (reader["StuName"] is DBNull)
{
stu.StuName = "";
}
else
{
stu.StuName = reader.GetString(reader.GetOrdinal("StuName"));
}
//性别
if (reader["Sex"] is DBNull)
{
stu.Sex = "";
}
else
{
stu.Sex = reader.GetString(reader.GetOrdinal("Sex"));
}
//身份证号码
if (reader["IdentityCard"] is DBNull)
{
stu.IdentityCard = "";
}
else
{
stu.IdentityCard = reader.GetString(reader.GetOrdinal("IdentityCard"));
}
//专业
if (reader["Major"] is DBNull)
{
stu.Major = "";
}
else
{
stu.Major = reader.GetString(reader.GetOrdinal("Major"));
}
//班级名称
if (reader["ClassName"] is DBNull)
{
stu.ClassName = "";
}
else
{
stu.ClassName = reader.GetString(reader.GetOrdinal("ClassName"));
}
//学院编码
if (reader["InstituteNum"] is DBNull)
{
stu.InstituteNum = "";
}
else
{
stu.InstituteNum = reader.GetString(reader.GetOrdinal("InstituteNum"));
}
//学院名字
if (reader["InstituteName"] is DBNull)
{
stu.InstituteName = "";
}
else
{
stu.InstituteName = reader.GetString(reader.GetOrdinal("InstituteName"));
}
//入学时间
if (reader["EnrollmentDate"] is DBNull)
{
stu.EnrollmentDate = DateTime.Now;
}
else
{
stu.EnrollmentDate = reader.GetDateTime(reader.GetOrdinal("EnrollmentDate"));
}
//民族
if (reader["Nation"] is DBNull)
{
stu.Nation = "";
}
else
{
stu.Nation = reader.GetString(reader.GetOrdinal("Nation"));
}
//籍贯
if (reader["Native"] is DBNull)
{
stu.Native = "";
}
else
{
stu.Native = reader.GetString(reader.GetOrdinal("Native"));
}
//联系电话
if (reader["Phone"] is DBNull)
{
stu.Phone = "";
}
else
{
stu.Phone = reader.GetString(reader.GetOrdinal("Phone"));
}
//邮箱
if (reader["Email"] is DBNull)
{
stu.Email = "";
}
else
{
stu.Email = reader.GetString(reader.GetOrdinal("Email"));
}
//状态
if (reader["State"] is DBNull)
{
stu.State = "正常";
}
else
{
stu.State = reader.GetString(reader.GetOrdinal("State"));
}
//提交时间
if (reader["SubDate"] is DBNull)
{
stu.SubDate = DateTime.Now;
}
else
{
stu.SubDate = reader.GetDateTime(reader.GetOrdinal("SubDate"));
}
//删除标识
if (reader["DelFlag"] is DBNull)
{
stu.DelFlag = 0;
}
else
{
stu.DelFlag = reader.GetInt32(reader.GetOrdinal("DelFlag"));
}
//备注
if (reader["Remark"] is DBNull)
{
stu.Remark = "";
}
else
{
stu.Remark = reader.GetString(reader.GetOrdinal("Remark"));
}
#endregion
return(stu);
}
else
{
stu.IsLogin = "******";
return(stu);
}
}
else//用户不存在
{
stu.IsLogin = "******";
return(stu);
}
}
}
}
}
catch (Exception e)
{
throw e;
}
}//
} //角色
/// <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="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
}
}