Building ASP.NET Web Forms to Use a MySQL Database

The MySQL database is the best open source database, which means it can be used for free without obtaining or paying for a license. In ASP.NET 3.5 hosting, there are some hosting packages that let you use the MySQL database because it can be a cheaper hosting alternative when compared to using the MS SQL database. However, things can be a bit complicated when querying a MySQL database in an ASP.NET environment.

This tutorial aims to create ASP.NET web forms that query or use a MySQL database. This basic guide can be used by any developer to create a more complex web form in ASP.NET using a MySQL database.

Install a Test MySQL Database

Since ASP.NET is developed under a Windows environment, you need to make your MySQL database run on a Windows system as well. During the website development phase, it is essential to run the application on your local Windows computer before transferring or uploading the files to your selected ASP.NET 3.5 hosting environment. Therefore, you need a MySQL server installed in Windows.

If you also develop websites in PHP, it is recommended that you have XAMPP installed in Windows. The good thing about having XAMPP fully installed on your Windows computer is that it includes a full installation of MySQL server with a phpmyadmin GUI interface (a user friendly graphical interface for managing a MySQL database). If you do not have XAMPP installed, you can read the following helpful tutorials to get you started:

https://www.aspfree.com/c/a/BrainDump/XAMPP-Tips-for-Running-an-ApacheMySQL-Server-in-Windows-XP/  

https://www.aspfree.com/c/a/BrainDump/Install-and-Run-WordPress-in-XAMPP-Local-Host/  

If you have XAMPP fully installed, download this test database. Unzip it and import it to your MySQL database using PHPmyadmin (the procedure is listed below):

Step 1: Using your browser, open this URL: http://localhost/phpmyadmin

Step 2: Enter login details if required.

Step 3: Under Create New Database, enter ttest.

Step 4: When you see the message “Database ttest has been created,” click the Import link. See the screen shot below:

Step 5: Locate the extracted SQL file, then set the character to “utf-8,” check “Allow the interruption…,” set the number of records to “0,” set “SQL” as the format of the imported file and select “None” for SQL compatibility. When all of these are set, click “Go.”

Step 6: If you can see the message “Import has been successfully finished, 7 queries executed,” then the test database has been successfully imported to your database server.

The test database to be used in this tutorial is a t-test table from statistics (p = 0.05). You can read up on the background of t-test tables. There are two fields stored in the database table, degrees of freedom and critical values. In statistical analysis, particularly as used by math students and researchers, analysts are asked to determine the critical values given certain degrees of freedom. What they will do is to look at a t-test table — mostly the ones found at the back of their statistics book.

Here is a case example. Let’s assume you are developing an ASP.NET web application that will accept inputs in the form of “degrees of freedom,” and then provide its equivalent critical values after form submission. The database uses MySQL and is provided in the test database link above. Below is the screen shot of this web application form:

{mospagebreak title=MySQL Connection String in ASP.NET}

There is a previous tutorial that covers how ASP.NET connects to a MySQL database. You may find it helpful to review that tutorial. 

The complete connection string that will be used to display all of the records in the database table is as follows:

<% ‘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>

{mospagebreak title=Incorporating Web Forms}

Note that the string shown on the previous page does not yet contain the web forms that will be used to ask for user input (in terms of degrees of freedom). However, the above code can be improved to incorporate web forms with the following approach:

1. In the server side Visual Basic script, you need to declare a variable that will grab “degrees of freedom” values entered by the user.

2. The MySQL query command: SELECT * FROM ttest is used to extract ENTIRE records from the database. However, since you are only interested in extracting one record of Critical Values for specific degrees of freedom, then the MySQL query command can be revised to:

SELECT `critical` FROM ttest WHERE `degrees`=’userdegrees’

3. In the <body> section, the form can be inserted.

Complete Script –Default.aspx

As usual, you can refer to a complete tutorial to help you create Default.aspx using Visual Basic language in a local file system. 

However, once the default content of Default.aspx is entirely set, the content of Default.aspx will be replaced with:

<% ‘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>

<title>Critical Values</title>

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

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

Sub getcritical_Click(ByVal Source As Object, ByVal e As EventArgs) Handles getcritical.Click

Dim userdegrees As Integer = df.Text

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 `critical` FROM ttest WHERE `degrees`=" & userdegrees

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 >

<style type="text/css">

.style1

{

font-family: Verdana;

}

</style>

</head>

<body>

<h3 style="font-family: Verdana">

Determine Critical Values based on Degrees of Freedom</h3>

<p class="style1">

In statistical t-test, the critical values is a function of degrees of freedom.</p>

<p class="style1">

Instead of using t-test tables to find the equivalent critical values for a

specific degrees of freedom;</p>

<p class="style1">

Use the form below; results will then be shown below the form after clicking

&quot;Get critical values&quot;.</p>

<form id="form1" runat="server">

<div style="font-family: Verdana">

<span class="style1">Enter the degrees of freedom here:</span>

<asp:TextBox ID="df" runat="server"></asp:TextBox>

<br />

<br />

<asp:Button ID="getcritical" runat="server" Text="Get critical values"

Font-Names="Verdana" />

<br />

<br />

</div>

</form>

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

<ASP:Datagrid ID="Datatable" Runat="server" style="font-family: Verdana" />

</body>

</html>

{mospagebreak title=A Closer Look at the Form}

Highlights of the code on the previous page include the following: 

Server side Visual Basic script to get the form values (degrees of freedom):

Dim userdegrees As Integer = df.Text

The form:

<form id="form1" runat="server">

<div style="font-family: Verdana">

<span class="style1">Enter the degrees of freedom here:</span>

<asp:TextBox ID="df" runat="server"></asp:TextBox>

<br />

<br />

<asp:Button ID="getcritical" runat="server" Text="Get critical values"

Font-Names="Verdana" />

<br />

<br />

</div>

</form>

The text box ID is df, so Visual Basic will assigned this to a variable called userdegrees. So how does the complete code work? First, the user is presented with a form. Second, once the form is submitted by clicking “Get critical values,” ASP.NET activates the Visual Basic script getcritical_Click

Visual Basic will then get the user form input values. Next it will start MySQL database connection, query the database and then assign the result to a datatable which will then be displayed back in the HTML browser:

<ASP:Datagrid ID="Datatable" Runat="server" style="font-family: Verdana" />

Below is a sample screen shot of the sample web form with the result:

[gp-comments width="770" linklove="off" ]