MySQL初心者奮闘記３～ASP.NETから呼び出してみよう～

# 今回やること
**1. ASP.NETからMySQLを呼び出す準備**
**2. ASP.NETからMySQLに接続して、データを取得する（SELECT）**
**3. UPDATE、INSERT、DELETEでデータを操作する**

## VisualSudioの準備
``サーバーエクスプローラーからデータ接続をクリック``してデータベースを追加する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/9e7c9d4e-04fc-36e6-e048-d1df9db917fb.png)

データソースは``MySQL Database``を選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/3e6714b8-4214-c832-db32-9349e2a96859.png)

ローカルに接続するので、``Server name: localhost``, ``User name: root``に設定。
passwordはMySQLに接続する時のパスワード　Database nameは接続するデータべース。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/619cfac4-fee2-97ce-cff3-503e7b32765d.png)

データ接続に追加されたことを確認。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/5274960f-e604-23db-ae4d-169d0dbb39ea.png)

接続確認：右クリックしてデータを取得
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/35cf56a9-ab6c-5672-b99b-852d3d66357c.png)

データが表示されたことを確認
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/aa05805b-d655-ed98-ce88-90825dfcff52.png)

## ASP.netから呼び出す
MySQLへ接続するために必要な情報を取得

```python:port確認
mysql> status
--------------
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)
:
:
TCP port:               3306
:
:
--------------
```

```python:hostとuserを確認
# localhost/rootに繋ぐ
mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.03 sec)
```

## ASP.NETからSELECT文で取得する
MySQLへ接続するためのコードをModuleに追加

