Connecting to a MySQL Database Programmatically

Microsoft Visual Studio lets you create a database connection using its IDE, and it’s quite powerful as well, but personally I prefer to create my database connections programmatically. When creating my connection’s code manually I find it easier to follow the code, plus it can be easier to debug as well. I am by no means saying that this is the best way to connect to a database – it’s just my personal preference.

In this article I will show you how to connect to a MySQL database programmatically through C#.

Why MySQL?

I personally like working with MySQL because it’s freely available under the GPL License. I have also been working with MySQL from version 4 (and that’s around 6 years now) so by now I am quite used to it. If you would like to install MySQL you can find it here: MySQL Community Server

The Connection String

Every database has its own specific connection string, and since there are so many different databases out there, it’s impossible to remember each connection string by heart. That is why I use ConnectionStrings.com to refresh my memory. It’s a great resource with connection strings for probably every type of database you can think of.

For this example we will be using the standard connection string which assumes MySQL is accessible on the default port of 3306. You can check out other MySQL connection strings here: Connection strings for MySQL

SERVER=myServerAddress; DATABASE=myDataBase; UID=myUsername; PWD=myPassword;

So, if MySQL was installed locally, and we had a database called testdb, and a MySQL database user called testuser with password testpass, the connection string would look like this:

SERVER=localhost; DATABASE=testdb; UID=testuser; PWD=testpass;

The Connection Code

To connect to MySQL we need to download and install the MySQL Connector for .NET which can be found here: MySQL Connectors. This is basically the MySQL standardized database driver for .NET platforms.

Once installed you will have access to the MySQL classes from .NET, but to use them you must first reference them. So from within your project navigate to the Add Reference window and select the MySql.Data .NET component. This will add it to you project and now you should be able to access the MySql.Data.MySqlClient namespace, which we will be using to connect to the database.

The actual code used to connect to the database is very simple. As long as we have access to the MySqlConnection class which is in the MySql.Data.MySqlClient namespace, we should be able to connect as shown in the below code:

string connStr = "SERVER=localhost;" +
                 "DATABASE=testdb;" +
                 "UID=testuser;" +
                 "PWD=testpass;";

MySqlConnection conn = new MySqlConnection(connStr);

conn.Open();

// Read/Write to and from your database here

conn.Close();

Since the connection string is an application setting and usually only changes per installation of your application, I would recommend placing the connection string in the Settings.settings file. If you do not know what this file is you can check out my article on Using the Settings file in C#.

If you were to place your connection string there you would then be able to access it from anywhere throughout your code by calling Properties.Settings.Default.ConnectionString.

Below is how your database connection code looks now:

MySqlConnection conn = new MySqlConnection(Properties.Settings.Default.ConnectionString);

conn.Open();

// Read/Write to and from your database here

conn.Close();

I hope you found this article useful. Stay tuned because I will soon be talking about how to retrieve data from MySQL.

Dave