MySQL初心者奮闘記４～JOINしてDataGridに表示しよう～

今回やること

1. MySQLのサンプルデータを取得
2. サンプルデータをJOINして取得できるようにする
3. 取得したいデータをドロップダウンリストで選択できる
4. 取得したデータをASP.NETのDataGridに表示
5. DataGridをソートできるようにする

SQL文を定数として設定するだけでなく、条件に応じて変更させる。
ASP.NETのコントロールを使ってみる

サンプルデータを取得

公式からZIPファイルをダウンロード

今回はworld databaseを使用します。
Zipを解凍して、MySQLにインストール

コマンドプロンプト

mysql> SOURCE 解凍したworld.sqlまでの絶対パス;

データベースを確認

コマンドプロンプト

# worldが追加されている
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test               |
| world              |
+--------------------+
7 rows in set (0.01 sec)

mysql> use world;
Database changed

# テーブルが追加されている
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.03 sec)

テーブルを確認

コマンドプロンプト

# cityテーブル
mysql> select * from city limit 10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.02 sec)

# countryテーブル
mysql> select * from country limit 10;
+------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+
| Code | Name                 | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP       | GNPOld    | LocalName                           | GovernmentForm                               | HeadOfState                 | Capital | Code2 |
+------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+
| ABW  | Aruba                | North America | Caribbean                 |      193.00 |      NULL |     103000 |           78.4 |    828.00 |    793.00 | Aruba                               | Nonmetropolitan Territory of The Netherlands | Beatrix                     |     129 | AW    |
| AFG  | Afghanistan          | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 |   5976.00 |      NULL | Afganistan/Afqanestan               | Islamic Emirate                              | Mohammad Omar               |       1 | AF    |
| AGO  | Angola               | Africa        | Central Africa            |  1246700.00 |      1975 |   12878000 |           38.3 |   6648.00 |   7984.00 | Angola                              | Republic                                     | Jos? Eduardo dos Santos     |      56 | AO    |
| AIA  | Anguilla             | North America | Caribbean                 |       96.00 |      NULL |       8000 |           76.1 |     63.20 |      NULL | Anguilla                            | Dependent Territory of the UK                | Elisabeth II                |      62 | AI    |
| ALB  | Albania              | Europe        | Southern Europe           |    28748.00 |      1912 |    3401200 |           71.6 |   3205.00 |   2500.00 | Shqip?ria                           | Republic                                     | Rexhep Mejdani              |      34 | AL    |
| AND  | Andorra              | Europe        | Southern Europe           |      468.00 |      1278 |      78000 |           83.5 |   1630.00 |      NULL | Andorra                             | Parliamentary Coprincipality                 |                             |      55 | AD    |
| ANT  | Netherlands Antilles | North America | Caribbean                 |      800.00 |      NULL |     217000 |           74.7 |   1941.00 |      NULL | Nederlandse Antillen                | Nonmetropolitan Territory of The Netherlands | Beatrix                     |      33 | AN    |
| ARE  | United Arab Emirates | Asia          | Middle East               |    83600.00 |      1971 |    2441000 |           74.1 |  37966.00 |  36846.00 | Al-Imarat al-´Arabiya al-Muttahida | Emirate Federation                           | Zayid bin Sultan al-Nahayan |      65 | AE    |
| ARG  | Argentina            | South America | South America             |  2780400.00 |      1816 |   37032000 |           75.1 | 340238.00 | 323310.00 | Argentina                           | Federal Republic                             | Fernando de la R?a          |      69 | AR    |
| ARM  | Armenia              | Asia          | Middle East               |    29800.00 |      1991 |    3520000 |           66.4 |   1813.00 |   1627.00 | Hajastan                            | Republic                                     | Robert Kot?arjan            |     126 | AM    |
+------+----------------------+---------------+---------------------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------------------------+----------------------------------------------+-----------------------------+---------+-------+
10 rows in set (0.03 sec)

