Considered the "world's most popular open source database," MySQL has come a long way from its beginnings as a no-frills, low-cost database server to one that powers high-volume Web sites and critical business systems. If you're an ASP.NET developer, though, there's always been one tiny glitch: MySQL was originally targeted at applications on the UNIX platform, with Windows support a secondary priority.

Today, however, the Windows version of MySQL is as full-featured and stable as the UNIX version, and it is considered a viable database server for Windows development. Let's look at how you can use ODBC to connect to MySQL databases within the .NET Framework.

Installation
Download and install MySQL for Windows. Installation is a breeze—just follow the instructions and you should be up and running in no time at all. If you encounter problems, drop by the MySQL Forums for assistance and tweaks.

To connect to MySQL with ASP.NET, you need to use ODBC.NET. Typically, the ODBC.NET DataProvider is part of the standard .NET Framework (version 1.1 and above) and is automatically installed with the latter.

Once you've verified that ODBC.NET is installed, you need to download the ODBC drivers for MySQL. Once again, the developers of MySQL are ready with a helping hand—they've made these drivers easily available on their Web site. Use the time spent on the file download to take a look at the FAQ document, which outlines all the issues that you will face when installing the MySQL ODBC drivers on your system.

All done? Let's get started with some code.

Using MySQL with ASP.NET
One of my favorite hobbies is reading, and when I'm not working on tutorials like this one, I find a quiet corner to catch up on my reading list. Unfortunately, I'm not the most organized person, so this usually results in a huge mess and much confusion.

What does this have to do with anything? Well, it's a nice lead-in to my first example, which involves creating a database of books like the list below:

+---------------------+--------------+------+-----+------------+--------
+---------------------+--------------+------+-----+------------+----
---+
| Field               | Type         | Null | Key | Default    | Extra	    |
+---------------------+--------------+------+-----+------------+--------
+---------------------+--------------+------+-----+------------+----
---+
| id                  | int(5)       |      | PRI | NULL       |
auto_increment|
| title               | varchar(255) |      |     |            |		    |
| authors             | varchar(255) |      |     |            |		    |
| year_of_publication | year(4)      |      |     | 0000       |		    |
| date_added          | date         |      |     | 0000-00-00 |		    |
+---------------------+--------------+------+-----+------------+--------
+---------------------+--------------+------+-----+------------+----
---+

To create this table, use the following SQL query:

CREATE TABLE `books` (
  `id` int(5) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `authors` varchar(255) NOT NULL default '',
  `year_of_publication` year(4) NOT NULL default '0000',
  `date_added` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`id`)


) TYPE=MyISAM;

Execution
To execute this query, use the MySQL command-line client "mysql.exe" in the "bin" folder of your installation directory. Here's how:

c:\mysql\bin>mysql -u guest -p test

Enter password: ******

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13 to server version: 4.0.12-nt




Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



mysql> CREATE TABLE `books` (
    ->   `id` int(5) NOT NULL auto_increment,
    ->   `title` varchar(255) NOT NULL default '',
    ->   `authors` varchar(255) NOT NULL default '',
    ->   `year_of_publication` year(4) NOT NULL default '0000',
    ->   `date_added` date NOT NULL default '0000-00-00',
    ->   PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM;


Query OK, 0 rows affected (0.02 sec)