Using the Data Pager Control in Visual Studio 2008

Get started with SharePoint Designer and learn to put together a business website with SharePoint with this book and eBook

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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".

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

 

 

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

 

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.

Using the Data Pager Control in Visual Studio 2008

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

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

After dropping the control, the design appears as shown.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

 

 

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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.

Using the Data Pager Control in Visual Studio 2008

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].

Books to Consider

comments powered by Disqus