# countrylanguageテーブル
mysql> select * from countrylanguage limit 10;
+-------------+------------+------------+------------+
| CountryCode | Language   | IsOfficial | Percentage |
+-------------+------------+------------+------------+
| ABW         | Dutch      | T          |        5.3 |
| ABW         | English    | F          |        9.5 |
| ABW         | Papiamento | F          |       76.7 |
| ABW         | Spanish    | F          |        7.4 |
| AFG         | Balochi    | F          |        0.9 |
| AFG         | Dari       | T          |       32.1 |
| AFG         | Pashto     | T          |       52.4 |
| AFG         | Turkmenian | F          |        1.9 |
| AFG         | Uzbek      | F          |        8.8 |
| AGO         | Ambo       | F          |        2.4 |
+-------------+------------+------------+------------+
10 rows in set (0.00 sec)

カラム情報を確認

コマンドプロンプト

mysql> show columns from city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

mysql> show columns from country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | decimal(10,2)                                                                         | NO   |     | 0.00    |       |
| IndepYear      | smallint                                                                              | YES  |     | NULL    |       |
| Population     | int                                                                                   | NO   |     | 0       |       |
| LifeExpectancy | decimal(3,1)                                                                          | YES  |     | NULL    |       |
| GNP            | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| GNPOld         | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int                                                                                   | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> show columns from countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

サンプルデータをJOINして取得

コマンドプロンプト

mysql> select cou.code, lan.language, city.name from country as cou inner join countrylanguage as lan on cou.code = lan.countrycode inner join city on cou.code = city.countrycode where cou.code = 'AFG';
+------+------------+----------------+
| code | language   | name           |
+------+------------+----------------+
| AFG  | Balochi    | Kabul          |
| AFG  | Dari       | Kabul          |
| AFG  | Pashto     | Kabul          |
| AFG  | Turkmenian | Kabul          |
| AFG  | Uzbek      | Kabul          |
| AFG  | Balochi    | Qandahar       |
| AFG  | Dari       | Qandahar       |
| AFG  | Pashto     | Qandahar       |
| AFG  | Turkmenian | Qandahar       |
| AFG  | Uzbek      | Qandahar       |
| AFG  | Balochi    | Herat          |
| AFG  | Dari       | Herat          |
| AFG  | Pashto     | Herat          |
| AFG  | Turkmenian | Herat          |
| AFG  | Uzbek      | Herat          |
| AFG  | Balochi    | Mazar-e-Sharif |
| AFG  | Dari       | Mazar-e-Sharif |
| AFG  | Pashto     | Mazar-e-Sharif |
| AFG  | Turkmenian | Mazar-e-Sharif |
| AFG  | Uzbek      | Mazar-e-Sharif |
+------+------------+----------------+
20 rows in set (0.00 sec)

取得したいデータをドロップダウンリストで選択

ドロップダウンリストで大陸と地域を選択できるように設定
『完成イメージ』

ドロップダウンリストへデータを設定

' 大陸データを取得するSQL文
Private Const Set_SQL_ddlcont As String = "" &
    " SELECT " &
    " DISTINCT " &
    "   Continent " &
    " FROM " &
    "   Country " &
    " ORDER BY " &
    "   Continent "

'地域データを取得するSQL文
Private Const Set_SQL_ddlregi As String = "" &
    " SELECT " &
    " DISTINCT " &
    "   Region " &
    " FROM " &
    "   Country " &
    " ORDER BY " &
    "   Region "

