01 Jun

Select,Insert,Update,Delete Data in MySQL using C#

This example shows how to insert ,update, delete and select data in MySQL.
Firstly, you should MySql Data Connector program.
https://dev.mysql.com/downloads/connector/net/6.9.html
Then, we need to add a reference to the MySyql.Data

  • Go to solution explorer of your project
  • Select add a reference
  • Click on .Net Tab
  • Select MySql.Data

Add Reference

Select Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();

                string stm = "SELECT * FROM Customers";
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
                dataAdapter.SelectCommand = new MySqlCommand(stm, connection);
                DataTable table = new DataTable();
                dataAdapter.Fill(table);
                return table;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (connection != null)
                    connection.Close();
            }

Insert Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "INSERT INTO Customers(Name) VALUES(@Name)";
                cmd.Prepare();

                cmd.Parameters.AddWithValue("@Name", "Bill Gates");
                cmd.ExecuteNonQuery();    
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

Update Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";

            MySqlConnection conn = null;
            MySqlTransaction tr = null;

            try
            {
                conn = new MySqlConnection(connectionString);
                conn.Open();
                tr = conn.BeginTransaction();

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = tr;

                cmd.CommandText = "UPDATE Customers SET Name='Bill Gates' WHERE Id=1";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE Books SET Title='New Title' WHERE Id=2";
                cmd.ExecuteNonQuery();

                tr.Commit();

            }
            catch (MySqlException ex)
            {
                tr.Rollback();
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }

Delete Command Example:

            string connectionString = @"server=localhost;userid=user1;password=12345;database=mydb";
            MySqlConnection connection = null;
            try
            {
                connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "delete from Customers where ID='" + "1" + "';";
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }