How to: Test MySQL database connectivity in ASP.NET, PHP, ASP

You are here: Sysadmins of the North » Code base » How to: Test MySQL database connectivity in ASP.NET, PHP, ASP

Simple MySQL connection test from your website with ASP.NET, PHP & classic ASP. Whenever you need a MySQL connectivity test from a website or server, it’s handy to have various test scripts nearby. Whether it is because you are setting up a new website or you have just installed a new server and are running your tests.

Nowadays, many websites depend on a MySQL or MariaDB database because CMS systems like WordPress, Joomla and Drupal are so popular, and Umbraco too. You’ll want your webserver to connect to MySQL fast and without errors :) , so quickly test your MySQL database connection using PHP, ASP.NET (PowerShell bonus!) and classic ASP.

Throughout the examples, we always use “” as our database hostname, “example_db” as databasename and username, “password” is our password.

Connect to a MySQL Database Using ASP.NET

MySql.Data.MySqlClient example

The following C# ASP.NET script tests MySQL database connectivity with MySql.Data (MySQL’s Connector/NET). All it does is: make a MySQL connection, execute one query and print the results on the screen.

As a bonus, it also prints out the .NET Framework version using System.Environment.Version.ToString(). Unfortunately, this is not always accurate. You can determine which .NET Framework versions are installed using the Windows registry.

First, verify all necessary assemblies are loaded in your web.config file:

Configure MySql.Data in your Web.config file

This assumes you are not using a system wide Connector/NET version, but your own version in the ~/bin folder.

<configuration> <system.web> <compilation debug="false"> <assemblies> <add assembly="MySql.Data, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/> <add assembly="MySql.Web, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/> <!-- <add assembly="MySql.Data.Entity, Version=, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /> --> </assemblies> </compilation> </system.web> <!-- [...] ---> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="MySql.Data" publicKeyToken="c5687fc88969c44d"/> <bindingRedirect oldVersion="" newVersion=""/> </dependentAssembly> <dependentAssembly> <assemblyIdentity name="MySql.Web" publicKeyToken="c5687fc88969c44d"/> <bindingRedirect oldVersion="" newVersion=""/> </dependentAssembly> <!-- <dependentAssembly> <assemblyIdentity name="MySql.Data.Entity" publicKeyToken="c5687fc88969c44d"/> <bindingRedirect oldVersion="" newVersion=""/> </dependentAssembly> --> </assemblyBinding> </runtime> <!-- [...] ---> </configuration>
Code language: HTML, XML (xml)

C# MySql.Data.MySqlClient test script

<%@ Page Language="C#" %> <%@ Import Namespace="MySql.Data" %> <%@ Import Namespace="MySql.Data.MySqlClient" %> <script runat="server"> protected void Page_Load(object sender, System.EventArgs e) { MySQLConn(); } void MySQLConn() { string connStr = ";" + "user=example_db;database=example_db;" + "port=3306;password=password;pooling=true;" + "SslMode=REQUIRED;"; MySqlConnection conn = new MySqlConnection(connStr); try { Response.Write("Connecting to MySQL database...<br/>"); conn.Open(); string sql = "show tables"; MySqlCommand cmd = new MySqlCommand(sql, conn); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Response.Write(rdr[0]+ "<br/>"); } rdr.Close(); } catch (Exception ex) { Response.Write(ex.ToString()); } conn.Close(); Response.Write("All done!"); Response.Write ("<br/>.NET framework version: " + System.Environment.Version.ToString()); } </script>
Code language: C# (cs)

The above ASP.NET example uses an SSL to connect to MySQL (SslMode=REQUIRED).

You may also be interested in my MySQL Connector/ODBC C# test script to use with MySQL Connector ODBC 8.0 Unicode Driver in ASP.NET.

Connect using PowerShell

In PowerShell you can use reflection to load the MySQL.Data.dll assembly. Doing so, you can connect to MySQL using PowerShell. Neat!

