/// <summary> /// Helper method to check that the Ingres database used for testing does not have any /// data in the Roles tables prior to commencing set up of a test case and after we /// tear down a test case. /// </summary> private void ValidateDatabaseIsEmpty() { // Validate that the aspnet_UsersInRoles table is empty IngresCommand cmd = new IngresCommand(); cmd.Connection = this.Connection; cmd = new IngresCommand(); cmd.Connection = this.Connection; string sql = @"SELECT COUNT(*) FROM aspnet_UsersInRoles"; cmd.CommandText = sql; int rows = (int)cmd.ExecuteScalar(); cmd.ExecuteNonQuery(); if (rows != 0) { throw new Exception("The aspnet_UsersInRoles table is not empty."); } // Validate that the aspnet_Users table is empty cmd = new IngresCommand(); cmd.Connection = this.Connection; sql = @"SELECT COUNT(*) FROM aspnet_Users"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); rows = (int)cmd.ExecuteScalar(); if (rows != 0) { throw new Exception("The aspnet_Users table is not empty."); } // Validate that the aspnet_Roles table is empty cmd = new IngresCommand(); cmd.Connection = this.Connection; sql = @"SELECT COUNT(*) FROM aspnet_Roles"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); rows = (int)cmd.ExecuteScalar(); if (rows != 0) { throw new Exception("The aspnet_Roles table is not empty."); } // Validate that the aspnet_Application table is empty. sql = @"SELECT COUNT(*) FROM aspnet_Applications"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); rows = (int)cmd.ExecuteScalar(); // The aspnet_applications table may contain 0 or 1 rows, depending where we are // in the test fixture. if (rows != 0) { throw new Exception("The aspnet_Applications is not empty."); } }
/// <summary> /// Takes, as input, a user name and a role name and determines whether the specified user /// is associated with the specified role. /// </summary> /// <remarks> /// This is the main implementation for the <c>IsUserInRole</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="username">The username that we wish to check.</param> /// <param name="roleName">The role which we wish to check.</param> /// <returns>Whether the given user is in the given role.</returns> internal bool IsUserInRole(string username, string roleName) { ValidationUtil.CheckParameterIsOK(ref roleName, true, true, true, 256, "roleName"); ValidationUtil.CheckParameterIsOK(ref username, true, false, true, 256, "username"); if (username.Length < 1) { return false; } if (!this.UserExists(username)) { throw new ProviderException(string.Format(Messages.UserNotFound)); } if (!this.RoleExists(roleName)) { throw new ProviderException(string.Format(Messages.RoleNotFound, roleName)); } // ensure that we have an appication id if (this.ApplicationId == null) { throw new ArgumentNullException("ApplicationId"); } // Instantiate a bool for whether the user is in the role - assume not. bool userIsInRole = false; // Build up the SQL string string sql = @" SELECT COUNT(*) FROM aspnet_UsersInRoles WHERE UserId = ? AND RoleId = ? "; // Create a new command and enrol in the current transaction. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Get the user and role id's for the specified user and role names. string userid = this.GetUserIdByName(username); string roleid = this.GetRoleIdByName(roleName); cmd.Parameters.Add("UserId", DbType.String).Value = userid; cmd.Parameters.Add("RoleId", DbType.String).Value = roleid; // Execute the query and determine is the user is in the role. int rows = Convert.ToInt32(cmd.ExecuteScalar()); if (rows > 0) { userIsInRole = true; } return userIsInRole; }
/// <summary> /// Returns a list of membership users where the user name contains a match of the supplied /// <c>usernameToMatch</c> for the this.configured <c>ApplicationName</c>. For example, if the <c>usernameToMatch</c> /// parameter is set to "user," then the users "user1," "user2," "user3," and so on are /// returned. Wildcard support is included based on the data source. Users are returned in /// alphabetical order by user name. /// </summary> /// <remarks> /// This is the main implementation for the <c>FindUsersByName</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="usernameToMatch">The username to match.</param> /// <param name="pageIndex">The page to return.</param> /// <param name="pageSize">The number of users to return.</param> /// <param name="totalRecords">[out] the total number of matches.</param> /// <returns>Returns a list of membership users where the user name contains a match of the /// supplied usernameToMatch for the this.configured ApplicationName.</returns> internal MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords) { // Validate the input ValidationUtil.CheckParameterIsOK(ref usernameToMatch, true, true, false, 256, "usernameToMatch"); if (pageIndex < 0) { throw new ArgumentException(string.Format(Messages.PageIndexInvalid), "pageIndex"); } if (pageSize < 1) { throw new ArgumentException(string.Format(Messages.PageSizeInvalid), "pageSize"); } long upperBound = ((long)pageIndex * pageSize) + (pageSize - 1); if (upperBound > Int32.MaxValue) { throw new ArgumentException(string.Format(Messages.PageIndexAndPageSizeCombinationInvalid), "pageIndex and pageSize"); } // Adjust the username so that it is in the correct format for an Ingres "LIKE" usernameToMatch = String.Format("%{0}%", usernameToMatch); string sql = @" SELECT COUNT(*) FROM aspnet_Membership, aspnet_Applications, aspnet_Users WHERE aspnet_Users.LoweredUsername LIKE ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId "; IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredUsername", DbType.String).Value = usernameToMatch.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); MembershipUserCollection users = new MembershipUserCollection(); totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); if (totalRecords <= 0) { return users; } // Create a new Ingres command cmd = new IngresCommand(); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters cmd.Parameters.Add("LoweredUsername", DbType.String).Value = usernameToMatch.ToLower(); cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Build up the SQL cmd.CommandText = @" SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Users, aspnet_Membership, aspnet_Applications WHERE aspnet_Users.LoweredUsername LIKE ? AND aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId ORDER BY Username ASC "; // Execute the query and return the correct subset as stipulated by the paging options. IngresDataReader reader = cmd.ExecuteReader(); int counter = 0; int startIndex = (pageSize * pageIndex) + 1; int endIndex = pageSize * (pageIndex + 1); while (reader.Read()) { counter++; if ((counter >= startIndex) && (counter <= endIndex)) { MembershipUser user = this.GetUserFromReader(reader); users.Add(user); } if (counter >= endIndex) { cmd.Cancel(); } } return users; }
/// <summary> /// Takes, as input, a <c>MembershipUser</c> object populated with user information and updates /// the data source with the supplied values. /// </summary> /// <remarks> /// This is the main implementation for the <c>Update</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="user">The membership user to update.</param> internal void UpdateUser(MembershipUser user) { if (user == null) { throw new ArgumentNullException("user"); } // Instatiate a string to hold the required sql. string applicationId; string userId; string sql = @" SELECT aspnet_Users.UserId, aspnet_Applications.ApplicationId FROM aspnet_Users, aspnet_Applications, aspnet_Membership WHERE LoweredUserName = ? AND aspnet_Users.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Users.UserId = aspnet_Membership.UserId "; // Instantiate a new Ingres command using the new connection and sql and add required parameters. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; cmd.Parameters.Add("LoweredUserName", DbType.String).Value = user.UserName.ToLower(); cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the reader and retrieve the user and application ids IngresDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); userId = DBUtil.ColValAsString(reader, "UserId"); applicationId = DBUtil.ColValAsString(reader, "ApplicationId"); reader.Close(); } else { throw new ProviderException("Could not find user."); } if (userId == null) { throw new ProviderException("Could not find user."); } // If we require a unique email then check that the email is unique. if (this.config.RequiresUniqueEmail) { sql = @"SELECT COUNT(*) FROM aspnet_Membership WHERE ApplicationId = ? AND UserId <> ? AND LoweredEmail = ?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add parameters cmd.Parameters.Add("ApplicationId", DbType.String).Value = applicationId; cmd.Parameters.Add("UserId", DbType.String).Value = user.ProviderUserKey; cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower(); // Ensure that we don't get anything returned - throw an exception if we do. int records = (int)cmd.ExecuteScalar(); if (records != 0) { throw new ProviderException(Messages.UniqueEmailRequired); } } // Update the last activity date sql = @"UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add required parameters. cmd.Parameters.Add("LastActivityDate", DbType.DateTime).Value = user.LastActivityDate; cmd.Parameters.Add("UserId", DbType.String).Value = userId; // Execute the query. int rows = cmd.ExecuteNonQuery(); if (rows != 1) { throw new ProviderException(Messages.FailedToUpdateTheLastActivityDate); } // Update the memebership details sql = @"UPDATE aspnet_Membership SET Email = ?, LoweredEmail = ?, Comment = ?, IsApproved = ?, LastLoginDate = ? WHERE UserId =?"; // Get a new Ingres command using the same connection and transaction cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the parameters cmd.Parameters.Add("Email", DbType.String).Value = user.Email; cmd.Parameters.Add("LoweredEmail", DbType.String).Value = user.Email.ToLower(); cmd.Parameters.Add("Comment", DbType.String).Value = user.Comment; cmd.Parameters.Add("IsApproved", DbType.String).Value = user.IsApproved; cmd.Parameters.Add("LastLoginDate", DbType.String).Value = user.LastLoginDate; cmd.Parameters.Add("UserId", DbType.String).Value = userId; rows = cmd.ExecuteNonQuery(); if (rows != 1) { throw new ProviderException(Messages.FailedToUpdateMembershipDetails); } }
/// <summary> /// Returns an integer value that is the count of all the users in the data source where /// the <c>LastActivityDate</c> is greater than the current date and time minus the /// <c>UserIsOnlineTimeWindow</c> property. The <c>UserIsOnlineTimeWindow</c> property is an integer /// value specifying the number of minutes to use when determining whether a user is online. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetNumberOfUsersOnline</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="conn">The Ingres connection to use.</param> /// <param name="tran">The Ingres transaction to use.</param> /// <returns>The number of users online.</returns> internal int GetNumberOfUsersOnline(IngresConnection conn, IngresTransaction tran) { TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); string sql = @" SELECT Count(*) FROM aspnet_Membership, aspnet_Applications, aspnet_Users WHERE aspnet_Users.LastActivityDate > ? AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Membership.ApplicationId = aspnet_Applications.ApplicationId AND aspnet_Users.UserId = aspnet_Membership.UserId "; // Create the Ingres command. IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("CompareDate", DbType.DateTime).Value = compareTime; cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Execute the command to obtain the number of users online int numOnline = Convert.ToInt32(cmd.ExecuteScalar()); return numOnline; }
/// <summary> /// Returns a <c>MembershipUserCollection</c> populated with <c>MembershipUser</c> objects for all of the /// users in the data source. /// </summary> /// <remarks> /// This is the main implementation for the <c>GetAllUsers</c> method of the provider. Please /// see the corresponding method in the Facade, which calls this method, for full documentaion. /// </remarks> /// <param name="pageIndex">Which page to return.</param> /// <param name="pageSize">The maximum number of users to return.</param> /// <param name="totalRecords">[out] The total number of users.</param> /// <returns>Returns a MembershipUserCollection populated with MembershipUser objects /// for all of the users in the data source.</returns> internal MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords) { // Validate the input. if (pageIndex < 0) { throw new ArgumentException(string.Format(Messages.PageIndexInvalid), "pageIndex"); } if (pageSize < 1) { throw new ArgumentException(string.Format(Messages.PageSizeInvalid), "pageSize"); } long upperBound = ((long)pageIndex * pageSize) + (pageSize - 1); if (upperBound > Int32.MaxValue) { throw new ArgumentException(string.Format(Messages.PageIndexAndPageSizeCombinationInvalid), "pageIndex and pageSize"); } // Put the SQL in a string. string sql = @" SELECT Count(*) FROM aspnet_Membership, aspnet_Applications WHERE aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId"; // Create a new command and enrol it in the current transaction IngresCommand cmd = new IngresCommand(sql, this.conn); cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the requires parameters cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); MembershipUserCollection users = new MembershipUserCollection(); // The result is the number of users that we have totalRecords = Convert.ToInt32(cmd.ExecuteScalar()); // If we don't have any users then just return the empty MembershipUserCollection if (totalRecords <= 0) { return users; } // Create a new Ingres command cmd = new IngresCommand(); cmd.Connection = this.conn; cmd.Transaction = this.tran; cmd.CommandTimeout = this.config.CommandTimeout; // Add the required parameters. cmd.Parameters.Add("ApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower(); // Build up the SQL to retrieve the users for the application. cmd.CommandText = @" SELECT aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.Comment, aspnet_Membership.IsApproved, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Membership.LastPasswordChangedDate, aspnet_Users.UserId, aspnet_Users.UserName, aspnet_Membership.IsLockedOut, aspnet_Membership.LastLockoutDate, aspnet_Users.LastActivityDate FROM aspnet_Users, aspnet_Membership, aspnet_Applications WHERE aspnet_Users.UserId = aspnet_Membership.UserId AND aspnet_Applications.LoweredApplicationName = ? AND aspnet_Applications.ApplicationId = aspnet_Membership.ApplicationId ORDER BY Username ASC "; // Execute the command as a reader and return the correct users as stipulated by the paging options IngresDataReader reader = cmd.ExecuteReader(); int counter = 0; int startIndex = (pageSize * pageIndex) + 1; int endIndex = pageSize * (pageIndex + 1); // Note: This might have to be done differently if the performance is too poor! while (reader.Read()) { counter++; if ((counter >= startIndex) && (counter <= endIndex)) { MembershipUser user = this.GetUserFromReader(reader); users.Add(user); } if (counter >= endIndex) { cmd.Cancel(); } } return users; }