Tuesday, October 9, 2007

Custom Serverside paging in GridView Vs DataGrid

 When doing serverside paging(that is paging at the database layer by returning only the paged result), one of the things I miss in the GridView control is the VirtualItemCount, which is supposedly only supported in the older control's like the DataGrid.

This property was quite useful because while being able to supply to the DataGrid a variable number of paged result sets, i was also able to tell the DataGrid, the total number of records, that way it knew how many pager buttons to display.

Eg. If we had, say a total of a 100 records and had the pageSize set to 7, so only 7 records are shown at a time, then how does the grid know how many numbered pager buttons to display allowing us to navigate from one page to another ? That's where the VirtualItemCount came into play and saved the day. To this property we'd pass a total records count and that was it. In the GridView today ? There is no VirtualItemCount present. The way it were planned it seems is to use the ObjectDataSource, which in my honest opinion is simply extra work, however it does abstract much of this code nicely and put it where it should be, in the data tier.

In all the example code in this post, I shall be using the MemberShip.GetAllUsers method. This method is overloaded and can retrieve a paged result of users Versus returning all the user's in the database, which is a quite handy overload and works out nicely for the code i want to use in this post.

Let's look at a simple example of how we couldof performed custom paging on the DataGrid control back in the old days :


int virtualItemCount = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataGridMembers.DataSource
= Membership.GetAllUsers(0,
DataGridMembers.PageSize,
out virtualItemCount);
DataGridMembers.VirtualItemCount
= virtualItemCount;
DataGridMembers.DataBind();
}
}

Note the VirtualItemCount ? Then on the PageIndexChanged event of the DataGrid we did :

protected void DataGridMembers_PageIndexChanged(object source, 
DataGridPageChangedEventArgs e)
{
DataGridMembers.CurrentPageIndex
= e.NewPageIndex;
DataGridMembers.DataSource
= Membership.GetAllUsers(e.NewPageIndex,
DataGridMembers.PageSize,
out virtualItemCount);
DataGridMembers.DataBind();
}

And that was it, it was as simple as that.

Now try to do that on the GridView ? Can't be done and this is a control that is replacing the old DataGrid control. To make things worse, the DataGrid is not a supported control anymore in 2.0 ; It's been obsoleted and by default you wont even find this control in your toolbox. You can still use it however by manually adding it to your toolbox. Unfortunate, because there are moments like this custom paging situation and i'm getting nostalgic already.

So, how to achieve the same thing in the GridView control which happens to replace the DataGrid ? Well, it's a long shot. Since we cannot achive this directly on the GridView, we are going to have to do it via the DataSource control, which is actually the control that is populating the data for the gridview and also the control that handles paging and sorting amoung other things. While i like this kind of data abstraction, i'm actually doing more work and making the extra effort,but this is how you would implement custom paging on  your GridView control.
The GridView alone is lacking a VirtualItemCount property, which i believe shouldn't have been so hard to implement. To compensate for this lacking, you perform custom serverside paging by using an ObjectDataSource control, defining a SelectMethod and a SelectCountMethod method. The SelectCountMethod is your custom method that returns the Total records count.

So let's look at some code, and there are few gotcha's that weren't exactly obvious to me in the begining :
First our custom SelectMethod :

public MembershipUserCollection GetAllUsers(int startRowIndex, 
int maximumRows)
{
if (startRowIndex > 0)
startRowIndex
= startRowIndex / maximumRows;
return Membership.GetAllUsers(startRowIndex,
maximumRows,
out selectCountValue);
}

One gotcha you want to make note of is how i have some extra code to divide startRowIndex by maximumRows ; This is because startRowIndex is actually the first row in the resultset as the variable name indicates, however what i really need is the current page index, because that is what our stored procedure is expecting, in this case that is what the internal MemberShip.GetAllUsers method is expecting.

Next we need to add a SelectCountMethod :

int selectCountValue = 0;
public int SelectVirtualCount()
{
return selectCountValue;
}

The code is minimum as you can note, but ofcourse, there is some extra effort to making the abstraction. The code above goes into the data layer.

And lastly, we need to subscribe to PageIndexChanging event of our GridView and pass the selected page index :

protected void GridViewMembers_PageIndexChanging(object sender, 
GridViewPageEventArgs e)
{
GridViewMembers.PageIndex
= e.NewPageIndex;
}

A peculiar behaviour you will notice is that the SelectCountMethod and the SelectMethod both share the same SelectParameters if SelectParameters are defined. Peculiar because i was not really expecting it, however I have no issues with it, For example if we had to rewrite our previous example to include also a search by userName, then our ObjectDataSource would be expecting some SelectParameters like this :

<asp:ObjectDataSource ID="ObjectDataSourceMembers"
EnablePaging
="True"
SelectCountMethod
="SelectVirtualCount"
SelectMethod
="GetAllUsers"
TypeName
="MembersData"
runat
="server">
<SelectParameters>
<asp:ControlParameter ControlID="TextBoxUserName" Name="userName"
PropertyName
="Text" DefaultValue="All" />
</SelectParameters>
</asp:ObjectDataSource>

And then modified our SelectMethod as such :

public MembershipUserCollection GetAllUsers(int startRowIndex, 
int maximumRows, string userName)
{
if (startRowIndex > 0)
startRowIndex
= startRowIndex / maximumRows;
if (userName == "all")
{
return Membership.GetAllUsers(startRowIndex,
maximumRows,
out selectCountValue);
}
else
{
return (MembershipUserCollection)
Membership.FindUsersByName(userName
+ "%",
startRowIndex, maximumRows,
out selectCountValue);
}
}

