public async Task InsertContact(UserModel user){
string cmd = string.Format ("INSERT INTO Contacts(GlobalID, Email, FirstName, LastName, DateCreated) " +
"VALUES('{0}', '{1}', '{2}', '{3}', '{4}')", user.GlobalID, user.UserID, user.FirstName, user.LastName, user.CreateDate);
try{
DatabaseHelper.ExecuteNonQuery(_dbpath, cmd);
}
catch(Exception ex){
Debug.WriteLine(string.Format("The following exception occurred in (ClientDatabaseManager.InsertLocalContact) {0}", ex.Message));
}
return;
}
public async Task InsertUserDetails(UserModel user){
string cmd = string.Format("INSERT INTO Users(GlobalID, Email, Password, LoginType_FK, FirstName, LastName, HomeAddress, DateCreated, LastModified)"
+ "VALUES('{0}','{1}','{2}',{3},'{4}','{5}','{6}','{7}','{8}')",
user.GlobalID,
user.UserID,
user.PasswordHash,
user.UserLoginType,
user.FirstName,
user.LastName,
"",
user.CreateDate,
user.LastModified);
Task insertusertask = Task.Run (() => {
int result = DatabaseHelper.ExecuteNonQuery(_dbpath, cmd);
});
await insertusertask;
return;
}
public async Task UpdateUser(UserModel user){
string cmd = string.Format ("UPDATE Users Set PasswordHash = {0}, FirstName = {1}, Lastname = {2}, " +
"LastModified = {3} WHERE UserID = {4}", user.PasswordHash, user.FirstName, user.LastName,
DateTime.UtcNow, user.UserID);
Task updateusertask = Task.Run (() => {
int result = DatabaseHelper.ExecuteNonQuery(_dbpath, cmd);
});
await updateusertask;
return;
}
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;
}
public void SignupFacebookUser(UserModel user)
{
}
/// <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 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>
/// 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;
}
