Paging ASP.NET ListView Control with a DataPagerControl

Today I would like to discuss paging a listview control. First I will show you paging a list view that uses a SqlDataSource control then I will show you paging a list view that does not use a data source control.

The DataPager control makes paging almost effortless when used in conjunction with a DataSource control as you will see.

You will need a database to query. Fortunately in the downloadable project there is a database included along with all the code examples we will be discussing.

On your .aspxpage add the following markup:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString='<%$ ConnectionStrings:DefaultConnection %>' SelectCommand="SELECT [TestCol1], [TestCol2], [TestCol3] FROM [TestTable]"></asp:SqlDataSource>
<asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="TestCol1" GroupItemCount="3">

    <AlternatingItemTemplate>
        <td runat="server" style="background:#808080;color:#ffffff;">TestCol1:
            <asp:Label Text='<%# Eval("TestCol1") %>' runat="server" ID="TestCol1Label" /><br /> TestCol2:
            <asp:Label Text='<%# Eval("TestCol2") %>' runat="server" ID="TestCol2Label" /><br /> TestCol3:
            <asp:Label Text='<%# Eval("TestCol3") %>' runat="server" ID="TestCol3Label" /><br />
        </td>
    </AlternatingItemTemplate>


    <EmptyDataTemplate>
        <table runat="server">
            <tr>
                <td>No data was returned.</td>
            </tr>
        </table>
    </EmptyDataTemplate>
    <EmptyItemTemplate>
        <td runat="server" />
    </EmptyItemTemplate>
    <GroupTemplate>
        <tr runat="server" id="itemPlaceholderContainer">
            <td runat="server" id="itemPlaceholder"></td>
        </tr>
    </GroupTemplate>


    <ItemTemplate>
        <td runat="server">TestCol1:
            <asp:Label Text='<%# Eval("TestCol1") %>' runat="server" ID="TestCol1Label" /><br /> TestCol2:
            <asp:Label Text='<%# Eval("TestCol2") %>' runat="server" ID="TestCol2Label" /><br /> TestCol3:
            <asp:Label Text='<%# Eval("TestCol3") %>' runat="server" ID="TestCol3Label" /><br />
        </td>
    </ItemTemplate>
    <LayoutTemplate>
        <table runat="server">
            <tr runat="server">
                <td runat="server">
                    <table runat="server" id="groupPlaceholderContainer" border="0">
                        <tr runat="server" id="groupPlaceholder"></tr>
                    </table>
                </td>
            </tr>
            <tr runat="server">
                <td runat="server">
                    <asp:DataPager runat="server" PageSize="5" ID="DataPager1">
                        <Fields>
                            <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" ShowNextPageButton="False" ShowPreviousPageButton="False"></asp:NextPreviousPagerField>
                            <asp:NumericPagerField></asp:NumericPagerField>
                            <asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True" ShowNextPageButton="False" ShowPreviousPageButton="False"></asp:NextPreviousPagerField>
                        </Fields>
                    </asp:DataPager>
                </td>
            </tr>
        </table>
    </LayoutTemplate>

</asp:ListView>

We have added a SqlDataSource control, a ListView control and a DataPager control. In the ListView control we have defined an EmptyDataTemplate which, as the name suggests, is displayed if no datahas been returned.

We have also defined an ItemTemplate which defines the layout of each individual item,A GroupTemplate which contains a placeholder for the item and a LayoutTemplate which defines the layout of the items and also contains our DataPager control.

If you run the application you’ll see that it displays the first 5 items and when you click the little number 2 you’ll see the final 2 items. And that’s it you now have a ListView that pages through data without writing a single line of C# code. Wonderful isn’t it? It is so easy that anyone could do it. A group of ASP.NET controls that work together in complete harmony straight out of the box.

However if you have ever tied this without a data source control you will know that some strange behavior occurs. What was so easy only moments ago, in the previous example, suddenly becomes a bit of a nightmare. To demonstrate thisI will show you what happens when you try to page a list view without a Sql Data Source. In the downloadable project included with this post there is a webform called Default2.aspx. On that webform is two list views, the next example is taken from the list view on the top half of the page.

On your .aspx page add the following markup:

<asp:Label ID="lblError" runat="server"></asp:Label>
<asp:ListView ID="ListView1" runat="server" GroupItemCount="3">
    <EmptyDataTemplate>
        <table runat="server">
            <tr>
                <td>No data was returned.</td>
            </tr>
        </table>
    </EmptyDataTemplate>
    <EmptyItemTemplate>
        <td runat="server" />
    </EmptyItemTemplate>
    <GroupTemplate>
        <tr runat="server" id="itemPlaceholderContainer">
            <td runat="server" id="itemPlaceholder"></td>
        </tr>
    </GroupTemplate>


    <ItemTemplate>
        <td runat="server">TestCol1:
            <asp:Label Text='<%# Eval("TestCol1") %>' runat="server" ID="TestCol1Label" /><br /> TestCol2:
            <asp:Label Text='<%# Eval("TestCol2") %>' runat="server" ID="TestCol2Label" /><br /> TestCol3:
            <asp:Label Text='<%# Eval("TestCol3") %>' runat="server" ID="TestCol3Label" /><br />
        </td>
    </ItemTemplate>
    <LayoutTemplate>
        <table runat="server">
            <tr runat="server">
                <td runat="server">
                    <table runat="server" id="groupPlaceholderContainer" border="0">
                        <tr runat="server" id="groupPlaceholder"></tr>
                    </table>
                </td>
            </tr>
            <tr runat="server">
                <td runat="server">
                    <asp:DataPager runat="server" PageSize="5" ID="DataPager1">
                        <Fields>
                            <asp:NumericPagerField></asp:NumericPagerField>

                        </Fields>
                    </asp:DataPager>
                </td>
            </tr>
        </table>
    </LayoutTemplate>

