devxlogo

Using MySQL 4.0 with .NET

Using MySQL 4.0 with .NET

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 mysqlin directory and run an application called winmysqladmin.exe (see Figure 1). This application is much like Enterprise Manager in SQL Server.

Using the MySQL Control Center
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.
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:

  1. Create a connection using the MySQLConnection class.
  2. Create a SELECT command to execute using the MySQLCommand class.
  3. Create an adapter using the MySQLDataAdapter class.
  4. Attach your query object to your adapter object.
  5. 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:

  1. Create a connection using the MySQLConnection class.
  2. Create an INSERT command to execute using the MySQLCommand class.
  3. Add parameters to the Command object representing the columns you wish to insert.
  4. 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 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.

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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist