Archive

Archive for June, 2013

SharePoint: SqlMembershipProvider – Get All Users In Role

June 30th, 2013 No comments

    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.

Related posts:

C#: How to set or get value of a private or internal field through the Reflection

June 30th, 2013 No comments

    The given post is an extension to the one How to set or get value of a private or internal property through the Reflection. So, here are two more methods to add to the ReflectionHelper. These methods are implemented as extensions to the object class and simplify getting and setting values of object’s private and internal fields.

public static class ReflectionHelper
{
	//...
	// here are methods described in the post 
	// http://dotnetfollower.com/wordpress/2012/12/c-how-to-set-or-get-value-of-a-private-or-internal-property-through-the-reflection/
	//...

	private static FieldInfo GetFieldInfo(Type type, string fieldName)
	{
		FieldInfo fieldInfo;
		do
		{
			fieldInfo = type.GetField(fieldName,
				   BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
			type = type.BaseType;
		}
		while (fieldInfo == null && type != null);
		return fieldInfo;
	}

	public static object GetFieldValue(this object obj, string fieldName)
	{
		if (obj == null)
			throw new ArgumentNullException("obj");
		Type objType = obj.GetType();
		FieldInfo fieldInfo = GetFieldInfo(objType, fieldName);
		if (fieldInfo == null)
			throw new ArgumentOutOfRangeException("fieldName",
			  string.Format("Couldn't find field {0} in type {1}", fieldName, objType.FullName));
		return fieldInfo.GetValue(obj);
	}

	public static void SetFieldValue(this object obj, string fieldName, object val)
	{
		if (obj == null)
			throw new ArgumentNullException("obj");
		Type objType = obj.GetType();
		FieldInfo fieldInfo = GetFieldInfo(objType, fieldName);
		if (fieldInfo == null)
			throw new ArgumentOutOfRangeException("fieldName",
			  string.Format("Couldn't find field {0} in type {1}", fieldName, objType.FullName));
		fieldInfo.SetValue(obj, val);
	}
}

The use of methods is shown below:

// get value
string privateValue = (string)someObj.GetFieldValue("_connectionString");
// set value
someObj.SetFieldValue("_connectionString", "some connection string");
Related posts:
Categories: C#, Reflection Tags: ,

SharePoint: Simple Event Logger

June 3rd, 2013 No comments

    Errors, warnings and info messages in all my SharePoint applications are being logged to the Application Event Log. For that I use a simple class tritely named EventLogger and listed later in this post. As for the moment, a couple of words about the EventLogger are stated below.

If necessary, the EventLogger registers a source in the Application Event Log once any its method is called for the first time (see the static constructor). The event logging uses the information stored in the Eventlog registry key. So, when dealing with the Application Event Log, we have to be ready to get exception about a lack of rights to read from or write to the registry. Because of that, the EventLogger initially tries adding a new source under the current user account and then, in case of failure, repeats the same under the application pool account (SPSecurity.RunWithElevatedPrivileges) that is supposed to have all suitable permissions.

Due to the same reason, whenever a user different from the application pool account writes anything to the log, he will likely get an exception which is reporting that the current user doesn’t have write access. To guard users from that, we as administrators have to do some manual work, namely, to add the CustomSD value to the [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog\Application] registry key how it’s described in the article SharePoint: Cannot open log for source. You may not have write access. If a SharePoint application supports anonymous access, use WD (all users) instead of AU (authenticated users). Also it’s very important to understand that the appropriate CustomSD must be added on all machines of a SharePoint farm. An alternative way is to wrap the writing to the log in SPSecurity.RunWithElevatedPrivileges. Remember, however, that the SPSecurity.RunWithElevatedPrivileges is quite resource-consuming and excessive for such frequent operation as event logging. So, use the SPSecurity.RunWithElevatedPrivileges as an extreme measure and only when the previous approach with CustomSD didn’t help for some reasons.

Another feature of the EventLogger is that, as a backup plan, it writes to the SharePoint Trace Log through the Unified Logging Service (see the WriteToHiveLog method). In other words, if the EventLogger doesn’t manage to write a message to the Application Event Log, it tries appending the message to the ULS Log stored in the file system and accessible, for example, through the ULS Viewer.

Logging an error or warning based on the passed exception, the EventLogger forms the final text, using the exception’s message along with the message of the inner exception (if any) and stack trace.

Below is a combined example that demonstrates how to use the EventLogger to log errors, warnings and info.

using dotNetFollower;
...

EventLogger.WriteInfo("How to use the EventLogger");

EventLogger.WriteError("Sorry, couldn't perform this operation!");
// OR
EventLogger.WriteWarning("Sorry, couldn't perform this operation!");

try
{
	// the next line throws an exception
	SPList spList = SPContext.Current.Web.Lists["Not existing list"];
}
catch (Exception ex)
{
	EventLogger.WriteError(ex);
	// OR
	EventLogger.WriteWarning(ex);
}

Below is depicted what those records look like in the Windows Event Viewer:
EventLogger Records

Ok, it’s about time for the EventLogger listing:

using System;
using System.Diagnostics;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace dotNetFollower
{
    public static class EventLogger
    {
        private const string SOURCE = "dotNetFollower"; // put here your own source name

        /// <summary>
        /// Writes an error message
        /// </summary>
        /// <param name="errorText">Error message</param>
        public static void WriteError(string errorText)
        {
            WriteWithinTryCatch(errorText, EventLogEntryType.Error);
        }
        /// <summary>
        /// Writes an error message
        /// </summary>
        /// <param name="ex">Exception</param>
        public static void WriteError(Exception ex)
        {
            WriteWithinTryCatch(GetExceptionFormatted(ex), EventLogEntryType.Error);
        }
        /// <summary>
        /// Writes a warning message
        /// </summary>
        /// <param name="text">Warning message</param>
        public static void WriteWarning(string text)
        {
            WriteWithinTryCatch(string.Format("Warning: {0}", text), EventLogEntryType.Warning);
        }
        /// <summary>
        /// Writes a warning message
        /// </summary>
        /// <param name="ex">Exception</param>
        public static void WriteWarning(Exception ex)
        {
            WriteWithinTryCatch(GetExceptionFormatted(ex), EventLogEntryType.Warning);
        }
        /// <summary>
        /// Writes an info message
        /// </summary>
        /// <param name="text">Info message</param>
        public static void WriteInfo(string text)
        {
            WriteWithinTryCatch(string.Format("Information: {0}", text), EventLogEntryType.Information);
        }

        /// <summary>
        /// Creates the appropriate source in Event Logs, if necessary
        /// </summary>
        public static void EnsureLogSourceExist()
        {
            if (!EventLog.SourceExists(SOURCE))
                EventLog.CreateEventSource(SOURCE, "Application");
        }

        /// <summary>
        /// Returns an error message based on a passed exception. Includes an inner exception (if any) and stack trace
        /// </summary>
        /// <param name="ex">Exception</param>
        /// <returns>Formed error message</returns>
        public static string GetExceptionFormatted(Exception ex)
        {
            return string.Format("Error: {0} (Inner Exception: {1})\t\nDetails: {2}", 
                ex.Message, 
                ex.InnerException != null ? ex.InnerException.Message : string.Empty, 
                ex.StackTrace);
        }

        static EventLogger()
        {
            bool error = false;

            Action action = delegate
                {
                    try
                    {
                        // register source in Event Logs
                        EnsureLogSourceExist();
                    }
                    catch
                    {
                        error = true;
                    }
                };

            // try under current user
            action();

            if(error)
                // try under application pool account
                SPSecurity.RunWithElevatedPrivileges(() => action());
        }

        private static void WriteWithinTryCatch(string message, EventLogEntryType type)
        {
            try
            {
                // To allow users (authenticated only or all of them) writing to Event Log,
                // follow the steps described in the article 
                // http://dotnetfollower.com/wordpress/2012/04/sharepoint-cannot-open-log-for-source-you-may-not-have-write-access/

                // If it doesn't help for some reason, uncomment the line with SPSecurity.RunWithElevatedPrivileges and 
                // comment the other one. Note, however, that the use of SPSecurity.RunWithElevatedPrivileges is 
                // resource-consuming and looks excessive for such frequent operation as event logging.

                //SPSecurity.RunWithElevatedPrivileges(() => EventLog.WriteEntry(SOURCE, message, type));
                EventLog.WriteEntry(SOURCE, message, type);
            }
            catch
            {
                WriteToHiveLog(message, type);
            }
        }

        private static void WriteToHiveLog(string message, EventLogEntryType type)
        {
            EventSeverity eventSeverity = type == EventLogEntryType.Error ? EventSeverity.Error : 
                (type == EventLogEntryType.Warning ? EventSeverity.Warning : EventSeverity.Information);

            var category = new SPDiagnosticsCategory(SOURCE, TraceSeverity.Unexpected, eventSeverity);

            SPDiagnosticsService.Local.WriteTrace(0, category, TraceSeverity.Unexpected, message, null);
        }
    }
}
Related posts: