Displaying MySQL data on an ASP.NET Web Page

Exclusive offer: get 50% off this eBook here
ASP.NET 3.5 Social Networking

ASP.NET 3.5 Social Networking — Save 50%

An expert guide to building enterprise-ready social networking and community applications with ASP.NET 3.5

$29.99    $15.00
by Dr. Jayram Krishnaswamy | August 2009 | Open Source PHP

Web enabling business data is one of the key devices used to advertise and market products. This can be done with various technologies such as VB, ASP, JSP, ASP.Net and many others. This article shows how you may view data from a table on a MySQL database server on a web page using ASP.NET. The table used in this tutorial was the one described in the first article in this series on Exporting data from MS Access 2003 to MySQL.

This article by Dr. Jay Krishnaswamy explains how to populate a GridView on an ASP.NET web page by data retrieved from a MySQL Server. MySQL.Data.MySqlClient is a connector (provider) provided by MySQL which you can use with the .NET Framework applications whose details may be reviewed here. MySQL is well integrated with Visual Studio (MySQL Visual Studio Tools: MySQL.VisualStudio.dll).

Overview

We first create an ASP.NET 3.5 Website Project (even .NET Framework 2.0 should be OK) in Visual Studio 2008. We then drag and drop a GridView ASP.NET control on to the Default.aspx page. We will then use the smart task on the GridView and follow it up to bring data to the GridView. Then we build the web site project and display the data in the GridView on the Default.aspx page.

Create a ASP.NET 3.5 Web Site Project

Launch Visual Studio 2008. Click File | New |Web Site... to open up the New Web Site window as shown. Change the default name of the web site to something suitable. Herein it is named WebMySQL as shown.

Drag and drop a GridView Control

From Toolbox under Data find the GridView Control. Drag and drop this control on to the Default.aspx page as shown. The GridView is 'unbound' when it is dropped and has a few template columns and the smart tasks menu. The menu item is shown in its drop-down state and  displays the menu items under 'Choose Data Source'.

Click on the <New Data Source...> item in Choose data source. This will bring up the Data Source Configuration wizard as shown. Herein you need to choose a source of the data you are trying to bring into the application to be bound to the GridView control.

You have several options here and for the present article we will be using data from a database. Click on the Database icon as shown in the previous figure. With this you will be specifying an instance of SQLDataSource1 as your source of data. Click OK.

This will take you to the next window shown here. Herein you will try to establish a connection to the data source. In the combo-box shown you may see some of the existing connections you have previously established one of which may initially show up. Herein we will be making a new connection. Click the New Connection... button.

This brings up the Add Connection window which gets displayed with the default data source, the Microsoft SQL Server Compact 3.5 as shown.

Connecting to MySQL

Before establishing the connection make sure that your MySQL Server is running. If you have not started you may do so as described in the article mentioned earlier(the first article). You can start the server from the command line as shown in the next figure.

Click the Change... button to open the Change Data Source window as shown in the next figure.

This window shows a number of Data Sources one of which is the MySQL Database. Scroll down and highlight MySQL Database as shown and click OK. This will bring you back to the Add Connection window with form controls appropriate for making a connection to a MySQL Database.

The Server name; user name and Password are appropriate to the MySQL Server on the local computer and you should enter those appropriate for your installation. You may also test the connection as shown. Click OK after the connection is successful. This adds the connection information to the Configure Data Source wizard. You may expand the connection string item to review the connection string created by your entries.

Click Next. Here you have an option to save the connection string to the Application Configuration File. This is a recommended practice and hence shown checked.

Click Next. Here you will be selecting the set of columns that you want to bring in to your application. It has already chosen the 'employees' table on the MySQL database Testmove.

Choose several columns from the list of columns. The SELECT statement is shown at the bottom of the above figure. If you were to click Next you would probably face a page which throws an exception. The square braces [ ] shown for each of the columns is not acceptable to the server.  Click on the first option, "Specify a custom SQL Statement or stored procedure" and then click Next. This opens the "Define Custom Statements or Stored Procedures" page with a Query Builder... button. Here you can not only select columns but also other data modification operations such as Update, Insert and Delete. For now we will be doing just a selection.

