How to connect MySql Database using C#

Step by steps

Create Database in MySql.

create database ConnectCsharpToMysql

Paste this command in your phpMyAdmin and after click Go to Run the command.

Done creating database in phpMyAdmin.

Create table in MySql.

CREATE TABLE userinfo(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    contact VARCHAR(30),
    email VARCHAR(30),
    PRIMARY KEY(id)
);

paste this command into SQL and RUN it.

Done creating table in database “connecttomysqldt”

add MySQL Reference in Project.

Copy this command and run to your project; Dowload DLL File

Install-Package MySql.Data -Version 6.7.9

Or click this button.

mySQL reference

Now.. Let go for coding.

Connect to Database “connecttomysqldt”

        private void connect()
        {
            try
            {
                server = "localhost"; // server Hostname
                database = "connecttomysqldt"; // database name
                uid = "root"; // username
                password = ""; //password
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
                connection = new MySqlConnection(connectionString);
                //MessageBox.Show("Success");
            }
            catch (Exception)
            {
                MessageBox.Show("ERROR");
            }
        }

Insert function

/// <summary>
        /// insert class function
        /// </summary>
        public void Insert()
        {
            try
            {
                string query = "INSERT INTO userinfo (name, contact, email)" + "" +
                              " VALUES('" + textBox1.Text + "', '" + textBox2.Text +
                              "', '" + textBox3.Text + "')";

                //open connection
                if (this.OpenConnection() == true)
                {
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();

                    //close connection
                    this.CloseConnection();
                }
            }
            catch (Exception ex)
            {
                this.CloseConnection();
                throw;
            }
        }

Update function

//Update statement
        public void Update()
        {
            string query = "UPDATE userinfo SET name='"+textBox1.Text+ "', contact='" + 
            textBox2.Text + "' , email='" + textBox3.Text + "' WHERE id='"+label2.Text+"'";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = query;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

Delete function

//Delete statement
        public void Delete()
        {
            string query = "DELETE FROM userinfo WHERE id='"+label2.Text+"'";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

connection ON/OFF

        /// <summary>
        /// Openconnection class function
        /// </summary>
        /// <returns></returns>
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

Download Sample File