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