C#で原始的にSQLを扱うお話
- C# - 10.04/05
世の中はLinq to SQLだのLinq to Entitiesだのを羨ましいなあ、と指を加えて眺めている昨今ですがこんばんわ。Linq好きーな私ですがこのサイトではObjectsとXmlしか扱っていないのは、単純に私が触った事ないから、です。あうあう。そんな私ですが、SQLを(大変嫌々ながら)触らなければならなかったりする場合もないわけじゃないのですが、アレですね、思うのはパラメータ。あれにadd。add。するのが大変美しくない。もっと格好良く、一発で決めようぜ。と思って色々悩んだんですが、どうにも上手く行きそうにない。やけくそになってビルダー＋コレクション初期化子を考えてみました。
var cmd = new SqlCommand(); cmd.CommandText = @"select * from Foo where Bar > @Hoge and Tako = @Ika"; cmd.Parameters.AddRange(new SqlParameterBuilder { {"@Hoge", "2"} {"@Ika", "たこやき"} }.ToArray());
分かりづらくなってるだけで、普通にadd, addでいいですね。ダメだこりゃ。ボツ。ちなみに実装は超単純。
public class SqlParameterBuilder : IEnumerable<SqlParameter> { List<SqlParameter> parameters = new List<SqlParameter>(); public SqlParameterBuilder Add(string parameterName, object value) { parameters.Add(new SqlParameter(parameterName, value)); return this; } public IEnumerator<SqlParameter> GetEnumerator() { return parameters.GetEnumerator(); } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return GetEnumerator(); } }
コレクション初期化子の復習をしますと、IEnumerableかつAddメソッドが実装されているクラスに対してコレクション初期化子が使えます。Addは名前で決め打ちされています。メソッド名はAddじゃなくてAppendがいいなー、とか思ってもダメです。コレクション初期化子を使いたい場合はAddです。何でこんなヘンテコなことになってるのか、の理由は2008-02-08 - 当面C#と.NETな記録の記事を参照に。Addが複数引数を取る場合は{{},{}}って書けますが、IntelliSenseの補助がないので割と不便だったりしますねえ。
関数渡し
whileでEndまで読んで、ってのは嫌いです。Stream系のは全部yield returnでライン毎に返す拡張メソッドを定義しますね、私は。というわけで、SQLのDataReaderもStreamと同じ図式なので、同じ感じにしましょー。
var command = new SqlCommand(); command.CommandText = @"select hogehogehoge"; var result = command.EnumerateAll(dr => new { AA = dr.GetString(0), BB = dr.GetInt32(1) });
いい感じに見えないでしょうかどうでしょうか？といっても、これは以前書いたものの使い回しだったりしますが、その以前書いたコードとやらは若干反省してます。以下、書き直したコード。
public static IEnumerable<T> EnumerateAll<T>(this IDbCommand command, Func<IDataReader, T> selector) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { yield return selector(reader); } } } public static T[] ReadAll<T>(this IDbCommand command, Func<IDataReader, T> selector) { return command.EnumerateAll(selector).ToArray(); }
そう、以前は無理やりEnumerable.Repeatで無限リピートさせておりましたが、あのテクニックはyield returnが使えない(別関数に分けない場合)時のためのテクニックであって、yield returnが使えるなら素直にwhileループを回して書いた方がマシなのです。どうにも、まずLinqで書いたらどうなるのか、というのが頭に最初に浮かんでしまってフツーの書き方を忘れてしまいがちなのですが、大事なのはシンプルに表現すること、です。何故Linqを使うのか、そのほうがシンプルに書けるから。whileのほうがシンプルになるなら、そちらを選ぼう。弁解のために言っておくと、あれはreturn IEnumerableとyield returnの挙動の違いの例のために書いたわけですががが。あと、yieldが言語によってサポートされてるからってのもありますね。C#1.0のように自前でEnumerator用意して書かなきゃならないのならば、Linqで生成したほうが良いわけで。
マッピング
GetInt32(1)とか列を意識してオブジェクトに詰めるのがダルい。定形作業だし列がズれたら修正面倒だし殺したい。こんなの人間のやる作業じゃない。つーわけで、入れ物に詰めるのぐらいは自動化しよう。ああ、Linq to Sql使いたいなあ。
// このクラスにデータベースから値を入れるとして class MyClass { public int IntProp { get; set; } public string StrProp { get; set; } } static void Main(string[] args) { // プロパティ名と対比させて自動詰め込み var command = new SqlCommand(); command.CommandText = @" select hoge as IntProp, huga as StrProp from NantokaTable"; var result = command.Map<MyClass>(); } public static T[] Map<T>(this IDbCommand command) where T : new() { Dictionary<string, PropertyInfo> properties = typeof(T) .GetProperties(BindingFlags.Public | BindingFlags.Instance) .ToDictionary(pi => pi.Name); return command.ReadAll(dr => { var result = new T(); for (int i = 0; i < dr.FieldCount; i++) { properties[dr.GetName(i)].SetValue(result, dr[i], null); } return result; }); }
列名とプロパティ名を摺り合わせているだけで、非常に単純なものです。気になるreaderのFieldCountとかGetNameの処理効率ですが、実行した時点で一行のキャッシュが生成されて、そこから取ってくる感じになるので重たくはないっぽいです。たぶん。ちゃんと追っかけたわけじゃないので全然断言は出来ませんが。あとは辞書生成のコストですかね。Type毎で固定なら毎回辞書作らなくてもキャッシュ出来るじゃん、という。静的コンストラクタを使えば、実現できます。
public static class Extensions { public static T[] Map<T>(this IDbCommand command) where T : new() { return SqlMapper<T>.Map(command); } private static class SqlMapper<T> where T : new() { static readonly Dictionary<string, PropertyInfo> properties; static SqlMapper() { properties = typeof(T) .GetProperties(BindingFlags.Public | BindingFlags.Instance) .ToDictionary(pi => pi.Name); } public static T[] Map(IDbCommand command) { return command.ReadAll(dr => { var result = new T(); for (int i = 0; i < dr.FieldCount; i++) { if (dr.IsDBNull(i)) continue; properties[dr.GetName(i)].SetValue(result, dr[i], null); } return result; }); } } }
割とスマート。ジェネリック静的クラスには直接拡張メソッドは定義出来ないので、入れ子のprivateな静的クラスを挟んでいます。 と、こんな感じにSQLを触っていると非常に原始人っぽい。先日のテンプレート置換と同じく、コピペに優しい小粒でピリッと役立ち、なメソッドの作成を志してる感じです。まあ実際は自分だけが使うUtilなんて許されるわけもなく普通に地味にドロドロと（ああ、胃が……）。
そういえばであまり関係ないのですが、SQL文ってどこに置くべきなんでしょうかね。外部ファイルにしておいて読み込み、などというのは個人的にはどうかなー、と思っていて。どうせ呼び出し部分と1:1になるなら、↑のようにハードコードでも別によくないかしらん、そのほうがソースコード上の距離が近いこともあって分かりやすくなる。とか、どうなんでしょうかねえ。再コンパイルが不要になるといって、どうせSQLに変更入ったらコードのほうも変更入れないとマズい可能性が高そう、とか。
追記
マッパーはSystem.Data.LinqにあるDataContext.ExecuteQuery(TResult) メソッド そのものですね、たはは、シラナカッタヨ。↑のほうが超単純実装＋キャッシュなので速いとは思いますが、どうでもいい差ですな。全く同じものだと悔しいので、outer joinとかでNullが混じる場合もすんなり使えるように、IsDBNullを足してスキップするようにしました。まあSQLでcoalesceで明示的にやったほうが良いとは思いますが。EnumerateAllのほうは普通に使えると思うのでぜひぜひ。
