Creating a Web Page for Displaying Data from SQL Server 2008

Exclusive offer: get 50% off this eBook here
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 — Save 50%

An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

$23.99    $12.00
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.

Creating a Web Page for Displaying Data from SQL Server 2008

Click on the Change... button which opens the Change Data Source window shown in the next figure.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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 Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from 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.

Creating a Web Page for Displaying Data from SQL Server 2008

Click on the Next button which opens a window where you choose the default database to which you want to establish a connection.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

On to the Default.aspx page, drag and drop a GridView control from the Toolkit as shown in this design view.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

Click on the OK button. The wizard's next task is to guide you to get the connection information as in the next figure.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

Click on the OK button. This brings the connection information to the wizard's screen as shown in the next figure.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

(For more resources on Microsoft, see here.)

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

Click on the Finish button. The design view of the GridView changes as shown after choosing paging and sorting.

Creating a Web Page for Displaying Data from SQL Server 2008

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.

Creating a Web Page for Displaying Data from SQL Server 2008

Inspection of web.config file shows that the connection information is stored in this file as shown.

Creating a Web Page for Displaying Data from SQL Server 2008

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:


Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.
Published: December 2007
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

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

Books From Packt


Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 and SharePoint 2010 Integration
Microsoft Silverlight 4 and SharePoint 2010 Integration

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software