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.

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;
            }
        }

Leave a Reply

Your email address will not be published. Required fields are marked *