Utilize MySQL's features through .NET

by Tony Patton | May 03, 2005 6:06:00 PM

Tags: Databases, OPEN SOURCE, .NET, Storage, Programming languages, Middleware, Tony Patton, MySQL, database, Microsoft .NET, SQL, .NET Newsletter

7 comment(s)

Takeaway: Discover how you can use the .NET Framework to work with all aspects of your MySQL data. Also, get extended examples of how to work with MySQL data via .NET.

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.

7 comment(s)