Home » C#

Connecting to MySQL database in C#

MySQL database connection with C#: Here, we are going to learn how to connect MySQL database with C#.Net with Example?
Submitted by IncludeHelp, on September 26, 2019

To connect with MySQL database using C#, we have some requirement to prepare setup are the following:

• Installation of MySQL in your PC.
• Installation of MySQL connector according to PC (32-bit, 64 bit) or according to installed Windows Operating System.
• Installation of Visual Studio.

Now, we are assuming you made all the above installations. First, we will open the MySQL terminal window, that appears like this,

Now, we will execute below SQL command to create database.

    mysql> create database mysqltest;

Using "show databases" command we can see how many databases is available in MySQL.

Now, we have to create an application in Visual Studio, that demonstrates the connectivity with MySQL. Here, we will develop a windows application. (Note: Before creating an application first we need to add the reference for MySQL connector in solution explorer, see the image below)

In solution explorer window, we have added a reference "MySql.Data", because does not come by default, we need it manually, if MySql connector is installed in your PC then right-click on "Reference" and then you can add it, without adding MySql.data reference we cannot connect with MySql database using C# program.

C# project with MySQL database connectivity

Now, we look to the application. Here, we took windows form with one command button.

In the above example, we changed two properties of both window form and command button are the following:

• Name
• Text

Forms properties

    Name:  "frmMySqlConn"
    Text:	"MY-SQL Connection test"

Button properties

    Name:	"btnConnect"
    Text:	"Connect"

C# source code to connect with MySQL database

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using MySql.Data.MySqlClient;


namespace MySqlConnect
{
    public partial class frmMySqlConn : Form
    {
        public frmMySqlConn()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            string MyConStr = "Server=localhost;Database=mysqltest;uid=root;pwd=root";

            MySqlConnection conn = new MySqlConnection(MyConStr);

            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                MessageBox.Show("Connection Opened Successfully");
                conn.Close();
            }
        }
    }
}

In the above code, most of the code is auto-generated we have to make the following changes,

• Added namespace
• Wrote code in button click event

Here, we added one extra namespace to use classes regarding MySQL connectivity.

    using MySql.Data.MySqlClient;

On button click event, we created a connection string that contains SERVER, SERVER could be "localhost" or we can also give the IP address of the server, in our case MySQL is installed on our PC. Then we are using the "localhost" as a SERVER and the database name is "mysqltest" along with the username and password of MySQL database.

Here, we have MySqlConnection class, then we pass the connection string while object creation, it is also possible to give connection string after object creation. Then we are using the "Open() method" for the database connection, if we pass the correct connection string with correct credentials, it will be connected to the database successfully, otherwise, it will generate an exception at the run time like this.

ERROR Message on connection fail:

Connection Successful message:

To check the successful connection, we can check the connection state, if it is connected successfully, we can display the error message and can close the connection.