Tags: , , | Categories: Articles, Tutorials Posted by RTomlinson on 6/15/2010 10:15 PM | Comments (0)

The Telerik RadGrid is a great grid component for .NET developers and I can't recommend it, or the rest of the ASP.NET controls from Telerik, highly enough. The control has a vast amount of options, from drag and drop columns, filtering, templating and hierarchical databinding. Go and check out the demo's on their demo website. This post will cover setting up the data access for true data paging as well as hooking into the appropriate server side events

The grid can handle 300,000 rows with in-memory databinding, according to the Telerik website. When you potentially reach this upper limit and beyond you want to look at true data paging of results SQL Server side, which is made even more easier in SQL Server 2005 and later using Common Table Expressions. This is the most efficient way of data paging within SQL by supplying a page number and a page size to work out the record set to retrieve, improving website response time and saving memory allocation. See an example below:

CREATE PROCEDURE [dbo].[Web_Accounts_Get]
    @CompanyID int,
    @PageSize int = 10,
    @PageNumber int = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @PageRecordStart INT
    DECLARE @PageRecordEnd INT

    SET @PageRecordStart = ((@PageNumber - 1 ) * @PageSize) + 1
    SET @PageRecordEnd = @PageNumber * @PageSize 


    ;WITH Accounts_Page AS
    (
        SELECT Row_Number() OVER (ORDER BY [AccountName]) AS RowNo
            ,[Id]
          ,[CompanyID]
          ,[AccountID]
          ,[AccountName]
          ,[PostCode]
          ,[AccountTypeID]
          ,[DateUpdated]
          ,[DateAdded]
        FROM [dbo].[tblAccount] WITH (NOLOCK)
        WHERE [CompanyID] = @CompanyID
    )
    SELECT ,[Id]
          ,[CompanyID]
          ,[AccountID]
          ,[AccountName]
          ,[PostCode]
          ,[AccountTypeID]
          ,[DateUpdated]
          ,[DateAdded]
    FROM Accounts_Page
    WHERE RowNo BETWEEN @PageRecordStart and @PageRecordEnd 
    ORDER BY RowNo ASC;

END

The Telerik RadGrid exposes an OnNeedDataSource event that you can hook into to provide data access to bind to the grid. Here I use the RadGrid's CurrentPageIndex and PageSize properties to pass to my data access for our SQL CTE.

        protected void rdgrdAccounts_OnNeedDataSource(object sender, GridNeedDataSourceEventArgs e)
        {
            int startRowIndex = rdgrdAccounts.CurrentPageIndex + 1;
            int maximumRows = rdgrdAccounts.PageSize;

            rdgrdAccounts.VirtualItemCount = Account.CountForFilters(CompanyId, filters);
            rdgrdAccounts.DataSource = Account.GetAccounts(CompanyId, maximumRows, startRowIndex);
            
        }

In the next post I will cover allowing multiple checkbox selection of rows when using true data paging.

blog comments powered by Disqus