未だにVB.netでDataTableを使ったりするが、最近DataTableを使用してLinqのクエリーを作ったりしている。外部結合もできるのだが、ネットで調べるとGroup Joinで外部結合する方法が頻繁に紹介されている…が、この方法だとどうも1つのテーブルとしか外部結合ができない感じ…(私のやり方が悪いのかもしれないが)。

と思ったら、where句を使うことで複数のテーブルと外部結合ができるようだ。ということでテーブル構成は以下の図にある3テーブルを作り、dtMainにdtSub1,dtSub2を外結するイメージで作ってみる。




(1) 「Microsoft Visual Studio Express 2012 for Windows Desktop」を開き、新規で「Windowsフォームアプリケーション」を開く

(2) Form1に適当にDataGridViewをいれておく。



(3) Form1.vbに以下のコードを追記

Public Class Form1

    Public Sub New()

        ' この呼び出しはデザイナーで必要です。
        InitializeComponent()

        ' InitializeComponent() 呼び出しの後で初期化を追加します。
        Construct()
    End Sub

    Private Sub Construct()
        ' テーブル定義
        Dim dtMain As New DataTable
        dtMain.Columns.Add(New DataColumn With {.DataType = System.Type.GetType("System.Int32"), .ColumnName = "ID"})
        dtMain.Columns.Add(New DataColumn With {.DataType = System.Type.GetType("System.String"), .ColumnName = "NAME"})

        Dim dtSub1 As DataTable = dtMain.Clone
        Dim dtSub2 As DataTable = dtMain.Clone
        Dim dtKekka As DataTable = dtMain.Clone
        Dim dtRow As DataRow = Nothing

        ' データインサート
        For i As Integer = 1 To 3
            dtRow = dtMain.NewRow
            dtRow("ID") = i
            dtMain.Rows.Add(dtRow)
            If i = 1 Then
                dtRow("NAME") = "外結DT１"
                dtSub1.ImportRow(dtRow)
            ElseIf i = 3 Then
                dtRow("NAME") = "外結DT２"
                dtSub2.ImportRow(dtRow)
            End If
        Next

        ' 外結クエリ①
        Dim query = From main In dtMain.AsEnumerable _
                    From sub1 In dtSub1.AsEnumerable.Where(Function(m) m.Field(Of Integer)("ID") = main.Field(Of Integer)("ID")).DefaultIfEmpty() _
                    From sub2 In dtSub2.AsEnumerable.Where(Function(m) m.Field(Of Integer)("ID") = main.Field(Of Integer)("ID")).DefaultIfEmpty() _
                    Select New With _
                    { _
                        .id = main!ID, _
                        .name = If(sub1 Is Nothing, If(sub2 Is Nothing, main!NAME, sub2!NAME), sub1!NAME) _
                    }
        For Each row In query
            dtRow = dtKekka.NewRow
            dtRow("ID") = row.id
            dtRow("NAME") = row.name
            dtKekka.Rows.Add(dtRow)
        Next
        Me.DataGridView1.DataSource = dtKekka

    End Sub
End Class

(4) ソリューションをビルド


①の外結クエリのところで実際に外部結合。
dtMainのIDに対し、dtSub1とdtSub2のIDを外結。Select句ではIDはdtMainのものを表示。NAMEはdtSub1に有効なNameがあればそれを表示、なければdtSub2に有効なNameがあればそれを表示、それ以外はdtMainのName(NULL)を表示。という感じ。

ということで実行。



想定通り、IDは全行表示、NAMEはあれば表示された。

Group JoinだとInto句でリスト化を挟むとかイマイチ不明な過程があったが、Where句だとそういう考慮も無くすっきり書けてイメージがとらえやすい感じ。

sub1やsub2でゴリゴリと長ったらしい集計クエリーを書いてももちろん外結してくれるので良い感じ。このサンプルはわざわざDataTableを挟んでるけど、もちろん普通のエンティティデータのクエリでもいけました。


しかしDataTableはAsEnumerable化してもデータ数が多いと集計が遅いね…。一回DataTableのデータをEntityの配列に入れなおして集計すると凄い早い。というか最初からDataTableを使うなよってことですが、何か使ってしまう。 続きを読む

