Resumé

SQL 2005 Row_Number and Paged Data

Cristian Merighi () 0.00

A quick example about custom paging enhancement to an ObjectDataSource properly fed. SQL Server 2005, DataObject marked classes and GridViews complete the scene.
This article is obsolete. Some functionalities might not work anymore. Comments are disabled.

Performance is, especially for a web app, the must pursuing goal right after that it ...has to work!
One of the most insidious bottle-necks is that one that binds a huge data base to the end-user interface.
There's a world of difficult choices in between, that may ease otherwise prevent a comfortable consultation.

Let us meet some friend that can be surely helpful in reaching the target:
.Net Framework 2.0 DataSourceObjects and some SQL Server 2005 new features.

Let's start from the database:
we have a table with tons of data! Avoid to put 'em all, at the same time into a poor (paged) GridView! Just load what you need to show and allow to browse back and forward.

DataBase technology: SQL Server 2005.

We'll use the already shown ROW_NUMBER() function, here's a sample stored procedure:

CREATE PROCEDURE [dbo].[sp_articles](
	@StartIndex int = 0,
	@Count int = 10
)
AS
BEGIN
	SET NOCOUNT ON;

	-- Query
	WITH ArticlesIndexed AS (
	SELECT ROW_NUMBER() OVER 
	(ORDER BY dteDate DESC) AS RowNumb, 
	Articles.*
	FROM Articles 
	)
	SELECT * FROM ArticlesIndexed 	
	WHERE RowNumb BETWEEN (@StartIndex+1) AND (@StartIndex + @Count)
	ORDER BY dteDate DESC
END

Here's another - trivial - one for counting pursposals:

SELECT COUNT(Articles.ID) FROM Articles

We have now to manage it all using our Business Logic Objects:

  • Article: class representing the single element,
  • ArticleCollection: collection of Articles,
  • ArticlesManager: static (Shared in VB.Net) DataObject class that manages Article objects.
class diagram

SelectMethod marked methods:

[System.ComponentModel.DataObjectMethod(DataObjectMethodType.Select)]
public static ArticleCollection GetPagedArticles(int maximumRows, int startRowIndex)
{
    // instances of ArticleCollection are constructed by arrays of DataRow.
    // this method calls the first stored: DAL.Bridge.GetArticles returns a DataTable.
    // [...] code omitted
    return new ArticleCollection(DAL.Bridge.Articles.GetArticles(maximumRows, startRowIndex).Select());
}

public static int GetArticlesCount()
{
    // this method calls the second stored: DAL.Bridge.GetArticlesCount returns an Int32 value.
    // [...] code omitted
    return DAL.Bridge.Articles.GetArticlesCount();
}
        

You can use the DataObjectMethodAttribute to identify data operation methods on a type marked with the DataObjectAttribute attribute so that they are more easily identified by callers using reflection.

ObjectDataSource and GridView dynamic-duo:

 <asp:GridView runat="server" ID="GV" AutoGenerateColumns="true"
DataSourceID="ODS" AllowPaging="True" PageSize="5" Width="100%">
    <PagerStyle HorizontalAlign="Center" />
</asp:GridView>
    
<asp:ObjectDataSource runat="server" ID="ODS" EnablePaging="True"
TypeName="BLL.ArticlesManager" 
SelectMethod="GetPagedArticles" 
SelectCountMethod="GetArticlesCount"></asp:ObjectDataSource>
 

It's fundamental to mention that, to make the whole thing work, SelectMethod must have the identical SelectCountMethod's signature plus the paging arguments:

  • maximumRows (Int32): related to GridView's PageSize property,
  • startRowIndex (Int32): 0-based index related to GridView's PageIndex property.

Take care. Bye.

feedback
 

Syndicate

Author

Cristian Merighi facebook twitter google+ youtube

Latest articles

Top rated

Archive

Where am I?

Author

Cristian Merighi facebook twitter google+ youtube

I'm now reading

Feeds