Although in this article I describe FBA in relation with Sharepoint most of described ideas and concepts will be also useful if you work with different applications, e.g. with common ASP.Net web applications.
Forms based authentication (FBA) is one of the authentication types available for web applications (concept of forms authentication when users identify themselves by providing username and password is applicable in different types of applications, but in this post I will mostly refer to web applications). With FBA users physically may be located in different storages. Most frequent storage type is SQL Server, although they may be located e.g. in AD (and accessible via LDAP) or in custom storage (e.g. in the file on file system). For SQL server there are ready for use database script prepared by MS, which creates db with all necessary tables for FBA, and .Net classes SqlMembershipProvider and SqlRoleProvider for working with this database from the code (for AD scenario classes are ActiveDirectoryMembershipProvider and AuthorizationStoreRoleProvider). Here is the schema of aspnetdb:
This model is quite flexible and may be used in lot of scenarios including those applications which work with very big number of users. However in this case you may encounter with performance problems e.g. when list all users in user management administration. It is possible to use paging of course but if you will do it in-memory (i.e. inside of your application, when all users are retrieved from the database and logic of calculating and displaying correct page is implemented in the code), there won’t be real performance improvement. You may add caching, but first load anyway may be very slow.
Problem become even worse if you use FBA in more complex applications which have own user management system like Sharepoint and you need to add additional info to each user retrieved from SQL Server from this system as well. In this part I will show how to solve 1st problem by using paging on SQL Server side and in next part 2nd part will be handled on example with Sharepoint (update 2014-06-22: second part is published here).
SQL Server side paging is not new idea. Nowadays SQL Server handles it with ROW_NUMBER T-SQL function which became available from 2005 version of SQL Server. However SqlMembershipProvider doesn’t use it (instead it uses temp tables) and those can be used in earlier versions of the SQL Server. Let’s see how we may use it in our FBA application for improving performance.
First of all let’s see interface of MembershipProvider class which is base class for SqlMembershipProvider and all other providers (it can be also used as base class for FBA with custom storage. In this case you need to implement all its abstract methods by yourself):
If we want to optimize user listing we are interested in the following methods:
As you can see they have 3 parameters which are used for pagination:
- totalRecords (output parameter)
In SqlMembershipProvider class these methods use the following stored procedures:
They are quite similar, let’s see how aspnet_Membership_GetAllUsers is implemented:
The idea is very simple here: it creates temporary table with auto incremented identity index column and userId (lines 22-27), then stores all userIds into this temp table ordered by user name (lines 29-34). As result we have own index table where first column contains number of row where appropriate user is stored. Last step is quite simple: initialize total records by number of affecting users (line 36) and return those users which correspond to the passed page index and page size by making cross-table query to database tables and built temporary table (lines 38-49). Stored procedures aspnet_Membership_FindUsersByEmail and aspnet_Membership_FindUsersByName use the same idea with additional filtering login by user name and email.
Now when we know how it works in backend, let’s prepare front end. In Sharepoint for displaying users list we may use convenient SPGridView class (in ASP.Net case you may use GridView control). For enabling paging we need to set AllowPaging property to true on the page where control is added and override its CreateDataSourceSelectArguments() method. Overriding can be done in custom class:
Here we only provide correct values for start row and page size. Without it SPGridView always passes 0 in start row. Then we need to implement DataSourceControl and DataSourceView classes which can be done like that:
For DataSourceView’s inheritor implementation is more complex:
First of all it contains necessary logic for retrieving paged users information from SQL database (lines 20-42). It became possible after implementing of CreateDataSourceSelectArguments() method in inheritor of SPGridView control above, because after that argument passed in ExecuteSelect() method in DataSourceView got correct values for start row and maximum rows. But this is only half of all work. In order to be able to switch between pages we also need to make grid view “think” that there are also other pages, and amount of these pages (both preceding and subsequent related to the current page) should be exactly as in SQL Server. In order to do it we add fake empty rows to the resulting DataView object (lines 45-68).
After these manipulations we will get quite fast users listing which will work with good performance even if we have many users in our FBA database. In next part we will add information to each returned user from Sharepoint which will be done with Camlex library – open source library for building dynamic CAML queries via C# lambda expressions. With such tasks flexibility of Camlex is revealed on its full power. See you on the next part (update 2014-06-22: second part is published here).