C#(Win forms)でDB2/400のSQL作成支援ツールを作ってみた

# 作ろうと思ったきっかけ
会社の基幹システムがAS/400(=IBM i)を使っていて、データの抽出を行う機会がちょくちょくあります。
これまでは、フリーソフトのA5M2を使っていて、これはこれで非常に使い勝手が良かったですが、DB2/400に保有している
テーブルや各カラムの日本語名が出ない事が、ほんのちょっとだけ不便でした。
というわけで、夏の工作がてらDB2/400のSQL実行ツールを作成しようと思い立ちました。

# この記事で実装する機能
SQL入力エリアで下記の機能を実装する
　①シンタックスハイライト
　②オートコンプリート機能
　③SQL整形機能

# 使ったライブラリ
  ①[Fast Colored TextBox for Syntax Highlighting](https://www.codeproject.com/Articles/161871/Fast-Colored-TextBox-for-syntax-highlighting)
  ②[Autocomplete Menu](https://www.codeproject.com/Articles/365974/Autocomplete-)
  ③[Poor Man's T-SQL Formatter](http://architectshack.com/PoorMansTSqlFormatter.ashx)

#まずはライブラリをNuGetで入手
##Fast Colored TextBox for Syntax Highlighting
![FCTB.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/9dd4498a-0ed6-01cd-b8ea-8ff54206ff9e.png)
##Autocomplete Menu
![ACM.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/6246dc41-8ac4-b35c-12ac-1352aac299ac.png)
##Poor Man's T-SQL Formatter
![PMT.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/bb1a7ec8-35eb-7f37-105d-cb28e5da779a.png)

#Fast Colored TextBox for Syntax Highlighting(以下、FCTB)をコントロールに配置する
FCTBはデザイナは使えないので、コードからコントロールを追加する必要があります。
配置については、Panelを使ってそのコントロールに追加するのが良いと思います。

```C#
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;

namespace WindowsFormsApplication1
{
    /// <summary>
    /// テスト用フォームです
    /// </summary>
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 初期読込時の動作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();
            sqlInputTextBox.Language = Language.SQL;
            sqlInputTextBox.Dock = DockStyle.Fill;
            sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません！(ToT)

            sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
        }
    }
}
```

たったこれだけで、それなりの見た目の入力エリアが出来ました。
![sample1.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/b70dc69a-a9ad-f816-5ede-e8480d6d5329.png)

#DB2への接続クラスを作成する
下記の様なClassを作成しました。キモになるのは、`QSYS2.SYSTABLES`です。このテーブルには、テーブルの物理名・論理名・テキスト名などが保管されています。
他にも`QSYS2.SYSCOLUMNS`というテーブルにはカラム名とそれに紐づくTEXT情報が格納されているので、工夫次第でカラム名の日本語化も可能です。

```C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    /// <summary>
    /// AS400からのデータ取得を行う
    /// </summary>
    class DA_AS400
    {
        private string ipAddress;
        private string schema;
        /// <summary>
        /// 接続先のIPアドレスと参照するDBを取得する
        /// </summary>
        /// <param name="ip">IPアドレス</param>
        /// <param name="db">接続するDB</param>
        public DA_AS400(String ip, String db)
        {
            ipAddress = ip;
            schema = db;
        }
        /// <summary>
        /// SQLを実行する
        /// </summary>
        /// <param name="SQL">入力されたSQL</param>
        /// <returns>データベースからの取得結果</returns>
        private DataTable execSQL(String SQL)
        {
            OleDbConnection cn = new OleDbConnection();
            DataTable returnTable = new DataTable();
            try
            {
                cn.ConnectionString = "Provider=IBMDASQL;Data Source=" + ipAddress +
                ";User ID=READONLY;Password=READONLY;Default Collection=" + schema + ";Force Translate = 0 ";
                cn.Open();
                SQL = SQL + " FETCH FIRST 10000 ROWS ONLY"; //レスポンスを考慮してレコード検索上限数を設ける(初期値は10,000件)
                OleDbDataAdapter da = new OleDbDataAdapter(SQL, cn);
                da.Fill(returnTable);
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);//例外処理は今回は適当に
            }
            finally
            {
                if (cn != null){
                    cn.Close();
                    cn.Dispose();
                }
            }
            return returnTable;
        }
        /// <summary>
        /// スキーマに存在するテーブル名を取得する
        /// </summary>
        /// <returns></returns>
        public DataTable getTableName()
        {
            return execSQL("SELECT Trim(TABLE_TEXT) as table_text,Trim(TABLE_NAME)  as table_name FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = " +
                quote(schema) + " AND TABLE_TEXT <> '' ORDER BY TABLE_TYPE DESC, TABLE_NAME");
        }
        /// <summary>
        /// 入力された文字にシングルクォートをつけて返却する
        /// </summary>
        /// <param name="inputText">入力文字列</param>
        /// <returns>クォートを付けた文字列</returns>
        private string quote(String inputText)
        {
            return "'" + inputText + "'";
        }

    }
}
```

#オートコンプリートを設定するように、フォームを設定する。
`setAutoCompleteMenuForSQLInputArea`というメソッドを作成しました。上記で取得したテーブルの一覧を、AutoComplete用の文字列として設定しています。`MulticolumnAutocompleteItem`に文字配列を設定すると、マルチカラムでオートコンプリートが出力されるようになります。

```C#
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;
using AutocompleteMenuNS;
using System.Data;

namespace WindowsFormsApplication1
{
    /// <summary>
    /// テスト用フォームです
    /// </summary>
    public partial class Form1 : Form
    {
        FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();

        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 初期読込時の動作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            sqlInputTextBox.Language = Language.SQL;
            sqlInputTextBox.Dock = DockStyle.Fill;
            sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません！(ToT)
            sqlInputTextBox.Font = new System.Drawing.Font("Meiryo UI", 10);//お好みで

            setAutoCompleteMenuForSQLInputArea(); //ここを追加

            sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
        }
        /// <summary>
        /// オートコンプリート用のデータを設定する
        /// </summary>
        private void setAutoCompleteMenuForSQLInputArea()
        {
            AutocompleteMenuNS.AutocompleteMenu acMenu = new AutocompleteMenuNS.AutocompleteMenu();
            acMenu.MinFragmentLength = 1;//何文字入れたらオートコンプリートを出力するかを設定
            acMenu.MaximumSize = new System.Drawing.Size(300, 400);//オートコンプリートの最小文字数を設定

            DA_AS400 da = new DA_AS400("XX.XX.XX.XX", "HOGELIB");
            DataTable tableNameTable = da.getTableName();
            foreach(DataRow rows in tableNameTable.Rows)
            {
                MulticolumnAutocompleteItem item = new MulticolumnAutocompleteItem
                    (new String[] {(String)rows["table_text"],(String)rows["table_name"] }, (String)rows["table_name"]);
                acMenu.AddItem(item);
            }
            acMenu.SetAutocompleteMenu(sqlInputTextBox, null); //以前に取得したオートコンプリート用コレクションをクリア
            acMenu.SetAutocompleteMenu(sqlInputTextBox, acMenu);
        }
    }
}
```

結果、テーブル名を入れると下記の様に日本語名+実際の名前の候補が選択肢として出力されるようになりました！実に簡単。
![sample3.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/789ea7f6-7298-cfa2-dc66-1ae26a24d010.png)

#SQL整形を実装する
最後にSQLを見易く整形する機能を実装します。formatSQLという名前のボタンをクリックすると、SQLを整形するようにしています。実際に追加したのは、`formatSQL_Click`というメソッドです。今回は全てデフォルトで整形していますが、プロパティの与え方で、整形の仕方は調整が可能です。

```C#
using System;
using System.Windows.Forms;
using FastColoredTextBoxNS;
using AutocompleteMenuNS;
using System.Data;
using PoorMansTSqlFormatterLib.Tokenizers;
using PoorMansTSqlFormatterLib.Parsers;
using PoorMansTSqlFormatterLib.Formatters;

namespace WindowsFormsApplication1
{
    /// <summary>
    /// テスト用フォームです
    /// </summary>
    public partial class Form1 : Form
    {
        FastColoredTextBox sqlInputTextBox = new FastColoredTextBox();

        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 初期読込時の動作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            sqlInputTextBox.Language = Language.SQL;
            sqlInputTextBox.Dock = DockStyle.Fill;
            sqlInputTextBox.ImeMode = ImeMode.Hiragana;//これが無いと日本語の入力が出来ません！(ToT)
            sqlInputTextBox.Font = new System.Drawing.Font("Meiryo UI", 10);//お好みで

            setAutoCompleteMenuForSQLInputArea(); //ここを追加

            sqlPanel.Controls.Add(sqlInputTextBox);//Panelにコントロールを追加
        }
        /// <summary>
        /// オートコンプリート用のデータを設定する
        /// </summary>
        private void setAutoCompleteMenuForSQLInputArea()
        {
            AutocompleteMenuNS.AutocompleteMenu acMenu = new AutocompleteMenuNS.AutocompleteMenu();
            acMenu.MinFragmentLength = 1;//何文字入れたらオートコンプリートを出力するかを設定
            acMenu.MaximumSize = new System.Drawing.Size(300, 400);//オートコンプリートの最小文字数を設定

            DA_AS400 da = new DA_AS400("10.100.1.151", "THMVLIBF");
            DataTable tableNameTable = da.getTableName();
            foreach(DataRow rows in tableNameTable.Rows)
            {
                MulticolumnAutocompleteItem item = new MulticolumnAutocompleteItem
                    (new String[] {(String)rows["table_text"],(String)rows["table_name"] }, (String)rows["table_name"]);
                acMenu.AddItem(item);
            }
            acMenu.SetAutocompleteMenu(sqlInputTextBox, null); //変更以前に取得したオートコンプリート用コレクションをクリア
            acMenu.SetAutocompleteMenu(sqlInputTextBox, acMenu);
        }
        /// <summary>
        /// SQL整形ボタンが押された時にSQLを整形する
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void formatSQL_Click(object sender, EventArgs e)
        {
            TSqlStandardTokenizer tokenizer = new TSqlStandardTokenizer();
            TSqlStandardParser parser = new TSqlStandardParser();
            TSqlStandardFormatter tsf = new TSqlStandardFormatter();
            var tokenizedSQL = tokenizer.TokenizeSQL(sqlInputTextBox.Text);
            var parsedSQL = parser.ParseSQL(tokenizedSQL);
            sqlInputTextBox.Text = tsf.FormatSQLTree(parsedSQL);
        }
    }
}
```

###結果
整形前
![sample4.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/da6a4e76-5f31-820e-6be7-163097cbdcc8.png)

整形後
![sample5.PNG](https://qiita-image-store.s3.amazonaws.com/0/154110/eeab3cd3-828f-1352-d4d6-5ba4642ec2cc.png)

それなりにSQLが見易くなりましたね。

####おまけ
ちなみに、上記で使った`TSqlStandardParser`ですが、SQLを構造化してXML化しています。
つまり下記の様なコードで、SQLに書かれた要素を抽出する事が出来ます。

```C#
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(parsedSQL.OuterXml);
XmlElement xmlel = xmlDoc.DocumentElement;
XmlNodeList nodes = xmlel.GetElementsByTagName("SelectionTarget");//SelectionTargetで、SQL内のテーブル名を抽出
var test = nodes.Item(0).InnerText;//この様な形でテーブル名を取得可能
```
私はこの機能を利用して、SQL中のテーブル名を抽出→カラム名のオートコンプリート候補作成機能を実装しています。

このあたりを弄りながら作成すると、DB2/400におけるSQL作成の生産性が上がるかもしれませんね。
(でもQUERYとかあるから、あんまりSQLの需要はないのかな…)