C#でSQLiteを利用する（クエリ編）

さて、前の投稿で設定したSQLiteをぜひ使ってやろう。

注意：そのまま流用して起こった不具合は責任を負えません。脆弱な面もあるので気をつけてください。

openしてみる

まず、SQLiteのデータは、ファイルで管理されるという仕様を思い出して欲しい。つまり、ファイルを扱うのと同じ感覚でOpenして、後はクエリを送りつければ良いということ。

bool open()
{
    if ((connect = new SQLiteConnection("Data Source=" + dbFilename)) == null)
    {
        error("DBの接続に失敗しました。");
        return false;
    }
    if ((command = connect.CreateCommand()) == null)
    {
        error("SQLの実行ができません。");
        return false;
    }
    connect.Open();
    transaction = connect.BeginTransaction();
    return true;
}

このようなメソッドを作ってみた。とりあえず大きな流れがあって、

ファイルを開く（DBを開く）
コマンドを作成（？）←ここの表現がいまいちわからない
トランザクションを開始

トランザクションにおける注意点

特に、3.に注意して欲しい。どうやらSQLiteでは明示的にトランザクションを行わないと、暗示的にINSERTの前後でトランザクションが行われるようだ。

トランザクションとして管理された処理は「すべて成功」か「すべて失敗」のいずれかであることが保証される。例えば、資金移動システムをコンピュータで処理する場合、出金処理と入金処理は「どちらも成功」か「どちらも失敗」のどちらかであることが要求される。「出金に成功して入金に失敗」すると、出金された資金が宙に浮いてしまうからである。このような場合に、出金と入金をまとめて1つのトランザクションとして管理し、どちらか一方が失敗したらもう片方も失敗させ、どちらも成功したときに初めて全体を成功と評価するのがTPである。引用

トランザクションってのはそんなに軽い処理ではないから、INSERTのたびにトランザクションを行われたら、速度は馬鹿みたいに遅くなるらしい。検証してるページはたくさんあるから、適当にググれば良いと思う。

SQLを送りつけてみる

結論としては、たくさんのINSERTを一度に行う必要がある処理では、すべてのINSERTを明示的に１つのトランザクションで囲わないと、速度的にまずいことが起こるということ。よって、SQLを実行するメソッドは以下の二つを用意すると便利そう。

public bool ExecuteCommand(string comm)
{
    if (open())
    {
        try
        {
            command.CommandText = comm;
            command.ExecuteNonQuery();
            close();
            return true;
        }
        catch (Exception ex)
        {
            error(ex.ToString());
        }
    }
    return false;
}

public bool ExecuteCommand(string[] comms)
{
    if (open())
    {
        try
        {
            for (int i = 0; i < comms.Length; i++)
            {
                command.CommandText = comms[i];
                command.ExecuteNonQuery();
            }
            close();
            return true;
        }
        catch (Exception ex)
        {
            error(ex.ToString());
        }
    }
    return false;
}

オーバーロードしてメソッドを宣言してある。文字列を渡せば前者が呼び出されて、文字列の配列を渡せば後者が呼び出される。という仕組みで、まとめてINSERTする場合は、前記の理由により、後者を用いたほうが、より高速。

注意点：DBを開くオーバーヘッド

DBファイルを開く
トランザクションを開始
処理を行う
DBを閉じる

サンプルがこのようになっていることに注意。SQLを送りつけるたびにDBを開きなおしているため、何度もSQLを送りつける場合は、DBを開くためのオーバーヘッドが大きくなりすぎる。この場合は、プログラム起動時に一度だけopenし、その後のSQLごとにトランザクションのみを行うなどの工夫が必要だろう。

closeを忘れない

後、忘れてはいけないのが、資源の解放。

bool close()
{
    transaction.Commit();
    transaction.Dispose();
    command.Dispose();
    connect.Close();
    return true;
}

何気なく今までのソースでclose()を使ってきたけれども、実はこれのこと。

注意点

SQLを送りつける注意点でも書いたように、DBを閉じるオーバーヘッドも考慮するなら、この部分も変える必要がある。

jikkenjo.net

適当なことを書くか書かないかわからないメモ帳。