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

作ろうと思ったきっかけ

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

この記事で実装する機能

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

使ったライブラリ

①Fast Colored TextBox for Syntax Highlighting
②Autocomplete Menu
③Poor Man's T-SQL Formatter

まずはライブラリをNuGetで入手

Fast Colored TextBox for Syntax Highlighting

Autocomplete Menu

Poor Man's T-SQL Formatter

Fast Colored TextBox for Syntax Highlighting(以下、FCTB)をコントロールに配置する

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

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にコントロールを追加
        }
    }
}

たったこれだけで、それなりの見た目の入力エリアが出来ました。

DB2への接続クラスを作成する

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

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に文字配列を設定すると、マルチカラムでオートコンプリートが出力されるようになります。

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);
        }
    }
}

結果、テーブル名を入れると下記の様に日本語名+実際の名前の候補が選択肢として出力されるようになりました！実に簡単。

SQL整形を実装する

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

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);
        }
    }
}

結果

整形前

整形後

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

おまけ

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

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の需要はないのかな…)