townx - Using MySQL with ASP.Net, under Mono on Linux - Comments

Thanks man!!, it is very

Crist — Wed, 07 Sep 2011 21:08:10 -0500

Thanks man!!, it is very useful.

MySql.Data.dl not in GAC?

petar petrov — Wed, 30 Mar 2011 10:08:16 -0500

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

Thank you

melf — Sun, 06 Feb 2011 12:29:17 -0600

Thank you very much for such a good tutorial.

I also came across this

KAD — Thu, 15 Jul 2010 03:04:56 -0500

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

Thanx for the support..

Vaibhav — Thu, 08 Jul 2010 09:40:22 -0500

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

Thanks, didn't know that.

elliot — Sat, 29 May 2010 06:56:40 -0500

Thanks, didn't know that.

Easier way to install MySql.Data

Karl Dickman — Mon, 17 May 2010 17:55:28 -0500

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

sudo apt-get install libmysql-cil-dev

I hope you've already solved

Mykolas Simutis — Fri, 25 Dec 2009 18:05:41 -0600

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!

Can'r run the test page.

Dick Steflik — Fri, 23 Oct 2009 12:29:14 -0500

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

Page won't refresh

Will — Thu, 06 Aug 2009 17:35:31 -0500

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

web designer tutorial — Sun, 08 Mar 2009 09:33:24 -0500

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.

Hi Dave. On Linux, I

elliot — Wed, 17 Dec 2008 15:06:22 -0600

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.

Using a Windows binary, MySQL Connector/NET

Dave — Wed, 17 Dec 2008 12:24:04 -0600

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.

CodePlex / Licenses

John — Sun, 30 Nov 2008 12:45:42 -0600

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.

Query caching in MySQL? Or

elliot — Wed, 12 Nov 2008 09:49:30 -0600

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

Using MySQL with ASP.Net, under Mono on Linux

elliot — Fri, 03 Oct 2008 16:35:57 -0500

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.