private static IList <User> GetUsersBySql(string sql, params SqlParameter[] values)
{
List <User> list = new List <User>();
try
{
DataTable table = DBHelper.GetDataSet(sql, values);
foreach (DataRow row in table.Rows)
{
User user = new User();
user.Id = (int)row["Id"];
user.LoginId = (string)row["LoginId"];
user.LoginPwd = (string)row["LoginPwd"];
user.Name = (string)row["Name"];
user.Address = (string)row["Address"];
user.Phone = (string)row["Phone"];
user.Mail = (string)row["Mail"];
user.UserState = UserStateService.GetUserStateById((int)row["UserStateId"]); //FK
user.UserRole = UserRoleService.GetUserRoleById((int)row["UserRoleId"]); //FK
list.Add(user);
}
return(list);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
}
public static User GetUserByMail(string mail)
{
string sql = "SELECT * FROM Users WHERE Mail = @Mail";
int userStateId;
int userRoleId;
try
{
SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Mail", mail));
if (reader.Read())
{
User user = new User();
user.Id = (int)reader["Id"];
user.LoginId = (string)reader["LoginId"];
user.LoginPwd = (string)reader["LoginPwd"];
user.Name = (string)reader["Name"];
user.Address = (string)reader["Address"];
user.Phone = (string)reader["Phone"];
user.Mail = (string)reader["Mail"];
userStateId = (int)reader["UserStateId"]; //FK
userRoleId = (int)reader["UserRoleId"]; //FK
reader.Close();
user.UserState = UserStateService.GetUserStateById(userStateId);
user.UserRole = UserRoleService.GetUserRoleById(userRoleId);
return(user);
}
else
{
reader.Close();
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
}
/// <summary>
/// 通过登录名查找管理员
/// </summary>
/// <param name="loginId"></param>
/// <returns></returns>
public static User GetAdminUserByLoginId(string loginId)
{
string sql = "SELECT * FROM users WHERE LoginId = @LoginId and UserRoleId=@RoleId";
int roleId;
int userStateId;
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LoginId", loginId),
new SqlParameter("@RoleId", 3)
};
using (SqlDataReader reader = DBHelper.GetReader(sql, para))
{
if (reader.Read())
{
User user = new User();
user.Id = (int)reader["Id"];
user.LoginId = (string)reader["LoginId"];
user.LoginPwd = (string)reader["LoginPwd"];
user.Name = (string)reader["Name"];
user.Address = (string)reader["Address"];
user.Phone = (string)reader["Phone"];
user.Mail = (string)reader["Mail"];
roleId = (int)reader["UserRoleId"];
userStateId = (int)reader["UserStateId"];
user.UserRole = UserRoleService.GetUserRoleById(roleId);
user.UserState = UserStateService.GetUserStateById(userStateId);
reader.Close();
return(user);
}
else
{
reader.Close();
return(null);
}
}
}
