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