ASP.NET 2.0 - Custom Paging and Custom Sorting for GridViews

Paging and sorting GridViews are the most sought after functionalities in most of the Web applications.When it comes to performance, for datacentric applications its very important to implement the custom paging/sorting mechanism instead of the
default paging/sorting for gridviews. This is because the page becomes heavily loaded when thousands of records are fetched at a strech from the
database.

To overcome this, we must implement a "Custom paging" mechanism which brings record only "on-demand". This article discusses how this can be
implemented in ASP.NET 2.0 with zero lines of code in presentation layer.

1. The first step is to write a stored proc, GetSortedItems that accepts these parameters and returns a resultset.

a. SortExpression
b. StartRow
c. EndRow

For Example, if the parameter values are ("ItemId desc", 5,10), which means the rows 5 to 10 have to be retrieved, with ItemId sorted in descending
order.

2. Another stored proc must be written, say GetItemsCount that returns the count of records that is returned from the resultset.

3. Next step is to call the stored proc in the Data Access layer, and return a Collection object.

The following is an example of a DAL method using ODP.NET of Oracle:

public QueryList GetSortedQueries(string sort, int startRowIndex, int maximumRows, int x, string y)
{
DataSet dsQueryList = null;
OdpHelper adoHelper = new OdpHelper();
QueryEntity queryEntity = null;
QueryList queryList = null;
string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
IDataParameter[] param = null;
param = adoHelper.GetDataParameters(4);
param[0] = adoHelper.GetRefCurParameter("p_cursor");
param[0].Direction = ParameterDirection.Output;
param[1] = adoHelper.GetParameter("P_SortExp",
DbType.String,
1000,
ParameterDirection.Input
);
param[1].Value = sort;
param[2] = adoHelper.GetParameter("BV_STARTROW",
DbType.Int32,
4,
ParameterDirection.Input
);
param[2].Value = startRowIndex;
param[3] = adoHelper.GetParameter("BV_ENDROW",DbType.Int32,4,ParameterDirection.Input);
int lastRow = startRowIndex + maximumRows;
param[3].Value = lastRow;
dsQueryList = adoHelper.ExecuteDataset(connectionString,"TestCustomPagingWithSorting",param);
queryList = new QueryList();
//loops through the dataset rows to load displaycontrols retrieved into the list
if (dsQueryList.Tables.Count > 0)
{
if (dsQueryList.Tables[0].Rows.Count > 0)
{
foreach (DataRow drQueryEntity in dsQueryList.Tables[0].Rows)
{
queryEntity = new QueryEntity();
if (drQueryEntity["queryid"] != DBNull.Value)
{
queryEntity.QueryId = Convert.ToInt32(drQueryEntity["queryid"].ToString());
}
if (drQueryEntity["querytypeid"] != DBNull.Value)
{
queryEntity.QueryTypeId = Convert.ToInt32(drQueryEntity["querytypeid"].ToString());
}
queryList.Add(queryEntity);
}
}
}
return queryList;
}

4. In the Presentation layer, all we have to do is to add an ObjectDataSource control and set the following properties:

• SelectMethod property to the GetSortedQueryList method - either from Data Access Layer or Business Facade
• TypeName property to the Data Access Layer or Business Facade class that has the specific method.
• SortParameterName property to the parameter name in which the Sort expression is passed.
• SelectCountMethod is the method in the Data Access Layer or Business Facade class that returns the count of records.
• The <SelectParameters> are the individual parameters that are present passed to the SelectMethod and SelectCountMethod.
• If the GridView has to be bound on triggering of an event, just do not specify the DataSourceID="ObjectDataSource1" in GridView, and add these
  two lines of code in the event.
  GridView1.DataSourceID = "ObjectDataSource1";
  GridView1.DataBind();

Note: The method signature of SelectCountMethod must necessarily contain all parameters of SelectMethod, even though these may not be used.

Ex:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSortedQueryList"
SortParameterName="sort" TypeName="CustomPagingService.CustomPagingService" SelectCountMethod="GetQueryListCount"
EnablePaging="True" >
<SelectParameters>
<asp:Parameter DefaultValue="Asc" Name="sort" Type="String" />
<asp:Parameter DefaultValue="1" Name="startRowIndex" Type="Int32" />
<asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" />
<asp:Parameter DefaultValue="10" Name="x" Type="Int32" />
<asp:Parameter DefaultValue="" Name="y" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>



<asp:GridView ID="GridView2" DataKeyNames="QueryId" runat="server" AllowPaging="True"
AllowSorting="true" AutoGenerateColumns="false"
PageSize="5">
<Columns>
<asp:BoundField DataField="QueryId" ReadOnly="true" HeaderText="QueryId" SortExpression="QueryId" />
<asp:BoundField DataField="QueryTypeId" ReadOnly="true" HeaderText="QueryTypeId"
SortExpression="QueryTypeId" />
</Columns>
<PagerSettings FirstPageText="First" LastPageText="Last" Mode="NumericFirstLast" />
</asp:GridView>

Thats it. Just compile and run your page. You will find that by just setting properties with no line of code in the presentation layer, Custom paging and sorting functionalites are achieved.