DbExecutor - Linqで操作しやすいSQL実行ライブラリ

2010-04-07

CodePlex : DbExecutor - linq based database executor

前回の記事を書いたところ、ついったで素敵な突っ込みを頂けたので、それを元にもう少し練り直してライブラリ化し、CodePlexに公開しました。ライブラリといってもAnonymousComparerと同じく単純なものですので、ソースコード一本のみ。ご自由にお使いください。例によってCodePlexでの英語がヤバい（小学生レベル、とりあえず何でもforつけておけばいいだろ、的な）ですね、世の中厳しい。

Linq to Sqlと名乗りたいところなのですが、本物がありますから名乗れないー。以前はIQueryableじゃないものをLinq to Hogeって言うのはどうよ、なんて思っていたのですが、考えてみるとLinq to XmlもXMLをIEnumerableベースに処理しやすいような構造を持たせたクラス群にすぎず、別にIQueryableは関係ない。Rxもそうで、あれはIEnumerableでもIQueryableでもなく完全に独立している、けれど、Linq to Events。ようするにLinq的な操作が出来ればLinqなわけです。というわけで、これはSqlをIEnumerableベースで処理出来るようにしたLinq to DB。でも本当に超絶薄いラッパーにすぎないのであんまカッコつけた名前付けるのも恥ずかしくDbExecutorという極々普通の名前に落ち着きました。「Linqで操作しやすい」とかいう釣りタイトルをつけてますが、ただたんにIEnumerable返すというだけです。SQL周りは真剣に追いかけると無限泥沼になる気がする(Entity Framework、そして更にその次へと……？)。追いかけてみたいですが、まあ、まずは、一歩目から。

何で作ったかと言うと、SQLを扱っていて嫌なusing地獄を殺したかったから。

