Using MySQL in C # –3 Try adding INSERT, updating UPDATE, deleting DELETE

Assumptions and preparations

C # article

-Use MySQL in C #– 1. Development environment installation
-Use MySQL in C #– 2. SELECT / screen display
–Using MySQL in C # –3 Additional updates and deletions [this article]
-Determining whether database data is NULL in C #
-C # delegate and => (lambda expression) are treated like pointers to C functions
-Dynamic add control– 1. Button click
-Dynamic add control– 2. Browse textbox

Last time uses MySQL Connectors .NET in Visual Studio 2019 to connect to MySQL for the time being and display the data contents on the screen using SELECT. did. This time, we will change the DB data itself (˶ ･ ᴗ ･) ੭

environment

• OS：Windows 10 Pro
  –Development environment: Visual Studio 2019 (using MySQL Connectors .NET)
  –Database: MySQL 5.7

MySQL server

Test user: * test * (password: test1)
Database: ** manutest **
Table name: ** testtb **

	id	name	memo
Type	INT	VARCHAR(64)	VARCHAR(256)
Required attributes	PRIMARY KEY	NOT NULL	Initial value NULL
Other attributes	AUTO_INCREMENT	-	-

Premise

Visual Studio 2019 and MySQL are installed and MySQL Connectors .NET is installed as in Last time (Visual Studio automatically MySQL Connectors Recognizes the library of

Work procedure

Coding in Visual Studio 2019

Placement of component controls

This time, press the button → pick up the data with MySQL → display it on the screen
In addition to this, add a form so that you can enter a name and note, add, update, etc.
If you select the list box on the upper right, the corresponding ID will be displayed on the screen, so you can update or delete the corresponding ID while selecting it, but I will not touch on detailed UI control

I created a new Windows C # form project and opened it by default in Form1.cs

–Upper left: Datagrid * dataGridView1 *
–Upper right: 2 list boxes * listBox1 and listBox2 *
–When selected, the ID corresponding to the text box “* idNum *” is displayed.
–Lower left: Button to read SQL * button1 *
–Right center: Name input * textBoxName * ・ Memo input * textBoxMemo *
–Lower right: Selected ID * idNum * -Newly added SQL execution * buttonAdd * -Updated SQL execution * buttonEdit * -Deleted SQL execution * buttonDel *

Paste these.
The data grid and list box have been placed to display SQL data

Added MySQL Connector .NET reference

Add references like Same as when installing MySQL.

Since it is actually automatically recognized in “Assembly”, if you enter “mysql” in the search screen on the upper right in the reference manager, “** MySql.Data **” will appear in many lines as shown in the screen above. Since it will come out, for the time being, select only one, any one (turn on the check box that appears when you hover the mouse to the left of the line) and select “OK” …

“** MySql.Data **” has been added (\ * ˘꒳˘ \ *)
It contains a C # object that handles MySQL.

Describe the source when the button is clicked

For the sake of simplicity this time as well, I just coded the click event behavior of Form1.cs by simply loading the SQL execution with a button click. Originally, the scale is usually large, so in most cases it is divided into independent classes instead of button clicks on the form. .. ..

Form1.cs sample code </summary> <div>

Form1.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySqlFormsTest
{
    public partial class Form1 : Form
    {
        //ID list of acquired data(Form internal variables)
        private List<int> idNums;

        //Selected ID
        private int selId;

        //MySQL connection information
        private string connStr = "server=127.0.0.1;user id=test;password=test1;database=manutest";

        public Form1()
        {
            InitializeComponent();
        }

        /**
         *Connect button(Connect to MySQL server and get list data)
         * */
        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);

            //Initialize screen and internal variables
            listBox1.Items.Clear();
            listBox2.Items.Clear();
            this.idNums = new List<int>();
            selId = -1;
            idNum.Text = "";
            buttonEdit.Enabled = false;
            buttonDel.Enabled = false;

            try
            {
                //Open connection
                conn.Open();

                //Table to get data
                DataTable tbl = new DataTable();

                //Execute SQL
                MySqlDataAdapter dataAdp = new MySqlDataAdapter("SELECT id, name, memo FROM testtb", conn);
                dataAdp.Fill(tbl);

                //Display in dataGridView
                dataGridView1.DataSource = tbl;

                //When referencing the execution result line by line
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    DataRow row = tbl.Rows[i];  //Data row

                    //Add item to the list box on the right
                    listBox1.Items.Add(row[1]);
                    listBox2.Items.Add(row[2]);
                    //Add ID to internal variable at the same time
                    this.idNums.Add((int)row[0]);
                }

                //Close connection
                conn.Close();
            }
            catch (MySqlException mse)
            {
                MessageBox.Show(mse.Message, "Data acquisition error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /**
         *Select a row in the list box
         */
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.listBox_SelectedIndexChanged(sender, e);

            //Display the selected ID on the screen
            if(listBox1.SelectedIndex != -1)
            {
                this.selId = idNums[listBox1.SelectedIndex];
                idNum.Text = idNums[listBox1.SelectedIndex].ToString();
            }
            else
            {
                this.selId = -1;
                idNum.Text = "";
            }
        }
        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.listBox_SelectedIndexChanged(sender, e);

            //Display the selected ID on the screen
            if (listBox2.SelectedIndex != -1)
            {
                this.selId = idNums[listBox2.SelectedIndex];
                idNum.Text = idNums[listBox2.SelectedIndex].ToString();
            }
            else
            {
                this.selId = -1;
                idNum.Text = "";
            }
        }
        private void listBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Add / Remove cannot be used unless either is selected
            if(listBox1.SelectedIndex != -1 || listBox2.SelectedIndex != -1)
            {
                buttonEdit.Enabled = true;
                buttonDel.Enabled = true;
            }
            else
            {
                buttonEdit.Enabled = false;
                buttonDel.Enabled = false;
            }
        }


        /**
         *Select the new add button
         */
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          //Execution transaction

            //Newly added SQL commands
            string sqlCmd = @"INSERT INTO testtb (name, memo) VALUES (@name, @memo)";

            //Start additional query
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                //Set of stakeholders
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              //Open connection
                
                //Transaction monitoring started
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                //SQL execution
                cmd.ExecuteNonQuery();

                //Commit DB
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   //Rollback when an exception occurs
                MessageBox.Show(mse.Message, "Data addition error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                //Connection closes
                cmd.Connection.Close();
            }
        }

        /**
         *Select the edit button
         */
        private void buttonEdit_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          //Execution transaction

            //Edit SQL command
            string sqlCmd = @"UPDATE testtb SET name = @name, memo = @memo WHERE id = @id";

            //Start edit query
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                //Set of stakeholders using the selected ID
                cmd.Parameters.AddWithValue("id", this.selId);
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              //Open connection

                //Transaction monitoring started
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                //SQL execution
                cmd.ExecuteNonQuery();

                //Commit DB
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   //Rollback when an exception occurs
                MessageBox.Show(mse.Message, "Data update error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                //Connection closes
                cmd.Connection.Close();
            }
        }

        /**
         *Select the delete button
         */
        private void buttonDel_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          //Execution transaction

            //Delete SQL command
            string sqlCmd = @"DELETE FROM testtb WHERE id = @id";

            //Start delete query
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                //Set of stakeholders using the selected ID
                cmd.Parameters.AddWithValue("id", this.selId);

                cmd.Connection.Open();              //Open connection

                //Transaction monitoring started
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                //SQL execution
                cmd.ExecuteNonQuery();

                //Commit DB
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   //Rollback when an exception occurs
                MessageBox.Show(mse.Message, "Data deletion error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                //Connection closes
                cmd.Connection.Close();
            }
        }
    }
}

