Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
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.
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.
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.
eBook Price: £13.99
Book Price: £21.99
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.
eBook Price: £13.99
Book Price: £21.99
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">
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
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
DataPager1.PageSize = 4
Protected Sub ListView1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As EventArgs) Handles ListView1.SelectedIndexChanged
Private Sub Page_PreInit(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.PreInit
DataPager1.EnableTheming = True
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.
In this case the rendered page would be displayed as shown.
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 :
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.
Books From Packt