Using MySQL with ASP.Net, under Mono on Linux

Right, I decided there's not much point writing more about ASP.Net pages until I can show you how to do something useful with them. So I'm going to dive straight in and install the MySQL connector for ASP.Net, so we can do a bit of database-driven page stuff.

Setting up a MySQL database

First, you'll need MySQL. I'm not going to tell you how to install that (depends on your Linux), but hopefully it could be as easy as it was for me on Ubuntu:

sudo apt-get install mysql-server mysql-client

How you create your database and users is up to you. I like MySQL Query Browser and phpMyAdmin. The latter needs PHP and a web server; but MySQL Query Browser is a standalone desktop application. If you want to do this in a tutorial style via the command line, you can run these once you've installed the MySQL server and client packages:

$ mysql -uroot -p
...

mysql> CREATE DATABASE cdcat;
Query OK, 1 row affected (0.10 sec)

mysql> USE cdcat;
Database changed

mysql> CREATE TABLE artist ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO artist VALUES(null, 'Wire');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO artist VALUES(null, 'The Fall');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM artist;
+----+----------+
| id | name     |
+----+----------+
|  1 | Wire     |
|  2 | The Fall |
+----+----------+
2 rows in set (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON cdcat.* TO cdcat@localhost IDENTIFIED BY 'hardpassword';
Query OK, 0 rows affected (0.12 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.13 sec)

What we've done here is setup the first table in a CD catalogue database, namely:

  • Created a new database called cdcat
  • Created a table called artist in that database
  • Put two artists into the table
  • Created a user called cdcat with SELECT, UPDATE, DELETE and INSERT privileges on all tables in the cdcat database
  • Made those privileges active

Installing Connector/Net

Now you've got a MySQL database server, database, table and user set up, you'll need the MySQL connector for ASP.Net. You need to download Connector/Net from the MySQL website. The one you need is Windows Binaries, no installer (ZIP).

Once the zip file is downloaded, create a directory somewhere and unzip its contents into it. The file you're after is in the resulting bin directory, MySQL.Data.dll. To install it, use the gacutil tool included in the Mono installer, which puts it into the right place in your Mono library directory:

gacutil -i /path/to/unzipped/connector/bin/MySQL.Data.dll

If gacutil isn't on your path you'll need to reference it correctly using its full path.

Creating a simple page to show data from a table

To prove you've got everything installed correctly, we'll create a page to display the contents of the artist table using one of the standard ASP.Net controls. Like I've said before, this isn't going to be a full ASP.Net tutorial, so I'm not going to try to explain Web Forms and all that jazz: I'm just giving a few examples to help you get the pieces working nicely together. See one of the countless ASP.Net books for more detail. (By the way, if anyone can recommend a half-decent tutorial book for ASP.Net, please let me know, as the ones I've looked at are generally good reference works, but lousy tutorials.) I'll try to put more tutorial material in as I learn more about ASP.Net.

First, create a file called artists.aspx inside your project folder.

Next, put this code into the file and save it:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>CD cat</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

    <script runat="server">
    private void Page_Load(Object sender, EventArgs e)
    {
       string connectionString = "Server=localhost;Database=cdcat;User ID=cdcat;Password=hardpassword;Pooling=false;";
       MySqlConnection dbcon = new MySqlConnection(connectionString);
       dbcon.Open();

       MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM artist", dbcon);
       DataSet ds = new DataSet();
       adapter.Fill(ds, "result");

       dbcon.Close();
       dbcon = null;

       ArtistsControl.DataSource = ds.Tables["result"];
       ArtistsControl.DataBind();
    }
    </script>

  </head>

  <body>
    <h1>Artists</h1>
    <asp:DataGrid runat="server" id="ArtistsControl" />
  </body>

</html>

Finally, you need a web.config file, again in the project root directory. This contains application settings, such as which libraries your application needs. It should contain the following to enable the MySQL libraries to be loaded:

<configuration>
  <system.web>
    <compilation>
      <assemblies>
        <add assembly="MySql.Data"/>
      </assemblies>
    </compilation>
  </system.web>
</configuration>

Now run your application again with xsp2 from inside the project directory and browse to http://localhost:8080/artists.aspx. You should see this:

I didn't get to code-behind pages this time. Maybe next time.

Comments

Thanks man!!, it is very

Thanks man!!, it is very useful.

Thank you

Thank you very much for such a good tutorial.

Thanx for the support..

Its a very nice tutorial combining both Mysql and Linux..

Easier way to install MySql.Data

On Ubuntu, I think you can get MySql.Data through

sudo apt-get install libmysql-cil-dev

Thanks, didn't know that.

Thanks, didn't know that.

Can'r run the test page.

Elliot,
I followed your instructions to a 'T' but when I do to run the page it gives me a message that it can't find the MySql.Data assembly.
I'm running Mono 2.2.2 on Fedora Core 9 with MySql 5.0.5 and MySql Connector/NET 5.2.6.

Dick Steflik
Binghamton University

I also came across this

I also came across this problem and found out that instead of using <%@ Import Namespace="MySql.Data" %> you should use <%@ Assembly name = "MySql.Data" %>

I hope you've already solved

I hope you've already solved it, however I came across the same situation and thought to post how I've solved it. Problem was that Linux is case sensitive, so be aware of what you copy to gac location. For me, renaming downloaded file mysql.data.dll to MySql.Data.dll did the trick (I also had to delete file from /usr/lib/mono/gac/MySql.Data/6.1.3.0__c5687fc88969c44d/ as it did not override somehow (?)).
Hope this will save some time for others.
p.s. Thanks for the tutorial, Elliot!

MySql.Data.dl not in GAC?

is it possible to use MySql.Data.dl from local directory rather than install in GAC? I am using shared hosting!

Page won't refresh

Well first, thanks for putting this out. I was able to to get mysql and mono working together on mac os x.

There is only one problem. When I load the example page (with my own db info of course) it loads right the first time but when I make changes and try to refresh it doesn't change the output. I have to kill xsp server, restart it, and then refresh the page to the changes to appear. The of course the problem repeats.

Any ideas?

Thanks!

Notes about Mono

Nice tutorial. It shows how easy to use and manipulate both ASP.Net and MySQL together as a powerful combination. It seems that if you are using open source under Mono development platform, it will run without problems.

Using a Windows binary, MySQL Connector/NET

I am running OSX, but new to that, and was curious about installing a windows binary of the MySQL Connector/NET provider. I would think there would be a compatibility issue. Am I understanding this right that I should install the windows binary of the provider? Please confirm and add any information on this. Thanks.

Hi Dave. On Linux, I

Hi Dave. On Linux, I installed the binary intended for Windows. I imagine you would need to do the same thing for Mac too. Not sure exactly how it works, but Mono can load the required classes from the Windows binary, regardless of OS.

How to enable Cache in ASP.NET with MySQL

Any Idea that how to enable cache with MySQL?

Query caching in MySQL? Or

Query caching in MySQL? Or doing the caching at the ASP.NET layer? Not something I've looked at yet, but interesting question.

Hi Elliot, Please can you

Hi Elliot,

Please can you comment on how this relates to the
licensing issues mentioned in this:
http://www.theregister.co.uk/2008/10/03/codeplex_licenses/

Regards

Hello there. As Mono is a

Hello there. As Mono is a fully open source project, enabling .Net applications to run on Linux and other operating systems, it's entirely possible to do ASP.Net coding without using any of the code on CodePlex. If you're prepared to avoid using code from CodePlex offered under faux open source licences like the MS-LPL (as used for MEF, the software that sparked this issue), there's no limitation on what you can do with Mono. (Miguel de Icaza recommends avoiding any MS-LPL code.) As with using any open source components, it's worth ensuring that what you're using is released under a licence which fits your requirements. I tend to stick with software which is purely open source, i.e. released under an OSI-compliant open source licence (like Mono's BSD-style licence).

It's worth reading this section of the Mono FAQ for more information about how Mono relates to Microsoft software.

CodePlex / Licenses

There are plenty of projects on CodePlex with licences other than the MS ones. If you use code provided by Microsoft that won't be the issue but there are many other good projects there with fully open licences. Depends on the project really.