</div></details>

First, as an internal variable of the C # source of the form, in order to understand ** which ID was acquired from the MySQL data ** and which ID corresponds to the item in the list box on the upper right, Form1 Added private variable to

//ID list of acquired data(Form internal variables)
private List<int> idNums;

//Selected ID
private int selId;

I think that “SELECT id, name, memo ~” is executed in * button1_Click () * with the button that executes the SQL to be read, but it is not just picked up in the list box on the upper right, but the corresponding ID is internally I also implemented the code to handle the variable idNums

//When referencing the execution result line by line
for (int i = 0; i < tbl.Rows.Count; i++)
{
    DataRow row = tbl.Rows[i];  //Data row
    
    //Add item to the list box on the right
    listBox1.Items.Add(row[1]);
    listBox2.Items.Add(row[2]);
    //Add ID to internal variable at the same time
    this.idNums.Add((int)row[0]);
}

row [0] is the ID number of the acquired data, but if you execute Add () to List as it is, an error that Object cannot be implicitly converted will occur, so I cast it with (int).

listBox1.SelectedIndex
listBox2.SelectedIndex

It is used in listBox1_SelectedIndexChanged () etc., but the selected index number (starting with 0) of the listbox uses this (-1 if it is not selected). When selected, I added a mechanism to display it in the field of the selected ID of idNum. The selected ID is stored in an internal variable.

this.selId = idNums[listBox1.SelectedIndex];

I used MySqlCommand for SQL to update the contents of DB such as INSERT and UPDATE, but I used MySqlCommand except for SELECT because this placeholder is easier to use.

string sqlCmd = @"INSERT INTO testtb (name, memo) VALUES (@name, @memo)";
MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

And when updating, we use transactions. I try to roll back whenever an exception occurs, but honestly it’s hard to think about the timing, so this time it’s easy and only catch the exception ;;

try
{
    trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
    cmd.ExecuteNonQuery();
    trans.Commit();
}
catch (MySqlException mse)
{
    trans.Rollback();                   //Rollback when an exception occurs
}

Execution result

This time, the button such as Add does not have a reload function, so after pressing the button such as Add, press the “Load” button to redisplay it (it is troublesome …)

If you first update the data with ID 6 assuming you have data like the image below … “Update”-> “Load”, the update was successful (˶ ･ ᴗ ･) ੭

Next, select the data with ID 5 “Testing” and delete it. Press the load button again and the deletion was successful! !!

I will add it at the end It was a great success (˶ ･ ᴗ ･) ੭! !!

next time

I’ve been addicted to MySQL data when it’s NULL in Visual Studio, so I’ll follow that.

References

1. [Visual Studio] How to use MySql from C # How to connect? How to execute a SQL statement? –I was in trouble
2. I want to retrieve the result of SELECT from MySQL in C #

Related literature

1. Use transaction in MySQL in C # / Implementation of transaction in C #
2. Symfoware Server Application Development Guide (Embedded SQL Edition)-FUJITSU- / SQL Transaction Understanding what is
3. [For MySQL 5.1] Using MySqlTransaction / In C # Transaction implementation example of
4. IsolationLevel Enum / Transaction level in C # –.NET API