This series of articles will show you how to get MySQL up and running and how to use it from ASP.NET applications. I bet noone has missed the hype around MySQL but still for a Windows developer there is a big question: why one should look at it? I mean, a Windows developer typically use MS SQL Server (or the free lightweight version MSDE) or for smaller solutions they can turn to Access. Well, to me MySQL is interesting for several reasons:
1. Better performance and less risk of data corruption than with Access
2. Cheaper (free) than MS SQL
3. Many webhosts offer MySQL and often at a cheaper price than a MS SQL database
While I personally always have used Access for my lowend solutions I have, for a year or so, now turned into MySQL. Many of my troubles has gone. One classic is (when you have an Access-based web application running at a webhost) is how to be able to add a column to a table. The database file(s) are probably locked because people are currently visiting your site. With MySQL you modify tables pretty much as with MS SQL, MySQL is a ?real? database where there is a dedicated server process running on the database server box which handles all requests, so file locks as for Access never happens ? which pretty much is simple file sharing.
Moreover, deployment is so easy. No uploading a .mdb file with FTP. You can easily export your local MySQL database into a (small, easily read and easily modifiable) SQL script. Your webhost most probably uses phpMyAdmin (which is a webbased administration tools) and using that you just paste the file content into and runs it.
Now lets see how to get MySQL running on your own box.
STEP 1: The database engine
MySQL is pretty easy to install. Just download
"MySQL database server & standard clients" ( select the current Production release )
from http://www.mysql.com/downloads/index.html
Then scroll down to "Windows downloads" and while you can select any of the two variants, I find it easier to go for the file with an installer. Be prepared - it will take a while, version 4.0.15 which is the latest production release as of this writing is over 22 MB.
During download, go grab a cup of coffee or something, and then just unzip the downloaded file. The simply run setup.exe.
Choose Typical installation and soon enough you will have the engine installed under your c:\mysql directory.
First of all we start c:\mysql\bin\winmysqladmin.exe. The first time it?s started it will show you a dialog where you should enter a user name and password. This is not much to care about just enter something, like ?test? and ?test? again.
After this MySQL is installed and running as well. It will now start automatically each time you logon to your computer (i.e it has added itself to the Startup folder). It is not installed as a service, if you are interested in that then please read more at:
http://www.mysql.com/doc/en/Windows_installation.html
http://www.mysql.com/doc/en/Windows_prepare_environment.html
http://www.mysql.com/doc/en/Windows_server_first_start.html
STEP 2: Something to administer it with
Although there are some graphical (Windows based) clients available for administrating and querying MySQL databases, I havn?t found anyone really good. All of them lacks at least some important features, so there is just one thing to do. Install PHP and use phpMyAdmin. PhpMyAdmin is superior in all aspects and it is also what most webhosts make available, so learning it is a good choice.
At http://sourceforge.net/project/showfiles.php?group_id=75482 there is a installer package which will install PHP and hook it up with IIS, so just download the latest release of install-php.exe and run it.
Next, download phpMyAdmin. http://sourceforge.net/project/showfiles.php?group_id=23067&release_id=191859
Be sure to select the.file which doesn?t contain php3, but only php. As of this writing the file is called phpMyAdmin-2.5.4-php.zip ( and NOT phpMyAdmin-2.5.4-php3.zip ).
Unzip it and copy all files to a newly created directory: c:\inetpub\phpMyAdmin (or somewhere under your webroot).
Now just surf to http://localhost/phpMyAdmin.
STEP 3: Lets create our first MySQL database and a table
When you surf to http://localhost/phpMyAdmin you will be presented with a page like this:
Now lets create a new database, call it aspnettest:
And press Create.
Now create a new table:
Enter columns for your new table:
And we can insert new values into the table:
We should really create a user account for us to use from our ASP.NET applications, but to keep this tutorial simple we are just gonna use the system admin account root with a blank password, which was added to the server by default when installing it.
So, in short, what we have now is a database called aspnettest and a table in it called product containing one row. So, now we should just be able to start coding our ASP.NET application, right? Sorry to say, but no, we must be able to access the database somehow from our code - .ie there needs to be a layer installed.
STEP 4: ODBC installation
We do have some options as how to connect to the database from ASP.NET. There are some (free and commercial) ADO.NET providers working against MySQL, but the one I am choosing is going via ODBC. I am very well aware that ODBC is not the preferred way of working with databases anymore (it?s not 1995), but when it comes to MySQL I still prefer it. The driver (called MyODBC) is ?official? ? that is developed by the MySQL team, it has been around for quite a while and therefore I trust it. So therefore I use it. In the future there might come (or maybe there already are) ADO.NET providers which are good enough but MyODBC is so far my first choice. So, lets install the ODBC driver. Go to http://www.mysql.com/downloads and look for Connector/ODBC - MySQL ODBC driver. As usual you should take the production release, and under Windows Downloads select the ?Driver Installer?. Run the install file and you are done. Now the driver is installed.
STEP 5: ASP.NET code
So the plan is to use the Odbc-classes in .NET framework. They are included in the 1.1 version of ASP.NET framework, so if your still using 1.0 this is a golden opportunity to upgrade ? something you should do anyhow.
http://www.asp.net is where you find it.
Now to the code. Just create a new ASP.NET project and on a webform throw in a datagrid. To keep it easy just set AutoGenerateColumns to True:
<?xml:namespace prefix = asp /><asp:datagrid id=DataGrid1 datakeyfield="id" autogeneratecolumns="True" runat="server"></asp:datagrid>
Then in the codebehind page, make sure you are
using System.Data.Odbc;
And then in Page_Load:
OdbcConnection oConn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Option=131072;Stmt=;Database=aspnettest;Uid=root;Pwd=;");
oConn.Open();
DataSet oDataSet = new System.Data.DataSet("tab1");
OdbcDataAdapter oAdapter = new OdbcDataAdapter("select * from product",oConn);
oAdapter.Fill(oDataSet, "tab1");
DataTable dt = oDataSet.Tables["tab1"];
DataGrid1.DataSource = dt;
DataGrid1.DataBind();
oConn.Close();
Not so hard is it? Your first MySQL application is done!
