C# Connect To MySQL In Visual Studio

Introduction

In this article, we’ll learn to connect the MySQL Database with the C# .NET Framework application in Visual Studio. This is a foundational hands-on article that will help beginners to connect a widely used freely available database like MySQL in C# through other freely available tools. This article doesn’t require any paid applications or software.

Step 1

First of all, let us start with the installation of XAMPP. XAMPP makes it possible to create a local server with Apache and MySQL databases with other features.

Once the installation is done, the control panel should look something like this.

Step 2

Click on Start on Apache, MySQL, and FileZilla. The buttons turn the labels into Stop. 

Step 3

Open your browser and type, localhost/phpmyadmin/ 

The page will look something like as follows. This is where the database can be accessed, and viewed and instructions can be performed right away. This is the location to access, the MySQL database.

Step 4

Click on Databases. 

Now, Under Create Database, Type the name of the new database we are going to create.

Type, TestDB, leave the Collation as it is, and then click on Create.

Step 5

The Database TestDB has now been created. Let us create a Table now.

Step 6

Run the SQL Query 

CREATE TABLE PhoneBook
{
    Id int,
    Name varchar(255),
    MobileNo int
}; 

Step 7

The Table has been created with an empty set and zero rows.

Step 8

You can view the table on the database from the Structure Option in the bar.

Click to browse and see the contents of the table. 

Step 9

Now, let us install Visual Studio. The Community Edition is free and can be accessed from the Microsoft Official Website.

Once installed, the Visual Studio Installer can be opened. Now, click on Launch.

Step 10

Click on Create a New Project.

Create the project for Windows Forms App in C#, .NET Framework with the Solution name of your choice.

Step 11

You can see the solution Explorers as shown below with the references and resources all set up for the Framework.

Step 12

Now, let us go to Tools on the Menu Bar and select Connect to Database. There are numerous but no MySQL Database Options. This can be resolved by installing the MySQL Connector and MySQL for Visual Studio.

Step 13

Next, we need to install MySQL Connectors and MySQL for Visual Studio. Visit the following sites to obtain the download file for the respective installations, ADO.NET Driver for MySQL (Connector/NET) and MySQL Installer for Visual Studio.

Download the ADO.NET Driver for MySQL (Connector/NET) and MySQL Installer for Visual Studio 1.2.10 – Microsoft Windows – x86 MSI Installer. 

Step 14

Once the MySQL Installations are done, restart Visual Studio.

Step 15

Now, go to Solution Explorer. Right-click on the project, click on Add, and select the Reference.

Step 16

Click on MySql.Data and MySql.DataEntityFramework for EF6 primarily. And then Click on Ok.

Step 17

Now, let us go back to Server Explorer. Double-Click on Data Connections and Choose Add Connections. You’ll be taken to Choose Data Source Option. 

Under Data Source, Now, we can see the MySQL Database option. This wasn’t there primarily when we hadn’t installed MySQL Connector and MySQL for Visual Studio.

Step 18

Now, Click on Continue. The Server Name, User Name, Password, and Database Name are to be filled in.

Remember, this is the same as that of your database on the localhost/phpmyadmin 

I’ve put in my Server name which is “localhost”, a User name that is “root” and my password. Remember, above we made the Database TestDB for this very experiment. Here, we can use the lowercase name “testdb”. 

Click on Test Connection and as the success is prompted, choose OK. 

Note

In case any issues occur that prompt an error for database connection, choose the Advanced option and under the SSL Mode, Choose None. This would solve the error.

Step 19

Now, we can see the localhost(testdb) database on our DataConnections with its tables. Remember, the PhoneBook table that we created earlier in phpmyadmin? See, it’s here now.

Step 20

Right Click on the table, and we can see the options for Trigger, Table Script Generation, Query, Designing, and Retrieving Data.

When we choose the generate Table Script, the following script will pop up. 

Similarly, if we choose to retrieve data, the data can be seen. Here, since, we haven’t entered any data, NULL is seen.

Step 21

To test, if the connection has been made, let's go back to localhost/phpmyadmin and insert some data on the table PhoneBook.

When we reload, we can see the NULL has now changed to numerous data on Id, Name, and MobileNo in the phonebook.

Step 22

Moreover, to connect using MySQL Connector/NET for Windows Applications, we can do it through the following code.

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=localhost;uid=root;" +
    "pwd=null;database=testdb";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection();
    conn.ConnectionString = myConnectionString;
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

The above code can be used to integrate MySQL in C# in the native Windows authentication and CRUD operations can then be performed on top of it. We’ll learn more about it in our next article.

Conclusion

Thus, in this article, we learned in step by step process the method to connect the MySQL Database in Visual Studio for C# .NET Framework. We set up XAMPP, Visual Studio, MySQL Connector, and MySQL for Visual Studio and then created a Windows Form App in C# .NET Framework. Lastly, we connected the MySQL database to our Windows Form Application.