Monday, May 12, 2014

Optimized paging in Infragistics WebDataGrid

In Infragistics WebDataGrid the Paging and sorting are easy. You can find the sample code at the following location: http://www.infragistics.com/products/aspnet/sample/data-grid/custom-databinding-paging-collection. If we want to work with large data sets, thousand or million  of records, querying all the data for every page click is not effective. Then we need to go for a optimized approach. This article describes how to go for a scalable application. This is applicable for ASP GridView as well.

I am using Northwind Customers database table for query the data. As the first step we need to create a stored procedure in SQL. The stored procedure should contain some techniques to work with page size and start index. Following is the stored procedure used:

Create PROCEDURE spGetAllCustomersForGrid
(
    @startIndex     int,
    @pageSize       int,
    @sortBy     nvarchar(30)   
)

AS
SET NOCOUNT ON
DECLARE

@sqlStatement nvarchar(max),  
@upperBound int

IF @startIndex  < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @upperBound = @startIndex + @pageSize

SET @sqlStatement = ' SELECT C.CustomerID,
                        C.CompanyName,
                        C.ContactName,
                        C.ContactTitle,
                        C.Address,
                        C.City,
                        C.Region,
                        C.PostalCode,
                        C.Country,
                        C.Phone,
                        C.Fax
                        FROM (
                        SELECT  ROW_NUMBER() OVER(ORDER BY '
+ @sortBy + ') AS rowNumber, *
                        FROM    Customers
                        )
                        AS C
                        WHERE  rowNumber >= '
+ CONVERT(varchar(9), @startIndex) + ' AND
                        rowNumber <  '
+ CONVERT(varchar(9), @upperBound)

exec (@sqlStatement)

This stored procedure uses start index, page size and sorting expression as input parameters. Start index is the starting item of the selected page. Page size is the no.of records to displayed in the grid. Sorting expression sort the data set async.

We have another stored procedure to get the total no.of records. The following is sp will return the total no.of records:

CREATE PROCEDURE TotalCustomers
AS
BEGIN

    SET NOCOUNT ON
    SELECT COUNT(*) FROM Customers
END

Now open Visual Studio and create a new ASP.NET application. Add a WebDataGrid to the aspx page. Following is the aspx page source:

<div>
    <asp:ScriptManager runat="server"></asp:ScriptManager>
        <ig:WebDataGrid ID="WebDataGrid1" runat="server" DataKeyFields="CustomerID" Width="100%"
        Height="467px" DefaultColumnWidth="90px" OnCustomDataBinding="WebDataGrid1_CustomDataBinding">
        <Behaviors>
            <ig:Paging PageSize="5" />
        </Behaviors>
    </ig:WebDataGrid>
</div>

We are creating a entity class to map the customer object with all the properties.

public class CustomerEntity
    {
        public string CustomerID { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public string ContactTitle { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Region { get; set; }
        public string PostalCode { get; set; }
        public string Country { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
    }

Open the code behind of the aspx page and create a method to query the records from database. Use the following method to query the database:

public List<CustomerEntity> GetCustomersInRange(int startIndex, int count)
        {
            CustomerEntity customer;
            List<CustomerEntity> customerList = new List<CustomerEntity>();

            using (SqlConnection conn = new SqlConnection("your connection string"))
            {
                SqlDataReader rdr = null;

                SqlCommand cmdSelect = new SqlCommand();

                conn.Open();
                cmdSelect.CommandText = "spGetAllCustomersForGrid";
                cmdSelect.CommandType = CommandType.StoredProcedure;
                cmdSelect.Connection = conn;

                int rangeCount = count;
                int repositoryCount = DataCount();

                if (startIndex + count > repositoryCount)
                    rangeCount = repositoryCount - startIndex;

                cmdSelect.Parameters.AddWithValue("@startIndex", startIndex);
                cmdSelect.Parameters.AddWithValue("@PageSize", rangeCount);
                cmdSelect.Parameters.AddWithValue("@sortBy", "CustomerId");
               
                rdr = cmdSelect.ExecuteReader();

                while (rdr.Read())
                {
                    customer = new CustomerEntity();

                    customer.CustomerID = rdr["CustomerID"].ToString();
                    customer.CompanyName = rdr["CompanyName"].ToString();
                    customer.ContactName = rdr["ContactName"].ToString();
                    customer.ContactTitle = rdr["ContactTitle"].ToString();
                    customer.Address = rdr["Address"].ToString();
                    customer.City = rdr["City"].ToString();
                    customer.Region = rdr["Region"].ToString();
                    customer.PostalCode = rdr["PostalCode"].ToString();
                    customer.Country = rdr["Country"].ToString();
                    customer.Phone = rdr["Phone"].ToString();
                    customer.Fax = rdr["Fax"].ToString();

                    customerList.Add(customer);
                }

                return customerList; 
            }


Then create a method to get the total count of records.

public int DataCount()
        {
            //Add code to access TotalCustomer SP
        } 

Now you are about to complete the last few steps. Define the data source at the page load event and at the custom data binding event call for our method developed for query.

protected void Page_Load(object sender, EventArgs e)
        {
            WebDataGrid1.DataSource = new List<CustomerEntity>();
        }

protected void WebDataGrid1_CustomDataBinding(object sender, DataBindingEventArgs e)
        {
            WebDataGrid grid = sender as WebDataGrid;
            grid.ScrollTop = 0;
            int currentPage = grid.Behaviors.Paging.PageIndex;
            int pageSize = grid.Behaviors.Paging.PageSize;
            int startIndex = currentPage * pageSize;

            e.Cancel = true;
           
            e.DataSource = GetCustomersInRange(startIndex, pageSize);
            e.SelectArguments.TotalRowCount = DataCount();
        }

Run the application and browse your aspx page, you will get the result.



You may test how the query works. You can use SQL Server Profiler to check the query execution. Since the page size is 5, the db returns only 5 records at a page click. It will not return all the records as the normal procedure.


3 comments:

  1. Hey Akbar nice post. can you please just clarify how you get the all the page numbers bottom of grid since initially the loading 5 records .
    thanks
    Mohammed

    ReplyDelete
  2. at this point filtering is not working. caused by e.Cancel = true

    ReplyDelete