All database developers should know about MySQL.

MySQL is an open source database that you can use to develop robust applications without paying the steep licensing fees attached to other database systems.

I have been a Microsoft SQL Server developer for a number of years. One the things I like about SQL Server is that it is easy to install and once installed it is easy to work with. I can say the same thing about MySQL, an open source database, distributed under the GPL license that you can use for free in most applications. This article will discuss MySQL in depth including installation, configuration, product limitations, and using MySQL from your Visual Studio applications.

Installing MySQL

The first step to using MySQL is to download and install MySQL. You can download MySQL (version 4.0) from www.mysql.com. While you're at the mysql.com Web site, you should download a couple of other items as well:

  • MySQL Control Center?This tool provides an interface similar to the one found in the SQL Server Enterprise Manager.
  • ByteFX .NET Driver for MySQL?Use this .NET data provider to access data stored in MySQL databases.

Once you have downloaded MySQL you simply run the installer for MySQL. The MySQL installer runs like any other Windows installation applications. Once you have installed the application, how do you know it is running? It was not intuitive. I e-mailed a friend who pointed out the tools to use to see if the server was running or not.

To test that MySQL is running, go to the mysql\bin directory and run an application called winmysqladmin.exe (see Figure 1). This application is much like Enterprise Manager in SQL Server.

Figure 1: The MYSQLAdmin screen shows the status of a running server.
Figure 1: The MYSQLAdmin screen shows the status of a running server.

Using the MySQL Control Center

You use the MySQL Control Center to perform the following tasks:

  • Create databases
  • Create tables
  • Edit table structures
  • Query data
  • Table optimization

As your first step, you need to register the location of your MySQL. Figure 2 shows you the Register Server dialog box from the MySQL Control Center.

Figure 2: Register Server from the MySQL Control Center.
Figure 2: Register Server from the MySQL Control Center.

Register Server lets you provide the name of your connection, the name of the server (or IP address) where you installed your MySQL server, your username, and password. You can also specify a number of other optimization and connections options for your session.

Once you have registered your server, simply right-click on your newly registered server and select Connect from the shortcut menu. This will connect you to your server and populate a list of databases found on the selected MySQL server. Now you can drill down into your databases (and their tables) managed by your server. Figure 3 shows the MySQL control center displaying the structure of a table.

Figure 3: MySQL Control Center shows the structure of a table.
Figure 3: MySQL Control Center shows the structure of a table.

MySQL Control Center has a Query window that lets you execute queries or other SQL statements against your database.

Using the Query Window

The Query window is similar to Microsoft's Query Analyzer. To activate the Query window, click the SQL icon on the Control Center tool bar. This will activate a window like the one shown in Figure 4. From the Query window you can type in queries, UPDATE statements, INSERT statements, and other SQL statements.

Figure 4: The Query window.
Figure 4: The Query window.

Other Control Center Features

The MySQL Control Center has a number of other features. One feature that I find particularly useful is the Control Center's history tracking feature. When you run the MySQL Control Center, a History window tab (Figure 5) displays all of the commands executed by the control center.

Figure 5: The History pane tab of the MySQL Control Center.
Figure 5: The History pane tab of the MySQL Control Center.

Another of the MySQL Control Center's useful features is its ability to change the structure of tables in a database. You can choose from a number of configuration options that you can specify or alter for every MySQL table. For example, you can choose to specify a different table type for each MySQL table.

By default, MySQL creates tables using the default MyISAM table type. This table type has one major weakness?it does not support transactions. If you want to use a MySQL table in a transaction, you must change the table to the InnoDB table type. Figure 6 shows you the table modification screen with the table type option being specified as InnoDB. It doesn't get any easier than this.

Figure 6: Changing the table type.
Figure 6: Changing the table type.

MySQL Control Center is also where you go to generate creation scripts for your MySQL tables. To generate a creation script, do the following: Right-click on the table, select Tools, then choose Show Create. Work through the dialog box to specify the tables you wish to generate scripts for. Figure 7 shows the create script dialog box and the output from that dialog.

Figure 7: Creating a script.
Figure 7: Creating a script.

Now that you understand how to install and configure a MySQL database you can begin working with MySQL in your .NET applications.

Working with MySQL and Visual Studio .NET

Once you have installed MySQL you can begin working with it from Visual Studio .NET. The first step to working with MySQL and .NET is to download a data adapter that can work with MySQL. You can find the driver I am using for this article at the following Web site: www.bytefx.com

Once you have downloaded and installed the appropriate drivers you can begin working on your .NET/MySQL application.

Connecting to MySQL

To create a .NET/MySQL application, you need to create a new project and add references to the appropriate .NET driver; in this case the driver you want is called bytefx.data.dll. This driver provides all the classes necessary to connect to MySQL.

Now that you have created a project and added the appropriate references, you can begin writing code to attach to a MySQL database. To connect to a MySQL database you must create a connection object, specify a connection string, and open the connection. The following code snippet shows how to connect to a MySQL database. Note how similar the code here is to what you would use to connect to SQL Server; even the connection strings are similar.

Imports ByteFX.Data

'-- create a connection string
Dim cConnString As String
cConnString = _
"Server=HOTH;Database=junk;User ID=root;Password="

'-- create a connection object and
'-- specify the conn string
Dim oConn As New MySqlClient.MySqlConnection
oConn.ConnectionString = cConnString

'-- open and close the connection
oConn.Open()
oConn.Close()

