Meditation and Mindfulness for Software / IT Professionals. Conducted by Bipin Joshi in Thane. Read more...

Untitled 1

Improving Performance using ODS Custom Paging

Introduction

Displaying database records in paged fashion is a common requirement in web applications. ASP.NET 2.0 controls such as GridView and DetailsView have inbuilt paging capabilities. Many developers rely on inbuilt paging to speed up their job. However, inbuilt paging comes with its own price. Fortunately, Object Data Source (ODS) control also provides a way by which you can implement a custom paging scheme. Such custom paging scheme can improve performance of your web application as compared to inbuilt paging scheme. In this article I will explain with an example how the custom paging scheme of ODS works.

The Problem with Inbuilt Paging

Before we delve into custom paging let's understand the problem with inbuilt paging features. Let's assume that you have a GridView whose PageSize property is set to 10. That means at a time the grid is going to show you only 10 records and you can navigate between pages to see other records. This grid is bound with an ODS. The ODS is using Select() method of a class (say Customer) to fetch required data. Let's assume that the Customers table contains 100 records. The Select() method returns a DataSet populated with results of some query (say SELECT * FROM CUSTOMERS). Under inbuilt paging scheme the ODS and Select() method are fetching 100 rows but the grid needs only 10. That means you are unnecessarily fetching 90 extra rows! If your table contains reasonable amount of data then this overhead can be considerable.

The problem mentioned above can be eliminated by implementing a custom paging scheme that will ensure that only required number of rows (10 in above example) are fetched from the database.

The Solution

The Object Data Source control helps us build a custom paging solution that will eliminate the problem mentioned above. The solution involves designing your Select() method in a specific way and setting some properties of ODS.

To be specific you need to design your Select() method to match the following signature:

DataSet Select(<param1>,..., string orderby, 
int startRowIndex, int pageSize)

In the above signature the Select() method is returning a DataSet but it can return any other object such as DataReader, DataTable or generic collection. Have a look at the last two parameters of Select() method. These parameters and their position is very important. The startRowIndex parameter is intended to tell the Select() method about the start row number from which data is to be fetched. Similarly, pageSize parameter is intended to convey the total number of records to be fetched. The startRowIndex and pageSize parameters must be the last two parameters of the Select() method. These parameters are supplied automatically to your Select() method by ODS.

You might be wondering about the orderby parameter in the above signature. When you implement a custom paging scheme the default sorting scheme of GridView sorts only that page of data and not the complete result set. Luckily ODS provides a way to cater this problem also. The orderby parameter indicates the SortExpression on which the result set is to be sorted. This parameter is also supplied automatically by ODS. If your grid doesn't need sorting then you can skip this parameter from the signature.

Additionally you need to write another method that will tell ODS about the total number of records in a table. The signature of this method must be as follows:

int GetRecordCount(string orderby, int startRowIndex, 
int pageSize)

The GetRecordCount() method accepts the same three parameters orderby, startRowIndex and pageSize and returns an integer indicating total number of rows from the database table.

Once you create these methods you also need to tell ODS about them. This is done by setting the following properties of ODS:

  • SelectMethod : Specifies name of the method that is fetching data from the database (Select() in our example)
  • SelectCountMethod : Specifies name of the method that is returning total number of records from the underlying table (GetRecordCount() in our example)
  • StartRowIndexParameterName : Specifies name of the parameter of Select() method that accepts start row number (startRowIndex in our example)
  • MaximumRowsParameterName : Specifies name of the parameter of Select() method that accepts the total number of records to be fetched i.e. page size (pageSize in our example)
  • SortParameterName : Specifies name of the parameter of Select() method that accepts sort expression. This property needs to be set if you wish to implement sorting (see above discussion)

An Example

Let's build a simple web application that demonstrated the use of above properties and methods. In order to complete this example you need SQL Server 2005 with Northwind database in addition to Visual Studio.

Creating stored procedures in SQL Server 2005

First of all open SQL Server 2005 Management Studio. Navigate to Northwind database and create two stored procedures namely GetCustomers and GetCustomerCount. The complete TSQL script of these stored procedures is given below:

