Using MySQL with ASP.Net, under Mono on Linux

The text below is not mine. However, I found it worth sharing since it is very appreciable and may take you by surprise, when you first see an ASP.NET application of yours, running on Linux or Mac, access your MySql database. I have just tried and it works wonderfully.

I hope you'll enjoy it!

Source: http://townx.org/blog/elliot/using-mysql-asp-net-under-mono-linux

##################################################################

Using MySQL with ASP.Net, under Mono on Linux

Fri, 2008-10-03 21:35 — elliot

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: