Paged Result Sets

Business Logic Framework - Tutorial 18:

This tutorial demonstrates a very powerful capability built into Quick Objects allow the developer to create a paged result set by specify two property values.

Before we get started with the details, lets review what is a paged result set and why it is important. Today almost all web based applications allow you to view a certain number of records at a given time while the others can be accessed by taking an action on the user interface. It would be not be practical to display a large number of records at the same time on a web page or even on a regular windows form for that matter. Therefore, a practice called paging is used to divide the number of records into multiple pages, where each page represents a subset of records. Default paging behavior of the GridView/DataGrid in ASP.NET is to display only the page that has been requested by the user, however the query from the database results in the entire set of records being returned. This is essentially a wastage of resources in many scenarios. If the returned number of rows was small then it may not impact your application's performance, however as the application grows or the user requests a large set of records then the default approach can severely impact your application's performance. To provide the developer with more control the GridView/DataGrid controls provide support for custom paging. Custom paging is a way for the developer to manually return a subset of the rows that are going to be visible to the user instead of returning all the records.

A paged result set is a way to represent a subset of records out of a given set of records. Essentially a Paged result set is what you will need to create to support the custom paging functionality of GridView/DataGrid controls, or the custom paging or virtual load functionality provided by many other third party controls. For example, if query would normally return 150 records the capability to show the first 20 and then the next 20 and so on is called paging, however if the query is modified to return only the set of 20 records that is to be shown that result set is called a paged result set. Many people give different names for a paged result set, but our purpose is to illustrate how you can achieve your target easily.

In Quick Objects, every business object provides two properties called StartRowIndex and PageSize, these allow the business object to use advanced techniques and create a paged result set from the provided parameters. Paged result sets use database specific techniques for ultimate performance, and this feature is only supported for the database server that provide this functionality. Since, SQL Server 2000 does not provide any means to create paged result sets, this functionality is not supported if the database type is SQL Server 2000. This functionality however is fully supported when the database type is SQL Server 2005 or Oracle (all versions).

C#
VB.NET

1
// The purpose of this tutorial is to demonstrate the ability of the business objects to created paged result sets automatically.  

2
// The technique used by the business objects is efficient and creates the paged result sets at the database level.  

3
// This ensures that only the needed amount of data is retrieved and transported from the database to the UI.  

4
 

5
// There is no need to write database server specific paged queries and just specifying two property values in Quick Objects adds the paged  

6
// result set support.  

7
// Please Note: Paging is only supported in SQL Server 2005 and all versions of Oracle.  

8
 

9
using (Customers customers = new Customers())  

10
{  

11
 

12
    // StartRowIndex value is used to specify the starting row location in the result set.  

13
    // The StartRowIndex value is based on a zero based index and hence it is easy to use with user interface controls that use zero based index paging.  

14
    // For example: If you want to return the first 2 records, you will set the StartRowIndex value to 0 and PageSize value to 2.  

15
    // When you want to return the second page, you will set the StartRowIndex value to 2 and PageSize value to 2.  

16
    customers.StartRowIndex = 2;  

17
 

18
    // PageSize value controls how many rows are returned. The value can be any number greater than zero.  

19
    customers.PageSize = 2;  

20
 

21
 

22
    // Find method supports paged result sets by automatically creating a database optimized paged query for the database server in use.  

23
    customers.Find();  

24
 

25
    // We can check if any records were returned during the Find command and attach the ResultSet to the GridView's DataSource property.  

26
    if (customers.AffectedRecords > 0)  

27
    {  

28
        this.GridView1.DataSource = customers.ResultSet;  

29
        this.GridView1.DataMember = customers.GetResultSetName();  

30
    }  

31
    else 

32
    {  

33
        this.GridView1.DataSource = null;  

34
    }  

35
}  

36
 

1
' The purpose of this tutorial is to demonstrate the ability of the business objects to created paged result sets automatically.  

2
' The technique used by the business objects is efficient and creates the paged result sets at the database level.  

3
' This ensures that only the needed amount of data is retrieved and transported from the database to the UI.  

4
 
5
' There is no need to write database server specific paged queries and just specifying two property values in Quick Objects adds the paged  

6
' result set support.  

7
' Please Note: Paging is only supported in SQL Server 2005 and all versions of Oracle.  

8
 
9
Using customers As New Customers()  

10
 
11
    ' StartRowIndex value is used to specify the starting row location in the result set.  

12
    ' The StartRowIndex value is based on a zero based index and hence it is easy to use with user interface controls that use zero based index paging.  

13
    ' For example: If you want to return the first 2 records, you will set the StartRowIndex value to 0 and PageSize value to 2.  

14
    ' When you want to return the second page, you will set the StartRowIndex value to 2 and PageSize value to 2.  

15
    customers.StartRowIndex = 2  

16
 
17
    ' PageSize value controls how many rows are returned. The value can be any number greater than zero.  

18
    customers.PageSize = 2  

19
 
20
 
21
    ' Find method supports paged result sets by automatically creating a database optimized paged query for the database server in use.  

22
    customers.Find()  

23
 
24
    ' We can check if any records were returned during the Find command and attach the ResultSet to the GridView's DataSource property.  

25
    If customers.AffectedRecords > 0 Then 

26
        Me.GridView1.DataSource = customers.ResultSet  

27
        Me.GridView1.DataMember = customers.GetResultSetName()  

28
    Else 

29
        Me.GridView1.DataSource = Nothing 

30
    End If 

31
End Using  

32

Send comments on this topic.

1	// The purpose of this tutorial is to demonstrate the ability of the business objects to created paged result sets automatically.
2	// The technique used by the business objects is efficient and creates the paged result sets at the database level.
3	// This ensures that only the needed amount of data is retrieved and transported from the database to the UI.
4
5	// There is no need to write database server specific paged queries and just specifying two property values in Quick Objects adds the paged
6	// result set support.
7	// Please Note: Paging is only supported in SQL Server 2005 and all versions of Oracle.
8
9	using (Customers customers = new Customers())
10	{
11
12	// StartRowIndex value is used to specify the starting row location in the result set.
13	// The StartRowIndex value is based on a zero based index and hence it is easy to use with user interface controls that use zero based index paging.
14	// For example: If you want to return the first 2 records, you will set the StartRowIndex value to 0 and PageSize value to 2.
15	// When you want to return the second page, you will set the StartRowIndex value to 2 and PageSize value to 2.
16	customers.StartRowIndex = 2;
17
18	// PageSize value controls how many rows are returned. The value can be any number greater than zero.
19	customers.PageSize = 2;
20
21
22	// Find method supports paged result sets by automatically creating a database optimized paged query for the database server in use.
23	customers.Find();
24
25	// We can check if any records were returned during the Find command and attach the ResultSet to the GridView's DataSource property.
26	if (customers.AffectedRecords > 0)
27	{
28	this.GridView1.DataSource = customers.ResultSet;
29	this.GridView1.DataMember = customers.GetResultSetName();
30	}
31	else
32	{
33	this.GridView1.DataSource = null;
34	}
35	}
36

1	' The purpose of this tutorial is to demonstrate the ability of the business objects to created paged result sets automatically.
2	' The technique used by the business objects is efficient and creates the paged result sets at the database level.
3	' This ensures that only the needed amount of data is retrieved and transported from the database to the UI.
4
5	' There is no need to write database server specific paged queries and just specifying two property values in Quick Objects adds the paged
6	' result set support.
7	' Please Note: Paging is only supported in SQL Server 2005 and all versions of Oracle.
8
9	Using customers As New Customers()
10
11	' StartRowIndex value is used to specify the starting row location in the result set.
12	' The StartRowIndex value is based on a zero based index and hence it is easy to use with user interface controls that use zero based index paging.
13	' For example: If you want to return the first 2 records, you will set the StartRowIndex value to 0 and PageSize value to 2.
14	' When you want to return the second page, you will set the StartRowIndex value to 2 and PageSize value to 2.
15	customers.StartRowIndex = 2
16
17	' PageSize value controls how many rows are returned. The value can be any number greater than zero.
18	customers.PageSize = 2
19
20
21	' Find method supports paged result sets by automatically creating a database optimized paged query for the database server in use.
22	customers.Find()
23
24	' We can check if any records were returned during the Find command and attach the ResultSet to the GridView's DataSource property.
25	If customers.AffectedRecords > 0 Then
26	Me.GridView1.DataSource = customers.ResultSet
27	Me.GridView1.DataMember = customers.GetResultSetName()
28	Else
29	Me.GridView1.DataSource = Nothing
30	End If
31	End Using
32