CREATE proc [dbo].[GetCustomers]
(@orderby varchar(50),@startrow int,@pagesize int)
As

declare @sql nvarchar(1000);
set @sql='
SELECT CustomerID,CompanyName,ContactName,Country
FROM
(SELECT  customerid,companyname,contactname,country,
 ROW_NUMBER() 
 OVER(ORDER BY ' + @orderby + ') as RowIndex
 FROM Customers
) 
as TempTable
WHERE 
RowIndex BETWEEN '
+ 
CONVERT(nvarchar(10),@startrow) 
+ 
' AND ('
+
CONVERT(nvarchar(10),@startrow)
+
' + '
+
CONVERT(nvarchar(10),@pagesize) 
+ 
') - 1 order by ' 
+ 
CONVERT(nvarchar(20),@orderby);

exec sp_executesql @sql

The GetCustomers procedure accepts three parameters - @orderby, @startrow and @pagesize. Inside it builds a SELECT query to fetch the rows from Customers table. The SELECT statement fetches only the rows as indicated by @pagesize parameter. This is done by using ROW_NUMBER() function of SQL Server 2005. Finally the SELECT statement is executed with the help of sp_ExecuteSql system stored procedure.

CREATE proc [dbo].[GetCustomerCount]
As
select count(*) from customers

The GetCustomerCount procedure is simple and returns total number of records from the Customers table.

Creating Customer class

Now create a new web site in Visual Studio using C# as the language. Add App_Code folder to it. Further add a new class called Customer to it.

Code the Customer class as shown below:

public class Customer
{
    private static string strConn;

    static Customer()
    {
        strConn = "data source=.;initial catalog
	=northwind;user id=sa;password=sa";
    }

    public static DataSet Select(string orderby,
    int startRowIndex, int maximumRows)
    {
        if (orderby == "")
        {
            orderby = "customerid";
        }
        SqlConnection cnn = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetCustomers";
        SqlParameter p1 = new SqlParameter
	("@orderby", orderby);
        SqlParameter p2 = new SqlParameter
	("@startrow", startRowIndex);
        SqlParameter p3 = new SqlParameter
	("@pagesize", maximumRows);
        cmd.Parameters.Add(p1);
        cmd.Parameters.Add(p2);
        cmd.Parameters.Add(p3);

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public static int GetCustomerCount(string orderby,
    int startRowIndex,int maximumRows)
    {
        SqlConnection cnn = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cnn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "GetCustomerCount";
        cnn.Open();
        object obj = cmd.ExecuteScalar();
        cnn.Close();
        return Convert.ToInt32(obj);
    }

}

The Customer class contains two static methods namely Select() and GetCustomerCount(). The signatures of these methods are as per our earlier discussion. The Select() method calls the GetCustomers stored procedure and returns the result set as a DataSet. Notice that we set orderby parameter to CustomerID if it is empty. This is done because when the grid renders for the first time the orderby parameter will be empty. Similarly, GetCustomerCount() method calls GetCustomerCount stored procedure and returns an integer indicating total number of records in the Customers table.

Creating a web form

Next, open the default web form. Drag and drop one ODS and one GridView on it. Run the configuration wizard of ODS and select business object to be Customer.

Set SELECT method of the ODS to Select().

After completing the wizard set the following additional properties:

Property Value
SelectCountMethod GetCustomerCount
StartRowIndexParameterName startRowIndex
MaximumRowsParameterName maximumRows
SortParameterName orderby

Now select the GridView and set its DataSourceID property to ObjectDataSource1.

Also enable paging and sorting for the GridView. Finally set PageSize property of GridView to 10. Now run the web form in the browser and your GridView should resemble as shown below:

Try navigating between pages. Also, try sorting the data.

That's it! Your custom paging scheme is ready.

 




Bipin Joshi is a software consultant, an author and a yoga mentor having 21+ years of experience in software development. He conducts online courses in ASP.NET MVC / Core, jQuery, and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced Yoga way of life he also teaches Meditation to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 01 Jul 2007



Tags : ASP.NET Web Forms Server Controls Data Controls