'大陸のドロップダウンリストにデータをセット
Protected Sub set_cont()
    Dim ssql As String
    Dim dsCont As New DataSet
    Dim i As Integer
    Dim dsrow As Integer
    Dim Item As New System.Web.UI.WebControls.ListItem

    ssql = Set_SQL_ddlcont
    dsCont = SelectFromWorld(ssql)

    ddlCont.Items.Clear()
    Item.Text = "すべて"
    Item.Value = "0"
    ddlCont.Items.Add(Item)
    dsrow = dsCont.Tables(0).Rows.Count

    If dsrow > 0 Then
        For i = 0 To dsrow - 1
            Item = New System.Web.UI.WebControls.ListItem
            Item.Text = dsCont.Tables(0).Rows(i).Item("Continent")
            Item.Value = i + 1
            ddlCont.Items.Add(Item)
        Next
    Else
        lblErr.Text = "失敗しました"
    End If
End Sub

' 地域のドロップダウンリストにデータをセット
Protected Sub set_regi()
    Dim ssql As String
    Dim dsRegi As New DataSet
    Dim i As Integer
    Dim dsrow As Integer
    Dim Item As New System.Web.UI.WebControls.ListItem

    ssql = Set_SQL_ddlregi
    dsRegi = SelectFromWorld(ssql)

    ddlRegi.Items.Clear()
    Item.Text = "すべて"
    Item.Value = "0"
    ddlRegi.Items.Add(Item)
    dsrow = dsRegi.Tables(0).Rows.Count
 
    If dsrow > 0 Then
        For i = 0 To dsrow - 1
            Item = New System.Web.UI.WebControls.ListItem
            Item.Text = dsRegi.Tables(0).Rows(i).Item("Region")
            Item.Value = i + 1
            ddlregi.Items.Add(Item)
        Next
    Else
        lblErr.Text = "失敗しました"
    End If
End Sub

ラジオボタンで選択するとドロップダウンリストが操作できるようにする

ラジオボタン操作

 Protected Sub radBtn_CheckedChanged(sender As Object, e As EventArgs) Handles radRegi.CheckedChanged, radCont.CheckedChanged
    If radRegi.Checked = True Then
        ddlRegi.Enabled = True
        ddlCont.Enabled = False
        lblErr.Text = "地域を選択してください"
    ElseIf radCont.Checked = True Then
        ddlCont.Enabled = True
        ddlRegi.Enabled = False
        lblErr.Text = "大陸を選択してください"
    End If
End Sub

大陸のドロップダウンリストと地域のドロップダウンリストを対応させる。
=>
大陸でAsiaを選択したら地域にはAsiaに属する地域のみ表示
地域でMiddle Eastを選択したら大陸にはMiddle Eastに対応する大陸のみ表示
すべてを選択したら全件表示される

ドロップダウンリスト操作

' 大陸ドロップダウンリストの選択
Protected Sub ddlCont_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlCont.SelectedIndexChanged
    Dim ssql As New System.Text.StringBuilder
    Dim dsRegi As New DataSet
    Dim dsrow As Integer
    Dim Item As New System.Web.UI.WebControls.ListItem

    ssql.Append(" SELECT ")
    ssql.Append(" DISTINCT ")
    ssql.Append("   Region ")
    ssql.Append(" FROM ")
    ssql.Append("   Country ")

'ddlCont.SelectedValueが0(すべて)でない場合、対応する地域のみ取得
    If ddlCont.SelectedValue <> 0 Then
        ssql.Append(" WHERE ")
        ssql.Append("   Continent = '" & ddlCont.SelectedItem.Text & "'")
        ssql.Append(" ORDER BY ")
        ssql.Append(" Region ")
    End If

    dsRegi = SelectFromWorld(ssql.ToString)
    ddlRegi.Items.Clear()
    Item.Text = "すべて"
    Item.Value = "0"
    ddlRegi.Items.Add(Item)
    dsrow = dsRegi.Tables(0).Rows.Count

    If dsrow > 0 Then
        For i = 0 To dsrow - 1
            Item = New System.Web.UI.WebControls.ListItem
            Item.Text = dsRegi.Tables(0).Rows(i).Item("Region")
            Item.Value = i + 1
            ddlRegi.Items.Add(Item)
        Next
    Else
        lblErr.Text = "地域の取得に失敗しました"
    End If

