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:
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 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.
General Motors (GM) recently announced its entry into the Eclipse Foundation. The Eclipse Foundation is a prominent open-source software foundation. In addition, GMC announced its contribution of “uProtocol” to facilitate
The world of software development is changing drastically with the introduction of Artificial Intelligence and Machine Learning technologies. In the past, software developers were in charge of the entire development
Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved
Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several
Creating a website is not easy, but protecting your website is equally important. Implementing copyright laws ensures that the substance of your website remains secure and sheltered. Copyrighting your website