/// <summary> /// The get user. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="providerUserKey"> /// The provider user key. /// </param> /// <param name="userName"> /// The user name. /// </param> /// <param name="userIsOnline"> /// The user is online. /// </param> /// <returns> /// </returns> public DataRow GetUser([NotNull] string appName, [NotNull] object providerUserKey, [NotNull] string userName, bool userIsOnline) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@UserName", userName); cmd.Parameters.AddWithValue("@UserKey", providerUserKey); cmd.Parameters.AddWithValue("@UserIsOnline", userIsOnline); cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow); using (DataTable dt = this._msSqlDbAccess.GetData(cmd)) { if (dt.Rows.Count > 0) { return(dt.Rows[0]); } else { return(null); } } } }
/// <summary> /// The create user. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="username"> /// The username. /// </param> /// <param name="password"> /// The password. /// </param> /// <param name="passwordSalt"> /// The password salt. /// </param> /// <param name="passwordFormat"> /// The password format. /// </param> /// <param name="email"> /// The email. /// </param> /// <param name="passwordQuestion"> /// The password question. /// </param> /// <param name="passwordAnswer"> /// The password answer. /// </param> /// <param name="isApproved"> /// The is approved. /// </param> /// <param name="providerUserKey"> /// The provider user key. /// </param> public void CreateUser([NotNull] string appName, [NotNull] string username, [NotNull] string password, [NotNull] string passwordSalt, int passwordFormat, [NotNull] string email, [NotNull] string passwordQuestion, [NotNull] string passwordAnswer, bool isApproved, [NotNull] object providerUserKey) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_createuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Input Parameters cmd.Parameters.AddWithValue("Username", username); cmd.Parameters.AddWithValue("Password", password); cmd.Parameters.AddWithValue("PasswordSalt", passwordSalt); cmd.Parameters.AddWithValue("PasswordFormat", passwordFormat); cmd.Parameters.AddWithValue("Email", email); cmd.Parameters.AddWithValue("PasswordQuestion", passwordQuestion); cmd.Parameters.AddWithValue("PasswordAnswer", passwordAnswer); cmd.Parameters.AddWithValue("IsApproved", isApproved); cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow); // Input Output Parameters var paramUserKey = new SqlParameter("UserKey", SqlDbType.UniqueIdentifier); paramUserKey.Direction = ParameterDirection.InputOutput; paramUserKey.Value = providerUserKey; cmd.Parameters.Add(paramUserKey); // Execute this._msSqlDbAccess.ExecuteNonQuery(cmd); // Retrieve Output Parameters providerUserKey = paramUserKey.Value; } }
/// <summary> /// The update user. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="user"> /// The user. /// </param> /// <param name="requiresUniqueEmail"> /// The requires unique email. /// </param> /// <returns> /// The update user. /// </returns> public int UpdateUser([NotNull] object appName, [NotNull] MembershipUser user, bool requiresUniqueEmail) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_updateuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("UserKey", user.ProviderUserKey); cmd.Parameters.AddWithValue("UserName", user.UserName); cmd.Parameters.AddWithValue("Email", user.Email); cmd.Parameters.AddWithValue("Comment", user.Comment); cmd.Parameters.AddWithValue("IsApproved", user.IsApproved); cmd.Parameters.AddWithValue("LastLogin", user.LastLoginDate); cmd.Parameters.AddWithValue("LastActivity", user.LastActivityDate.ToUniversalTime()); cmd.Parameters.AddWithValue("UniqueEmail", requiresUniqueEmail); // Add Return Value var p = new SqlParameter("ReturnValue", SqlDbType.Int); p.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(p); this._msSqlDbAccess.ExecuteNonQuery(cmd); // Execute Non SQL Query return(Convert.ToInt32(p.Value)); // Return } }
/// <summary> /// The get profile structure. /// </summary> /// <returns> /// </returns> public DataTable GetProfileStructure() { string sql = @"SELECT TOP 1 * FROM {0}".FormatWith(MsSqlDbAccess.GetObjectName("prov_Profile")); using (var cmd = new SqlCommand(sql)) { cmd.CommandType = CommandType.Text; return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// Database Action - Create Role /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="roleName"> /// Role Name /// </param> public void CreateRole(object appName, object roleName) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_createrole"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("RoleName", roleName); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// Database Action - Find Users in Role /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="roleName"> /// Role Name /// </param> /// <returns> /// Datatable containing User Information /// </returns> public DataTable FindUsersInRole(object appName, object roleName) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_findusersinrole"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("RoleName", roleName); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// Database Action - Get Role Exists /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="roleName"> /// Role Name /// </param> /// <returns> /// Database containing Role Information /// </returns> public object GetRoleExists(object appName, object roleName) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_exists"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("RoleName", roleName); return(this._msSqlDbAccess.ExecuteScalar(cmd)); } }
/// <summary> /// Database Action - Get Roles /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="username"> /// The username. /// </param> /// <returns> /// Database containing Role Information /// </returns> public DataTable GetRoles(object appName, object username) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_getroles"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("Username", username); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// Database Action - Remove User From Role /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="userName"> /// User Name /// </param> /// <param name="roleName"> /// Role Name /// </param> public void RemoveUserFromRole(object appName, string userName, string roleName) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_removeuserfromrole"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("Username", userName); cmd.Parameters.AddWithValue("RoleName", roleName); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The get user name by email. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="email"> /// The email. /// </param> /// <returns> /// </returns> public DataTable GetUserNameByEmail([NotNull] string appName, [NotNull] string email) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getusernamebyemail"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Email", email); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// The unlock user. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="userName"> /// The user name. /// </param> public void UnlockUser([NotNull] string appName, [NotNull] string userName) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_unlockuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@UserName", userName); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// Binds the data. /// </summary> private void BindData() { using (DataSet ds = LegacyDb.ds_forumadmin(this.PageContext.PageBoardID)) { this.CategoryList.DataSource = ds.Tables[MsSqlDbAccess.GetObjectName("Category")]; } // Hide the New Forum Button if there are no Categories. this.NewForum.Visible = this.CategoryList.Items.Count < 1; this.DataBind(); }
/// <summary> /// The delete user. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="username"> /// The username. /// </param> /// <param name="deleteAllRelatedData"> /// The delete all related data. /// </param> public void DeleteUser([NotNull] string appName, [NotNull] string username, bool deleteAllRelatedData) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_deleteuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Username", username); cmd.Parameters.AddWithValue("@DeleteAllRelated", deleteAllRelatedData); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The get all users. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="pageIndex"> /// The page index. /// </param> /// <param name="pageSize"> /// The page size. /// </param> /// <returns> /// </returns> public DataTable GetAllUsers([NotNull] string appName, int pageIndex, int pageSize) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getallusers"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@PageIndex", pageIndex); cmd.Parameters.AddWithValue("@PageSize", pageSize); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// The upgrade membership. /// </summary> /// <param name="previousVersion"> /// The previous version. /// </param> /// <param name="newVersion"> /// The new version. /// </param> public void UpgradeMembership(int previousVersion, int newVersion) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_upgrade"))) { cmd.CommandType = CommandType.StoredProcedure; // Nonstandard args cmd.Parameters.AddWithValue("@PreviousVersion", previousVersion); cmd.Parameters.AddWithValue("@NewVersion", newVersion); cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The find users by name. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="usernameToMatch"> /// The username to match. /// </param> /// <param name="pageIndex"> /// The page index. /// </param> /// <param name="pageSize"> /// The page size. /// </param> /// <returns> /// </returns> public DataTable FindUsersByName([NotNull] string appName, [NotNull] string usernameToMatch, int pageIndex, int pageSize) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_findusersbyname"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Username", usernameToMatch); cmd.Parameters.AddWithValue("@PageIndex", pageIndex); cmd.Parameters.AddWithValue("@PageSize", pageSize); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// The get user password info. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="username"> /// The username. /// </param> /// <param name="updateUser"> /// The update user. /// </param> /// <returns> /// </returns> public DataTable GetUserPasswordInfo([NotNull] string appName, [NotNull] string username, bool updateUser) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getuser"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Username", username); cmd.Parameters.AddWithValue("@UserIsOnline", updateUser); cmd.Parameters.AddWithValue("@UTCTIMESTAMP", DateTime.UtcNow); return(this._msSqlDbAccess.GetData(cmd)); } }
/// <summary> /// The change password question and answer. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="username"> /// The username. /// </param> /// <param name="passwordQuestion"> /// The password question. /// </param> /// <param name="passwordAnswer"> /// The password answer. /// </param> public void ChangePasswordQuestionAndAnswer([NotNull] string appName, [NotNull] string username, [NotNull] string passwordQuestion, [NotNull] string passwordAnswer) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_changepasswordquestionandanswer"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Username", username); cmd.Parameters.AddWithValue("@PasswordQuestion", passwordQuestion); cmd.Parameters.AddWithValue("@PasswordAnswer", passwordAnswer); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// Bind data for this control. /// </summary> private void BindData() { // get list of forums and their moderating data using (DataSet ds = LegacyDb.forum_moderatelist(this.PageContext.PageUserID, this.PageContext.PageBoardID)) { this.CategoryList.DataSource = ds.Tables[MsSqlDbAccess.GetObjectName("Category")]; } // bind data to controls this.DataBind(); if (this.CategoryList.Items.Count.Equals(0)) { this.InfoPlaceHolder.Visible = true; } }
/// <summary> /// The change password. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="username"> /// The username. /// </param> /// <param name="newPassword"> /// The new password. /// </param> /// <param name="newSalt"> /// The new salt. /// </param> /// <param name="passwordFormat"> /// The password format. /// </param> /// <param name="newPasswordAnswer"> /// The new password answer. /// </param> public void ChangePassword([NotNull] string appName, [NotNull] string username, [NotNull] string newPassword, [NotNull] string newSalt, int passwordFormat, [NotNull] string newPasswordAnswer) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_changepassword"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@Username", username); cmd.Parameters.AddWithValue("@Password", newPassword); cmd.Parameters.AddWithValue("@PasswordSalt", newSalt); cmd.Parameters.AddWithValue("@PasswordFormat", passwordFormat); cmd.Parameters.AddWithValue("@PasswordAnswer", newPasswordAnswer); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The get number of users online. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="timeWindow"> /// The time window. /// </param> /// <returns> /// The get number of users online. /// </returns> public int GetNumberOfUsersOnline([NotNull] string appName, int timeWindow) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_getnumberofusersonline"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@TimeWindow", timeWindow); cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow); var p = new SqlParameter("ReturnValue", SqlDbType.Int); p.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(p); this._msSqlDbAccess.ExecuteNonQuery(cmd); return(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value)); } }
/// <summary> /// Database Action - Delete Role /// </summary> /// <param name="appName"> /// Application Name /// </param> /// <param name="roleName"> /// Role Name /// </param> /// <param name="deleteOnlyIfRoleIsEmpty"> /// The delete Only If Role Is Empty. /// </param> /// <returns> /// Status as integer /// </returns> public int DeleteRole(object appName, object roleName, object deleteOnlyIfRoleIsEmpty) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_role_deleterole"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("ApplicationName", appName); cmd.Parameters.AddWithValue("RoleName", roleName); cmd.Parameters.AddWithValue("DeleteOnlyIfRoleIsEmpty", deleteOnlyIfRoleIsEmpty); var p = new SqlParameter("ReturnValue", SqlDbType.Int); p.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(p); this._msSqlDbAccess.ExecuteNonQuery(cmd); return(Convert.ToInt32(cmd.Parameters["ReturnValue"].Value)); } }
/// <summary> /// The add profile column. /// </summary> /// <param name="name"> /// The name. /// </param> /// <param name="columnType"> /// The column type. /// </param> /// <param name="size"> /// The size. /// </param> public void AddProfileColumn([NotNull] string name, SqlDbType columnType, int size) { // get column type... string type = columnType.ToString(); if (size > 0) { type += "(" + size + ")"; } string sql = "ALTER TABLE {0} ADD [{1}] {2} NULL".FormatWith( MsSqlDbAccess.GetObjectName("prov_Profile"), name, type); using (var cmd = new SqlCommand(sql)) { cmd.CommandType = CommandType.Text; this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The reset password. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="userName"> /// The user name. /// </param> /// <param name="password"> /// The password. /// </param> /// <param name="passwordSalt"> /// The password salt. /// </param> /// <param name="passwordFormat"> /// The password format. /// </param> /// <param name="maxInvalidPasswordAttempts"> /// The max invalid password attempts. /// </param> /// <param name="passwordAttemptWindow"> /// The password attempt window. /// </param> public void ResetPassword([NotNull] string appName, [NotNull] string userName, [NotNull] string password, [NotNull] string passwordSalt, int passwordFormat, int maxInvalidPasswordAttempts, int passwordAttemptWindow) { using (var cmd = new SqlCommand(MsSqlDbAccess.GetObjectName("prov_resetpassword"))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ApplicationName", appName); // Nonstandard args cmd.Parameters.AddWithValue("@UserName", userName); cmd.Parameters.AddWithValue("@Password", password); cmd.Parameters.AddWithValue("@PasswordSalt", passwordSalt); cmd.Parameters.AddWithValue("@PasswordFormat", passwordFormat); cmd.Parameters.AddWithValue("@MaxInvalidAttempts", maxInvalidPasswordAttempts); cmd.Parameters.AddWithValue("@PasswordAttemptWindow", passwordAttemptWindow); cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow); this._msSqlDbAccess.ExecuteNonQuery(cmd); } }
/// <summary> /// The set profile properties. /// </summary> /// <param name="appName"> /// The app name. /// </param> /// <param name="userID"> /// The user id. /// </param> /// <param name="values"> /// The values. /// </param> /// <param name="settingsColumnsList"> /// The settings columns list. /// </param> public void SetProfileProperties([NotNull] object appName, [NotNull] object userID, [NotNull] SettingsPropertyValueCollection values, [NotNull] List <SettingsPropertyColumn> settingsColumnsList) { using (var cmd = new SqlCommand()) { string table = MsSqlDbAccess.GetObjectName("prov_Profile"); StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(table); sqlCommand.Append(" WHERE UserId = @UserID) "); cmd.Parameters.AddWithValue("@UserID", userID); // Build up strings used in the query var columnStr = new StringBuilder(); var valueStr = new StringBuilder(); var setStr = new StringBuilder(); int count = 0; foreach (SettingsPropertyColumn column in settingsColumnsList) { // only write if it's dirty if (values[column.Settings.Name].IsDirty) { columnStr.Append(", "); valueStr.Append(", "); columnStr.Append(column.Settings.Name); string valueParam = "@Value" + count; valueStr.Append(valueParam); cmd.Parameters.AddWithValue(valueParam, values[column.Settings.Name].PropertyValue); if (column.DataType != SqlDbType.Timestamp) { if (count > 0) { setStr.Append(","); } setStr.Append(column.Settings.Name); setStr.Append("="); setStr.Append(valueParam); } count++; } } columnStr.Append(",LastUpdatedDate "); valueStr.Append(",@LastUpdatedDate"); setStr.Append(",LastUpdatedDate=@LastUpdatedDate"); cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow); sqlCommand.Append("BEGIN UPDATE ").Append(table).Append(" SET ").Append(setStr.ToString()); sqlCommand.Append(" WHERE UserId = '").Append(userID.ToString()).Append("'"); sqlCommand.Append(" END ELSE BEGIN INSERT ").Append(table).Append(" (UserId").Append(columnStr.ToString()); sqlCommand.Append(") VALUES ('").Append(userID.ToString()).Append("'").Append(valueStr.ToString()).Append( ") END"); cmd.CommandText = sqlCommand.ToString(); cmd.CommandType = CommandType.Text; this._msSqlDbAccess.ExecuteNonQuery(cmd); } }