Effective Paging and Sorting Using GridView and ObjectDataSource – Part I

Wednesday, November 4, 2009

With ObjectDataSource, you can do paging and sorting very effectively in gridview. In real time, we have millions of records in database. At that time, when we have to fetch those records and display in grid will take more time which makes performance issue. For this purpose, objectDataSource provide an good option for using paging and sorting in gridview. I had taken help from asp.net book and modify some of things.

Download source code here.

Paging :-

Notice that the objectDataSource control includes as EnablePaging property that has value True. The ObjectDataSource also includes a SelectCountMethod property that represents the name of a method that retrieves a record count from the data source. Even when using data source paging, you still need to enable AllowPaging property to True and using PageSize property.

When an ObjectDataSource control has its EnablePaging property set to the value True, the ObjectDataSource passes additional parameters when calling the method represented by its SelectMethod property. The two additional parameters are named StartRowIndex and MaximumRows, this parameter will be added implicitly.

To improve performance, the GetCustomerCount() method attempts to retrieve the total count from server cache. If the record count cannot be retrieved from the cache, the count is retrieved from the DB.

At store procedure side, we can use Row_Number() function to select a range of rows. Note that Row_Number() available above Sql Server 2005 versions only.

Property Use for paging in ObjectDataSource is SelectCountMethod and EnablePaging.

here more logic is at store procedure side then coding side. So mainly I will explain store procedure. and provide code at the end.

WITH CustDetails AS

(

SELECT CustomerID, ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNo

FROM CUSTOMER

)

SELECT

Customer.CustomerID,

Customer.FirstName,

Customer.MiddleName,

Customer.LastName,

Customer.Desgination,

Customer.Address,

Customer.City,

Customer.State,

Customer.Country

FROM

CustDetails

INNER JOIN

Customer

CustDetails.CustomerID = Customer.CustomerID

WHERE (RowNo BETWEEN @StartRowIndex + 1 AND @StartRowIndex + @MaximumRows + 1)

with clause is used for getting customer details with ROW_NUMBER() function to get row number wise. Then finally using inner join with CET table and customer table and fetching row that has been passed in SP between @StartRowIndex and @MaximumRows.

Sorting :-

For sorting, ObjectDataSource control includes a property named SortParameterName. Note, this is just the portion of the query for sorting. You can download full store procedure script which link has been given below.

SELECT

Customer.CustomerID,

Customer.FirstName,

Customer.MiddleName,

Customer.LastName,

Customer.Desgination,

Customer.Address,

Customer.City,

Customer.State,

Customer.Country

FROM

CustDetails

INNER JOIN

Customer

CustDetails.CustomerID = Customer.CustomerID

ORDER BY

CASE WHEN @SortExpression='CustomerID' THEN Customer.CustomerID END ASC,

CASE WHEN @SortExpression='CustomerID DESC' THEN Customer.CustomerID END DESC

Notice that the store procedure uses SQL CASE function to sort the records before they are added to query. Unfortunately, you can’t use a parameter with an ORDER BY clause, so the sort columns must be hard-coded in the CASE functions.

Html markup look for gridview look like below.

<div>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<asp:GridView ID="gvPagingSorting" DataSourceID="Customer" AllowSorting="true" AllowPaging="true"

PageSize="5" DataKeyNames="CustomerID" runat="server" CellPadding="4" ForeColor="#333333"

GridLines="None">

</asp:GridView>

<asp:ObjectDataSource ID="Customer" TypeName="ObjCustomer" SortParameterName="SortExpression"

SelectCountMethod="GetCustomerCount" EnablePaging="true" SelectMethod="GetCustomer"

runat="server"/>

</ContentTemplate>

</asp:UpdatePanel>

</div>

objCustomer class look like below

using System;

using System.Web;

using System.Data;

using System.Data.SqlClient;

using System.Web.Configuration;

public class ObjCustomer

