Arjun Krishna Babu bio photo

Arjun Krishna Babu

Python. Machine Learning. Systems. Open source.

Email LinkedIn Github

Setting up Visual Studio to connect to a MySql database can be trickier than you think. Here, I attempt to elucidate the whole process for you.

I was never really a fan of the ubiquitous Windows operating system, and have stayed away from it whenever I can (which fortunately happens to be always).

However, due to certain particular circumstances, I had to develop a C# project on Visual Studio that would work with a MySql database.

As I discovered the hard way, connecting to the database on my host machine wasn’t particularly a piece of cake as I had initially thought it would be. And becuase I had to run around through pages and pages of StackOverflow for what turned out to be a trivial solution in retrospect, I sort of figured I’d rather pen down the whole process.*

Install Visual Studio (if you haven’t already done so)

Before you do anything else, get your copy of Visual Studio from the official website. The exact version you want to use depends on what all you plan to do with Visual Studio. Keep in mind that not all versions of Visual Studio are free, and some are available for free only for a limited trial period. As for me, I got myself the Visual Studio Community 2013 – ticked all the right boxes. I didn’t go for the latest version as it was giving me minor hiccups.

Get MySql Packages for Visual Studio and .Net

You’re going to have to install a couple of packages in Windows so that your program can connect to the MySql database. Go to MySql’s official website and download and install the following two packages:

It’s best if you install the latest available versions of both the packages. Keep a note of the installation directory of both the above packages.

Setup your database

The next thing you need to do is set up Apache and MySql on your system. The way you install it depends on your specific platform, and I leave it to you to figure out that part. For most linux distributions, install the LAMP stack using the package manager on your system. If you’re on Windows, try installing WAMP, XAMP or some viable alternative (I have never really used WAMP/XAMP, so I have limited knowledge on that).

Optionally, if somebody from your local network has Apache and MySql installed, you might as well make use of their database over the network rather than taking the trouble to install the whole thing onto your machine.

In my case, I had Windows 8.1 installed as a VirtualBox guest OS in linux. Apache and MySql were already installed on my host linux system, and so I used that instead of installing everything in my guest Windows OS. This situation is similar to connecting to a database over the network.

Also, to make your life a little bit easier while dealing with databases you need to install phpMyAdmin or something similar. PhpMyAdmin is pretty-good for all practical purposes, and is included by default in WAMP. Linux users however might have to explicitly install phpMyAdmin.

Once you have done all of that, you’re ready to start coding MySql applications using Visual Studio!

If you are connecting to a remote MySql database (ie., over the network), there’s this one last step that should be done on the server side.

(If you’re not connectiong to a remote MySql database, skip to the next section).

You have to change the bind-address of MySql if you haven’t previously done so. In the simplest terms, the address you specify in bind tells mysql where to listen to.

If you’re unsure of what bind address you should choose, you may use 0.0.0.0 that allows any computer which is able to reach the server over the network to connect to the database. This should suffice for developmental purposes at least. I recommend you contact your system-administrator/DBA for more advise on this.

To change the bind-address of mysql in linux, open /etc/mysql/my.conf. (For windows, go through the WAMP menu on the status-bar to locate this file).

Go through the file, and you should eventually find the line “bind-adress”. Change the whole line to

bind-address = 0.0.0.0

You would usually have to restart MySql once the bind-address has been changed. Also keep a note of the IP address of the system where your database server resides. This information is necessary when connecting to the database from Visual Studio.

Now you’re all set to establish connection from Visual Studio to a MySql database on a remote server!

I’ll walk you through creating a sample C# code in Visual Studio that connects to a database in Visual Studio.

Sample Database Connectivity Program

Add a connection (GUI method)

First up, you would need to set up a connection to the database if you want to do anything practical. There are several methods to accomplish this. Here, I’m going to mention one of the ways.

For that, click the ‘Server Explorer’ on your screen. If you can’t find it, click on ‘View’ in the menustrip, and select the ‘Server Explorer’ option.

From the ‘Server Explorer’ option, search around until you find an option like “Add Database Connection”. Click on it.

A simple menu like the one shown below would appear.

Add Connection Window

First, make sure that the data source is set to MySql Database. If it’s something else by default, click on the Change button and choose MySql from the options.

Fill up the data.

If the server resides in your own machine, type “localhost” as the servername. Else, type the IP address of the remote server.

If your mysql does not need a password (which tends to be the default if you’ve installed WAMP), leave the password field blank.

Choose your database from the drop-down combo-box. Once you enter the servername and login credentials, the database names would most likely have been automatically populated into the combo-box for choosing database.

Finally, press the Test Connection button to make sure that the connection works. If everything went according to plan, you should be getting something like this:

Test Connection Window

Set up MySql Reference in your program

Next up, create a C# project in Visual Studio if you haven’t already done so.

For almost all projects that works with MySql database, you need to have access to certain libraries. The libraries would have come along with the MySql/Net Connector which you installed earlier.

On your project in the Solution Explorer pane, right click References, and choose Add Reference. Click on the browse button, and locate MySql.Data.dll. It will be in the installation directory of MySql/Connector installation directory.

I had done a default installation for the MySql/Net connector, and so the aforementioned .dll was in
C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.8\Assemblies\v4.0

This dll needs to be added as a reference in your Visual Studio project for you to make use of the MySql classes.

Keep in mind that, depending on your specific installation, there might be minor changes in the location of the .dll, or its exact path name. I leave it to your good judgement to figure out the exact file-path in your system.

Sample Code to test Database Connectivity.

Below is a sample program that connects to a database. The critical bit is conString, the value of which you’d get from the Properties panel when you (from the Server Explorer) click on the database connection you just created.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient; // Required for making use of methods to facilitate MySql operations

namespace DbConnectivity
{
    class DbCon
    {
        private MySqlConnection conn;
        private string conString;

        public DbCon()
        {
            conString = "server=10.100.16.124;user;id=myuser;password=mypass;database=mydb;persistsecurityinfo=True";
	    // You might have to manually add in the password field in the connection string.

            conn = new MySqlConnection(conString);
        }

        public void openConnection()
        {
            try
            {
                conn.Open(); //open the connection

                Console.WriteLine(" Connection Status: " + conn.Ping());
                // Ping() returns true if connection has been successfully established
            }
            catch (Exception)
            {
                throw;
            }

            conn.Close(); //close the connection
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            DbCon obj = new DbCon();
            obj.openConnection();
            Console.ReadKey();
        }
    }
}

(In the conString above, replace the connection string with the values for your specific database.)

For more diverse and versatile methods on how you can create connection strings, check out https://www.connectionstrings.com/mysql/ .

If everything went well, the program would output: Connection Status: True

You should all be set by now!