using (var conn = new SqlConnection("connectionString"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "select ....";
    using (var reader = cmd.ExecuteReader())
    {
        foreach (IDataRecord item in reader)
        {

ちょっとクエリ呼びたいだけなのに、普通にこの量、このネスト。こんなんだからLLの人に馬鹿にされてしまうんだよ。で、ふと思ったのは、この図式ってもしかしてWebRequestと同じですか？

string html;
var req = (HttpWebRequest)WebRequest.Create("http://google.co.jp");
using (var res = req.GetResponse())
using (var stream = res.GetResponseStream())
using (var sr = new StreamReader(stream))
{
    html = sr.ReadToEnd();
}

ちょっとWebからデータを取得したいだけなのに狂ったようにusingを重ねなければならない！そしてもう一つ嫌なのが、変数に渡す際に、usingのスコープが絡むので場合によっては外で定義しなければならないこと。string html;だってさ。嫌だ嫌だ。別にvarが使えないから嫌だと言っているわけじゃなくて(半分はそうなのですが)、代入位置と宣言が離れるのは可読性が落ちます。あとは、単純に不恰好ですしね。そんなWebRequestですが、WebClientという簡単に使えるものが用意されているので、普段はこっちを使うわけです。

var html = new WebClient().DownloadString("http://google.co.jp");

素晴らしい！私はWebClientが好きです。簡単なのは良いこと、を体現していますから。ネット上のサンプルがやたらとWebRequestを使うものばかりなことを嘆きます。WebRequestとWebClientでCookie認証をする方法とかいう記事を書いたりと、必死に普及に励んだりしていますが中々どうして焼け石に水、ていうか確かに少し凝ったことをやろうとすると面倒くさいのは否めませんね……。

というわけかで、偉大なるWebClientを見習って、SqlConnectionの一連の流れを抹殺するラッパーを作ってみました。プリミティブなAPIなんて触りたくないっす。プリミティブなものは魅力どころか穢れたものに見えてしまうので、可能な限り隠蔽してやりたいのです。本当は生のSQLだって触りたくないんですけどね……。さて、目標は、簡単に使えることと、usingが極力表に出ないようにすること。とりあえず利用例から。PersonTableというAgeとFirstNameとLastNameが格納されてるテーブルからデータを引っ張ってきます。

// ただの入れ物
public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

static class Program
{
    static void Main(string[] args)
    {
        // 実行→即Closeの場合静的メソッドを用いる(Regexと同じ感覚で)
        var maxAge = DbExecutor.ExecuteScalar<int>(new SqlConnection("ConnectionString"),
            @"select max(Age) from PersonTable");

        // 接続を維持して複数回実行する場合はインスタンス生成で
        using (var executor = new DbExecutor(new SqlConnection("ConnectionString")))
        {
            // 列名とプロパティ名を対比させてマッピング
            // パラメータはstring.Format的に@p0, @p1などに対して適用される
            var persons = executor.ExecuteQuery<Person>(@"
select
    FirstName + ' ' + LastName as Name,
    Age
from PersonTable
where Age < @p0 and FirstName = @p1"
                , 20, "Osakana").ToList(); // 遅延評価なのでusingを抜ける前にリスト化などどうぞ

            // ExecuteReadはIEnumerable<IDataRecord>で一行ずつ取得できる
            var persons2 = executor.ExecuteRead(@"select * from PersonTable")
                .Select(dr => new
                {
                    Age = dr.GetInt32(0),
                    FirstName = dr.GetString(1),
                    LastName = dr.GetString(2)
                });
        }
    }
}

といった感じに、処理用のラッパーにDB接続を渡して実行します。メソッドはExecuteScalar, ExecuteNonQuery, ExecuteRead, ExecuteQueryの4つ。ScalarとNonQueryは普通のと同じ、Readは一行毎に列挙、Queryはオブジェクトへのマッピングが出来ます。4つのメソッドの引数は全て同じで、string query, params object[] parametersになります。パラメータは@p0, @p1, といったように「@p順番」に対して適用されます。

実行して即座にコネクションを閉じたい場合は静的メソッドを、接続を維持したまま複数実行した場合はインスタンスを生成してください。DbExecutor自体がIDisposableで、Dispose時に中のコネクションに対しDisposeを呼びます。なお、トランザクション関連のメソッドはありませんが、それはTransactionScopeを使ってくださいな。

スコープと遅延評価

同じ処理はメソッドに括り出す！Don't Repeat Yourself！コピペ禁止！ではあるものの、スコープが絡むと結構難しい。usingは難敵です。以下、実装の一部。

// 実装(一部抜粋)
public class DbExecutor : IDisposable
{
    // usingを共通化させたいんだけど、普通に値返すとスコープ抜けちゃう
    // →IEnumerableで包んでしまえばいいぢゃない！
    private IEnumerable<DbCommand> UsingCommand(string query, object[] parameters)
    {
        using (var cmd = dbConnection.CreateCommand())
        {
            if (dbConnection.State != ConnectionState.Open) dbConnection.Open();
            cmd.CommandText = query;
            foreach (var p in parameters.Select((v, i) => CreateParameter(cmd, "@p" + i, v)))
            {
                cmd.Parameters.Add(p);
            }
            yield return cmd;
        }
    }

    // UsingCommand().First().ExecuteScalar()だとusingを抜けてから実行になるのでダメなのですよー
    public T ExecuteScalar<T>(string query, params object[] parameters)
    {
        return UsingCommand(query, parameters).Select(c => (T)c.ExecuteScalar()).First();
    }

    public IEnumerable<IDataRecord> ExecuteRead(string query, params object[] parameters)
    {
        return UsingCommand(query, parameters).SelectMany(c => c.EnumerateAll());
    }
}

public static class IDbCommandExtensions
{
    public static IEnumerable<IDataRecord> EnumerateAll(this IDbCommand command)
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read()) yield return reader;
        }
    }
}

UsingCommandメソッドが苦心の跡です。ExecuteScalarとExecuteReadの処理(DbCommand取ってパラメータ足す)を共通化したかったのですが、usingが難敵で。ExecuteScalarはTを返すから即時実行、これをusingで一部括り出すのは簡単なのですが、問題はExecuteRead。IEnumerableを返すから遅延実行で、これに対してusing(cmd){return cmd}なんて関数を使ってしまうと、実行時に即座にusingのスコープを抜けてしまってusingが無効になってしまう(どころかDispose済みになってしまうので実効時エラー)。

じゃあどうすればいいか、というと、usingに括り出した部分も遅延評価してしまえばいい。そこで要素一つのみでyield returnする。そして、即時評価のものはSelect->First、遅延評価のものはSelectManyを使うことで、無事Usingを共通化出来ました。おお、Linqは何と素晴らしいのでしょうか！何かと言うと、つまりは、Linq to Objectsの用途はリスト処理だけじゃないんですね。IEnumerableはインフラ。そして、Reactive Extensionsに入っているEnumerableEx.Return(これはEnumerable.Repeat(elem,1)に等しい)の意味合いがジワジワくる。Returnは明らかにHaskell由来の命名で、モナドが(以下略、もしくはナンダッテー)

で、まあ、利用時は結局データベースとの接続やトランザクションとの兼ね合いもあるので、接続の状態自体は割と意識してないとダメですね。適当なところでToListとでもしておいてください。この辺も含有した上での解決策は課題ですね、今はうまいやり方が全然思いつかない。

SQLiteで使う

勿論、SqlServer以外でも使えます。System.Data.SQLiteで試しましたが、問題なく動きました。というわけで、Hello, SQLite。SQLiteをインストールしたら参照設定にSystem.Data.SQLiteを加えて

var builder = new SQLiteConnectionStringBuilder { DataSource = "test.db" };

using (var executor = new DbExecutor(new SQLiteConnection(builder.ConnectionString)))
{
    var existsTable = executor.ExecuteRead(@"select * from sqlite_master where type='table' and name = @p0", "test")
        .Any();
    if (!existsTable)
    {
        executor.ExecuteNonQuery(@"create table test (Age, Name)");
        executor.ExecuteNonQuery(@"insert into test values(10,'hoge')");
        executor.ExecuteNonQuery(@"insert into test values(20,'tako')");
        executor.ExecuteNonQuery(@"insert into test values(30,'ika')");
    }

    executor.ExecuteRead(@"select * from test where Age >= @p0", 20)
        .Select(dr => new { Age = dr.GetInt32(0), Name = dr.GetString(1) })
        .ToList()
        .ForEach(a => Console.WriteLine(a.Name + ":" + a.Age));
}

existsTableのクエリはテーブルがあるかないかを調べるもので、一行帰ってくるならテーブルが存在する、何も帰ってこない時はテーブルが存在しない。というわけで、それAny()で、ですね。IEnumerableベースで扱えると、こういうことが非常に楽です。

それにしてもSystem.Data.SQLiteいいですね、初めて使ったんですが拍子抜けするぐらいに簡単に試せました。インストールしたら参照設定に加えるだけ、DataSourceに直にファイル名指定すれば、あれば読み込み、なければ生成してくれる。データベースは設定が面倒っちいですからねー、こう簡単に出来るのは嬉しいです。

おまけ

で、まあ、VS2008ならLinq to SQL/Entities使わないのー？って話であり、まあ、ねえ、確かに、ねえ。なので、ひっそりとVS2005バージョンも作ってみました(zipに同梱してあります)。内部的にも(当然)Linq未使用なので、VS2008で対象フレームワークが.NET 2.0の場合でも、こちらなら使えます。内部でSelectとかSelectManyを再定義して、拡張メソッドの呼出じゃなくて普通の呼び出しに書き換えただけです。あまりの型推論の効かなさにイライラしました。もうC#2.0に戻るとか無理すぎるだろ常識的に考えて。

List<Person> persons = executor.ExecuteRead<Person>(@"select * from PersonTable", null,
    delegate(IDataRecord dr)
    {
        Person p = new Person();
        p.Age = dr.GetInt32(0);
        p.Name = dr.GetString(1) + " " + dr.GetString(2);
        return p;
    });

ExecuteReadとExecuteQueryは、IEnumerableを返されても困ると思うので、Listを返すようにしています。ExecuteReadはSelectがないので、第三引数でConverterデリゲートを受けるようにして、Selectの代わりにしました。使う分には、VS2008のものとさして変わらないと思います。

追記

初回リリース時の名前はDbExecuterだったんですが、DbExecutorに変更しました。stableとか言っておきながら4時間で撤回とか、殺されていいですね、ほんとすみませんすみません。あまりの恥ずかしさに穴掘って埋まりたいです……。まあ、executerでもよくね？と思わなくもなくもないのですが、実際問題ぐぐる先生の検索結果で大きな差があるので、むしろ変えるなら、たった4時間の今のうちしかない、と思ったので変更しちゃいました。しかし、ああ……。スペルは結構気をつけてるほうだと思ったんだけどなあー。

DbExecutor - Linqで操作しやすいSQL実行ライブラリ

スコープと遅延評価

SQLiteで使う

おまけ

追記

Profile

Archive