|
Developer's Guide to ASP.NET 3.5 |
|
Master ASP.NET 3.5 development using C# and Visual Studio.NET 2008. Web forms, server controls, data binding, AJAX, ASMX and WCF services and more... |
|
|
Beat stress - learn Kriya Yoga Meditations |
|
A six week online course in our style of Kriya Yoga is available absolutely FREE. Very few websites teach you the ancient art and science of Kriya Yoga in such a systematic and authentic manner. |
|
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.