public List <UsersDAL> GetUsersRelatedToRoleID(int RoleID, int skip, int take)
{
List <UsersDAL> ProposedReturnValue = new List <UsersDAL>();
try
{
EnsureConnected();
using (SqlCommand command = new SqlCommand("GetUsersRelatedToRoleID", _connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@RoleID", RoleID);
command.Parameters.AddWithValue("@Skip", skip);
command.Parameters.AddWithValue("@Take", take);
using (SqlDataReader reader = command.ExecuteReader())
{
UserMapper m = new UserMapper(reader);
while (reader.Read())
{
UsersDAL r = m.UserFromReader(reader);
ProposedReturnValue.Add(r);
}
}
}
}
catch (Exception ex) when(Log(ex))
{
}
return(ProposedReturnValue);
}
public UsersDAL FindUser(int UserID)
{
UsersDAL ProposedReturnValue = null;
try
{
EnsureConnected();
//The _connection is required for the connection to the database
using (SqlCommand command
= new SqlCommand("FindUser", _connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@UserID", UserID);
using (SqlDataReader reader = command.ExecuteReader())
{
UserMapper m = new UserMapper(reader);
int count = 0;
while (reader.Read())
{
ProposedReturnValue = m.UserFromReader(reader);
count++;
}
if (count > 1)
{
throw new
Exception($"Found more than 1 User with key {UserID}");
}
}
}
}
catch (Exception ex) when(Log(ex))
{
}
return(ProposedReturnValue);
}
public UsersDAL UserFromReader(System.Data.SqlClient.SqlDataReader reader)
{
UsersDAL ProposedReturnValue = new UsersDAL();
ProposedReturnValue.UserID = reader.GetInt32(OffsetToUserID);
// reader["UserID"] is very slow and makes a lot of garbage
// reader[0] makes a lot of garbage
// reader.GetInt32(0) is fast, but hard codes the offset to 0
// reader.GetInt32(OffsetToUserID) is best and allows verification
ProposedReturnValue.Email = reader.GetString(OffsetToEmail);
ProposedReturnValue.UserName = reader.GetString(OffsetToUserName);
//The GetStringOrDefault fuction is a Helper from the Parent Mapper because the Hash and Salt are Nullable at the DataBase Level.
ProposedReturnValue.Hash = GetStringOrDefault(reader, OffsetToHash);
ProposedReturnValue.Salt = GetStringOrDefault(reader, OffsetToSalt);
ProposedReturnValue.RoleID = reader.GetInt32(OffsetToRoleID);
ProposedReturnValue.RoleName = reader.GetString(OffsetToRoleName);
return(ProposedReturnValue);
}
public UsersDAL FindUserByEmail(string Email)
{
UsersDAL ProposedReturnValue = null;
try
{
//talk about the command statement, the stored procedure
EnsureConnected();
using (SqlCommand command
= new SqlCommand("FindUserByEmail", _connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Email", Email);
using (SqlDataReader reader = command.ExecuteReader())
{
//
UserMapper m = new UserMapper(reader);
int count = 0;
while (reader.Read())
{
ProposedReturnValue = m.UserFromReader(reader);
count++;
}
if (count > 1)
{
throw new
Exception($"Found more than 1 Email with key {Email}");
}
}
}
}
catch (Exception ex) when(Log(ex))
{
}
return(ProposedReturnValue);
}