A Step-by-Step Guide To Using MySQL with ASP.NET

Many people have asked me for this tutorial, so here goes.

For those of you who don't know, MySQL is an open-source DataBase server. Being that it's open-source, it's also free. That's about as low-cost as you can get. Of course, you may ask 'Why use MySQL'? Did you read the previous sentences?? It's free, as well as being a fairly robust database server!

To be able to use MySQL, there are a couple of downloads that must be done:

1. MySQL Itself
2. MySQL ODBC Driver

The Database Server (MySQL), itself can be downloaded here: http://dev.mysql.com/downloads/mysql/4.0.html

The Driver (for Windows) can be downloaded here:
http://dev.mysql.com/downloads/connector/odbc/3.51.html

The biggest hurdle in using MySQL, is the setup - much like many other users of MSDE have found out. Without a user interface, it's quite cumbersome, requiring a command prompt to do all the work. Here's the best page I've found so far to take you, step-by-step through this process:
http://www.analysisandsolutions.com/code/mybasic.htm#installation

Now, once this is all set up - guess what - you have no data. If you want, the above installation page will also help you get started creating databases, tables and fields for your sample data. But, if you want a much quicker way to populate your database server, so you can get started, AugustWind Software has user interface for MySQL called Data Management Toolkit. Check it out here

Now - the part you actually came here for - - the easy part!

On other parts of this site, you've seen many samples, using MS Access and SQL Server. As you might have noticed, there are only three major differences in using these.

1. Namespaces used
2. Connection String
3. Prefix to data classes (like: OleDbDataAdapter vs. SQLDataAdapter, specific to the Imported Namespaces

For MySQL, the driver which you downloaded (above), is an ODBC Driver, therefore, numbers 1 and two in the above list use 'ODBC':

<%@ Import Namespace="System.Data.ODBC" %>

And, you use classes like ODBCDataAdapter, ODBCDataReader, ODBCCommand, etc.

To finish out the three items in the above list, here, we show you the Connection String needed:

"Driver={MySQL ODBC 3.51 Driver};uid=YourUID;password=YourPWD;Server=YourServerIP;Option=16834;Database=YourDB;"
or, you can use:
"DRIVER={MySQL};SERVER=ServerIP; DATABASE=YourDB;USER=YourUID;PASSWORD=YourPWD; OPTION=3;"

Just change these few things, in your code, and you're off and running, using MySQL in your own ASP.Net documents!

Now, do you believe me, when I say that using MySQL in your ASP.Net documents is the easy part? In fact, if you have a hosted web site, chances are, most of the setup can be done by someone else and all you will need to do is the pages themselves!

Now, you can't put off using MySQL, for lack of knowledge!

To see a full code sample, check out this page:
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=mysqlcode
To see a code sample with ASP.Net 2.0, check out:
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=mySQLv2