ASP.NET 3.5 Social Networking An expert guide to building enterprise-ready social networking and community applications with ASP.NET 3.5
Published: December 2008
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

The Query Builder displays the syntax errors in the previous statement.

Remove all the square brackets "[ ]" from the statement as shown. This time you can run the query and verify the rows returned in the Query Builder tool.

Click OK to the Query Builder and click Next. This brings you to the page where you can test the query and review the results as shown.

Click Finish. You will be returned to the Default.aspx page. The GridView now has the column names you chose in  configuring SQLDataSource1. The smart tasks list has grown with some additional tasks. From adding new columns to editing the template you can carry out additional tasks.

The GridView's properties window can be used to set many of the formatting properties.

If you were to use the Auto Format... link at the bottom of the above window you can auto format to choose a scheme for the window. Choosing a scheme on the left you can preview how it would appear when rendered in the Preview area as shown.

After choosing the scheme build the project and browse to the Default.aspx  page on your browser to display the page as shown.

In order to implement sorting of the data by column headers you need to choose the option Enable Sorting at design time as shown.

You can also reorder column positions by highlighting the column you wish to move around. This will add further tasks as shown here when the 'Postal Code' column is chosen. With the new tasks you can manipulate the column or even remove it.

After you download and run the MySQL connector program you will find the MySQL.Data in the References folder of Visual Studio as shown above. Even the splash screen of Visual Studio shows MySQL as one of the products with which it is integrated.

Source code

The source code of the Default.aspx page follows (the connection string is by choice stored in the web.config file of the site):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC"
BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="SqlDataSource1">
<RowStyle BackColor="White" ForeColor="#003399" />
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"
SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="Region" HeaderText="Region"
SortExpression="Region" />
<asp:BoundField DataField="PostalCode" HeaderText="PostalCode"
SortExpression="PostalCode" />
</Columns>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:testmoveConnectionString %>"
ProviderName="<%$ ConnectionStrings:testmoveConnectionString.ProviderName %>"
SelectCommand="SELECT EmployeeID, LastName, FirstName, City, Region,
PostalCode FROM employees"
>
</asp:SqlDataSource>

</form>
</body>
</html>

Summary

The article described the steps involved in creating an ASP.NET web page that displays data from a MySQL Server. This is made possible by the integration of MySQL Server with Visual Studio 2008. In addition to just displaying the data, CRUD operations are also possible.

If you have read this article you may be interested to view :

ASP.NET 3.5 Social Networking An expert guide to building enterprise-ready social networking and community applications with ASP.NET 3.5
Published: December 2008
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore and 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 website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

Books From Packt

Grails 1.1 Web Application Development
Grails 1.1 Web Application Development

ASP.NET MVC 1.0 Quickly
ASP.NET MVC 1.0 Quickly

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

Pentaho Reporting 3.5 for Java Developers
Pentaho Reporting 3.5 for Java Developers

Drools JBoss Rules 5.0 Developer's Guide
Drools JBoss Rules 5.0 Developer's Guide

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

Joomla! 1.5x Customization: Make Your Site Adapt to Your Needs
Joomla! 1.5x Customization: Make Your Site Adapt to Your Needs

Your rating: None Average: 4.5 (2 votes)
Thanks for this information!! by
Thanks for this information!! But i would also like to know how to use 'enable editing' option with gridview with mysql database.
Same problem by
I don't have the option to choose MySql either.
Cannot see the Click the Change... button to open the Change Dat by
HI I am trying to follow your step by step guide but i am struct at Click the Change... button to open the Change Data Source window as shown in the next figure I cannot see MYSQL Connector. I have installed full MYSQL and also its mysql-connector-net-6.1.4 Still i cannot see it I am using WEb developer 2010 Express. Please help. Thanks Sri

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Z
1
P
r
k
H
Enter the code without spaces and pay attention to upper/lower case.
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