public int UpdateUser(PokedexUser oldUser, PokedexUser newUser)
{
int rows = 0;
var conn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_update_user", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", oldUser.PokedexUserID);
cmd.Parameters.AddWithValue("@NewUserName", newUser.PokedexUserName);
cmd.Parameters.AddWithValue("@NewEmail", newUser.Email);
cmd.Parameters.AddWithValue("@OldUserName", oldUser.PokedexUserName);
cmd.Parameters.AddWithValue("@OldEmail", oldUser.Email);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rows);
}
public List <string> SelectRollsByUserID(int UserID)
{
List <string> roles = new List <string>();
var dexConn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_roles_by_userid");
cmd.Connection = dexConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.Int);
cmd.Parameters["@UserID"].Value = UserID;
try
{
dexConn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string role = reader.GetString(0);
roles.Add(role);
}
}
catch (Exception ex)
{
throw ex;
}
return(roles);
}
public bool UpdatePasswordHash(int userID, string oldPasswordHash, string newPasswordHash)
{
bool succesfulUpdate = false;
var dexConn = PokedexDBConnection.GetConnection();
var dexCmd = new SqlCommand("sp_update_user_password");
dexCmd.Connection = dexConn;
dexCmd.CommandType = CommandType.StoredProcedure;
dexCmd.Parameters.Add("@UserID", SqlDbType.Int);
dexCmd.Parameters.Add("@OldPasswordHash", SqlDbType.NVarChar, 100);
dexCmd.Parameters.Add("@NewPasswordHash", SqlDbType.NVarChar, 100);
dexCmd.Parameters["@UserID"].Value = userID;
dexCmd.Parameters["@OldPasswordHash"].Value = oldPasswordHash;
dexCmd.Parameters["@NewPasswordHash"].Value = newPasswordHash;
try
{
dexConn.Open();
int rows = dexCmd.ExecuteNonQuery();
succesfulUpdate = (rows == 1);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dexConn.Close();
}
return(succesfulUpdate);
}
public int InsertOrDeleteUserRole(int UserID, string role, bool delete = false)
{
int rows = 0;
string cmdOption = delete ? "sp_delete_user_role" : "sp_insert_user_role";
var conn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand(cmdOption, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", UserID);
cmd.Parameters.AddWithValue("@RoleID", role);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rows);
}
public int GetUserID(string email)
{
int userID = 0;
var dexConn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_get_userID_by_email", dexConn);
// setting the command type here
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 250);
cmd.Parameters["@email"].Value = email;
try
{
dexConn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
userID = reader.GetInt32(0);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dexConn.Close();
}
return(userID);
}
private PokedexUser getUserByEmail(string email)
{
PokedexUser dexUser = new PokedexUser();
var dexConn = PokedexDBConnection.GetConnection();
//Two of our command objects
var cmd1 = new SqlCommand("sp_select_pokedex_user_by_email");
var cmd2 = new SqlCommand("sp_select_roles_by_UserID");
// Getting connection on command objects
cmd1.Connection = dexConn;
cmd2.Connection = dexConn;
// Setting the command type for them
cmd1.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
// parameters for connection
cmd1.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd1.Parameters["@Email"].Value = email;
cmd2.Parameters.Add("@UserID", SqlDbType.Int);
try
{
//open connection, Remember to CLOSE it!
dexConn.Open();
//execute
var reader1 = cmd1.ExecuteReader();
if (reader1.Read())
{
//UserID
dexUser.PokedexUserID = reader1.GetInt32(0);
dexUser.PokedexUserName = reader1.GetString(1);
dexUser.Email = email;
}
else
{
throw new ApplicationException("User not found");
}
reader1.Close();
cmd2.Parameters["@UserID"].Value = dexUser.PokedexUserID;
List <String> roles = new List <String>();
var reader2 = cmd2.ExecuteReader();
while (reader2.Read())
{
string role = reader2.GetString(0);
roles.Add(role);
}
dexUser.PokedexRoles = roles;
}
catch (Exception ex)
{
throw ex;
}
return(dexUser);
}
public PokedexUser AuthenticatePokedexUser(string email, string passwordHash)
{
// This will change to 1 if the user is authenticated already.
PokedexUser dexUserResult = null;
// We must get a connection
var dexConn = PokedexDBConnection.GetConnection();
// YOU NEED A USER SPROC
var cmd = new SqlCommand("sp_authenticate_pokedex_user");
// Part of the command object
cmd.Connection = dexConn;
// setting the command type here
cmd.CommandType = CommandType.StoredProcedure;
//Add parameters for the procedure
// CHECK VARIABLE LENGTHS
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 250);
cmd.Parameters.Add("@PasswordHash", SqlDbType.NVarChar, 100);
//Set the values for the parameters
cmd.Parameters["@Email"].Value = email;
cmd.Parameters["@PasswordHash"].Value = passwordHash;
// now that the command is set up we can execute it
try
{
//Open the connection, make sure you close it.
dexConn.Open();
// Execute
if (1 == Convert.ToInt32(cmd.ExecuteScalar()))
{
// if the command worked correctly, get a user object
dexUserResult = getUserByEmail(email);
}
else
{
// Let the user know that their credentials weren't found
throw new ApplicationException("User not found");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
//Remembered to close the connection
dexConn.Close();
}
return(dexUserResult);
}
public List <Locations> GetLocations(String gameName)
{
List <Locations> locationList = new List <Locations>();
var dexConn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_get_locations", dexConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Location", SqlDbType.NVarChar, 1000);
cmd.Parameters["@Location"].Value = gameName;
try
{
dexConn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Locations _location = new Locations();
_location.Name = reader.GetString(0);
_location.gameName = reader.GetString(1);
locationList.Add(_location);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
dexConn.Close();
}
return(locationList);
}
public List <PokedexUser> SelectUsersByActive(bool active = true)
{
List <PokedexUser> users = new List <PokedexUser>();
var conn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_users_by_active");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Active", SqlDbType.Bit);
cmd.Parameters["@Active"].Value = active;
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var user = new PokedexUser();
user.PokedexUserID = reader.GetInt32(0);
user.PokedexUserName = reader.GetString(1);
user.Email = reader.GetString(2);
user.Active = reader.GetBoolean(3);
users.Add(user);
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(users);
}
public int DeactivateUser(int userID)
{
int rows = 0;
var conn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_deactivate_user", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", userID);
try
{
conn.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(rows);
}
public List <string> SelectAllRoles()
{
List <string> roles = new List <string>();
var dexConn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_select_all_roles");
cmd.Connection = dexConn;
cmd.CommandType = CommandType.StoredProcedure;
try
{
dexConn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string role = reader.GetString(0);
roles.Add(role);
}
}
catch (Exception ex)
{
throw ex;
}
return(roles);
}
public int InsertUser(PokedexUser user)
{
int UserID = 0;
var dexConn = PokedexDBConnection.GetConnection();
var cmd = new SqlCommand("sp_insert_user", dexConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", user.PokedexUserName);
cmd.Parameters.AddWithValue("@Email", user.Email);
try
{
dexConn.Open();
UserID = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
dexConn.Close();
}
return(UserID);
}