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!

In last week’s
column, I introduced the concept of utilizing MySQL as a backend for a .NET
application. The MySQL connector and examples will get you up and running, but
I’m going to take it a step further this week with extended examples of working
with MySQL data via .NET.

MySQL is an accepted player

While many larger companies recognize MySQL’s
power, it is also a valid choice for smaller organizations. Database systems
like SQL Server are often overkill for a small company or Web application.
Also, many hosting companies offer MySQL database functionality for
applications hosted in their environment. MySQL database connectors exist for
the majority of programming languages used today. This includes Perl, PHP,
Python, Java, .NET, and so forth. With this in mind, I’ll take a closer look at
more detailed examples of combining the power of MySQL with .NET. I’ll begin by
creating a sample database.

The MySQL backend

For this example, I’ll create a sample database called techrepublic. It consists of a table called Person with the
following columns/fields: firstName, lastName, address, address2, city, state, zip, country, and
id. The id is numeric value while all the other fields are text. The following
SQL is used to set up the database:

create database techrepublic;

Listing A
contains the SQL that creates the person table. (Note: The SQL
language and MySQL database administration are beyond the scope of this
article, but one excellent resource I recommend is O’Reilly’s Managing & Using MySQL, 2nd Edition by George Reese, Randy Jay Yarger, and Tim King.

With the table created, we can populate it with sample data
using the SQL in
Listing B. You can execute the previous SQL multiple times to insert multiple rows into the sample database (of course, you should use different
values).

Now that we have our sample database let’s
dig into the code. With the MySQL database set up, we can turn to .NET
to connect and utilize the data source.

Connecting to MySQL

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 C contains the connection string used
to access the sample database.

This connection string accesses a MySQL server named MySQLTestServer using the supplied user id and password. A
connection is made to the techrepublic database. With
the connection string established, the MySqlConnection
object’s Open method may be called to open the connection. With the connection
open, commands may be sent to work with MySQL. The example in Listing D displays all
data from the Person table in an ASP.NET DataGrid.

Issues with MySQL

While MySQL is an excellent database platform, many
organizations and developers shy away from it. One major issue is the perceived
lack of support. Many companies are comforted by the support agreements
available when utilizing other database platforms like SQL Server or DB2, but
the MySQL community is enormous. Help is often just a few keystrokes away on
the Internet (via a Google search). Also, MySQL AB offers support contracts as
well as commercial versions of the MySQL platform.

Scalability is another concern, but MySQL has proved itself
in some of the most volatile environments, including Google.

Another issue developers often cite for dismissing MySQL is
its lack of support for stored procedures. A stored procedure is a set of SQL
statements that are stored under a procedure name so that the statements can be
executed as a group by the database server. This is an issue that always
bothered me, but stored procedure support is being added to the platform with
the next version (5.x).

Putting it all together

The next example demonstrates all aspects of interacting
with MySQL. An ASP.NET DataGrid control is populated
with data from the people table. The user is allowed to edit or delete the data
as well as add new persons to the table. New data is added via TextBox controls located below the DataGrid
with a button performing the actual insert of data into the table.

The GetData method handles the
loading of data into the DataGrid. It resembles the
code previously used with a couple of additions. Two ASP.NET session variables
are used to track the column used to sort the data (the id field is used by default) and another
variable holds the sort order. These values are altered when a user selects a
column header to sort data based upon a column.

Additional methods are used to handle updating data as well
as deleting, sorting, and adding rows. Here is a rundown of these methods:

  • btnAddRow_Click: Triggered when the user selects the
    ‘Add Person’ button. The data from the TextBoxes
    is used to add a row to the underlying people table.
  • dgResults_Cancel: Triggered when a user selects the
    Cancel link within an individual DataGrid row
    while in edit mode. This negates the current action and reloads the data.
  • dgResults_Delete: The currently selected row is
    deleted from the person table. It is triggered when the user selects the
    Delete link within a row.
  • dgResults_Sort: Triggered when a user selects a column
    header to sort the data based upon that column. The session variables are
    set accordingly.
  • dgResults_Update: The selected row’s contents are
    permanently saved to the database. It is triggered when the user selects
    the Update button while in edit mode.

Listing E contains
the complete ASP.NET source.

A great platform with an attractive price

The widespread adoption of MySQL and its overwhelming
support within the open source community make it an attractive option. The
availability of a .NET connector for MySQL data sources extends its power to a
whole new group of developers, including open source developers active within
the Mono project. You can use the .NET
Framework to work with all aspects of your MySQL data.