public void InsertLogMessage(LogMessageModel message)
{
string cmd = "INSERT INTO Logs (Status, Message, Code, DateCreated) VALUES(@Status, @Message, @Code, @DateCreated)";
DatabaseHelper.ExecuteNonQuery(cmd, _connectionString,
new SqlParameter("@Status", message.Status),
new SqlParameter("@Message", message.Message),
new SqlParameter("@Code", message.Code),
new SqlParameter("@DateCreated", message.DateCreated));
}
/// <summary>
/// Activate User record within the database
/// </summary>
public int ActivateUser(string userid)
{
int result = 0;
string cmd = "UPDATE USERS SET IsActivated = 1 WHERE UserID = @UserID";
try
{
result = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString,
new SqlParameter("@UserID", userid));
}
catch (Exception ex)
{
LogMessageModel message = new LogMessageModel(LogStatus.Error,
string.Format("An error occurred during user activation within DatabaseManager.ActivateUser: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(message);
result = -1;
}
return result;
}
public LoginResultModel AuthenticateStandardUser(string userID, string passwordHash)
{
LoginResultModel result = new LoginResultModel();
SqlParameter[] parameters = new SqlParameter[]{
new SqlParameter("@UserID",userID),
new SqlParameter("@Password",passwordHash)};
string cmd = "SELECT * FROM Users WHERE UserID = @UserID and Password = @Password";
try
{
DataSet resultSet = DatabaseHelper.ExecuteQuery(cmd, _connectionString, parameters);
//- Determine if user exists within database
if (resultSet != null && resultSet.Tables[0].Rows.Count > 0)
{
DataTable resultTable = resultSet.Tables[0];
result.Message = "User successfully authenticated";
result.ResultStatus = LoginResultType.Success;
result.ConnectionIDs = GetUserConnectionIDs(userID);
}
else
{
result.Message = "User not found";
result.ResultStatus = LoginResultType.UserNotFound;
}
}
catch (Exception ex)
{
result.Message = ex.Message;
result.ResultStatus = LoginResultType.UnexpectedException;
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.AuthenticateStandardUser: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return result;
}
/// <summary>
/// Updates an existing User relationship
/// </summary>
/// <param name="sourceuserid">Requesting User ID</param>
/// <param name="targetuserid">Requested User ID</param>
public Task<int> UpdateUserRelationshipRequesAsync(string sourceuserid, string targetuserid, bool isactive)
{
Task<int> requesttask = new Task<int>(() =>
{
int result = 0;
string cmd = "UPDATE UserRelationships SET IsActive = @IsActive, DateActivated = @DateActivated ";
cmd += "WHERE (Source_UserID = @Source_UserID and Target_UserID = @Target_UserID) or (Source_UserID = @Target_UserID and Target_UserID = @Source_UserID);";
SqlParameter sourceparam = new SqlParameter("@Source_UserID", sourceuserid);
SqlParameter targetparam = new SqlParameter("@Target_UserID", targetuserid);
SqlParameter isactiveparam = new SqlParameter("@IsActive", isactive);
SqlParameter dateactivated = new SqlParameter("@DateActivated", DateTime.UtcNow);
try
{
result = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString, sourceparam, targetparam, isactiveparam, dateactivated);
}
catch (Exception ex)
{
result = -1;
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.UpdateUserRelationshipRequesAsync: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return result;
});
return requesttask;
}
/// <summary>
/// Updates the signalr connection for a specific user on a specific device
/// </summary>
public int UpdateUserDeviceConnection(string userid, string deviceid, string connectionid)
{
int result = 0;
string cmd = "UPDATE UserConnections SET ConnectionID = @ConnectionID WHERE UserID_FK = @UserID_FK and DeviceID = @DeviceID";
try
{
result = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString,
new SqlParameter("@ConnectionID", connectionid),
new SqlParameter("@UserID_FK", userid),
new SqlParameter("@DeviceID", deviceid));
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.UpdateUserDeviceConnection: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
result = -1;
}
return 0;
}
public SignupResultModel SignupStandardUser(UserModel user)
{
SignupResultModel model = new SignupResultModel();
string cmd = "INSERT INTO USERS(GlobalID, UserID, LoginType, Password, FirstName, LastName, CreateDate, IsActivated)";
cmd += "VALUES(@GlobalID, @UserID, @LoginType, @Password, @FirstName, @LastName, @CreateDate, @IsActivated)";
SqlParameter globalid = new SqlParameter("@GlobalID", user.GlobalID);
SqlParameter userid = new SqlParameter("@UserID", user.UserID);
SqlParameter userlogintype = new SqlParameter("@LoginType", user.UserLoginType);
SqlParameter password = new SqlParameter("@Password", user.PasswordHash);
SqlParameter fname = new SqlParameter("@FirstName", user.FirstName);
SqlParameter lname = new SqlParameter("@LastName", user.LastName);
SqlParameter createdate = new SqlParameter("@CreateDate", user.CreateDate);
SqlParameter isactive = new SqlParameter("@IsActivated", user.IsActivated);
try
{
//- check if user exists
bool doesExist = CheckUserExists(user.UserID);
if (doesExist)
{
model.ResultStatus = SignupResultType.UserExists;
model.Message = "The user id already exists";
return model;
}
// - insert new user into database
int queryresult = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString,
globalid, userid, userlogintype, password, fname, lname, createdate, isactive);
if (queryresult < 0)
{
model.ResultStatus = SignupResultType.UnexpectedException;
model.Message = "The database was unable to add the user record";
}
else
{
model.ResultStatus = SignupResultType.Success;
model.Message = "User account successfully added";
}
}
catch (Exception ex)
{
LogMessageModel message = new LogMessageModel(LogStatus.Error,
string.Format("An error occurred during user signup within DatabaseManager.SignupStandardUser: {0}", ex.Message), -1);
model.ResultStatus = SignupResultType.UnexpectedException;
model.Message = string.Format("An exception occurred while attempted to add the user account: {0}", ex.Message);
}
return model;
}
/// <summary>
/// Inserts a new relationship request from one user to another
/// </summary>
/// <param name="sourceuserid">Requesting User ID</param>
/// <param name="targetuserid">Requested User ID</param>
public Task<int> InsertUserRelationshipRequestAsync(string sourceuserid, string targetuserid)
{
Task<int> requesttask = new Task<int>(() =>
{
int result = 0;
string cmd = "INSERT INTO UserRelationships(Source_UserID, Target_UserID, IsActive, DateCreated) ";
cmd += "VALUES(@Source_UserID, @Target_UsesrID, 0, @DateCreated);";
SqlParameter sourceparam = new SqlParameter("@Source_UserID", sourceuserid);
SqlParameter targetparam = new SqlParameter("@Target_UserID", targetuserid);
SqlParameter datecreated = new SqlParameter("@DateCreated", DateTime.UtcNow);
try
{
//- check if target user exists and is active
bool targetexists = CheckUserExists(targetuserid);
bool targetactive = CheckIfUserActive(targetuserid);
if (targetexists && targetactive)
result = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString, sourceparam, targetparam, datecreated);
else if (!targetexists)
throw new Exception(string.Format("Error: Could not create relationship request. Target user '{0}' does not exist", targetuserid));
else
throw new Exception(string.Format("Error: Could not create relationship request. Target user '{0}' is not active", targetuserid));
}
catch (Exception ex)
{
result = -1;
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.InsertUserRelationshipRequesAsync: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return result;
});
return requesttask;
}
/// <summary>
/// Add a new UserID / ConnectionID mapping
/// </summary>
public int InsertUserConnection(string userID, string connectionID)
{
int result = 0;
string cmd = "INSERT INTO UserConnections(UserID_FK, ConnectionID) VALUES(@UserID_FK, @ConnectionID)";
SqlParameter useridparam = new SqlParameter("@UserID_FK", userID);
SqlParameter connectidparam = new SqlParameter("@ConnectionID", connectionID);
try
{
//- check if connection is already mapped
string existingUser = GetUserByConnectionID(connectionID);
if(string.IsNullOrEmpty(existingUser))
result = DatabaseHelper.ExecuteNonQuery(cmd, _connectionString, useridparam, connectidparam);
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.InsertUserConnection: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
result = -1;
}
return result;
}
/// <summary>
/// Get all device id's bleonging to this user
/// </summary>
/// <param name="userID">ID of the user the device id are associated with</param>
public List<string> GetUserDeviceIDs(string userID)
{
List<string> deviceIDs = new List<string>();
string cmd = "SELECT DeviceID FROM UserConnections WHERE UserID_FK = @UserID_FK";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString, new SqlParameter("@UserID_FK", userID));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string device = ds.Tables[0].Rows[0]["DeviceID"].ToString();
deviceIDs.Add(device);
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserDeviceIDs: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return deviceIDs;
}
/// <summary>
/// Gets a user's relationships
/// </summary>
/// <param name="userid">Identifier for the user to get UserRelationships for</param>
/// <param name="isactive">0 - gets all pending UserRelationships; 1 - gets all active relationships</param>
/// <returns>List of UserModels belonging to users that this user relates to </returns>
public Task<List<UserModel>> GetUserRelationshipsAsync(string userid, DateTime datecreated, bool isactive)
{
Task<List<UserModel>> usertask = new Task<List<UserModel>>(() =>
{
List<UserModel> users = new List<UserModel>();
try
{
string cmd = "SELECT * FROM UserRelationships WHERE (Source_UserID = @UserID or Target_UserID = @UserID) and IsActive = @IsActive and ";
cmd += "DateCreated > '@DateCreated'";
SqlParameter userparam = new SqlParameter("@UserID", userid);
SqlParameter createparam = new SqlParameter("@DateCreated", datecreated);
SqlParameter isactiveparam = new SqlParameter("@IsActive", isactive);
DataSet resultSet = DatabaseHelper.ExecuteQuery(cmd, _connectionString, userparam, createparam, isactiveparam);
//- Create UserModel's from the other user in the relationship
if (resultSet != null && resultSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in resultSet.Tables[0].Rows)
{
UserModel user = new UserModel();
if (row["Source_UserID"].ToString() == userid)
user = GetUserByUserID(row["Target_UserID"].ToString());
else
user = GetUserByUserID(row["Source_UserID"].ToString());
users.Add(user);
}
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetActiveUserRelationshipAsync: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return users;
});
return usertask;
}
/// <summary>
/// Get all connection id's belonging to the specified user
/// </summary>
public List<string> GetUserConnectionIDs(string userID)
{
List<string> ids = new List<string>();
string cmd = "SELECT * FROM UserConnections WHERE UserID_FK = @UserID_FK";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString, new SqlParameter("@UserID_FK", userID));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
//- iterate through results
foreach (DataRow row in table.Rows)
{
ids.Add(row["ConnectionID"].ToString());
}
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserConnectionIDs: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return ids;
}
/// <summary>
/// GET the current Signalr connection id associated with a specified user's device
/// </summary>
/// <param name="userid">ID of the user</param>
/// <param name="deviceid">ID for the device the user is accessing</param>
/// <returns>The Signalr connection id for that user on that device</returns>
public string GetUserConnectionByDevice(string userid, string deviceid)
{
string connectionid = "";
string cmd = "SELECT ConnectionID FROM UserConnections WHERE UserID_FK = @UserID_FK and DeviceID = @DeviceID";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString, new SqlParameter("@UserID_FK", userid), new SqlParameter("@DeviceID", deviceid));
if (ds != null && ds.Tables[0].Rows.Count > 0)
connectionid = ds.Tables[0].Rows[0]["ConnectionID"].ToString();
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserConnectionByDevice: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return connectionid;
}
/// <summary>
/// Get user associated with the provided UserID
/// </summary>
public UserModel GetUserByUserID(string userID)
{
UserModel user = null;
string cmd = "SELECT * FROM Users WHERE UserID = @UserID";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString, new SqlParameter("@UserID", userID));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataRow row = ds.Tables[0].Rows[0];
user = new UserModel();
user.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
user.LastModified = DateTime.Parse(row["LastModified"].ToString());
user.FirstName = row["FirstName"].ToString();
user.LastName = row["LastName"].ToString();
user.UserID = row["UserID"].ToString();
user.GlobalID = row["GlobalID"].ToString();
user.UserLoginType = (LoginType)int.Parse(row["IsFacebookLogin"].ToString());
user.PasswordHash = row["Password"].ToString();
user.IsActivated = bool.Parse(row["IsActivated"].ToString());
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserByUserID: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return user;
}
/// <summary>
/// Gets user associated with the Connection
/// </summary>
public string GetUserByConnectionID(string connectionID)
{
string userid = "";
string cmd = "SELECT * FROM UserConnections WHERE Connection = @ConnectionID";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString, new SqlParameter("@ConnectionID", connectionID));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
userid = ds.Tables[0].Rows[0]["ConnectionID"].ToString();
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserByConnectionID: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return userid;
}
/// <summary>
/// Get all active users
/// </summary>
public List<UserModel> GetAllActiveUsers()
{
List<UserModel> activeusers = new List<UserModel>();
string cmd = "SELECT * FROM Users WHERE IsActivated = 1";
try
{
DataSet ds = DatabaseHelper.ExecuteQuery(cmd, _connectionString);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable table = ds.Tables[0];
//- iterate through results
foreach (DataRow row in table.Rows)
{
UserModel user = new UserModel();
user.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
user.FirstName = row["FirstName"].ToString();
user.LastName = row["LastName"].ToString();
user.UserID = row["UserID"].ToString();
user.GlobalID = row["GlobalID"].ToString();
user.UserLoginType = (LoginType)int.Parse(row["LoginType"].ToString());
user.PasswordHash = row["Password"].ToString();
user.IsActivated = true;
activeusers.Add(user);
}
}
}
catch (Exception ex)
{
LogMessageModel logmessage = new LogMessageModel(LogStatus.Error,
string.Format("Login exception occurred within DatabaseManager.GetUserConnectionIDs: {0}", ex.Message), -1);
ExceptionManager.Instance.InsertLogMessage(logmessage);
}
return activeusers;
}