```vb:MySQLへ接続するモジュール
Imports System.Web
Imports MySql.Data.MySqlClient
Module SQLModule
    Public mysqlCon As New MySqlConnection
    Public sqlCommand As New MySqlCommand
    
    'MySQLへ接続
    Sub sql_st()
        Dim Builder = New MySqlConnectionStringBuilder()
        Builder.Server = "localhost"
        Builder.SslMode = MySqlSslMode.None
        Builder.Port = 3306
        Builder.UserID = "root"
        Builder.Password = ""
        Builder.Database = "test"

        Dim ConStr = Builder.ToString()
        mysqlCon.ConnectionString = ConStr
        mysqlCon.Open()
    End Sub

    '切断
    Sub sql_cl()
        mysqlCon.Close()
    End Sub

End Module
```
```vb:SELECTデータ取得用
Function SelectFromSQL(ByVal query As String) As DataSet
    Dim ds As New DataSet
    Call sql_st()
    Try
        Dim Adapter = New MySqlDataAdapter(query, mysqlCon)
        Adapter.Fill(ds)
        Call sql_cl()
        Return ds
    Catch ex As Exception
        Call sql_cl()
        Return ds
    End Try
End Function
```
```python:SQL側の準備
# Genderカラムをchar(3)へ変更
mysql> show columns from test_users;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int unsigned | NO   | PRI | NULL    | auto_increment |
| No     | int          | YES  |     | NULL    |                |
| Name   | varchar(100) | NO   |     | NULL    |                |
| Gender | char(1)      | YES  |     | NULL    |                |
| Age    | tinyint      | YES  |     | NULL    |                |
| Intro  | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
6 rows in set (0.13 sec)

mysql> alter table test_users change column Gender Gender char(3);
Query OK, 11 rows affected (0.69 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> show columns from test_users;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int unsigned | NO   | PRI | NULL    | auto_increment |
| No     | int          | YES  |     | NULL    |                |
| Name   | varchar(100) | NO   |     | NULL    |                |
| Gender | char(3)      | YES  |     | NULL    |                |
| Age    | tinyint      | YES  |     | NULL    |                |
| Intro  | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)

データの確認
mysql> select * from test_users;
+----+------+--------------+--------+------+--------------------------+
| id | No   | Name         | Gender | Age  | Intro                    |
+----+------+--------------+--------+------+--------------------------+
|  1 |    1 | テスト１     | 男     |   28 | よろしくね               |
|  2 |    2 | テスト花子   | 女     |   20 | NULL                     |
| 25 |    3 | テスト太郎   | 男     |   23 | 趣味はキャンプ           |
| 26 |    4 | テスト洋子   | 女     |   45 | 私は洋子                 |
| 27 |    5 | テスト右衛門 | 男     |   68 | ポケモンゲットだぜ!      |
| 28 |    6 | テストめい   | 女     |   24 | おねぇちゃんのばかぁ!    |
| 29 |    7 | テスト愛     | 女     |   17 | 貢いでくれる人が好きです |
| 30 |    8 | テスト三郎   | 男     |   43 | お金ならありません       |
| 31 |    9 | テスト志郎   | 男     |   38 | デュクシ、デュクシ       |
| 32 |   10 | テスト和恵   | 女     |   48 | NULL                     |
| 33 | NULL | テスト陽太   | 男     | NULL | NULL                     |
+----+------+--------------+--------+------+--------------------------+
11 rows in set (0.03 sec)
```
```vb:SELECT文でデータを取得する
Public Class WebForm3
    Inherits System.Web.UI.Page

    'SELECT文
    Private Const SELECT_SQL As String = "" &
        " SELECT " &
        "   Name, Gender, Age, Intro " &
        " From " &
        "   test_users " &
        " Where " &
        "   id = {0} "
    
    '検索ボタン
    Protected Sub BtnSearch_Click1(sender As Object, e As EventArgs) Handles BtnSearch.Click
        Dim ssql As String
        Dim code As Integer
        Dim dsData As DataSet

        '　エラーハンドリング
        If txtCode.Text = String.Empty Then
            lblErr.Text = "表示するコードを入力してください"
            txtName.Text = String.Empty
            txtGender.Text = String.Empty
            txtAge.Text = String.Empty
            txtIntro.Text = String.Empty
            Exit Sub
        End If

        If IsNumeric(txtCode.Text) = False Then
            lblErr.Text = "コードは数値で入力してください"
            txtName.Text = String.Empty
            txtGender.Text = String.Empty
            txtAge.Text = String.Empty
            txtIntro.Text = String.Empty
            Exit Sub
        End If

        code = Integer.Parse(StrConv(txtCode.Text, VbStrConv.Narrow))

        ' MySQLへ接続しデータを取得
        ssql = String.Format(SELECT_SQL, code)
        dsData = SelectFromSQL(ssql)

        'データがある場合は表示(NULLは空白)
        If dsData.Tables(0).Rows.Count > 0 Then
            txtName.Text = If(IsDBNull(dsData.Tables(0).Rows(0).Item("Name")), "", dsData.Tables(0).Rows(0).Item("Name"))
            txtGender.Text = If(IsDBNull(dsData.Tables(0).Rows(0).Item("Gender")), "", dsData.Tables(0).Rows(0).Item("Gender"))
            txtAge.Text = If(IsDBNull(dsData.Tables(0).Rows(0).Item("Age")), "", dsData.Tables(0).Rows(0).Item("Age"))
            txtIntro.Text = If(IsDBNull(dsData.Tables(0).Rows(0).Item("Intro")), "", dsData.Tables(0).Rows(0).Item("Intro"))
            lblRegister.Text = "更新"
        Else
            lblErr.Text = "データがありません"
            lblRegister.Text = "新規"
            txtName.Text = String.Empty
            txtGender.Text = String.Empty
            txtAge.Text = String.Empty
            txtIntro.Text = String.Empty
            Exit Sub
        End If
    End Sub
End Class
```
### 結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/05655e23-658e-9b81-2417-18d38ac37a19.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/b2bf4d11-430a-41ef-1d93-cfc0f5b94ac9.png)

## ASP.netからMySQLを操作する
```vb:データ操作用
' 処理に成功したかどうかを返す
Function ImplementToSQL(ByVal query As String) As Boolean
    Call sql_st()

    Try
        sqlCommand.Connection = mysqlCon
        sqlCommand.CommandText = query
        sqlCommand.ExecuteNonQuery()
        Call sql_cl()
        Return True
    Catch ex As Exception
        Call sql_cl()
        Return False
    End Try
End Function
```

