How to use MySql with ASP.NET

This tutorial will show you how to easily write ASP.NET applications to interface with MySQL. Usually ASP.NET is commonly used with Microsoft SQL Server but I will show you how you can easily integrate with MySQL with some simple code. This is the way I like to do this - I have used this method for a long time and it works for my needs, there are other ways such as using LINQ which is out of scope of this article. By the way, there is no warranty of any kind provided with this so use it at your own risk!

Prerequisites

MySQL Database Server

ASP.NET 2.0 or later

MySql.Data.dll (MySQL .NET connector)

MySqlHelper.cs

Setup and Install MySQL

A new MySQL server installation is out of the scope of this article but you will need to setup and install an instance of MySql and setup your database with appropriate user credentials. You will need to use the server address and database user credentials in your connection strings so keep a note of them. If you are testing this locally you should be ok to use root but DO NOT EVER deploy any web applications with root as the user in your connection string this is asking for trouble..

Install the files into your project

The connector needs to be copied into the /bin directory of the asp.net application and the MySqlHelper.cs needs to be installed in your /App_Code folder.

Define your connection string

In our web.config we need to add a new connectionstring to our mysql database. Simply add a new connection string in your configuration section like this.

<configuration>

<connectionStrings>

<add name="dbconn.net" connectionString="Server=localhost;Uid=root;Pwd=password;Database=mydatabase;default command timeout=240;"/>

</connectionStrings>

</configuration>

Pretty simple - we just add our servername, username, password and database name.

MySqlHelper.cs - Database Access Class

The MySqlHelper.cs is a class I wrote quite a few years ago before the days of LINQ but I continue to reuse it in all of my applications today. Originally it was called SqlHelper.cs for use with SQL Server but I reworked it for MySQL. I am not a code expert so I would love to hear any suggestions on making this more efficient or if I am doing something really bad. Its pretty easy to follow, I am just using the MySql Client objects and encapsulating them in methods within my static class to be re-used in my application.

This is a basic version which only returns DataSets its possible to modify this to return Single values (Scalars) and so forth but for basic needs this should work no problem. I have also written a modified version of this to accept the connection string as a paramater as the application I was working on had a need for a dynamic connection string determind by the application state.

Once everything is installed in the right place you can then start writing some MySql queries in your application.

In its basic non-parameterised form you can bind to a gridview/datagrid/datalist/repeater like this

string sql = "SELECT * FROM myTable";

GridView1.DataSource = MySqlHelper.GetDataset(sql).Tables[0];

GridView1.DataBind();

Reasonably Neat!

Final words

This is quite a basic example and I wouldnt recommend deploying this in high throughput websites without thorough testing. I seem to remember I had issues with earlier versions of the MySql .Net Connector not pooling connections properly and causing problems under load but recently I have not had any issues (touch wood) the new MySql Connector seems to work fine.