Querying MySQL Data

With the connection now available, you can begin querying data from your database. Listing 1 demonstrates how to query data from a MySQL database and display its contents in a data grid.

Listing 1: Code to query a MySQL database and show results in a grid

Public Function LoadGrid()
    '-- create a connection string
    Dim cConnString As String
    cConnString = "Server=HOTH;Database=junk;User ID=root;Password="
    
    '-- create a connection object and specify the conn string
    Dim oConn As New MySqlClient.MySqlConnection
    oConn.ConnectionString = cConnString
    oConn.Open()
    
    '-- create a command object
    Dim oCmd As New MySqlClient.MySqlCommand
    oCmd.CommandText = "select * from customers"
    oCmd.Connection = oConn
    
    '-- create a data adapter
    Dim oAdapt As New ByteFX.Data.MySqlClient.MySqlDataAdapter
    oAdapt.SelectCommand = oCmd
    
    '-- load the data into a dataset
    Dim ds As New DataSet
    oAdapt.Fill(ds, "customers")
    
    '-- show the results in a grid
    Me.DataGrid1.DataSource = ds.Tables("customers")
    
    oConn.Close()
End Function

As you can see in Listing 1, querying data from MySQL is no different than SQL Server or any other database. You follow the same set of steps:

The .NET drivers for MySQL provide constructs exactly like those found in all of the other data adapters, including: Connection, Command, Parameter, Adapter, and other familiar data access objects.

Inserting MySQL Data

The next step is to add some data to your MySQL database. To add data to a MySQL table you do the following:

Listing 2 demonstrates how to add data to a MySQL database using classes found in the ByteFx .NET driver.

Listing 2: Adding data to a MySQL database

'-- create a connection string
Dim cConnString As String
cConnString = "Server=HOTH;Database=junk;User ID=root;Password="

'-- create a connection object and specify the conn string
Dim oConn As New MySqlClient.MySqlConnection
oConn.ConnectionString = cConnString
oConn.Open()

'-- create a command object
Dim oCmd As New MySqlClient.MySqlCommand
oCmd.CommandType = CommandType.Text
oCmd.CommandText = "insert into customers "
oCmd.CommandText +="(lastname, firstname)"
oCmd.CommandText +=" values (@lastname, @firstname)"

'-- specify the parameter values
oCmd.Parameters.Add("@lastname", MySqlClient.MySqlDbType.VarChar).Value = Me.txtLastName.Text
oCmd.Parameters.Add("@firstname", MySqlClient.MySqlDbType.VarChar).Value = Me.txtFirstName.Text

oCmd.Connection = oConn

Dim lnRetVal = oCmd.ExecuteNonQuery()

oConn.Close()

Me.LoadGrid()

As you can see, adding data to a MySQL database is very easy. If you have spent any time at all working with other databases you should feel right at home with MySQL.

MySQL Limitations and Uniqueness

Now that you know how to install and access data from MySQL, you need to understand some of the limitations and unique features of MySQL.

MySQL Limitations

MySQL doesn't support stored procedures. On the project teams I work with, we require that all data access must be performed using stored procedures. The MySQL.com Web site promises that MySQL version 5.0 will support stored procedures. (As stated earlier this article covers version 4.0).

MySQL version 4 also lacks support for triggers. A number of development shops would say that triggers are a critical feature. This is another feature slated for the MySQL 5.0 timeframe.

Finally, MySQL has no support for creating views. The MySQL Web site promises that this feature is slated for inclusion in the 5.1 release.

Unique and Cool MySQL Features

MySQL supports of a number of unique data types, or at least these data types are unique from a SQL Server developer's perspective. MySQL supports date and enum data types. The date type is useful for storing date data that does not need a time parameter?birthdates and anniversary dates come to mind. The enum data type gives developers the ability to control the contents of a data field via a list of valid values.

I think that it's also nice to see support in MySQL for replication, cross platform database deployment (Windows, Linux, SGI, HPUX, and others), full text indexing, searches, and many other features.

MySQL Performance

I did some rudimentary performance testing of MySQL and overall I am rather impressed. The machine I have MySQL installed on is a Pentium III running Windows 2000 with 128MB of RAM.

In the first part of my benchmark I loaded a small table with the first 95,000 words from a text file of Leo Tolstoy's War and Peace, which I use for benchmark testing. Loading these records took approx 53 minutes. Not too shabby considering the hardware.

Using the data I loaded I began running some queries. One query I ran was a simple group, count, and sort by of each word in the table similar to what you see in the following code:

SELECT word, COUNT(*)
FROM words
GROUP BY word
ORDER BY 2

The query returned 20,000 records grouped by word in approx 1.20 seconds. That's acceptable in my book. I performed numerous other queries like the one above and found that the performance was consistent across all queries. Not too bad on a machine with only 128MB of memory.

Conclusion

When I started this article I was very unfamiliar with MySQL. Like many people, I read about MySQL but wasn't sure how it might play a role in my development process. Now a short time later I find that MySQL is a nice database platform that I may recommend for some database applications to my clients. I say some because MySQL, while robust, is missing some very critical features needed for real enterprise applications. By the time MySQL version 5.1 is available, the product may be a serious contender for the kind of enterprise applications I build. At the time I'm writing this article, MySQL version 4.1.0 is in alpha according to the MySQL Web site. There isn't a posted time frame (that I could see) for version 5.

I also want to say thank you to Ed Leafe for helping me find the tools to make this article happen.