When query results exceed the display area then you resort to scrolling and wish you had some way to limit the number of results displayed and comfortable to view without scrolling. Paging functionality which provides an answer to this is therefore a much desired feature. The Data Pager Control in Visual Studio 2008 provides this functionality when you create an ASP.NET web form under .NET Framework 3.5. It can be configured automatically using the GUI, or it can be installed manually after installing the ListView. In this article both of them are described. While the number of items displayed in a list can be declaratively coded, it is possible to set it at page load time as well.

This article by Dr. Jayaram Krishnaswamy describes how you may connect to SQL Server 2008 and display the retrieved data in a ListView Control on a web page. The ListView Control is the only control in ASP.NET that supports the new ASP.NET control, the Data Pager Control. This article shows how the data from a table in SQL Server 2008 is displayed in the ListView and how the Data Pager is configured to cycle the List View items.

A direct connection to SQL Server 2008 is not possible with this version of SQL Server and Visual Studio 2008. One way to get around this is to use an ODBC connection to the SQL Server and then using the ODBC connection to retrieve the data. Another way described is to use the OLEDB connectivity option shown in this article.

Article Overview

We first create an ASP.NET Web Application project. To the default.aspx page we add a ListView control. Then we configure the ListView Control by configuring its data source and its displayed features. At this point a DataPager can be included as part of the ListView, but adding a DataPager manually is also shown. Controlling the number of displayed items in a page can be carried out using page load event code or declaratively.

Creating an ASP.NET Web Application

From the File menu item create a new project that opens up the window shown in the next figure. Make sure you are creating a .NET Framework 3.5 ASP.NET Web application project (use drop-down at top right of this window). The default name of the application has been changed to DataPager as shown. Click on the OK button to create the project.

The project is created with all the necessary files and the template for the Default.aspx page as shown. The Solution Explorer and the Class View of the project has all the information on this project.

The Split tab at the bottom of the 'Default.aspx' shows both the Design page as well as the HTML code for the page.

Adding a ListView Control and connecting to a Data Source

Drag and drop a ListView Control from the Toolbox on to the design page between the <div/> tags as shown. The Code is automatically generated as shown in the next figure. The ListView instance has a Id property "ListView1". Now you can configure the ListView using the Smart Tasks handle - the small arrow head [>] attached to the list view at the top right.

Click the Smart Task handle to open the list of tasks to be performed as shown. The only task you find here is the "Choosing the data source".

Configuring the Data Source

Now click on <New data source...>. This opens the Data Source Configuration Wizard. Click on Database icon which sets the stage for bringing data from SQL Server with an Id property "SQLDataSource1". This supports connecting to any ADO.NET datasource.

Click on the OK button in the above window. This opens the window where you need to choose the "Connection String", a very important item for connecting to a source of data.

Get started with SharePoint Designer and learn to put together a business website with SharePoint Become comfortable in the SharePoint Designer environment Learn about SharePoint Designer features as you create a SharePoint website Step-by-step instructions and careful explanations http://www.PacktPub.com/sharepoint-designer-tutorial/book

Click on the <New Connection...> button. This opens the Add Connection window with the default options displayed.

Click on the Change... button since we are interested in connecting to SQL Server 2008. Click on the <other> drop-down menu item and choose the .NET Framework Data Provider for OLEDB as shown.

Click on the OK button. This brings you back to the Add Connection window and you need to indicate the DataLinks. Click on the OLEDB Providers drop-down and choose Microsoft OLEDB Provider for SQL server as shown.

Click on the Data Links... button. This brings up the Data Link Properties window as shown. Choose the Windows authentication. If you click on the drop-down handle for Selecting the databases on the server a list of databases will be displayed. Choose the pubsx database.

Click on the OK on the Data Link properties page which will take you back to the Add Connection window updating all the information. You may test and verify the connection on this page as well. Click on the OK button on the Add Connection window. This will take you back to the Configure Data Source window seen earlier after updating the connection string as shown.

Click on the Next button. The window that shows up is about saving the connection information to the web.config file. Make sure you read the notes on this window.

Click on the Next button. In the window that gets displayed you can choose either a table from the database, or provide a SQL statement, or the name of a stored procedure. Here to keep it simple, the authors table is chosen from the drop-down list. You can make use of other buttons on this window to refine your select statement. Here just the table name is chosen. From the columns that are displayed a few columns are chosen.