'ddlCont.SelectedValueが0のときは大陸も全件表示させる
    If ddlCont.SelectedValue = 0 Then
        Call set_cont()
    End If
End Sub

' 地域ドロップダウンリストの選択
Private Sub ddlRegi_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlRegi.SelectedIndexChanged
    Dim ssql As New System.Text.StringBuilder
    Dim dsCont As New DataSet
    Dim dsrow As Integer
    Dim Item As New System.Web.UI.WebControls.ListItem

    ssql.Append(" SELECT ")
    ssql.Append(" DISTINCT ")
    ssql.Append("   Continent ")
    ssql.Append(" FROM ")
    ssql.Append("   Country ")

'ddlRegi.SelectedValueが0(すべて)でない場合、対応する大陸のみ取得
    If ddlRegi.SelectedValue <> 0 Then
        ssql.Append(" WHERE ")
        ssql.Append("   Region = '" & ddlRegi.SelectedItem.Text & "'")
        ssql.Append(" ORDER BY ")
        ssql.Append(" Continent ")
    End If

    dsCont = SelectFromWorld(ssql.ToString)
    ddlCont.Items.Clear()
    Item.Text = "すべて"
    Item.Value = "0"
    ddlCont.Items.Add(Item)
    dsrow = dsCont.Tables(0).Rows.Count

    If dsrow > 0 Then
        For i = 0 To dsrow - 1
            Item = New System.Web.UI.WebControls.ListItem
            Item.Text = dsCont.Tables(0).Rows(i).Item("Continent")
            Item.Value = i + 1
            ddlCont.Items.Add(Item)
        Next
    Else
        lblErr.Text = "大陸の取得に失敗しました"
    End If

'ddlRegi.SelectedValueが0(すべて)のときは大陸もすべてを、すべて以外のときは対応する大陸を選択する
    If ddlRegi.SelectedValue = 0 Then
        ddlCont.SelectedValue = 0
    Else
        ddlCont.SelectedValue = dsrow
    End If

'ddlRegi.SelectedValueが0のとき、地域も全件表示
    If ddlRegi.SelectedValue = 0 Then
        Call set_regi()
    End If
End Sub

取得したデータをASP.NETのDataGridに表示

検索ボタンをクリックすると上位100件までデータが表示される
『完成イメージ』

DataGridの操作

Protected Sub btnSerch_Click(sender As Object, e As EventArgs) Handles btnSerch.Click
    Dim ssql As New System.Text.StringBuilder
    Dim dsGrid As New DataSet
    Dim dt As New DataTable
    Dim i As Integer

    For i = 0 To gridM.Columns.Count - 1
        gridM.Columns(i).HeaderText = gridM.Columns(i).HeaderText.Trim(AscCap).Trim(DescCap)
    Next

    lblErr.Text = ""
    ssql.Append(" SELECT ")
    ssql.Append(" ROW_NUMBER() OVER() AS Num, ")
    ssql.Append(" ctn.Name AS Name, ")
    ssql.Append(" ctn.Code AS Code, ")
    ssql.Append(" city.District AS District, ")
    ssql.Append(" city.Name AS City, ")
    ssql.Append(" city.Population AS Population, ")
    ssql.Append(" lang.Language AS Language")
    ssql.Append(" FROM ")
    ssql.Append("   country AS ctn")
    ssql.Append(" INNER JOIN ")
    ssql.Append("   city ")
    ssql.Append(" ON ")
    ssql.Append("   ctn.Code = city.CountryCode ")
    ssql.Append(" INNER JOIN ")
    ssql.Append("   countrylanguage AS lang ")
    ssql.Append(" ON ")
    ssql.Append("   ctn.Code = lang.CountryCode ")
    ssql.Append(" Where ")
    ssql.Append("   lang.IsOfficial = 'T' ")
    If ddlCont.SelectedValue <> 0 Then
        ssql.Append(" AND ")
        ssql.Append("   ctn.Continent = '" & ddlCont.SelectedItem.Text & "'")
    End If
    If ddlRegi.SelectedValue <> 0 Then
        ssql.Append(" And ")
        ssql.Append("   ctn.Region ='" & ddlRegi.SelectedItem.Text & "'")
    End If
    ssql.Append(" LIMIT ")
    ssql.Append("   100 ")

    dsGrid = SelectFromWorld(ssql.ToString)
    dt = dsGrid.Tables(0)

    If dt.Rows.Count < 1 Then
        lblErr.Text = "データがありません"
        Session("GridData") = Nothing
        gridM.DataSource = Session("GridData")
        gridM.DataBind()
        Exit Sub
    End If

    Session("GridData") = dt
    gridM.DataSource = Session("GridData")
    gridM.CurrentPageIndex = 0
    gridM.DataBind()
