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

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 "mysql.example.com" 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=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
        <add assembly="MySql.Web, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
        <!--
          <add assembly="MySql.Data.Entity, Version=6.10.6.0, 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="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="MySql.Web" publicKeyToken="c5687fc88969c44d"/>
        <bindingRedirect oldVersion="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
      </dependentAssembly>
      <!--
        <dependentAssembly>
          <assemblyIdentity name="MySql.Data.Entity" publicKeyToken="c5687fc88969c44d"/>
          <bindingRedirect oldVersion="1.0.0.0-6.10.6.0" newVersion="6.10.6.0"/>
        </dependentAssembly>
      -->
    </assemblyBinding>
  </runtime>
  <!-- [...] -->
</configuration>

C# MySql.Data.MySqlClient test script

MySQL.Data test script for C# and MySQL.

<%@ 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 = "server=mysql.example.com;" +
   "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>

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\example.com\www\bin\MySql.Data.dll")
$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myconnection.ConnectionString = "server=mysql.example.com;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()

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: https://www.paypal.me/jreilink
 **/

function checkMySQL() {
  $error = false;
  $connerror = false;
  @$mysqli = new mysqli( "mysql.example.com", "example_db", "password", "example_db" );
  if ( $mysqli->connect_error ) {
    $connerror .= $mysqli->connect_error . " on mysql.example.com";
  } else if ( $resultaat = $mysqli->query( "SHOW TABLES" ) ) {
    $error = false;
  } else {
    $error .= @$mysqli->error . " on mysql.example.com";
  }
  @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() );
}
?>

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};" & _
  "Server=mysql.example.com;" & _
  "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
%>

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};" &_
  "Server=mysql.example.com;" & _
  "Database=example_db;" & _
  "UID=example_db;" & _
  "PWD=password;" & _
  "SSLMODE=REQUIRED;"

See the ODBC connection parameters for more information.

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