Reza Alirezaei’s Blog

Almost a year ago, I wrote a post explaining how CTE (Common Table Expressions) can make your life much easier when working with your membership users in chunks and pages in SharePoint (or ASP.NET). Well, since then I have received couple emails from people asking for the UI part of that post (SPGridView) so I decided to write another follow up post here to demonstrate how to use SPGridView,  SPGridViewPager and Common Table Expression to achieve a true pagination through your membership users.

Well, We all know that pagination has been always a very common feature when displaying and working with data in an application. The GridView control in ASP.NET 2.0 (my Swiss army control to display and work with data) offers a great support for standard paging with small data sets but when it comes to larger sets of data, things can get a bit funky (It pulls all the data back to the client by default). Back in good old ASP.NET 1.1 days, DataGrid control used to expose a property called VirtualItemCount that defines the number of records/items when custom paging is used. Unfortunately, this is missing in the GridView control!

In SharePoint, the SPGridView control inherits from the ASP.NET GridView control . The SPGridView control comes with some SharePoint flavors by a fairly good integration with the cascading style sheets that are built into WSS 3.0 . If you cruise around SharePoint application pages, you will soon realize that the SPGridView is heavily used in many application pages and Web Parts. Due to its parent’s funky behavior in pulling back the whole dataset, the SPGridView also offers the same limitations. Browsing on the web you can find various alternatives for simulating the VirtualItemCount behavior in the GridView, Here are couple of them:

• Solution1: Use SelectCountMethod in ObjectDataSource to return the virtual item count, which the GridView will use for setting up its pagination. This solution works only if your datasource is an ObjectDataSource. You can see this solution POCed here in my codeplex project.
• Solution2 (here and here): Provide a Custom Pager where you will create your own page navigation controls, handle all the events and deal with the display.

The alternative I’ve selected is the second one; create a Custom Pager that extends the control SharePoint provides for pagination called SPGridViewPager (OOTB style pagination).

Let’s see first the sample code for extending the SPGridViewPager control.

[CSharp]
using Microsoft.SharePoint.WebControls;

namespace SharePoint.MembershipControls.WebControls
{
public class CustomPager : SPGridViewPager
{
protected int _virtualItemCount;

public int VirtualItemCount
{
get
{
return this._virtualItemCount;
}
set
{
this._virtualItemCount = value;
}
}

public int PageIndex
{
get
{
return (this.ViewState[“PageIndex”] != null ? (int)this.ViewState[“PageIndex”] : 0);
}
set
{
this.ViewState[“PageIndex”] = value;
}
}

protected override void Render(System.Web.UI.HtmlTextWriter output)
{
if ((this.GridViewControl != null) && (this.PreviousPageLinkIsEnabled || this.NextPageLinkIsEnabled))
{
// Previous Page
this.RenderImage(output, this.PreviousPageLinkIsEnabled, “previouspage”, “/_layouts/images/prev.gif”, “/_layouts/images/blank.gif”, “Previous Page”);

// Records Range
output.AddAttribute(“class”, “ms-listheaderlabel”);
output.RenderBeginTag(“font”);
output.Write(string.Format(“{0}-{1}”, ((this.PageIndex * this.GridViewControl.PageSize) + 1), ((this.PageIndex + 1) * this.GridViewControl.PageSize)));
output.RenderEndTag();

// Next Page
this.RenderImage(output, this.NextPageLinkIsEnabled, “nextpage”, “/_layouts/images/next.gif”, “/_layouts/images/blank.gif”, “Next Page”);
}
}

protected override void OnClickPrevious(System.EventArgs args)
{
this.PageIndex–;
this.GridViewControl.PageIndex = 1;
base.OnClickPrevious(args);
}

protected override void OnClickNext(System.EventArgs args)
{
this.PageIndex++;
this.GridViewControl.PageIndex = 0;
base.OnClickNext(args);
}

private bool PreviousPageLinkIsEnabled
{
get
{
if (this.GridViewControl != null)
{
if (this.PageIndex > 0)
{
return true;
}
}
return false;
}
}

private bool NextPageLinkIsEnabled
{
get
{
if (this.GridViewControl != null)
{
if (this.PageIndex < (this.PageCount - 1)) { return true; } } return false; } } private int PageCount { get { return (int)(this._virtualItemCount / this.GridViewControl.PageSize) + 1; } } private void RenderImage(System.Web.UI.HtmlTextWriter output, bool isEnabled, string postbackEventArgument, string enabledImageUrl, string disabledImageUrl, string toolTip) { string str = disabledImageUrl; if (isEnabled) { output.AddAttribute(System.Web.UI.HtmlTextWriterAttribute.Href, "#"); output.AddAttribute(System.Web.UI.HtmlTextWriterAttribute.Onclick, this.Page.ClientScript.GetPostBackEventReference(this, postbackEventArgument) + "; return false;"); output.RenderBeginTag(System.Web.UI.HtmlTextWriterTag.A); str = enabledImageUrl; } output.AddAttribute(System.Web.UI.HtmlTextWriterAttribute.Src, str); output.AddAttribute(System.Web.UI.HtmlTextWriterAttribute.Border, "0"); output.AddAttribute(System.Web.UI.HtmlTextWriterAttribute.Alt, toolTip); output.RenderBeginTag(System.Web.UI.HtmlTextWriterTag.Img); output.RenderEndTag(); if (isEnabled) { output.RenderEndTag(); } } } } [/CSharp] The following code demonstrates how to integrate the CustomPager web control in an application page: ASPX page: [Xml] <%@ Register TagPrefix="wssawc" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="wssawc" Namespace="Controls.WebControls" Assembly="Controls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=388cedd2af9642c2" %>