### UPDATE文で上書き
```vb:UPDATE文
Private Const UPDATE_SQL As String = "" &
    " UPDATE " &
    "   test_users " &
    " SET " &
    "   Name='{1}', Gender='{2}', Age={3}, Intro='{4}' " &
    " WHERE " &
    "   id = {0} "

Protected Sub BtnRegister_Click(sender As Object, e As EventArgs) Handles BtnRegister.Click
    Dim selssql As String
    Dim ssql As String
    Dim dsData As DataSet
    Dim bRet As Boolean
    Dim lenstr As Integer
    Dim code As Integer
    Dim name As String = String.Empty
    Dim gender As String = String.Empty
    Dim age As Integer = 0
    Dim intro As String = String.Empty

    'エラーハンドリング
    If txtCode.Text = String.Empty Then
        lblErr.Text = "コードを入力してください"
        Exit Sub
    ElseIf IsNumeric(txtCode.Text) = False Then
        lblErr.Text = "コードは数値で入力してください"
        Exit Sub
    Else
        code = Integer.Parse(StrConv(txtCode.Text, VbStrConv.Narrow))
    End If

    lenstr = System.Text.Encoding.GetEncoding("Shift_JIS").GetByteCount(txtName.Text)
    If txtName.Text = String.Empty Then
        lblErr.Text = "名前を入力してください"
        Exit Sub
    ElseIf lenstr > 100 Then
        lblErr.Text = "名前は50文字以内で入力してください"
        Exit Sub
    Else
        name = txtName.Text
    End If

    lenstr = System.Text.Encoding.GetEncoding("Shift_JIS").GetByteCount(txtGender.Text)
    If txtGender.Text <> String.Empty Then
        If lenstr > 3 Then
            lblErr.Text = "性別は「男」か「女」で入力してください"
            Exit Sub
        Else
            gender = txtGender.Text
        End If
    End If

    If txtAge.Text <> String.Empty Then
        If IsNumeric(txtAge.Text) = False Then
            lblErr.Text = "年齢は数値で入力してください"
            Exit Sub
        Else
            age = Integer.Parse(StrConv(txtAge.Text, VbStrConv.Narrow))
        End If
    End If

    lenstr = System.Text.Encoding.GetEncoding("Shift_JIS").GetByteCount(txtIntro.Text)
    If txtIntro.Text <> String.Empty Then
        If lenstr > 255 Then
            lblErr.Text = "紹介は125文字以内で入力してください"
            Exit Sub
        Else
            intro = txtIntro.Text
        End If
    End If

    'MySQLへ接続
    'SELECT分でデータを探し、あればUPDATEする。
    selssql = String.Format(SELECT_SQL, code)
    dsData = SelectFromSQL(selssql)
    If dsData.Tables(0).Rows.Count > 0 Then
        ssql = String.Format(UPDATE_SQL, code, name, gender, age, intro)
    End If

    bRet = ImplementToSQL(ssql)
    If bRet = False Then
        lblErr.Text = "登録に失敗しました"
        Exit Sub
    End If
    lblErr.Text = "登録しました"
End Sub
```
#### 結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/3ceecf0c-1a5e-799b-4bb8-f8430b3d07b5.png)

```python:MySQLで結果を確認
mysql> select * from test_users where id = 1;
+----+------+----------+--------+------+------------+
| id | No   | Name     | Gender | Age  | Intro      |
+----+------+----------+--------+------+------------+
|  1 |    1 | テスト１ | 男     |   28 | よろしくね |
+----+------+----------+--------+------+------------+

mysql> select * from test_users where id = 1;
+----+------+------------+--------+------+------------+
| id | No   | Name       | Gender | Age  | Intro      |
+----+------+------------+--------+------+------------+
|  1 |    1 | テスト野郎 |        |   28 | よろしくね |
+----+------+------------+--------+------+------------+
```

