C#でMySQLを使う - 3.INSERT追加・UPDATE更新・DELETE削除してみる

# 前提と準備

## C#の記事
- [C#でMySQLを使う - 1.開発環境インストール](https://qiita.com/kazumi75kitty/items/9953bb84edd7717bc90a)
- [C#でMySQLを使う - 2.SELECT・画面表示](https://qiita.com/kazumi75kitty/items/dd391cab06762555dc5d)
- C#でMySQLを使う - 3.追加更新と削除【この記事】
- [C#でのデータベースのデータのNULLかどうかの判定](https://qiita.com/kazumi75kitty/items/64002467350a1ea08ab0)
- [C#のdelegate(デリゲート)と=>(ラムダ式)はC言語の関数へのポインタに似た扱い](https://qiita.com/kazumi75kitty/items/fdf4e1d2ea8e9c9326ba)
- [動的にコントロールを追加 - 1.ボタンクリック](https://qiita.com/kazumi75kitty/items/2e3f4c10a93d26032382)
- [動的にコントロールを追加 - 2.テキストボックスの参照](https://qiita.com/kazumi75kitty/items/093b1d679e016782d3af)
- [動的にコントロールを追加 - 3.DB(MySQL)から動的に作成](https://qiita.com/kazumi75kitty/items/3bbb8afdfbc21fb6b3f1)

[前回](https://qiita.com/kazumi75kitty/items/dd391cab06762555dc5d)はVisual Studio 2019にMySQL Connectors .NETを用いて、とりあえずMySQLに接続して、SELECTを用いてデータの内容を画面に表示しました。今回はDBのデータそのものを変更を施します(˶ ･ᴗ･ )੭

## 環境
- OS：Windows 10 Pro
- 開発環境：Visual Studio 2019 (MySQL Connectors .NET利用)
- データベース：MySQL 5.7

### MySQLサーバー
テスト用ユーザー：*test* (パスワード：test1)
データベース：**manutest**
テーブル名：**testtb**

||id|name|memo|
|:-:|:-:|:-:|:-:|
|型|INT|VARCHAR(64)|VARCHAR(256)|
|必須属性|PRIMARY KEY|NOT NULL|初期値NULL|
|その他属性|AUTO_INCREMENT|-|-|

## 前提
[前回](https://qiita.com/kazumi75kitty/items/9953bb84edd7717bc90a)のように、Visual Studio 2019とMySQLをインストールし、かつMySQL Connectors .NETがインストールされていること(Visual Studioは自動でMySQL Connectorsのライブラリを認識してくれる)

# 作業手順

## Visual Studio 2019でのコーディング
### コンポーネント・コントロールの配置
今回はボタンを押下→MySQLでデータを拾ってくる→画面に表示
これに加えて、名前とメモを入力し、追加や更新などができるようにフォームを追加
右上のリストボックスを選択すると該当するIDが画面表示されるので、選択している際に該当IDの更新や削除ができるようになりますが、細かいUI制御までは触れません

WindowsのC#フォームのプロジェクトを新規作成し、Form1.csデフォルトで開きました

![フォームへのコンポーネント配置](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/edd4be1f-a58c-0ffe-00f1-96d24ffc6420.png)

- 左上：データグリッド *dataGridView1*
- 右上：リストボックス2つ *listBox1・listBox2*
  選択するとテキストボックス「*idNum*」に該当するIDを表示する
- 左下：SQLを読み込むボタン *button1*
- 右中央：名前入力 *textBoxName*・メモ入力 *textBoxMemo*
- 右下：選択中のID *idNum*・新規追加SQL実行 *buttonAdd*・更新SQL実行 *buttonEdit*・削除SQL実行 *buttonDel*

これらを貼り付けます。
データグリッドとリストボックスはSQLのデータを表示させるために配置しました

### MySQL Connector .NETの参照を追加
[MySQLインストール時と同じ](https://qiita.com/kazumi75kitty/items/9953bb84edd7717bc90a)ように、参照を追加していきます。

![MySql.Data](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/6e02427c-925e-2f9f-b1f5-0ccf6785f84e.png)

「アセンブリ」の中に実際自動で認識してくれるので、参照マネージャーで、右上の検索画面に「mysql」を入力すると、↑の画面のように「**MySql.Data**」が何行も出てくるので、とりあえず1つだけ、どれでもいいので選択して(行左にマウスを当てると出てくるチェックボックスをON)「OK」を選択すると…

![MySQLを使う](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/9e206a77-07e1-e99b-b84f-e9e9cd0b4f0c.png)

「**MySql.Data**」が追加されました(\*˘꒳˘\*)
この中にMySQLを扱うC#オブジェクトが入っているのです。

### ボタンクリック時のソースを記述する
今回も簡単のため、SQLの実行はボタンクリックで読み込むだけで、Form1.csのクリックイベント動作のみをコーディングしました。本来は規模が大きくなることがほとんどなので、formのボタンクリックではなく、独立したクラスに分けるケースがほとんどですが。。。

<details><summary>Form1.cs サンプルコード</summary><div>

```c#: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一覧(フォーム内部変数)
        private List<int> idNums;

        // 選択中のID
        private int selId;

        // MySQL接続情報
        private string connStr = "server=127.0.0.1;user id=test;password=test1;database=manutest";

        public Form1()
        {
            InitializeComponent();
        }

        /**
         * 接続ボタン(MySQLサーバーに接続し、一覧データを取得する)
         * */
        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);

            // 画面と内部変数を初期化
            listBox1.Items.Clear();
            listBox2.Items.Clear();
            this.idNums = new List<int>();
            selId = -1;
            idNum.Text = "";
            buttonEdit.Enabled = false;
            buttonDel.Enabled = false;

            try
            {
                // 接続を開く
                conn.Open();

                // データを取得するテーブル
                DataTable tbl = new DataTable();

                // SQLを実行する
                MySqlDataAdapter dataAdp = new MySqlDataAdapter("SELECT id, name, memo FROM testtb", conn);
                dataAdp.Fill(tbl);

                // dataGridViewに表示させる
                dataGridView1.DataSource = tbl;

                // 実行結果を1行ずつ参照する場合
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    DataRow row = tbl.Rows[i];  // データ行

                    // 右のリストボックスにアイテムを追加
                    listBox1.Items.Add(row[1]);
                    listBox2.Items.Add(row[2]);
                    // 同時にIDを内部変数に追加
                    this.idNums.Add((int)row[0]);
                }

                // 接続を閉じる
                conn.Close();
            }
            catch (MySqlException mse)
            {
                MessageBox.Show(mse.Message, "データ取得エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /**
         * リストボックスの行を選択
         */
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.listBox_SelectedIndexChanged(sender, e);

            // 選択したIDを画面表示
            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);

            // 選択したIDを画面表示
            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)
        {
            // どちらかを選択していないと追加と削除が使えない
            if(listBox1.SelectedIndex != -1 || listBox2.SelectedIndex != -1)
            {
                buttonEdit.Enabled = true;
                buttonDel.Enabled = true;
            }
            else
            {
                buttonEdit.Enabled = false;
                buttonDel.Enabled = false;
            }
        }


        /**
         * 新規追加ボタンを選択
         */
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 新規追加のSQLコマンド
            string sqlCmd = @"INSERT INTO testtb (name, memo) VALUES (@name, @memo)";

            // 追加クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // ステークホルダーのセット
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              // 接続を開く
                
                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ追加エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }

        /**
         * 編集ボタンを選択
         */
        private void buttonEdit_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 編集のSQLコマンド
            string sqlCmd = @"UPDATE testtb SET name = @name, memo = @memo WHERE id = @id";

            // 編集クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // 選択中のIDを用いて、ステークホルダーのセット
                cmd.Parameters.AddWithValue("id", this.selId);
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              // 接続を開く

                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ更新エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }

        /**
         * 削除ボタンを選択
         */
        private void buttonDel_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 削除のSQLコマンド
            string sqlCmd = @"DELETE FROM testtb WHERE id = @id";

            // 削除クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // 選択中のIDを用いて、ステークホルダーのセット
                cmd.Parameters.AddWithValue("id", this.selId);

                cmd.Connection.Open();              // 接続を開く

                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ削除エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }
    }
}
```

</div></details>

まずフォームのC#ソースの内部変数として、MySQLのデータで**どのIDを取得したか**と、右上のリストボックスの項目に対応するIDはどれを選択しているのかを把握するため、Form1にprivate変数を追加しました

```c#
// 取得したデータのID一覧(フォーム内部変数)
private List<int> idNums;

// 選択中のID
private int selId;
```
読み込むSQLを実行するボタンで「SELECT id, name, memo～」を*button1_Click*()内で実行していると思いますが、単に右上のリストボックスにピックアップするだけでなく、対応するIDを内部変数idNumsで受け持つコードも実装しました

```c#
// 実行結果を1行ずつ参照する場合
for (int i = 0; i < tbl.Rows.Count; i++)
{
    DataRow row = tbl.Rows[i];  // データ行
    
    // 右のリストボックスにアイテムを追加
    listBox1.Items.Add(row[1]);
    listBox2.Items.Add(row[2]);
    // 同時にIDを内部変数に追加
    this.idNums.Add((int)row[0]);
}
```
row[0]が取得したデータのID番号ですが、そのままList<int>にAdd()を実行するとObjectを暗黙的に変換できないエラーとなるため、(int)でキャストしました。

```c#
listBox1.SelectedIndex
listBox2.SelectedIndex
```
listBox1_SelectedIndexChanged()などで使っていますが、リストボックスの選択したインデックス番号(0で始まるもの)はこれを使っています(未選択の場合は-1)。選択するとidNumの選択中IDのフィールドに表示する仕組みを入れたんです。選択中のIDは内部変数に入れています。

```c#
this.selId = idNums[listBox1.SelectedIndex];
```

INSERTやUPDATEなどDBの内容を更新するSQLは、MySqlCommandを使ったんですが、こっちのほうがプレースホルダーが使いやすいので、SELECT以外はMySqlCommandを使いました。

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

そして更新の際はトランザクションを使っています。例外が発生したときはいつもロールバックするようにしていますが、正直タイミングは考えるのは大変なので、今回は簡単で例外キャッチのみで；；

```c#
try
{
    trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
    cmd.ExecuteNonQuery();
    trans.Commit();
}
catch (MySqlException mse)
{
    trans.Rollback();                   // 例外発生時はロールバック
}
```

## 実行結果
今回は追加などのボタンに再読み込み機能がないので、追加などのボタンを押した後は「読み込み」ボタンを押して再表示させています(面倒ですが…)

最初に下の画像のようなデータがあるとして、IDが6のデータを更新すると…
![編集](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/cad5fa8b-2b5d-ce2f-47fb-c7d50697e4b6.png)
![編集](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/858cdf4f-4ffe-0362-a151-3c897e5c969a.png)
「更新」→「読み込み」で、更新に成功しました(˶ ･ᴗ･ )੭

次はIDが5の「Testing」というデータを選択して消してみます
![削除](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/e615bf0d-6628-562e-bf7c-464072f3f3bf.png)
![削除](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/161d26c6-7334-56f6-780f-12f8822427fe.png)
再度読み込みボタンを押して、これも削除成功しました！！

最後に追加してみます
![追加](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/7e06d9d8-e938-55ad-c2a9-e17083f1fcfc.png)
![追加](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617906/3e697e01-1a1a-ad97-6278-c78e3b576bab.png)
見事成功しました(˶ ･ᴗ･ )੭！！

# 次回
Visual StudioでMySQLのデータがNULLの場合にハマったことがあるので、そっちについて追ってみます

# 参考文献
  1. 【Visual Studio】C#からMySqlの使い方　接続するには？SQL文を実行するには？ - 困ったー(ページは削除された可能性)
  2. [C#でMySQLからSELECTした結果を取り出したい](https://qiita.com/rana_kualu/items/06afeb7359b95a602dbd)

## 関連文献
  1. [C#のMySQLでトランザクション使用する](https://www.paveway.info/entry/2020/01/17/csharp_mysqltransaction) / C#でのトランザクションの実装について
  2. [Symfoware Server アプリケーション開発ガイド(埋込みSQL編) - FUJITSU -](https://software.fujitsu.com/jp/manual/manualfiles/M080272/J2X02268/02Z201/aplsq02/aplsq029.html) / SQLのトランザクションとは何かの理解
  3. [【MySQL 5.1向けのもの】MySqlTransaction の使用](http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/connector-net-examples-mysqltransaction.html) / C#でのトランザクション実装例
  4. [IsolationLevel 列挙型](https://docs.microsoft.com/ja-jp/dotnet/api/system.transactions.isolationlevel?view=netcore-3.1) / C#でのトランザクションレベル - .NET API