</asp:ListView>

As you can see the only difference between this example and the previous example is there is nodata source control present. Obviously it still requires a data source soin your code behind you will query the database to retrieve the data and populate the list view with the returned data.

Before we do that we need to add a reference to the connection string that is stored in the web.config file. I have added mine in above the Page_Load event.

private string connectionString =
    WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

Then we need the code that actually queries the database. To do this we will create a sql query, which we will place into a variable for ease of use.

string queryString = "select * from dbo.TestTable";

Then we need to create a Sql connection object which, as the name suggests connects to the database server.

SqlConnection con = new SqlConnection(connectionString);

After which we need to do something with this connection so we create a Sql command object which takes two parameters. It needs the command we need to perform, which is currently stored in a handy variable (called queryString) and it also requires the connection object.

SqlCommand cmd = new SqlCommand(queryString, con);

We will query the database and put the data returned into a dataset which is then used to populate the list view. So we create a new Sql data adapter and initialize it with the command object.

SqlDataAdapter adp = new SqlDataAdapter(cmd);

Now we need to attempt to open the connection to the database.

Con.Open();

The next step is to extract the required data and put it data into a dataset. To do that we create a new data set(the first of the following two lines) then we use the Sql Data Adapter to fill the newly created dataset with the data returned by the query (the second line). We do this by calling the sql data adapters fill method which takes two arguments

DataSet ds = new DataSet();
adp.Fill(ds, "TestTable");

Then we bind the data in the dataset to the list view. To do this we add the following two lines of code, which adds the data set we created as the data source of the listview then it binds the data to the list view.

ListView1.DataSource = ds;
ListView1.DataBind();

Now we need to close the connection to make sure your application runs efficiently and the server doesn’t get jammed up with lots of lingering connections left lying around. We do this by adding the following line of code.

con.Close();

Below is the complete code that queries the database and binds the list view. I have also added in some error handling code in the form of a Try…Catch…Finally code block.

protected void getData()
{
    string queryString = "select * from dbo.TestTable";
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand(queryString, con);
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    try
    {
        con.Open();
        DataSet ds = new DataSet();
        adp.Fill(ds, "TestTable");
        ListView1.DataSource = ds;
        ListView1.DataBind();

    } catch (Exception er)
    {
        lblError.Text = "Error retrieving data: " + er.Message;
    } finally
    {
        con.Close();
    }
}

Now we have the code to get the data we need to call it. We will do that in the Page_Load event and we will wrap it in an if statement which checks to make sure that it is not a post back as the data only needs to be called once, after which it is stored in memory in a data set. Below is the complete Page_Load event.

protected void Page_Load(object sender, EventArgs e)
{

    if (!this.IsPostBack)
    {
        getData();
    }

}

Go ahead and run the application and see what happens. It displays the first page just fine but when you click on the little number 2 you have to click it twice to see the next page but then you are unable to view the previous page again.Very annoying.

In order to correct this you will need to implement the following steps:

First add the ‘OnPagePropertiesChanging’ event to the listview. It is highlighted in yellow for convenience.

<asp:ListView ID="ListView1" runat="server" OnPagePropertiesChanging="ListView1_PagePropertiesChanging" GroupItemCount="3">

In your code behind you will need to add the event.

protected void ListView1_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e) {}

Then inside that event you will need to find the Data Pager control

DataPager dp = (DataPager)ListView1.FindControl("DataPager1");

On the line immediately after finding the data pager control you will need to call the data pagers SetPageProperties method. In that method you will set the index of the first record on the page of data, the maximum number of items on a single page of data and rebind the control to false.

dp.SetPageProperties(e.StartRowIndex, e.MaximumRows, false);

Because we have set the rebind property to false in the previous line we need to call our code that queried the database and data bound the control in the first place.

getData();

Your complete ListView1_PagePropertiesChanging event should look like this.

protected void ListView1_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
{
    DataPager dp = (DataPager) ListView1.FindControl("DataPager1");
    dp.SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
    getData();
}

Now run your application and you will see that it now performs the pagination correctly. And as a bonus, despite the initial frustration of discovering the nuances of paging without a Sql Data Source control, it was remarkably simple and easy to implement. Pagination is now demystified for all, I hope.

I have included a downloadable project for you to examine and play with at your leisure.I hope you found this tutorial helpful and easy to follow if you have any questions or comments please add them below in the comments section.

If you know of anyone who may find this article interesting or helpful then please share it around.