|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.
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:
- Create a connection using the MySQLConnection class.
- Create a SELECT command to execute using the MySQLCommand class.
- Create an adapter using the MySQLDataAdapter class.
- Attach your query object to your adapter object.
- Create and fill a DataSet object using the adapter.
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:
- Create a connection using the MySQLConnection class.
- Create an INSERT command to execute using the MySQLCommand class.
- Add parameters to the Command object representing the columns you wish to insert.
- Execute your command using the ExecuteNonQuery method.
Listing 2 demonstrates how to add data to a MySQL database using classes found in the ByteFx .NET driver.
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 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.
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.
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.