Using MySQL from C# and Mono.NET

• Download Mono files for Linux or Windows XP - 139 Kb
• Download Visual Studio project for Windows XP - 143 Kb

Introduction

Most programs need some kind of database connectivity. It may be for storing data, settings or license details. If you are used to using Visual Studio .NET in Windows, you will usually use ADO.NET to connect to Access or SQL Server, so what do you use in Linux. The most commonly used database on this platform is MySQL which is also available for Windows. If you want to produce a truly cross platform application, MySQL is the preferred choice. In this article, I will demonstrate how you can connect to and create a new database in MySQL from a Console application using Mono. Mono is the open source implementation of the Microsoft .NET Framework, CLR, C# and Visual Basic compilers. In fact the application you produce from this article will run on both platforms without modifications.

Background

The basics of connecting to databases is more or less the same using Mono as in Microsoft .NET. The main difference is the data provider used to connect and run commands. When writing this article, I used the latest version of Mono 1.1.8 installed on SUSE Linux 9.3 Professional. This includes the ByteFX provider for MySQL. If you install the Windows install of Mono 1.1.8, you can follow this article on the Windows platform. You will also need the latest free version of MySQL downloaded from the MySQL website (version 4.1) or from your Linux distros media. In SUSE 9.3, this is included in the installation as well as an administration application and query browser.

This article assumes you have basic SQL skills and know how to log into MySQL from the command line. After you have installed MySQL and Mono, connect to your MySQL server using a console in Linux or the command prompt on Windows. Set the server to use OLD_PASSWORD encryption using the following command at the MySQL prompt.

mysql> SET PASSWORD FOR root@yourserver = OLD_PASSWORD('yourpassword')

Failure to do this will result in a protocol error when connecting from your application. I wrote the application featured in this article using MonoDevelop, the open source free .NET development environment, but you could just as easily use the Kate or Emacs text editors on Linux. On Windows, I recommend SharpDevelop, Visual Studio .NET or Notepad.

Using the code

To keep the code as simple as possible, I used a single C# class file and a configuration file. The configuration file should be placed in the application directory and named as per your exe file with a .config extension, i.e., yourApp.exe.config.

In the configuration file, place the MySQL connection string setting in the following format replacing mypassword with your own connection password.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>l;
    <appSettings>
        <add key="mysqlConnectionString" 
           value="Server=localhost;User ID=root;password=mypassword;" />
    </appSettings>
</configuration>

Of course we could have hard coded this into the application but this is a good coding practice which will allow the server to be changed without recompiling the application.

Create a new file in your choice of editor and save as Main.cs. This will be our application source file. At the very top of this file, we need to import the namespaces we wish to use in the class as follows:

using System;
using System.Configuration;
using ByteFX.Data;
using System.IO;
using System.Reflection;

As in any application, we need an entry point class and Main method, so you can now add this to your class file as below including some variables we will be using in the Main function.

class MainClass
{
    private static string connectionString = string.Empty;
    private static string databaseName = string.Empty;
    private static ByteFX.Data.MySqlClient.MySqlConnection mysqlConn;

    public static void Main(string[] args)
    {
        try
        {

        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message.ToString());
        }
    }
}

In order to connect to your MySQL database, we now need to add code to the Main function to read in the connection string from the configuration file. You could of course extend this code to look at the command args and therefore specify a connection string dynamically in the command line.

public static void Main(string[] args)
{
    try
    {
        connectionString = 
          ConfigurationSettings.AppSettings["mysqlConnectionString"].ToString();
    }
    catch(Exception ex)
    {
        Console.WriteLine(ex.Message.ToString());
    }
}

In order to create a database, a database name is required from the user. If a name is input, the database connection is initialized and opened. You can see from the code below that I have also added a finally block to ensure the connection will be closed and marked as disposed before the application exits.

public static void Main(string[] args)
{
    try
    {
        connectionString = 
          ConfigurationSettings.AppSettings["mysqlConnectionString"].ToString();

        Console.WriteLine("Please enter a new database name:");
        databaseName = Console.ReadLine();

        if(databaseName.Length > 0)
        {
            mysqlConn = new ByteFX.Data.MySqlClient.MySqlConnection();
            mysqlConn.ConnectionString = connectionString;
            mysqlConn.Open();
        }

    }
    catch(Exception ex)
    {
        Console.WriteLine(ex.Message.ToString());
    }
    finally
    {
        if(mysqlConn != null)
        {
            mysqlConn.Close();
            mysqlConn.Dispose();
        }
    }
}

The next code extract shows the creation of the SQL script and the use of a MySqlCommand object to execute it. If successful, the user is informed that the database was created.

...
if(databaseName.Length > 0)
{
    mysqlConn = new ByteFX.Data.MySqlClient.MySqlConnection();
    mysqlConn.ConnectionString = connectionString;
    mysqlConn.Open();

    //Create the sql script...

    string createdbSql = 
      string.Format("CREATE DATABASE {0}", databaseName);

    using(ByteFX.Data.MySqlClient.MySqlCommand cmd
        = new ByteFX.Data.MySqlClient.MySqlCommand(createdbSql, mysqlConn))
    {
        cmd.ExecuteNonQuery();
        Console.WriteLine(string.Format("Database" + 
                " {0} successfully created", databaseName));
    }

}
...

Compiling the code

This article has described how you can connect to and interact with a MySQL Server installation using Mono or Microsoft .NET. To compile this code outside of an IDE, run the following from a command window in the application directory:

mcs Main.cs /r:ByteFX.Data /r:Mono.Posix 
            /r:ICSharpCode.SharpZipLib /out:MySQLConsole.exe

On Windows, run the application using the Microsoft CLR, and on Linux su to root and run the following from a command window in the application directory:

mono MySQLConsole.exe

History

• Version 1.0 - 28/06/2005.