.NET console app on Mono on Raspberry Pi writing to MySQL (compiled on a Mac)

Sun, Sep 17, 2017 6-minute read

Update: if you’re just looking to build a simple LAMP server on a Pi, then there is an updated version of this guide which installs more up to date versions of e.g., PHP, here.

For reasons I won’t go into, I have an unnecessarily complicated set of requirements:

  • Dev environment is Windows 10 + Visual Studio 2015 running as a virtual machine inside VMWare Fusion on Max OSX 10.11.6
  • Target application is .NET 4.5.2 running on a Raspberry Pi 3 under Mono
  • App must write data to MySQL db

This is my first time working with Mono. It’s actually relatively straight-forward to setup, but there were one or two hoops I jumped through, so I figure this may be helpful. You could choose to use VSCode on your Mac if you didn’t want to go down the Windows dev VM route.

TL;DR

The main issue I encountered is the latest MySQL Connector for .NET that you get using NuGET doesn’t seem to work in Mono. The trick was to install an early version (6.4.6 to be exact) and once I did this, all seemed to work OK. Have not got much further yet, so not yet sure what other issues I will encounter.

This assumes you have a completely fresh copy of Raspbian latest. If you haven’t, follow a guide here. I used Etcher for the first time and was very impressed as it worked perfectly.

I am using the -Lite (no UI) version so may have had to do extra stuff that the full fat version didn’t require. I also configured Apache/PHP etc. so I could have phpmyadmin running.

If you’re doing this, then commands you need to type will look like this.

  1. Set the Pi up on a screen with keyboard, boot it, login using default credentials (user pi, pass: raspberry)
  2. sudo raspi-config. From here: enable SSH, assign a hostname (if you like) and also change the root password
  3. sudo reboot
  4. Login again. ifconfig. In the first section (eth0) make a note of your IP address. Something like 192.168.1.2. Note: it’s a good idea to set a static IP address for the next bit, but I couldn’t for the life of me get this working using the ‘new’ Raspbian way using dhcpcd.conf. (I didn’t want to do the old /etc/network/interfaces route, even though I know this works.) So instead I have just configured my router to ensure the Pi gets the same IP address every time, but you may like to set a static IP if you can’t do that.
  5. On your Mac, open up terminal and type sudo nano /etc/hosts. Enter your Mac password. At the bottom of the file create a new line with the ip address a space and the hostname you’d like to use for your Pi. (Possibly what you set it to earlier.) 192.168.1.2 mypi. This is just so we don’t have to reference the Pi by IP every time.
  6. Now time to start installing some stuff. Let’s start with the easy stuff.
  7. In Terminal, ssh pi@mypi and enter your password. Type ‘yes’ when prompted.
  8. sudo apt-get update
  9. sudo apt-get upgrade
  10. sudo-apt-get install samba-common-bin. This will install Samba to ease getting things on to the Pi. I just share the pi user’s home directory.
  11. sudo nano /etc/samba/smb.cnf. Add the following to the bottom of the file.
[PiShare]
Comment = Pi shared folder
Path = /home/pi
Browseable = yes
Writeable = Yes
only guest = no
create mask = 0777
directory mask = 0777
Public = yes
Guest ok = yes
  1. sudo smbpasswd -a pi. Enter a password for the samba share.
  2. sudo /etc/init.d/samba restart. With this done you should be able to open a new Finder window on your Mac and using ⌘K type smb://mypi.local, enter pi as user and the password, and then connect to ‘PiShare’. Test that you can create files/folders etc.
  3. sudo apt-get install apache2. This will install the apache web server. Once installed you should be able (on your Mac) to browse to http://mypi.local and see a working holding page.
  4. sudo apt-get install php5. Yes it’s older than current v7, but it works for me.
  5. sudo apt-get install mysql-server --fix-missing
  6. sudo apt-get install mysql-client
  7. At time of writing there was an issue installing php5-mysql - the bits that let you access mysql from PHP. The error received was due to a dependency failure on libmysqlclient18 - ‘no installation candidate’ or similar. The default packages won’t install it and the package managers don’t reference it by default. To get around this sudo /etc/apt/sources.list and add this to the end: deb http://security.debian.org/debian-security wheezy/updates main. Then sudo apt-get update and finally sudo apt-get install php5-mysql. If this doesn’t work, you may need to install libmysqlclient18 manually from here and then try the php5-mysql install again.
  8. Apache/PHP/MySQL should now be installed. The first thing to do is sort out a few security issues. sudo mysql_secure_installation will open a graphical wizard and step you through basic things, such as setting a root password and so on. Be aware that if you follow its advice and remote non-localhost login for the root user then you will need to add a new user which can login from non-localhost in order to be able to use phpmyadmin (etc.) from non-Pi places.
  9. You can do this with: GRANT ALL PRIVILEGES ON *.* To 'user'@'%' IDENTIFIED BY 'password';. Note that the % indicates a hostname wildcard which may not be desirable in your situation. This gives your ‘user’ account access to everything, so there’s little difference to leaving it as root, but hey ho - you can always scale back its permissions once you have things working.
  10. sudo apt-get install phpmyadmin. This will install phpmyadmin. At the end a graphical wizard should step you through the configuration. You should now be able to get in to phpmyadmin at http://mypi.local/phpmyadmin using the user created above.
  11. sudo apt-get install mono-runtime

This should be all the setup you need to do. Now you should be able to create a simple “HelloWorld” console application in Visual Studio copy the .exe to a location on your Pi and then on the Pi run mono HelloWorld.exe and it should run no probs.

Getting mysql working

As noted previously, I found that none of the installable packages for MySQL in Visual Studio / NuGet would actually work within Mono. A mixture of error messages to do with TypeLoadException, mysql.data Exception has been thrown by the target of an invocation. etc. My way around this was to download a specific version of MySql.data from MySQL - this one: 6.4.6 to be exact. Add this to your solution, and add a reference to the .dll. Copy mysql.data.dll to your Pi and then gacutil -i mysql.data.dll. Note: if you get an issue with the assembly name not matching, rename the .dll to MySql.Data.dll (note that capitalisation) and try gacutil again.

With that in place, create a ‘test’ database in MySQL with one table ‘testtable’ with one column of type varchar(100) called ‘col1’. Add the following to your HelloWorld app:

private static void TestMySQL()
        {
            var connection = new MySqlConnection
            {
                ConnectionString = "Server=localhost;Database=test;Uid=user;Pwd=password;"
            };

            try
            {
                Console.WriteLine("Trying to create connection");
                var command = connection.CreateCommand();
                command.CommandText = "INSERT INTO testtable (col1) VALUES ('Bob')";
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.InnerException.Message);
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                connection.Close();
            }
        }

Call that method from your app, compile, copy to the Pi and mono HelloWorld.exe as before. If all is well, it should fire one entry in to your test table.

And that should be that. If I’ve missed anything, leave a comment.