Using ASP.NET with a MySQL Database

ASP.NET is a powerful and fast web application framework by Microsoft commonly used for building websites in Windows technology. It is a proprietary product (not open source); however it has been faster than PHP in terms of performance.

MySQL is the best open source (free) database because it is fast, efficient, robust and stable, especially for large scale website implementation. Using ASP.NET (for server side scripting) and MySQL for databases is one of the great combinations in developing websites in terms of performance (ASP.NET connects at much the same speed as PHP when it comes to MySQL, according to the linked report).

ASP.NET is commonly associated with proprietary databases such as SQL server and MS Access, but not as much with the MySQL database. This is a tutorial on how ASP.NET can connect to a MySQL database, retrieve records and then display them on a browser.

Using this basic approach, any web developer can build even more complex applications based on ASP.NET and MySQL databases.

If you are ready, then let’s get started.

Required system components

This tutorial takes advantage of a local host Windows environment (you can use this approach in hosting provided it supports both ASP.NET and MySQL at the same time). It has been tested to work in these minimum system and application requirements:

• Windows XP Home SP3 (fully updated). 
  
• XAMPP for Windows Version 1.7.0. 
  
• MySQL connector/ODBC 5.1. 
  
• Microsoft Visual Web Developer 2008 Express Edition.

Here is a screen shot of the vital components:

The ASP.NET website is developed using a Microsoft application called MS Visual Web Developer. The Windows operating system is used, since you are using ASP.NET web application. If you do not have Visual Web developer installed, you can download it. 

The “XAMPP for Windows” is the bundled version of Apache and MySQL all in one package, which will run in Windows XP. You need that fully installed in your local computer. However, it is only the MySQL service that will be used in this tutorial. You can download XAMPP and refer to a related tutorial for installation and basic operation.

The MySQL connector/ODBC 5.1 is an API which will be used by ASP.NET to connect to any open source database, such as MySQL. ODBC stands for “Open Database Connectivity.” You can download the ODBC driver for Windows. The installation is straightforward.

To confirm that you have correctly installed the ODBC 5.1 driver, go to Control Panel -> Add or Remove Programs, and you should see “MySQL Connector/ODBC 5.1.”

Import test MySQL database

For the purpose of this tutorial, there is a demo MySQL database included that we'll use for illustration purposes. This will let you test this database in your own ASP.NET and XAMPP installation. The objective is to have results similar to those in this tutorial.

Step 1: Download test MySQL database. 

Step 2: Right click on the zip file. Select “Extract files…” In the Extraction path, select “Desktop.” You can use Winrar to unzip.

Step 3: You need to have XAMPP/Apache/MySQL enabled. To enable, go to: Start -> All programs -> Apache friends -> XAMPP -> XAMPP Control Panel. Make sure you can see both Apache and MySQL running; see screenshot below, inside red box:

Step 4: Open a browser, type the following URL and then press enter key: http://localhost/phpmyadmin. Enter login information if required.

Step 5: You will then land in the phpmyadmin dashboard. In the menu, click “Import.”

Step 6: Under “location of the text file,” browse to the location where you have unzipped the test database. Set everything to default, for example:

Character set of the file: utf8

Partial Import: Checked

Number of records (queries) to skip from start: 0

Format of imported file: SQL

SQL compatibility mode: NONE

Step 7: Click “GO.”

Step 8: If the import has been successful, you should see a message: “Import has been successfully finished, 9 queries executed.”

Step 9: To test that it has been successfully imported, click “SQL” in the phpmyadmin menu, type a sample query below and click “GO:”

USE ttest;

SELECT * FROM ttest

It should give the whole database table as an output of the query. See related screenshot below:

Start ASP.NET to connect with MySQL

Let us create one ASP.NET page using the file name Default.aspx. Please refer to this tutorial. 

Leave the source code of Default.aspx at default (do not edit files yet), and then follow the steps below to connect to MySQL:

Step 1: Using Visual Web Developer Express, delete all of the source code of Default.aspx.

Step 2: Replace it with the source code below:

<% 'Step 1: Import data sets which is needed to store a copy of data from database tables. %>

<%@ Import Namespace="System.Data" %>

<% 'Step 2: Import classes for connecting to a data source that has an ODBC driver, as discussed ASP.NET needs ODBC to communicate to MySQL. %>

<%@ Import Namespace="System.Data.Odbc" %>

<% 'Step 3: This is needed for ODBC .NET Data Provider. %>

<%@ Import Namespace="Microsoft.Data.Odbc" %>

<HTML>

<HEAD>

<% 'Step 4: Connect to MySQL using ASP.NET server side scripting. %>

<SCRIPT Language="VB" Runat="server">

Sub Page_Load(Source as object, e as EventArgs)

Dim connectionstring As String = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=ttest; UID=root; PASSWORD=codex; OPTION=3"

Dim connectme As OdbcConnection = New OdbcConnection(connectionstring)

Dim sqlquery As String = "SELECT * FROM ttest"

Dim ODBCdataadapter As OdbcDataAdapter = New OdbcDataAdapter(sqlquery, connectme)

Dim ODBCdataset As DataSet = New DataSet()

ODBCdataadapter.Fill(ODBCdataset)

DataTable.DataSource = ODBCdataset

Datatable.DataBind()

End Sub

</SCRIPT >

</HEAD>

<BODY>

<% 'Step 5: Display MySQL database table in the browser. %>

<ASP:Datagrid ID="Datatable" Runat="server" />

</BODY>

</HTML>

This is how your Visual Web Developer Express should look after pasting the above code into your Default.aspx:

The MySQL query line: Dim sqlquery As String = "SELECT * FROM ttest" can be customized to output specific database records. The database table is a statistical t test table.

So for example, say we would like MySQL to output critical value if the degrees of freedom is 30. It would be:

Dim sqlquery As String = "SELECT `critical` FROM ttest WHERE `degrees`=30"

Using ASP.NET controls, you can even make a web form to accept user inputs and then let the server side script output the corresponding request after querying the database.

Web Hosting Implementation and Security

To implement the techniques in this tutorial using ASP.NET to use a MySQL database, you need to find web hosting that will support MySQL in an ASP.NET environment. You can read some suggestions.  

Bear in mind that if you plan to implement this approach full scale in a website, security is a top priority; you will need to avoid MySQL injection attacks in your ASP.NET environment. In relation to this, you can start learning how to do this by reading this Microsoft tutorial.