最近OracleのデータをMySQLに移行しているが、以前の記事で書いた(VBAでRefCursorのデータ操作(ADO版))長いSelect文だけをストアドで保持する方法をMySqlで出来ないかな？と探していたら、簡単にできることがわかった。という事でVB.netでちょっとSelectしてみるとこまでやってみる。

ちなみに現在の開発環境
・Windows7 Ultimate
・Microsoft Visual Studio Express 2012 for Windows Desktop
・MySQL CommunityServer5.1.73


■１．DB側の準備

MySQLで適当にsampledbというデータベースを作成して、vbというユーザにsampleというテーブルを作成し、sampleテーブルをSelectするプロシージャを作成。

(1) DBとユーザ作成

コマンドプロンプトでrootユーザで下記を実行

>mysql -u root -pパスワード
mysql>create database sampledb;
mysql>grant all on sampledb.* to vb@localhost identified by 'vb';

(2) テーブルとサンプルデータ作成

続いてvbユーザでテーブルを作成

>mysqlt -u vb -pvb sampledb;
mysql>create table sample(user_id int primary key, user_name varchar(40), message varchar(200));
mysql>insert into sample values (1, 'hoge-a', 'good morning.'), (2, 'hoge-b', 'good evening.');

(3) プロシージャの作成

vbユーザのままProcedureを作成。
パッケージはMySQLには無いので、stored_sampleというプロシージャを作成。create前には必ずdelimiterを変更。

mysql>delimiter //
mysql>create procedure stored_sample(in set_user_id int)
      begin
          select * from sample where user_id = set_user_id; /* ① 保持したいSQL文 */
      end;
mysql>//
mysql>delimiter ;

指定のuser_idのデータをSelectするような形にしてみる(以前の記事(VBAでRefCursorのデータ操作(ADO版))と同じ)。
①の部分が目的の保持したいSQL文になる。MySQLだとRefCusorとか何の小細工も無くSelect文を置くだけ…。


■２．VB.net側の準備

(1) Visual Studio Express 2012 for Windows Desktopを開いて、新規でwindowsフォームアプリケーションを作成。
(2) メニューバーから「ツール」→「ライブラリ パッケージ マネージャ」→「ソリューションのNuGetパッケージの管理」を開く。
(3) オンラインで「MySql」を検索して「MySql.Data」ドライバをインストールする。
(4) フォーム(Form1.vb)にDataGridViewを貼り付ける(コントロール名：DataGridView1)。



(5) フォーム(Form1.vb)のコードを表示して以下を追記。

Imports MySql.Data.MySqlClient
Public Class Form1
    Public Sub New()
        ' この呼び出しはデザイナーで必要です。
        InitializeComponent()

        ' InitializeComponent() 呼び出しの後で初期化を追加します。
        Construct()
    End Sub

    Private Sub Construct()
        Dim ds As New DataSet

        ' sampleデータ取得
        Using cmd As New MySqlCommand
            Try
                cmd.CommandType = CommandType.StoredProcedure ' ②
                cmd.CommandText = "stored_sample"
                cmd.Connection = New MySqlConnection("Database=sampledb;Data Source=localhost;User Id=vb;Password=vb")
                cmd.Parameters.Add("set_user_id", MySqlDbType.Int32).Value = 2 ' ③ user_id=2を抽出

                Using da As New MySqlDataAdapter
                    da.SelectCommand = cmd
                    da.Fill(ds, "sample")
                End Using
            Catch ex As MySqlException
                Throw ex
            End Try
        End Using

        DataGridView1.DataSource = ds.Tables("sample")
    End Sub
End Class

②でコマンドタイプをStoredProcedureにして、③でプロシージャの引数set_user_idに指定のuser_id番号を渡してSelect文を実行。
その結果をDataGridViewで表示してるだけ。

実行してみると、



のような感じでuser_id=2のデータが取得できる。

ちなみにMySQLってfull outer joinできないのかぁ。with句も使えないみたいだし、またSQLが汚くて長くなるなぁ・・・(って自分のセンスの問題だけど)。