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#:
usingMySql.Data.MySqlClient;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:
"server=localhost; database=sitepoint;"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:
usingByteFX.Data.MySqlClient;Wrapping up
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.