SharePoint: SqlMembershipProvider – Get All Users In Role
In the SharePoint application I’m currently working on, I configured Form Based Authentication (FBA) using the SqlMembershipProvider and SqlRoleProvider. Implementing some user management functionality, I run into the lack of a method to get the users in particular role by portions (so-called pagination). The SqlRoleProvider exposes the GetUsersInRole method which returns only names of users in the passed role and doesn’t support pagination. The direct way in this case is to get user names and then get appropriate users, calling the GetUser method of SqlMembershipProvider one time per name. This approach results in a bunch of requests to the database: one request is to get names of users in a role and a number of requests are to get each user by his name. In addition, we have somehow to implement pagination ourselves. The approach is acceptable, but let’s try to reduce requests to the database and borrow somewhere the pagination logic.
GetAllUsersInRole Stored Procedure
It’s interesting that the SqlMembershipProvider provides the GetAllUsers method that supports pagination. On the database level, every call of SqlMembershipProvider.GetUsersInRole and SqlMembershipProvider.GetAllUsers ends with executing such Stored Procedures as aspnet_UsersInRoles_GetUsersInRoles and aspnet_Membership_GetAllUsers respectively. So, we know that the aspnet_UsersInRoles_GetUsersInRoles searches for names of users in a role while the aspnet_Membership_GetAllUsers is able to return users by portions. Let’s combine these two Stored Procedures and create another one which would select users in a role and return a required portion of the result. The sql script below creates such Stored Procedure, I named it aspnet_Membership_GetAllUsersInRole. Note the script should be executed on MembershipProvider database, it’s aspnetdb in my case.
USE [aspnetdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: .Net Follower
-- Description: Returns users in role by portions
-- =============================================
CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsersInRole]
@ApplicationName nvarchar(256),
@PageIndex int,
@PageSize int,
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN 0
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN 0
-- Set the page bounds
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId uniqueidentifier
)
-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
WHERE u.ApplicationId = @ApplicationId AND u.UserId = m.UserId AND
u.UserId = ur.UserId AND @RoleId = ur.RoleId
ORDER BY u.UserName
SELECT @TotalRecords = @@ROWCOUNT
SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
WHERE u.UserId = p.UserId AND u.UserId = m.UserId AND
p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
ORDER BY u.UserName
RETURN @TotalRecords
END
Custom Membership Provider
Now let’s extend our Membership Provider with a new method that deals with the aspnet_Membership_GetAllUsersInRole. I created a class SqlMembershipProviderEx derived from SqlMembershipProvider and containing the target GetAllUsersInRole method. The class is demonstrated below, but first of all a few remarks on the code:
- I had to use Reflection to get values of some important fields (like Connection String to the database, for example) as Microsoft makes everything private or internal;
- The SqlMembershipProvider elevates privileges when opening SqlConnection. Since the extended Membership Provider is going to be used in SharePoint application, I did the same by means of SPSecurity.RunWithElevatedPrivileges. Note however that if you want to use the extended Membership Provider in a pure ASP.Net application you will need to deal with such internal (of course) classes as SqlConnectionHolder and ApplicationImpersonationContext through Reflection;
- The code of GetAllUsersInRole method is mainly based on the GetAllUsers of the parent SqlMembershipProvider class.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Web.Security;
using Microsoft.SharePoint;
namespace dotNetFollower
{
public class SqlMembershipProviderEx : SqlMembershipProvider
{
protected string _connectionString;
protected int? _sqlCommandTimeout;
protected string ConnectionString
{
get
{
return _connectionString ??
(_connectionString = Convert.ToString(this.GetFieldValue("_sqlConnectionString")));
}
}
protected int CommandTimeout
{
get
{
if (_sqlCommandTimeout == null)
_sqlCommandTimeout = Convert.ToInt32(this.GetFieldValue("_CommandTimeout"));
return _sqlCommandTimeout.Value;
}
}
public MembershipUserCollection GetAllUsersInRole(string role, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
throw new ArgumentException("The pageIndex must be greater than or equal to zero.", "pageIndex");
if (pageSize < 1)
throw new ArgumentException("The pageSize must be greater than zero.", "pageSize");
long num = ((pageIndex * pageSize) + pageSize) - 1;
if (num > 0x7fffffff)
throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.", "pageIndex and pageSize");
MembershipUserCollection users = new MembershipUserCollection();
int recordsAmount = 0;
DoInSqlConnectionContext(delegate(SqlConnection connection)
{
//this.CheckSchemaVersion(connection.Connection);
SqlCommand command = new SqlCommand("dbo.aspnet_Membership_GetAllUsersInRole", connection);
SqlDataReader reader = null;
SqlParameter parameter = new SqlParameter("@ReturnValue", SqlDbType.Int);
command.CommandTimeout = CommandTimeout;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(CreateInputParam("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
command.Parameters.Add(CreateInputParam("@PageIndex", SqlDbType.Int, pageIndex));
command.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize));
command.Parameters.Add(CreateInputParam("@RoleName", SqlDbType.NVarChar, role));
parameter.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(parameter);
try
{
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
string nullableString = GetNullableString(reader, 0);
string email = GetNullableString(reader, 1);
string passwordQuestion = GetNullableString(reader, 2);
string comment = GetNullableString(reader, 3);
bool boolean = reader.GetBoolean(4);
DateTime creationDate = reader.GetDateTime(5).ToLocalTime();
DateTime lastLoginDate = reader.GetDateTime(6).ToLocalTime();
DateTime lastActivityDate = reader.GetDateTime(7).ToLocalTime();
DateTime lastPasswordChangedDate = reader.GetDateTime(8).ToLocalTime();
Guid providerUserKey = reader.GetGuid(9);
bool isLockedOut = reader.GetBoolean(10);
DateTime lastLockoutDate = reader.GetDateTime(11).ToLocalTime();
users.Add(new MembershipUser(Name, nullableString, providerUserKey, email, passwordQuestion,
comment, boolean, isLockedOut, creationDate, lastLoginDate,
lastActivityDate, lastPasswordChangedDate, lastLockoutDate));
}
}
catch (Exception ex)
{
EventLogger.WriteError(ex);
throw;
}
finally
{
if (reader != null)
reader.Close();
if (parameter.Value is int)
recordsAmount = (int)parameter.Value;
}
});
totalRecords = recordsAmount;
return users;
}
protected void DoInSqlConnectionContext(Action<SqlConnection> action)
{
SqlConnection connection = null;
try
{
connection = new SqlConnection(ConnectionString);
SPSecurity.RunWithElevatedPrivileges(connection.Open);
action(connection);
}
finally
{
if (connection != null)
connection.Close();
}
}
protected SqlParameter CreateInputParam(string paramName, SqlDbType dbType, object objValue)
{
SqlParameter parameter = new SqlParameter(paramName, dbType);
if (objValue == null)
{
parameter.IsNullable = true;
parameter.Value = DBNull.Value;
return parameter;
}
parameter.Value = objValue;
return parameter;
}
protected string GetNullableString(SqlDataReader reader, int col)
{
return !reader.IsDBNull(col) ? reader.GetString(col) : null;
}
}
}
Note the EventLogger class is described in the post SharePoint: Simple Event Logger while the GetFieldValue method is provided by ReflectionHelper described in the C#: How to set or get value of a private or internal field through the Reflection and C#: How to set or get value of a private or internal property through the Reflection.
The latest version of the SqlMembershipProviderEx along with all used additional classes are available to download here.