{

private readonly string conStr;

public ObjCustomer()

{

conStr = WebConfigurationManager.ConnectionStrings["MyBlogConnectionString"].ConnectionString;

}

public SqlDataReader GetCustomer(int StartRowIndex, int MaximumRows, String SortExpression)

{

try

{

SqlConnection con = new SqlConnection(conStr);

SqlCommand cmd = new SqlCommand("GetCustomer", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@StartRowIndex",StartRowIndex);

cmd.Parameters.AddWithValue("@MaximumRows",MaximumRows);

cmd.Parameters.AddWithValue("@SortExpression", SortExpression);

con.Open();

return cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (Exception ex)

{

throw ex;

}

public int GetCustomerCount()

{

try

{

HttpContext context = HttpContext.Current;

if (context.Cache["CustomerCount"] == null)

{

SqlConnection con = new SqlConnection(conStr);

string sqlQuery = "SELECT COUNT(CUSTOMERID) FROM [Customer]";

SqlCommand cmd = new SqlCommand(sqlQuery, con);

cmd.Connection.Open();

context.Cache["CustomerCount"] = (int)cmd.ExecuteScalar(); ;

}

return (int)context.Cache["CustomerCount"];

}

catch (Exception ex)

{

throw ex;

}

Explanation of class file :-

GetCustomer() method pass three parameters, StartRowIndex, MaximumRows and SortExpression. First two parameter used for paging and last parameter used for sorting. GetCustomer() method used for ObjectDataSource property SelectMethod. GetCustomerCount() method used for getting total count for customer from DB. GetCustomerCount() method used for ObjectDataSource property SelectCountMethod.

Note :-
One important thing to note about GetCustomer() method is that, return type of method is SqlDataReader(forward-read only) which will also help in improving performance. Of course, returning only grid pagesize should not be major problem even if you use datatable or dataset. Just once I remember user asking question in asp.net forum, whether its possible paging with datareader ?. So, I think this is the answer.

Output looks like below :-

Hope you like this article.

Kindly Note down changes for this article made on 24th nov 2009

I had modify the store procedure to work correctly with sorting for all grid pages. Previously, only current page was sorting by that field for that particular page size. And, next page will sort again. So, I modify that store procedure which i change download link on top. And re-writing procedure down also

ALTER PROC [dbo].[GetCustomer]

(

@StartRowIndex INT,

@MaximumRows INT,

@SortExpression VARCHAR(100)

)

WITH CustDetails AS

(

SELECT CustomerID, ROW_NUMBER() OVER (ORDER BY

CASE WHEN @SortExpression='CustomerID' THEN Customer.CustomerID END ASC,

CASE WHEN @SortExpression='CustomerID DESC' THEN Customer.CustomerID END DESC,

CASE WHEN @SortExpression='FirstName' THEN Customer.FirstName END ASC,

CASE WHEN @SortExpression='FirstName DESC' THEN Customer.FirstName END DESC,

CASE WHEN @SortExpression='MiddleName' THEN Customer.MiddleName END ASC,

CASE WHEN @SortExpression='MiddleName DESC' THEN Customer.MiddleName END DESC,

CASE WHEN @SortExpression='LastName' THEN Customer.LastName END ASC,

CASE WHEN @SortExpression='LastName DESC' THEN Customer.LastName END DESC,

CASE WHEN @SortExpression='Desgination' THEN Customer.Desgination END ASC,

CASE WHEN @SortExpression='Desgination DESC' THEN Customer.Desgination END DESC,

CASE WHEN @SortExpression='Address' THEN Customer.Address END ASC,

CASE WHEN @SortExpression='Address DESC' THEN Customer.Address END DESC,

CASE WHEN @SortExpression='City' THEN Customer.City END ASC,

CASE WHEN @SortExpression='City DESC' THEN Customer.City END DESC,

CASE WHEN @SortExpression='State' THEN Customer.State END ASC,

CASE WHEN @SortExpression='State DESC' THEN Customer.State END DESC,

CASE WHEN @SortExpression='Country' THEN Customer.Country END ASC,

CASE WHEN @SortExpression='Country DESC' THEN Customer.Country END DESC) AS RowNo

FROM CUSTOMER

)

SELECT Customer.CustomerID,

Customer.FirstName,

Customer.MiddleName,

Customer.LastName,

Customer.Desgination,

Customer.Address,

Customer.City,

Customer.State,

Customer.Country

FROM CustDetails INNER JOIN

Customer ON CustDetails.CustomerID = Customer.CustomerID

WHERE (RowNo BETWEEN @StartRowIndex + 1 AND @StartRowIndex + @MaximumRows + 1)

Dear All reader,
I had changed store procedure for sorting purpose, which you can download with new link provided on top.

Thanks,
Manoj Karkera

Manoj karkera - Tuesday, November 24, 2009 4:21:16 PM

Nice Article Manoj..I enjoyed reading it. How about adding or writing new article on custom Gridview Paging?

Thanks
Pankaj

mishra.p - Tuesday, January 19, 2010 12:52:28 AM

You want only to do custom paging and not sorting ? correct me if I am wrong ? In that case, you have to modify above code and a sort of SP.

Manoj karkera - Tuesday, January 19, 2010 2:58:20 AM

Thanks a million! I too was having the exact sort issue as you. Moving the sort order case from the 'order by' to the 'ROW_NUMBER OVER' saved my !@#. Thanks for sharing.

Scott - Tuesday, January 19, 2010 4:22:49 PM

Thanks for appreciating my work, which will help me to do more articles

Manoj karkera - Wednesday, January 20, 2010 2:40:28 AM

it is tomuch that i cant change all my 30 procedures for just sorting and pass the parameters by this way colud you please send any good one for only front end changes
Thanks
Jalpa

jalparushi - Tuesday, May 3, 2011 3:22:54 AM

you can do that, but performance issue might occured

Manoj karkera - Tuesday, May 3, 2011 1:36:03 PM

7 Comments