Open Source

Using ODBC you can connect MySQL with ASP.NET

The popular MySQL database application is no stranger to the Microsoft Windows platform. You can use ODBC to connect to MySQL databases with ASP.NET.

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.

0 comments

Editor's Picks