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;
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
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
- 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.