This article by Jayaram Krishnaswamy describes how you may connect to SQL Server 2008 and display the retrieved data in a GridView Control on a web page. Trying to establish a connection to the SQL Server 2008 is not possible in Visual Studio 2008 as you will see soon in the tutorial. One way to get around this, as shown in this tutorial, is to create an ODBC connection to the SQL Server and then using the ODBC connection to retrieve the data.
Visual Studio 2008 Version: 9.0.21022.8 RTM, Microsoft Windows XP Professional Media Center Edition, and SQL Server ‘Katmai’ were used for this tutorial.
(For more resources on Microsoft, see here.)
Connecting to SQL Server 2008 is Not Natively Supported in Microsoft Visual Studio 2008 Designer
In the Visual Studio 2008 IDE make a right click on the Data Connections node in the Server Explorer. This will open up the Add Connection window where the default connection being displayed is MS SQL Server Compact.
Click on the Change… button which opens the Change Data Source window shown in the next figure.
Highlight Microsoft SQL Server as shown and click on the OK button. This once again opens the Add Connection window showing SQL Server 2008 on the machine, Hodentek as shown in the next figure in this case.
The connection is set for Windows Authentication and should you test the connectivity you would get ‘Success’ as a reply. However when you click on the handle for the database name to retrieve a list of databases on this server, you would get a message as shown.
Creating a ODBC DSN
You will be using the ODBC Data Source Administrator on your desktop to create a ODBC DSN. You access the ODBC Source Administrator from Start | All Programs | Control Panel | Administrative Tools | Data Sources(ODBC). This opens up ODBC Data Source Administrator window as shown in the next figure.
Click on System DSN tab and click on the Add… button. This opens up the Create New Data Source window where you scroll down to SQL Server Native Client 10.0.
Click on the Finish button. This will bring up the Create a New Data Source to SQL Server window. You must provide a name in the Name box. You also provide a description and click on the drop-down handle for the question, Which SQL Server do you want to connect to? to reveal a number of accessible servers as shown. Highlight SQL Server 2008.
Click on the Next button which opens a window where you provide the authentication information. This server uses windows authentication and if your server uses SQL Server authentication you will have to be ready to provide the LoginID and Password. You may accept the default for other configurable options.
Click on the Next button which opens a window where you choose the default database to which you want to establish a connection.
Click on the Next button which opens a window where you accept the defaults and click on the Finish button.
This brings up the final screen, the ODBC Data SQL Server Setup which summarizes the options made as shown. By clicking on the Test Data Source… button you can verify the connectivity.
When you click on the OK button you will be taken back to the ODBC Data Source Administrator window where the DSN you created is now added to the list of DSNs on your machine as shown.
Retrieving Data from the Server to a Web Page
You will be creating an ASP.NET website project. As this version of Visual Studio supports projects in different versions, choose the Net Framework 2.0 as shown.
On to the Default.aspx page, drag and drop a GridView control from the Toolkit as shown in this design view.
Click on the Smart task handle to reveal the tasks you need to complete this control. Click on the drop-down handle for the Choose Data Source: task as shown in the previous figure.
Now click on the <New data Source…> item. This opens the Data Source Configuration Wizard window which displays the various sources from which you may get your data. Click on the Database icon. Now the OK button becomes visible.
Click on the OK button. The wizard’s next task is to guide you to get the connection information as in the next figure.
Click on the New Connection… button. This will take you back to the Add Connection window. Click on the Change… button as shown earlier in the tutorial. In the Change Data Source window, you now highlight the Microsoft ODBC Data Source as shown in the next figure.
Click on the OK button. This opens the Add Connection window where you can now point to the ODBC source you created earlier, using the drop-down handle for the Use user or system data source name. You may also test your connection by hitting the Test Connection button.
Click on the OK button. This brings the connection information to the wizard’s screen as shown in the next figure.
Click on the Next button which opens a window in which you have the option to save your connection information to the configuration node of your web.config file. Make sure you read the information on this page. The default connection name has been changed to Conn2k8 as shown.
Click on the Next button. This will bring up the screen where you provide a SQL Select statement to retrieve the columns you want. You have three options and here the Specify a custom SQL Statement or stored procedure option is chosen.