[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Creating a Web Page for Displaying Data from SQL Server 2008, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Dr.Jay Krishnaswamy | April 2008 |
.NET Microsoft
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.
(For more resources on Microsoft, see here.)
Click on the Next button. This brings up the screen where you can use the interface to define your custom selection. Here only the fname, lname, and hire-date have been chosen. You also have the option to select stored procedure as well as invoking the Query Builder graphic interface. These options were not chosen. A select statement as shown was directly typed into the SQL Statement window.
Probably a Bug!
Probably there is a bug in the Query Builder interface. While trying to add another table by bringing up the query builder, you see only the tables from 'master' as shown in the next figure which is a collage of two figures. Although the pubsx database was chosen for 'katma' dsn, the query builder is only showing the 'master'. Clearly there is a disconnect. The query builder's Add Table was not used in this example.
Click on the Next button. This brings up the Test Query window as shown which is empty. However when you hit the Test Query button, you will see the data corresponding to the selection made earlier as shown.
Click on the Finish button. The design view of the GridView changes as shown after choosing paging and sorting.
Build the web site project and open the default page of the web site in IE browser. The GridView displays the data from SQL Server 2008 which may be sorted, and can be displayed in groups of 10 rows (default) per page.
Inspection of web.config file shows that the connection information is stored in this file as shown.
Summary
The article shows a work around for connecting to a database on SQL Server 2008 using a system DSN. The retrieved data is displayed on a GridView in an ASP.NET web page. The web site is created for NET Framework 2.0. There may be a 'bug' in the query builder tool in VS 2008 as described in the tutorial.
Further resources on this subject:
- Best Practices for Microsoft SQL Server 2008 R2 Administration [Article]
- SQL Server 2008 R2: Multiserver Management Using Utility Explorer [Article]
- Microsoft LightSwitch Application using SQL Azure Database [Article]
- Easy guide to understand WCF in Visual Studio 2008 SP1 and Visual Studio 2010 Express [Article]
About the Author :
Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his web site or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net
Post new comment