[System.Reflection.Assembly]::LoadFrom("z:\sites\\www\bin\MySql.Data.dll") $myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection $myconnection.ConnectionString = ";user id=example_db;password=password;database=example_db;pooling=true" $myconnection.Open() $mycommand = New-Object MySql.Data.MySqlClient.MySqlCommand $mycommand.Connection = $myconnection $mycommand.CommandText = "SHOW TABLES" $myreader = $mycommand.ExecuteReader() while($myreader.Read()){ $myreader.GetString(0) } $myconnection.Close()
Code language: PowerShell (powershell)

This is ideal if you want to test and debug MySQL connection issues from your PowerShell command line.

PHP MySQLi connection to MySQL

The PHP example code uses MySQLi, connects to MySQL and performs a single query when the connection is made.

<?php /** * PHP/mysqli example to connect to a MySQL database * Support me: **/ function checkMySQL() { $error = false; $connerror = false; @$mysqli = new mysqli( "", "example_db", "password", "example_db" ); if ( $mysqli->connect_error ) { $connerror .= $mysqli->connect_error . " on"; } else if ( $resultaat = $mysqli->query( "SHOW TABLES" ) ) { $error = false; } else { $error .= @$mysqli->error . " on"; } @mysqli_free_result( $resultaat ); @$mysqli->close(); if ( $error !== false || $connerror !== false ) { echo( "Connection error:<br/> " . $connerror . ", query error:<br/>" . $error ); return false; } else { return true; } } if ( checkMySQL() ) { // print PHP version as bonus echo( "All done! PHP version: " . phpversion() ); } ?>
Code language: PHP (php)

Learn how to use SSL in PHP Data Objects (PDO) mysql connections.

ASP connection to a MySQL database – VBScript

The classic ASP (VbScript) code snippet uses an ADODB connection with the MySQL ODBC 5.1 Driver to connect to a MySQL database. Then it executes a single query when the connection is made.

<%@ Language=VBScript %> <% '' ' ASP example to connect to a MySQL database ' ADODB/MySQL ODBC '' Option Explicit Dim sConn, sSQL, oRs, x Set sConn = Server.CreateObject("ADODB.Connection") sConn.Open "Provider=MSDASQL;" & _ "Driver={MySQL ODBC 5.1 Driver};" & _ ";" & _ "Database=example_db;" & _ "UID=example_db;" & _ "PWD=password;" If sConn.errors.count = 0 Then Response.write "Connected OK" End If sSQL = "SHOW TABLES" ' create the recordset object Set oRs = Server.CreateObject("ADODB.Recordset") ' Open the recordset object executing the SQL statement oRs.Open sSQL,sConn If oRs.EOF Then Response.Write("No records found.") Else Do While Not oRs.EOF for each x in oRs.Fields Response.Write(x.value & "<br />") next oRs.MoveNext Loop End If oRs.Close Set oRs = Nothing sConn.Close Set sConn = nothing %>
Code language: VBScript (vbscript)

Using SSL with MySQL and ASP

To connect to MySQL over SSL in ASP, I use Connector/ODBC version 5.3.10, with the following connection string in ASP:

' sConn.Open "DRIVER={MySQL ODBC 5.3 ANSI Driver};" &_ sConn.Open "DRIVER={MySQL ODBC 5.3 UNICODE Driver};" &_ ";" & _ "Database=example_db;" & _ "UID=example_db;" & _ "PWD=password;" & _ "SSLMODE=REQUIRED;"
Code language: VBScript (vbscript)

See the ODBC connection parameters for more information.

SQL Server Compact: learn how to connect to an SqlCe database with ASP.

Are you looking for rock solid, eco-friendly, .NET hosting? Look no further! UmbHost offers powerful hosting services for websites and businesses of all sizes, and is powered by 100% renewable energy!

Hi! Join the discussion, leave a reply!

Scroll to Top