[/Xml]

Here is the Code Behind of the page above:

[CSharp]
using Microsoft.SharePoint.WebControls;
using Controls.WebControls;

protected SPGridView MemberGrid;
protected CustomPager MemberGridViewPager;

protected void MemberGrid_ClickNext(Object sender, EventArgs e)
{
FillGrid();
}

protected void MemberGrid_ClickPrevious(Object sender, EventArgs e)
{
FillGrid();
}

protected void FillGrid()
{
MembershipUserCollection users = new MembershipUserCollection();
int totalUsers;

CustomMembershipProvider customProvider = Membership.Provider as CustomMembershipProvider;
users = customProvider.GetAllUsers(MemberGridViewPager.PageIndex, MemberGrid.PageSize, out totalUsers);
MemberGridViewPager.VirtualItemCount = totalUsers;

MemberGrid.DataSource = users;
MemberGrid.DataBind();
}

[/CSharp]

The following code demonstrates how to integrate the CustomPager web control in a web part:

[CSharp]
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;
using Microsoft.SharePoint.WebControls;

namespace SharePoint.MembershipControls.WebParts
{
public class MemberList : System.Web.UI.WebControls.WebParts.WebPart
{

SPGridView gridView;
CustomPager customPager;

protected override void CreateChildControls()
{
base.CreateChildControls();

//
// SPGridView
//

this.gridView = new SPGridView();
this.gridView.ID = “MemberGrid”;
this.gridView.AutoGenerateColumns = false;
this.gridView.CssClass = “ms-vb2”;
this.gridView.GridLines = GridLines.None;
this.gridView.AllowPaging = true;
this.gridView.PageSize = 15;

// Columns
BoundField boundField;

boundField = new BoundField();
boundField.DataField = “Username”;
boundField.HeaderText = “Username”;
boundField.HeaderStyle.Width = new Unit(200, UnitType.Pixel);
this.gridView.Columns.Add(boundField);

boundField = new BoundField();
boundField.DataField = “Email”;
boundField.HeaderText = “E-mail”;
boundField.HeaderStyle.Width = new Unit(200, UnitType.Pixel);
this.gridView.Columns.Add(boundField);

this.Controls.Add(this.gridView);

//
// CustomPager
//

this.customPager = new CustomPager();
this.customPager.ID = “MemberGridViewPager”;
this.customPager.GridViewId = this.gridView.ID;
this.customPager.ClickPrevious += new EventHandler(MemberGrid_ClickPrevious);
this.customPager.ClickNext += new EventHandler(MemberGrid_ClickNext);

this.Controls.Add(this.customPager);

// MemberGrid Databind
this.FillGrid();
}

void MemberGrid_ClickPrevious(Object sender, EventArgs e)
{
FillGrid();
}

void MemberGrid_ClickNext(Object sender, EventArgs e)
{
FillGrid();

}

void FillGrid()
{
MembershipUserCollection users = new MembershipUserCollection();
CustomMembershipProvider customProvider = Membership.Provider as CustomMembershipProvider;
int totalUsers;

users = customProvider.GetAllUsers(this.customPager.PageIndex, this.gridView.PageSize, out totalUsers);
this.customPager.VirtualItemCount = totalUsers;

this.gridView.DataSource = users;
this.gridView.DataBind();
}
}
}
[/CSharp]

SPGridView listing backend users:

As you can tell, CustomPager control only supports SharePoint OOTB pagination style, it could be extended for supporting other styles.

For retrieving the data displayed on the grid a custom Membership Provider was used. The GetAllUsers method of that provider executes a stored procedure by passing page index and page size parameters. The stored procedure logic uses these values for returning only the rows that corresponds to the page the user is seeing. Here is an example of how to implement that stored procedure using CTE technique (http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx):

[Sql]
CREATE PROCEDURE [dbo].[sharepoint_sp_getUsers]
(
@pageIndex AS INTEGER,
@pageSize AS INTEGER
)
AS

SET NOCOUNT ON

— Set the page bounds
DECLARE @PageLowerBound AS INT
DECLARE @PageUpperBound AS INT

SET @PageLowerBound = @PageSize * @PageIndex + 1
SET @PageUpperBound = @PageSize + @PageLowerBound – 1

— Get records based on criteria, one page only
WITH ContactsCTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY UserName) AS RowNum,
COUNT(*) OVER () AS TotalRows,
Email,
UserName
FROM
dbo.contact con
)
SELECT
con.*
FROM
ContactsCTE con
WHERE
con.RowNum BETWEEN @PageLowerBound AND @PageUpperBound

SET NOCOUNT OFF
[/Sql]

First time the grid is loaded:

When clicking on the next arrow for displaying second page:

There you go, you have successfully managed  to land your memebrship users onto your SharePoint page and more importantly paginate through them efficiently to save the bandwidth.

Special thanks goes to my colleague, Diego Altmann for helping out on writing this lengthy blog post.