Asp.net Data Repeater control and database level paging

This article describes how to use asp.net data repeater control for paging with database level paging. This approach will give you significant performance for your page. Also this article will highlight some of the common things which you can use to improve your repeater controls performance.


Asp.net Data Repeater control

Asp.net repeater control basically used for showing data which needs to be shown in repeated format. It is light weight and faster control compare to GridView. Binding a DataRepeater control is easy however binding it with pagination is harder as it does not provide any built in events for paging and sorting like GridView. It iterates over the data source and produces output without applying additional formatting.


Database level paging

In web development it is common practice to show detail data in GridView or DataRepeater control in paged accessed to data instead of showing all rows on first load. There are two kinds of Paging

Default Paging : Very easy to implement, just required to set AllowPaging property to True to GridView or PagedDataSource. It loads all the data from datasource and at client side truncates unnecessary data with just showing the required data. For example you have DataRepeater with PageDatasource set for paging with PageSize is 10 and needs to show data on page index 5. From database it will load all the rows and truncate all the rows except 10 rows which requires on page 5.

So this method requires fetching unnecessary data on wire and lot of I/O opertions for truncate it resulting in poor performance of page. See more tips on ASP.NET Website performance improvements.

Database Level or Custom Paging : bit difficult to implement as it requires creating stored procedure or SQL which will fetch only required number of rows depending on PageSize and PageIndex. So it does not require to fetch all rows or truncation of rows resulting in better performance of page.

In this article we will create a stored procedure for custom paging and use it for asp.net DataRepeater controls paging. We will be using Northwind database and Customer table. Northwind database can be download here.

The stored procedure execution will happen in below way

  1. Sort the result set with specific column where you required the ranking.
  2. Use ROW_NUMBER() function to rank each row of result set. ROW_NUMBER() function is introduced in SQL server 2005 and is one of the function used for rankings. You can get more details at Generate Sequence number in SQL query.
  3. Filter the result set on Row Number as required by PageIndex and PageSize.

In below example we are retrieving result set from customer table sorted on CustomerID column. It returns only the number of rows which are specified by PageSize and ranked with specific number required for PageIndex.


CREATE PROCEDURE [dbo].[GetAllCustomers]

@PageIndex SMALLINT,
@PageSize SMALLINT
	
AS
BEGIN
 	
SET NOCOUNT ON;
	
DECLARE @FirstRow SMALLINT
DECLARE @LastRow SMALLINT
	
SET @FirstRow = @PageIndex * @PageSize 
SET @LastRow = @FirstRow + @PageSize + 1
    
SELECT CustomerID, CompanyName, ContactName, ContactTitle, 
    [Address],[City], Country
FROM
	(SELECT CustomerID, CompanyName, ContactName, ContactTitle, 
        [Address],[City], Country, 
		ROW_NUMBER() OVER(ORDER BY CustomerID ASC) AS CustomerRank
	FROM Customers
	) AS CustomerRowNumber
WHERE CustomerRank > @FirstRow AND
	CustomerRank < @LastRow

END
    
    

Creation of DataAccess Layer

Create a Customer class which will represent customer entity. Use below code and its properties to create it.


public class Customer
{
    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 Country { get; set; }
}
    
    

Create a CustomerService class which will talk to the database, execute the stored proc, apply business logic if required and send the list of customers to Presentation layer.

CustomerService class will have a method GetAllCustomers which returns the List<Customer> and accepts parameter pageIndex and pageSize of int, it specifies which set of rows are required for current page.
Write below code to your CustomerService class.


public static List<Customer> GetAllCustomers(int pageIndex, int pageSize)
{
    List<Customer> lstCustomers = new List<Customer>();   
    SqlConnection cnNorthwind = new SqlConnection();
            
    try
    {
        cnNorthwind.ConnectionString = 
            ConfigurationManager.ConnectionStrings["cnNorthwind"].ToString();
        SqlCommand cmdCustomers = new SqlCommand();
        cmdCustomers.CommandType = CommandType.StoredProcedure;
        cmdCustomers.CommandText = "GetAllCustomers";
        cmdCustomers.Parameters.Add(new SqlParameter("PageIndex", pageIndex));
        cmdCustomers.Parameters.Add(new SqlParameter("PageSize", pageSize));
        cmdCustomers.Connection = cnNorthwind;
        cnNorthwind.Open();

        IDataReader reader =
                cmdCustomers.ExecuteReader(CommandBehavior.SequentialAccess);

        while (reader.Read())
        {
            Customer customer = new Customer();
            customer.CustomerID = 
                reader.GetString(reader.GetOrdinal("CustomerID"));
            customer.CompanyName = 
                reader.GetString(reader.GetOrdinal("CompanyName"));
            customer.ContactName = 
                reader.GetString(reader.GetOrdinal("ContactName"));
            customer.ContactTitle = 
                reader.GetString(reader.GetOrdinal("ContactTitle"));
            customer.Address = 
                reader.GetString(reader.GetOrdinal("Address"));
            customer.City = 
                reader.GetString(reader.GetOrdinal("City"));
            customer.Country = 
                reader.GetString(reader.GetOrdinal("Country"));
                    
            lstCustomers.Add(customer);   
        }
        reader.Close(); 
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {   
        cnNorthwind.Close();
        cnNorthwind.Dispose(); 
    }
    return lstCustomers; 
}
    
    

Quick Note : By reading values from datareader like reader.GetString(reader.GetOrdinal("CustomerID")); improves your application performance as it does not require boxing and unboxing of values.


Creating Presentation Layer

Add new Customer.aspx to your web application or web site. As mentioned earlier use DataRepeater control to display Customer rows fetched by previous step.
Use below HTML to format your DataRepeater control.


<asp:Repeater ID="repCustomers" runat="server" EnableViewState="false">
<HeaderTemplate>
    <table>
        <tr style="color: White; background-color: #DF5015;
                        font-weight: bold;">
            <th>
                CustomerID
            </th>
            <th>
                CompanyName
            </th>
            <th>
                ContactName
            </th>
            <th>
                ContactTitle
            </th>
            <th>
                Address
            </th>
            <th>
                City
            </th>
            <th>
                Country
            </th>
        </tr>
        <tr>
            <td colspan="7">
                <hr />
            </td>
        </tr>
</HeaderTemplate>
<ItemTemplate>
    <tr>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).CustomerID %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).CompanyName %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).ContactName %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).ContactTitle %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).Address %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).City %>
        </td>
        <td>
            <%# ((NorthwindApp.Customer)Container.DataItem).Country %>
        </td>
    </tr>
</ItemTemplate>
<SeparatorTemplate>
    <tr>
        <td colspan="7">
            <hr />
        </td>
    </tr>
</SeparatorTemplate>
<FooterTemplate>
    <tr>
        <td colspan="7">
            <hr />
        </td>
    </tr>
    </table>
</FooterTemplate>
</asp:Repeater>
<br />
<table>
    <tr style="width: 100%">
        <td style="padding-left: 300px">
            <asp:PlaceHolder ID="plcPaging" runat="server" />
        </td>
    </tr>
</table>
    
    

Quick Notes : 1. It make sense to disable ViewState of repeater control as on every request the data will be different.
2. Do not use DataBinder.Eval method to bind column details to DataRepeater or GridView control as it uses Reflection to convert data instead use
<%# ((NorthwindApp.Customer)Container.DataItem).CustomerID %>
3. We added a PlaceHolder plcPaging for adding paging numbers.

Create Paging controls and events Add below code to your Customer.aspx.cs (code behind file) which will create the paging controls dynamically depending on the total number of rows (Without filtering) and set its event to fetch data for current page.


private void CreatePagingControl()
{
    int rowCount = CustomerService.GetCustomerCount();
    rowCount = (rowCount / PageSize) + 1;
            
    for (int i = 0; i < rowCount; i++)
    {
        LinkButton lnk = new LinkButton();
        lnk.Click += new EventHandler(lbl_Click);
        lnk.ID = "lnkPage" + (i + 1).ToString();
        lnk.Text = (i + 1).ToString();
        plcPaging.Controls.Add(lnk);
        Label spacer = new Label();
        spacer.Text = " ";
        plcPaging.Controls.Add(spacer);
    }
}

void lbl_Click(object sender, EventArgs e)
{
    LinkButton lnk = sender as LinkButton;
    int currentPage = int.Parse(lnk.Text);
            
    FetchData(currentPage);
}
    
    

Bind Data to DataRepeater Add below code which calls CustomerService's GetAllCustomer method, fetch data required for current page and bind it to DataRepeater


private const int PageSize = 10;

protected void Page_Load(object sender, EventArgs e)
{
    plcPaging.Controls.Clear();
    CreatePagingControl();
    if (!Page.IsPostBack)
    {
        FetchData(1); 
    }
}

private void FetchData(int pageIndex)
{
    repCustomers.DataSource = 
        CustomerService.GetAllCustomers(pageIndex - 1, PageSize);
    repCustomers.DataBind();
}
    
    

Things to do :
1. The below code from CreatePagingControl method calls GetCustomerCount from CustomerService which return all customers(with out filtering) from database. You can write it on your own method and stored proc or hard code the value for it. The working code with required stored proc and method is available in download link below.
2. If you wish to develop your DataAccessLayer using WCF RESTFul services Click here.

        
int rowCount = CustomerService.GetCustomerCount();
rowCount = (rowCount / PageSize) + 1; 
            
    

Your DataRepeater with database level paging is ready.

DataRepeater with custom paging

Here is a link for converting repeater control or entire webpage HTML to PDF using iTextSharp.

Here is a link for export repeater data to excel .

Download source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by saurabh at 10/11/2013 3:55:00 PM
What is NorthWindapp.Container in this code... my database name is student Sql server 2008 and i am using databinder to bind data to the repeater. please tell me how should i do having database of sql server2008...
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by saurabh at 10/11/2013 4:56:00 PM
What is CustomerService.GetCustomerCount()?
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by Laxmikant at 10/13/2013 8:54:00 PM
@Saurabh, your first question is not clear. CustomerService.GetCustomerCount() is method which gives you Total number of Customers in Northwind Database before applying any filter. You can download source code to see how it works.
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by saurabh at 10/14/2013 9:15:00 AM
Thank u sir I got it...And Thanks for this article which have helped me to complete my project...and its the best method of paging for repeater...Thanks a lot...Not this article of repeater is useful but all about repeater...
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by manish at 11/29/2013 8:51:00 PM
i did'nt find Stored Procedure GetAllCustomerCount
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by Laxmikant at 12/2/2013 12:19:00 AM
I updated the article for detail description of GetCustomerCount method, added required stored proc. Also updated the download link file to include required stored proc under SQL folder
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by smakers at 11/19/2014 7:43:00 AM
Please give correct link to example. Link http://dotnetmentors.com/CodeFiles/NorthwindApp.rar not works.
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by smakers at 11/19/2014 7:44:00 AM
Please give correct link to example. Link http://dotnetmentors.com/CodeFiles/NorthwindApp.rar not works.
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by Laxmikant at 11/24/2014 1:13:00 AM
@smakers, it works now ... please check
post comment on dotnetmentors.com