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
Listing A.

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;

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)

Once the “books” table has been created, go ahead
and insert some data.
Listing B
contains a couple of sample entries. Now, do the same thing with ASP.NET—execute
a SELECT query
(Listing C) and display
the results in your browser. If all is well—the MySQL server is running, the
MySQL ODBC drivers are correctly installed, and the “books” table
contains data—you should see something like Figure A.

Figure A

My book collection

Let’s take a closer look at the code listing. Everything
begins with the import of the required .NET libraries. Because I’m using
ODBC.NET to connect to the MySQL server, it is necessary to import the System.Data.Odbc and System.Data assemblies. Once the assemblies
have been imported, define the connection string, by modifying the Server,
Database, uid, and pwd variables to reflect your local server settings.

After creating instances of the OdbcConnection() and OdbcCommand()
object, initialize a local OdbcDataReader()
object to retrieve the data from the “books” table. This object
offers an ExecuteReader() command to
execute an SQL query. The rest is routine: assign the OdbcDataReader() object as the data source for the dbgooks DataGrid, and bind the two with a call to the

DataBind() method
of the DataGrid() object.

Of course, there is a lot more that you can do with a
database—INSERT data into tables, modify them using UPDATE, get rid of records
with the DELETE command, or just select particular subsets based on the
condition in a WHERE clause.


Additional resources


Handling exceptions

Let’s now bring in some exception handling
(Listing D) to take care of those
unforeseen situations that programmers seem to come across with alarming
regularity. As you will have noticed, Listing D uses nested
“try-catch” blocks to provide multiple levels of error handling. The
skeletal code listing below should help you understand this better:

<%
      try {
            try {

            } catch (OdbcException e) {

            } catch (Exception e) {

            } finally {

            }                     

      } catch (OdbcException e) {

      } catch (Exception e) {

}
%>

Let’s take the inner try-catch block first. This block
handles errors that might occur at the time of creating instances of the OdbcCommand() or OdbcDataReader() objects. Such errors might be generated if the
table “books” does not exist in the database. If such an error
occurs, the “catch” section raises a new Exception(). The “finally” block of the inner
“try-catch” then does its bit by closing the OdbcConnection() object before the script proceeds further.

The action then shifts to the outer try-catch block. This
one can handle two kinds of exceptions—an OdbcException()
or a general Exception(). The former
is raised when a connection cannot be opened to the database server or the
script cannot instantiate an OdbcConnection()
object for some reason, while the latter handles exceptions that have been
raised by the inner try-catch block. In both cases, an ASP.NET label server
control is used to display an appropriate error message to the user.

Figure B shows
you what happens when the script tries to connect to a non-existent database
server (this is handled by the outer try-catch block).

Figure B

Non-existent database

Figure C shows
you the error message that appears when the script tries to access a database
table that does not exist (note that the exception raised by the inner
try-catch block is handled by the outer one):

Figure C

Non-existent table

That’s about it for this introductory article on working
with MySQL and the ASP.NET DataGrid server control. There is a lot more that
you can do with these two technologies; I hope this article and the additional
references provided above will help get you started.