.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using ADO

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  
 

 

Using ADO

Another option for implementing paging is to use COM-based ADO to do the paging. The primary motivation behind this option is to gain access to server-side cursors, which are exposed through the ADO Recordset object. You can set the Recordset cursor location to adUseServer. If your OLE DB provider supports it (SQLOLEDB does), this will result in the use of a server-side cursor. You can then use this cursor to navigate to the starting record directly without having to pull all of the records across the network to the data access client code.

There are two primary drawbacks to this approach:

  • In most cases, you will want to translate the records returned in the Recordset object into a DataSet for use in your client managed code. Although OleDbDataAdapter does overload the Fill method to take an ADO Recordset object and translate that into a DataSet, there is no facility to start and end with a particular record. The only realistic option is to move to the start record in the Recordset object, loop through each record, and manually copy the data to a new manually generated DataSet. It is possible that doing this, particularly through the overhead of COM Interop calls, will more than negate the benefits of not pulling extra data across the network, especially for small DataSet (s).
  • During the time it takes to pull the data that you want from the server, you hold open a connection and a server-side cursor. Cursors are typically an expensive resource to open and maintain on a database server. Although this option might increase your performance, it is also likely to diminish your scalability by consuming valuable resources on the server for extended periods of time.

Using a Manual Implementation

The final option discussed in this section for paging through your data is to manually implement paging functionality for your application through the use of stored procedures. For tables that contain a unique key, you can implement the stored procedure relatively easily. For tables without a unique key (and you shouldn't have many of those), the process is more complicated.

Paging Against a Table with a Unique Key

If your table contains a unique key, you can use the key in a WHERE clause to create a result set starting from a specific row. This, coupled with the SET ROWCOUNT statement or the SQL Server TOP statement used to restrict the size of the result set, provides an effective paging mechanism. This approach is illustrated in the following stored procedure code:

CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
  

The caller of this stored procedure simply maintains the lastProductID value and increments or decrements it by the chosen page size between successive calls.

Paging Against a Table Without a Unique Key

If the table through which you want to page doesn't have a unique key, consider adding one-for example, by using an identity column. This will enable you to implement the paging solution discussed previously.

It is still possible to implement an effective paging solution for a table with no unique key, as long as you can generate uniqueness by combining two or more other fields that are part of the result set.

For example, consider the following table:

Col1

Col2

Col3

Other columnsç

A

1

W

ç

A

1

X

ç

A

1

Y

ç

A

1

Z

ç

A

2

W

ç

A

2

X

ç

B

1

W

ç

B

1

X

ç

With this table, it is possible to generate uniqueness by combining Col1, Col2, and Col3. As a result, you can implement a paging mechanism by using the approach illustrated in the following stored procedure.

CREATE PROCEDURE RetrieveDataPaged
@lastKey char(40),
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT
Col1, Col2, Col3, Col4, Col1+Col2+Col3 As KeyField
FROM SampleTable
WHERE [Standard search criteria]
AND Col1+Col2+Col3 > @lastKey
ORDER BY Col1 ASC, Col2 ASC, Col3 ASC
GO
  

The client maintains the last value of the KeyField column returned by the stored procedure and plugs it back in to the stored procedure to control paging through the table.

Although the manual implementation increases the strain placed on the database server, it avoids passing unnecessary data over the network. Performance tests have shown this approach to work well across a range of stress levels. However, depending on how much of the work of your site involves data paging functionality, performing manual paging on the server might impact the scalability of your application. You should run performance tests in your own environment to find the optimum approach for you specific application scenario.

Shashi Ray


 Subscribe to Articles

     

Further Readings:

    Responses

    No response found. Be the first to respond this post

    Post Comment

    You must Sign In To post reply
    Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend