ASP.NET Data Repeater control and database level paging

ASP.NET Data Repeater control

ASP.NET repeater control basically used for showing data that needs to be shown in the repeated format. It is lightweight 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 detailed data in GridView or DataRepeater control in paged access to data instead of showing all rows on the 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 the 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 the database it will load all the rows and truncate all the rows except 10 rows which require on page 5.

So this method requires fetching unnecessary data on wire and a lot of I/O operations 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 the 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 the 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 the 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 a specific column where you required the ranking.
2. Use ROW_NUMBER() function to rank each row of the result set. ROW_NUMBER() function is introduced with 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 the below example, we are retrieving a result set from the customer table sorted on the CustomerID column. It returns only the number of rows that are specified by PageSize and ranked with a 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 that will represent the customer entity. Use the 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 that will talk to the database, execute the stored proc, apply business logic if required, and sends the list of customers to the 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; 
}
    
    

Creating a 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 makes 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 the 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 the 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 the CreatePagingControl method calls GetCustomerCount from CustomerService which returns all customers(without filtering) from the 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 the 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.

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 .

Source Code on Github