TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!
MySQL has emerged as a viable option when choosing a database platform. A good testimonial to MySQL's power lies in the many companies utilizing it, which include Google, The Associated Press, and NASA. The cost of ownership associated with open source software is usually cited when describing its benefits, but a large organization such as Google will not entrust its most valuable commodity (data) to a system based on cost alone. MySQL's true power extends beyond price; it offers a plethora of add-on tools that are available via open source and commercial products.
Data integration with .NET
The MySQL community has developed MySQL data providers—which are basically the links between a data source and code. The following connectors are available for the Windows platform:
- MySQL Connector/Net 1.0 (formerly known as ByteFX.Data): An open-source .NET data provider for MySQL. It is written entirely in managed code (C#) and is available from the MySQL.com Web site. Note: I use the MySQL Connector/Net 1.0 data provider in the examples included in this article. A Windows installer makes it easy to install. Example code and basic documentation are included as well.
- MySQLDirect .NET Data Provider: A commercial data provider available from Core Lab. Prices vary depending on the license purchased, but trial versions are available for you to download.
If you are working with Mono, a PHP connector is available from the Mono Web site. If you are running Mono on Windows, the connectors are included with the installer. Otherwise, follow the link to the appropriate operating system to locate the necessary connector.
Using a MySQL data provider
After installing a MySQL data provider, you must reference it in your code to utilize its classes. You may use the MySql.Data.MySqlClient namespace when accessing a MySQL server. This is accomplished with the using statement in C#:
Additionally, you may add a reference to an ASP.NET Web Form via its Imports directive:
<%@ Import Namespace "MySql.Data.MySqlClient" %>
Or, you can put the complete path to a class in your code by including its namespace, but this involves a lot more typing than adding a reference using the Imports directive. With the namespace established, now you can interact with the MySQL server. The MySql.Data.MySqlClient namespace provides numerous classes for working with MySQL data. Here is a sampling of these classes:
- MySqlConnection: The connection to the MySQL server/database.
- MySqlDataAdapter: A set of data commands and database connections used to fill a DataSet object and update a MySQL database.
- MySqlDataReader: Allows you to read data from a MySQL database. It is a forward-only stream.
- MySqlCommand: Handles sending commands to the database server.
- MySqlException: The exception is raised when MySQL problems arise.
We'll use this subset of the available classes to interact with our sample database.
Connecting to MySQL
The first step in working with MySQL is connecting to it via the MySQLConnection class. An instance of the MySqlConnection class is instantiated with a connection string. It tells the code where to find the MySQL server as well as other options.Listing A contains a sample connection string. This connection string accesses a MySQL server named MySQLTestServer using the supplied user id and password. A connection is made to the techrepublic database. I have anonymous access set up on my test MySQL server (this is a huge security hole, so it is not recommended for a production server) so the following connection string will be used in the sample code:
With the connection string established, the MySqlConnection object's Open method is called to open the connection. When the connection is open, you can send commands to the server and you can access database data.
Combining ASP.NET and MySQL
Let's take it further by combining the MySqlConnection class with other classes to generate a list of databases on a MySQL server. Listing B shows an ASP.NET Web Form that utilizes C#. It establishes a connection, sends a command (SHOW DATABASES) to the server, and displays the results via a MySqlReader object.
Using a MySqlCommand object to send the SHOW DATABASES command to the MySQL server is the same as entering it via the MySQL monitor. The difference is that another object is used to retrieve the result set. The MySqlDataReader object is instantiated with the results of the command (via MySqlCommand's ExecuteReader method). The GetString method of the MySqlDataReader object is used to display data from the result set via an ASP.NET Label control. The zero index is passed to the GetString method to display the first column from the current row of the result set (in the while loop).
Notes about Mono
If you are using the open source Mono development platform, the code from the examples will run without problems with one small change. The MySql data provider classes are located in the ByteFX.Data.MySqlClient namespace as opposed to the MySql.Data.MySqlClient namespace on Windows. This is leftover from the fact that MySql data provider was originally developed by ByteFX, Inc., but it was acquired by MySQL. So, you should use the following using statement to access MySql with Mono:
The combination of MySQL and .NET provides a powerful development platform. MySQL's strong support within the open source community and .NET's acceptance in the open source community via the Mono project make the combination a highly portable development environment with support on Windows as well as the many flavors of UNIX and Linux.
Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a production environment on a daily basis.