As you can see while our GetAllUsers(SelectMethod) has the needed userName parameter, our SelectVirtualCount method defined above does not have any parameters defined on it, since we don't need to pass it anything. However, the ObjectDataSource is going to complain with :

ObjectDataSource 'ObjectDataSourceMembers' could not find a non-generic method 'SelectVirtualCount' that has parameters: userName

So it means both the SelectMethod and the SelectCountMethod share the same Select parameters. I resolved by adding the extra userName parameter in the SelectCountMethod as well, while i did not clearly need it, but no big deal.
Here is what the modified SelectCountMethod wouldof looked like :

int selectCountValue = 0;
public int SelectVirtualCount(string userName)
{
return selectCountValue;
}

The SelectCountMethod is treated exactly in the same way the SelectMethod is treated, so the ObjectDataSource's Selected Event is going to fire twice for example, once when the SelectMethod is called and once when the SelectCountMethod is called. These are all gotchas i was not really prepared for.

Full code for custom serverside paging in DataGrid :

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
>
<script runat="server">
int virtualItemCount = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataGridMembers.DataSource
= Membership.GetAllUsers(0,
DataGridMembers.PageSize,
out virtualItemCount);
DataGridMembers.VirtualItemCount
= virtualItemCount;
DataGridMembers.DataBind();
}
}

protected void DataGridMembers_PageIndexChanged(object source,
DataGridPageChangedEventArgs e)
{
DataGridMembers.CurrentPageIndex
= e.NewPageIndex;
DataGridMembers.DataSource
= Membership.GetAllUsers(e.NewPageIndex,
DataGridMembers.PageSize,
out virtualItemCount);
DataGridMembers.DataBind();
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="DataGridMembers" AllowPaging="True"
AllowCustomPaging
="true" PageSize="2" runat="server"
OnPageIndexChanged
="DataGridMembers_PageIndexChanged">
<PagerStyle Mode="NumericPages"
HorizontalAlign
="Right" />
</asp:DataGrid>
</div>
</form>
</body>
</html>

And the full code for custom paging in GridView :

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
>

<script runat="server">
protected void GridViewMembers_PageIndexChanging(object sender, 
GridViewPageEventArgs e)
{
GridViewMembers.PageIndex
= e.NewPageIndex;
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView ID="GridViewMembers" DataSourceID="ObjectDataSourceMembers"
runat
="server" AllowPaging="True" PageSize="2"
OnPageIndexChanging
="GridViewMembers_PageIndexChanging">
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSourceMembers"
EnablePaging
="True"
SelectCountMethod
="SelectVirtualCount"
SelectMethod
="GetAllUsers"
TypeName
="MembersData"
runat
="server"></asp:ObjectDataSource>
</div>
</form>
</body>
</html>
public class MembersData
{
public MembersData()
{
//
// TODO: Add constructor logic here
//
}
int selectCountValue = 0;
public int SelectVirtualCount()
{
return selectCountValue;
}
public MembershipUserCollection GetAllUsers(int startRowIndex,
int maximumRows)
{
if (startRowIndex > 0)
startRowIndex
= startRowIndex / maximumRows;
return Membership.GetAllUsers(startRowIndex,
maximumRows,
out selectCountValue);
}
}

12 comments:

  1. >And lastly, we need to subscribe to PageIndexChanging event

    >of our GridView and pass the selected page index



    You don't need to do that for the GridView.

    ReplyDelete
  2. Raj, You are correct. Many thanks.

    ReplyDelete
  3. Sorry...scratch my original comment; I re-read your article, and it does talk about the ObjectDatasource, my bad.

    ReplyDelete
  4. Uh... seriously??? THIS is the solution for custom paging with the GridView? This is completely ridiculous - it's no wonder .NET developers are becoming so disgruntled. .NET is becoming a declarative "magic-word" programming environment - STOP SCREWING STUFF UP!

    ReplyDelete
  5. I'm so glad to have invested my time in designing a data-access layer consisting of static methods only to find out that without an instance class I'll have to either rewrite my infrastructure code OR make two trips back to the database to retrieve the values and the selected count of the objects - did anyone on the ASP.NET team with ACTUAL knowledge of programming design this?

    ReplyDelete
  6. Hi, if you have not already, then try transforming your concrete class(the one with the actual method definitions, which is currently a static class) into an instance class instead and create a second singleton class whose sole purpose will be that of creating an instance of your concrete class and exposing the methods as static. This is also what is being adopted as per the provider model introduced in asp.net 2.0



    Good luck

    ReplyDelete
  7. Thanks for the suggestion, but decided just to write my own pager control that I could use to link up to the GridView and ListView - probably not as elegant an approach as you suggested, but it simplifies the process a bit for me since I am dealing with an existing code base and do not want to re-write everything to take advantage of the dreaded object data source...

    ReplyDelete
  8. LOL - no, I did NOT realize there was a DataPager control in .NET 3.5! I am usually pretty good about looking for things like that before I write my own, but didn't think about it in this case. I will take a look at the DataPager control - I did use the PagerSettings class in my Pager control, so I tried to reuse as much existing code as I could. Thanks again!

    ReplyDelete
  9. I was wondering if there is anyway of NOT using the object data source control and still having the Gridview to generate the page links for me in .NET 2.0.

    ReplyDelete
  10. Pager control only connects to ListView , otherwise you need to write an interface for otyer controls - why do Microsoft make life a pain like this? Check out Apress (www.apress.com)book "C# E-Commerce" and get source that shows how to create custom pager control using Repeater.

    ReplyDelete