Esempio n. 1
0
        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));
        }
Esempio n. 2
0
        /// <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;
        }
Esempio n. 3
0
        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;
        }
Esempio n. 4
0
        /// <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;
        }
Esempio n. 5
0
        /// <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;
        }
Esempio n. 6
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;
        }
Esempio n. 7
0
        /// <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;
        }
Esempio n. 8
0
        /// <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;
        }
Esempio n. 9
0
        /// <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;
        }
Esempio n. 10
0
        /// <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;
        }
Esempio n. 11
0
        /// <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;
        }
Esempio n. 12
0
        /// <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;
        }
Esempio n. 13
0
        /// <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;
        }
Esempio n. 14
0
        /// <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;
        }
Esempio n. 15
0
        /// <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;
        }