Click here to Skip to main content
15,860,972 members
Articles / Database Development / MySQL
Article

Using MySQL from C# and Mono.NET

Rate me:
Please Sign up or sign in to vote.
4.74/5 (13 votes)
3 Jul 20054 min read 179.7K   2.7K   60   18
An article on connecting to and using MySQL from Mono.

Windows XP Version

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
<?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:

C#
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.

C#
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.

C#
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.

C#
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.

C#
...
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United Kingdom United Kingdom
Sixteen years experience in software programming and web development, using C, Visual Basic and C Sharp on Windows and Linux.

Comments and Discussions

 
Questionhow to make service for import data form webserver in C# mono Pin
Deval910-Nov-13 23:35
professionalDeval910-Nov-13 23:35 
SuggestionRe: how to make service for import data form webserver in C# mono Pin
Custec10-Nov-13 23:51
Custec10-Nov-13 23:51 
GeneralMy vote of 5 Pin
Mavei25-Sep-12 22:25
Mavei25-Sep-12 22:25 
GeneralFrench char issue Pin
Ajay Kale New31-Aug-10 18:06
Ajay Kale New31-Aug-10 18:06 
GeneralRe: French char issue Pin
Custec31-Aug-10 21:50
Custec31-Aug-10 21:50 
GeneralArticle out of date Pin
Yuval Naveh8-Jul-08 9:52
Yuval Naveh8-Jul-08 9:52 
GeneralRe: Article out of date Pin
Custec9-Jul-08 1:51
Custec9-Jul-08 1:51 
GeneralRe: Article out of date Pin
Yuval Naveh9-Jul-08 2:01
Yuval Naveh9-Jul-08 2:01 
QuestionSql server2005 connection error Pin
bruze10-Oct-07 21:11
bruze10-Oct-07 21:11 
Generaltanx friend Pin
Juan Carlos Guillén Zurita25-Sep-07 9:40
Juan Carlos Guillén Zurita25-Sep-07 9:40 
Generalsettings not being retreived Pin
tscharf113815-Sep-06 13:03
tscharf113815-Sep-06 13:03 
GeneralRe: settings not being retreived Pin
Custec25-Sep-07 21:58
Custec25-Sep-07 21:58 
GeneralClient does not support authentication protocol requested by server Pin
msgclb20-Feb-06 0:00
msgclb20-Feb-06 0:00 
GeneralRe: Client does not support authentication protocol requested by server Pin
Custec25-Sep-07 21:55
Custec25-Sep-07 21:55 
QuestionWhy reference ICSharpCode.SharpZipLib? Pin
Mardawi13-Jul-05 2:40
Mardawi13-Jul-05 2:40 
AnswerRe: Why reference ICSharpCode.SharpZipLib? Pin
Custec13-Jul-05 4:35
Custec13-Jul-05 4:35 
GeneralRe: Why reference ICSharpCode.SharpZipLib? Pin
Alex LE8-Jan-06 1:10
Alex LE8-Jan-06 1:10 
QuestionMySQL .NET Connector? Pin
Huisheng Chen4-Jul-05 3:07
Huisheng Chen4-Jul-05 3:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.