End Sub

' グリッドビューのページ移動処理
Private Sub gridM_PageIndexChanged(source As Object, e As DataGridPageChangedEventArgs) Handles gridM.PageIndexChanged
    Dim dt As New DataTable
    dt = CType(Session("GridData"), DataTable)
    gridM.CurrentPageIndex = e.NewPageIndex
    gridM.DataSource = dt
    gridM.DataBind()
End Sub

DataGridをソートできるようにする

『完成イメージ』
人口でソート

ソート機能

'char型は1文字+c　▲(昇順)　▼(降順)
Private Const AscCap As Char = "▲"c
Private Const DescCap As Char = "▼"c

Public Sub gridM_Sort(sender As Object, e As DataGridSortCommandEventArgs) Handles gridM.SortCommand
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim dv As New DataView
    Dim curSortOrder As String
    Dim newSortOrder As String
    Dim sortColumn As New System.Text.StringBuilder
    Dim k As Integer

    '昇順か降順かを変数にセット、Sessionに記録する
    curSortOrder = CType(Session("viewstate"), String)
    If curSortOrder Is Nothing Or curSortOrder = "DESC" Then
        newSortOrder = "ASC"
    Else
        newSortOrder = "DESC"
    End If
    Session("viewstate") = newSortOrder

    ' 選択したカラムに並び順をセット
    sortColumn.Append(e.SortExpression)
    sortColumn.Append(" ")
    sortColumn.Append(newSortOrder)

    ' カラムの▲と▼を除く処理
    For k = 0 To gridM.Columns.Count - 1
        gridM.Columns(k).HeaderText = gridM.Columns(k).HeaderText.TrimEnd(AscCap).TrimEnd(DescCap)
        '選択したカラムとデータグリッドのカラムが等しい場合▲か▼を付ける
        If e.SortExpression.Equals(gridM.Columns(k).SortExpression) Then
            If newSortOrder = "ASC" Then
                gridM.Columns(k).HeaderText = String.Concat(gridM.Columns(k).HeaderText, AscCap)
            Else
                gridM.Columns(k).HeaderText = String.Concat(gridM.Columns(k).HeaderText, DescCap)
            End If
        End If
    Next

    dt = CType(Session("GridData"), DataTable)
    dv = dt.DefaultView
    dv.Sort = sortColumn.ToString
    gridM.DataSource = dv
    gridM.DataBind()
    If gridM.Visible = False Then
        lblErr.Text = "処理に失敗しました"
        Exit Sub
    End If
End Sub

ページ初期化処理

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Me.IsPostBack Then
        Exit Sub
    End If
    radCont.Checked = True
    ddlRegi.Enabled = False
    Session("GridData") = Nothing
    Session("viewstate") = Nothing
    Call set_cont()
    Call set_regi()
End Sub

参考資料

MySQLのちゃんとしたサンプルデータ
【SQL】MySQL公式サンプルデータベースを使う
［ASP.NET］DataGridコントロールにソート機能を追加するには？
［ASP.NET］DataGridコントロールのソートを双方向にするには？