How to connect MySQL and ASP.NET using ODBC

Author：Eve Cole Update Time：2009-07-01 16:28:47

MySQL, considered "the most popular open source database in the world", has gone through a long journey from its humble beginnings as a low-cost database server to a server that drives huge Web sites and important business systems. However, if you are an ASP.NET developer, then you will always encounter a dissatisfaction: MySQL was originally designed for applications on the UNIX platform, so support for Windows is secondary.

But now, the Windows version of MySQL has the same features and stability as the UNIX version, and it is considered a viable database server for Windows development. Now let's see how you can connect to a MySQL database within the .NET framework using ODBC.

Installation

Download and install the Windows version of MySQL. Installation is easy - just follow the prompts and you'll be up and running in no time. If you encounter a problem, you can go to the MySQL forum to seek help and solutions.

To connect ASP.NET and MySQL, you need to use ODBC.NET. Generally speaking, the DataProvider of ODBC.NET is part of the standard .NET framework (version 1.1 and above), so it will be automatically installed with the latter.

Once you confirm that ODBC.NET is installed, you will need to download the ODBC driver for MySQL. Again, the MySQL developers are happy to help - they provide these drivers on their Web site. You can read the FAQ document when downloading the file, which will list all the problems you may encounter during the installation of the MySQL ODBC driver on your system.

All done? Now let's start with some code.

Connecting to MySQL with ASP.NET

One of my favorite things to do is read, and when I'm not writing instructional articles like this one, I find a quiet corner to complete my bibliography list. Unfortunately, I'm not a very organized person, so this often makes a mess.

So what does this have to do with the topic we are going to talk about today? Well, that's the beginning of my first example, which is to create a database of books like the ones in List 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, you must use the command line client software "mysql.exe" in the "bin" folder in the MySQL installation directory. The following are the specific commands:

c:mysqlbin>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 is created, you can start inserting data. List B lists some items. Now, do the same thing with ASP.NET - execute the SELECT query (Listing C) and display the results in the browser. If everything is fine—the MySQL server is running, the MySQL ODBC driver is installed correctly, and the Books table contains data—you should see a page like the one in Figure A.

Figure A

Now let's take a closer look at the code listing. Everything starts by loading the required .NET libraries. Since I'm using ODBC.NET to connect to the MySQL server, I need to call the System.Data.Odbc and System.Data assemblies. Once the assembly is called, the connection string must be defined, which includes modifying the Server, Database, uid, and pwd variables to reflect the local server settings.

After creating the OdbcConnection() and OdbcCommand() objects, initialize the local OdbcDataReader() object to retrieve data from the "Books" table. This object provides an ExecuteReader() command to execute SQL queries. The rest is routine: specify OdbcDataReader() as the data source of the dbgooks DataGrid, and call the DataBind() method of the DataGrid() object to bind the two.

Of course, you can do more with the database - insert data into tables, modify them using UPDATE, get a grid of records using the DELETE command, or just use conditions in the WHERE statement. Select a subset.

Exception Handling

Now let's look at some exception handling (Listing D) methods to deal with some unforeseen situations that programmers often encounter. As you'll notice, Listing D uses a nested try-catch structure to provide multiple levels of error handling. The code snippet listed below should help you understand it better:

<% try {try {} catch (OdbcException e)
{} catch (Exception e) { } finally { }
} catch (OdbcException e)
{ } catch (Exception e) {}%>

Now let's first look at the inner "try-catch" structure. This structure is used to handle errors that may occur when creating instances of OdbcCommand() or OdbcDataReader() objects. If the "Books" table does not exist in the database, such an error may occur. If such an error occurs, the "catch" part will issue a new Exception(). The "finally" block of the inner "try-catch" structure then closes the OdbcConnection() object before further execution of the script.

The action then goes to the outer "try-catch" structure. The outer structure can handle two types of exceptions - OdbcException() or regular Exception(). The former is generated when the connection to the database server cannot be opened for some reason or the script cannot instantiate the OdbcConnection() object, while the latter is used to handle exceptions generated by the inner "try-catch" structure. No matter which of the two situations occurs, there will be an ASP.NET tag server control that displays the appropriate error message to the user.

Figure B shows you what happens when the script tries to connect to a database server that doesn't exist (this is handled by the outer "try-catch" structure).

Figure B and

Figure C show you the error message that will appear when the script attempts to access a non-existent database table (note that exceptions generated by the inner "try-catch" structure will be handled by the outer structure):

Figure C

above is an introduction to how to use the DataGrid server control of MySQL and ASP.NET. There's a lot more to be said about how you can use these two technologies; so I hope this article and the other reference resources provided above will help you get a feel for it.