### INSERT文で追加
```vb:
Private Const INSERT_SQL As String = "" &
    " INSERT INTO " &
    "   test_users " &
    "       ( id, Name, Gender, Age, Intro ) " &
    " VALUES " &
    "       ({0}, '{1}', '{2}', {3}, '{4}') "

Protected Sub BtnRegister_Click(sender As Object, e As EventArgs) Handles BtnRegister.Click
    Dim selssql As String
    Dim ssql As String
    Dim dsData As DataSet
    Dim bRet As Boolean
    Dim lenstr As Integer
    Dim code As Integer
    Dim name As String = String.Empty
    Dim gender As String = String.Empty
    Dim age As Integer = 0
    Dim intro As String = String.Empty

    'エラーハンドリング
     "~ 省略 ~"

    'MySQLへ接続
    'SELECT分でデータを探し、なければINSERTする。
    selssql = String.Format(SELECT_SQL, code)
    dsData = SelectFromSQL(selssql)
    If dsData.Tables(0).Rows.Count > 0 Then
        ssql = String.Format(UPDATE_SQL, code, name, gender, age, intro)
    Else
        ssql = String.Format(INSERT_SQL, code, name, gender, age, intro)
    End If

    bRet = ImplementToSQL(ssql)
    If bRet = False Then
        lblErr.Text = "登録に失敗しました"
        Exit Sub
    End If
    lblErr.Text = "登録しました"
End Sub
```
#### 結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/02ebfb95-6122-cfb4-0c1e-205c4b1b213f.png)

```python:
mysql> select * from test_users where id=3;
Empty set (0.00 sec)

mysql> select * from test_users where id=3;
+----+------+----------+--------+------+-------+
| id | No   | Name     | Gender | Age  | Intro |
+----+------+----------+--------+------+-------+
|  3 | NULL | てすと３ |        |   90 |       |
+----+------+----------+--------+------+-------+
1 row in set (0.01 sec)
```

### DELETE文で削除
```vb:
Private Const DELETE_SQL As String = "" &
    " DELETE " &
    " FROM " &
    "   test_users " &
    " WHERE " &
    "   id = {0} "

Protected Sub BntDelete_Click(sender As Object, e As EventArgs) Handles BntDelete.Click
    Dim ssql As String
    Dim code As Integer
    Dim bRet As Boolean

    'エラーハンドリング
    If txtCode.Text = String.Empty Then
        lblErr.Text = "表示するコードを入力してください"
        txtName.Text = String.Empty
        txtGender.Text = String.Empty
        txtAge.Text = String.Empty
        txtIntro.Text = String.Empty
        Exit Sub
    End If

    If IsNumeric(txtCode.Text) = False Then
        lblErr.Text = "コードは数値で入力してください"
        txtName.Text = String.Empty
        txtGender.Text = String.Empty
        txtAge.Text = String.Empty
        txtIntro.Text = String.Empty
        Exit Sub
    End If

    code = Integer.Parse(StrConv(txtCode.Text, VbStrConv.Narrow))
    
    'MySQLへ接続
    ssql = String.Format(DELETE_SQL, code)

    bRet = ImplementToSQL(ssql)
    If bRet = False Then
        lblErr.Text = "削除に失敗しました"
        Exit Sub
    End If
    lblErr.Text = "削除しました"
End Sub
```
#### 結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/683664/90822448-91ab-8a4e-df4c-1e6aa56e4fa3.png)

```python:
mysql> select * from test_users where id=3;
+----+------+----------+--------+------+-------+
| id | No   | Name     | Gender | Age  | Intro |
+----+------+----------+--------+------+-------+
|  3 | NULL | てすと３ |        |   90 |       |
+----+------+----------+--------+------+-------+
1 row in set (0.00 sec)

mysql> select * from test_users where id=3;
Empty set (0.00 sec)
```


# 参考資料
[VB.NETでMySQLに接続してデータグリッドに表示【初心者向】](https://pan-blog.com/vb-net-mysql-beginner.html/)
[VB.NETでMySQLに接続](https://lab.unicast.ne.jp/2014/06/03/vb-net%E3%81%A7mysql%E3%82%92%E6%8E%A5%E7%B6%9A/)
[VB.NETでデータベースを操作](https://yoshisyou.com/manipulate-mysql-using-vb-net/)
[【MySQL】ポートの確認方法や変更方法について簡単に分かりやすく解説。](https://style.potepan.com/articles/19452.html)
[MySQLでユーザー一覧を確認する「mysql.userテーブル」](https://uxmilk.jp/12703)
[文字列を全角／半角に変換するには？（VB.NET関数活用）](https://atmarkit.itmedia.co.jp/ait/articles/0502/18/news130.html)