Click on the Next button. This displays the window where you can test your query. It comes up blank, but when you hit the button Test Query, the blank area gets populated by the result returned by the query as shown.

Now click on the Finish button. This closes this window and the details of the just finished data source gets into the designer interface as shown.

Code and explanation for real-world MFC C++ Applications Learn C++ Windows programming by studying realistic, interesting examples A quick primer in Visual C++ for programmers of other languages, followed by deep, thorough examples Example applications include a Tetris-style game, a spreadsheet application, a drawing application, and a word processor Each application demonstrates key real-world techniques: parsing text, working with files, creating memory structures, displaying interactive graphics, and more http://www.PacktPub.com/visual-c++-windows-application-programming/book

Configuring the ListView

Now click on the Smart Task of the List View and you will find additional tasks listed. You may Refresh the schema and then click on Configure ListView.... This opens the Configure ListView window as shown in the next figure showing the layout and style selected from the options. The paging is enabled by placing a check mark in the Enable Paging check box. This action reveals two options for paging of which the Next/Previous is chosen.

This changes the List View control in the designer as shown.

Since paging was enabled, a Data Pager control was added automatically as shown in the next figure which shows the items at the very bottom of the previous figure with the associated code in the split view.

This is the Current View in the designer same as the Runtime View. You have other choices as shown in the next figure. We accept the default view.

Manually Configuring the Data Page Control

In case the page enabling was ignored by not checking the check box or you want to do independently, you can add a Data Pager Control from the Toolbox by double clicking the control in the Toolbox, or by dragging and dropping it on the design surface.

After dropping the control, the design appears as shown.

You may edit how the Data Pager Control is displayed by editing its properties as shown after clicking the Edit Pager Fields option in the Smart Tasks of the Data Pager.

A simple, clear, step-by-tutorial to creating DotNetNuke skins to put you in control of the look and feel of your DotNetNuke website Take control of the look and feel of your DotNetNuke website Simple, clear, tutorial to creating DotNetNuke skins Practical step-by-step guidance No knowledge of DotNetNuke skinning required http://www.PacktPub.com/dotnetnuke-4.0-skinning-tutorial/book

If you now Build the project and try to view it in the Browser you will get a server error. The message of this exception is:

No IPageableItemContainer was found. Verify that either the DataPager is inside an IPageableItemContainer or PagedControlID is set to the control ID of an IPageableItemContainer.

In this case you need to manually associate a PagerControlID to the Data Pager in the code view. Presently it has no PagerControlID as shown in the code.

<asp:DataPager ID="DataPager1" runat="server">
            <Fields>
                <asp:NextPreviousPagerField ButtonType="Button" 
                ShowFirstPageButton="True" 
                    ShowLastPageButton="True" />
            </Fields>
</asp:DataPager>

You may add this PagerControlID to the above code and set its value to "ListView1" which is its container. You may choose this item from the intellisense drop-down list as shown.

Setting the value of [PageControlID=ListView1] which is the ID of the container, you should be able to build and browse to the page as shown. You may also verify that the control is cycling the data as it should.

Data Pager Control

Data Pager Class

This control is rich in properties, methods and events it can respond to, as seen in this view of the control in the Object Browser. Using code, a lot more customizing can be done as shown in the paragraph after the figure which limits the number of list items shown to 4.

Control Page Size using Code

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
 ByVal e As System.EventArgs) Handles Me.Load
        DataPager1.PageSize = 4
    End Sub

    Protected Sub ListView1_SelectedIndexChanged(ByVal sender As Object, _
     ByVal e As EventArgs) Handles ListView1.SelectedIndexChanged

    End Sub

    Private Sub Page_PreInit(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.PreInit
        DataPager1.EnableTheming = True
    End Sub
End Class

Control Page Size Declaratively

Another way to limit items displayed is by a declarative code as shown in the next paragraph. You may choose the PageSize property from the drop-down provided by intellisense.

<asp:DataPager ID="DataPager1" 
               runat="server" 
               PagedControlID="ListView1" 
               PageSize="4">
         <Fields
                  <asp:NumericPagerField/>
         </Fields>
</asp:DataPager>

In this case the rendered page would be displayed as shown.

Summary

The article described the usage of a Data Pager Control in an ASP.NET Web Application using Visual Studio 2008 using data retrieved from SQL Server 2008 [November CTP].

About the Author