DbExecutor ver.2 - C#での生SQL書き補助ライブラリ
- 2011-04-29
データベース用のどこにでも転がっていそうなシンプルなユーティリティ、Part2。全面的につくり直しました(Part1もありました、一年前に公開してます、が、正直イマイチだった！)。
何でこの時期に？というと、睨んでいるのはWP7にSQLCEが乗るという話、です。まあ、Linq to Hogeが積まれるようなので、イラネーだろという話はあるのですが！あるのですが、それでも生SQLを使わざるを得ないシチュエーションは出てくるはずで、そのために、今のうちに作っておく/作りなおしておこうかと。まだWP7でどういう形で載るのか分からないので、今は普通に.NET 4 Client Profile用です。WP7へはSDKが出次第、すぐに対応させるつもり。
さて、どんな場合がターゲットかというと、生SQLを発行したい場合向け。大抵は軽くラップしたの作ってると思うんですが、そういう軽めのユーティリティとしてはベストなものを提供したいな、と考えました。この手のもので一番なのはEnterprise LibraryのDataなのでしょうか。確かに立派なんですが、見た感じ高尚すぎてお口に合いません(個人的にはかなり嫌いな雰囲気……)。古いものがベースのまま拡張している感がありありなところも見えるので、余分な贅肉をバッサリ切り落として極限までライトウェイトにしました。
ExecuteReader
基本的にはADO.NETのシンプルなラッパーです。生SQLを書いて実行を、少しだけ楽にサポートするという、それだけのものです。単純明快にIDbConnectionからインターフェイスだけで生やしているので、Sql Server, Sql Server Compactはもとより、Entity SQLでも動きます。SQL Azureもいけるかな。依存は極力廃したので、MySqlやOrcale、SQLiteでも(多分)動きます。但しプレースホルダは名前付きでないとダメなので(順序依存のものは動作を保証しません)、Accessとかはきっとダメ。
とりあえず、何もかぶせてないものとの比較で例を。DBは、例なので何でもいいんですが、Productsテーブルに、ProductNameとQuantityPerUnitとSupplierIDとUnitPriceというカラムがある。といったような代物です。ようは、Northwindですが。
// こんなデータ格納クラスがあるとして
public class Product
{
public string ProductName { get; set; }
public string QuantityPerUnit { get; set; }
}
//
var connStr = @"Data Source=NORTHWIND"; // Northwindサンプルから...
// 何もかぶせてない素の状態だと結果セットを取得するためのListを予め作ってAdd
// コマンドの準備も面倒、結果セットを回すのも定型句なのに行数沢山取ってシンドイ
var products1 = new List<Product>();
using (var conn = new SqlConnection(connStr))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"
select ProductName, QuantityPerUnit from Products
where SupplierID = @SupplierID and UnitPrice > @UnitPrice";
cmd.Parameters.Add(new SqlParameter("SupplierID", 1));
cmd.Parameters.Add(new SqlParameter("UnitPrice", 10));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var p = new Product
{
ProductName = (string)reader["ProductName"],
QuantityPerUnit = (string)reader["QuantityPerUnit"]
};
products1.Add(p);
}
}
}
// DbExecutorを使うとパラメータは匿名型で生成
// 結果はIEnumerable<IDataRecord>からLinq to Objectsでさらさら書ける
var products2 = DbExecutor.ExecuteReader(new SqlConnection(connStr), @"
select ProductName, QuantityPerUnit from Products
where SupplierID = @SupplierID and UnitPrice > @UnitPrice
", new { SupplierID = 1, UnitPrice = 10 })
.Select(dr => new Product
{
ProductName = (string)dr["ProductName"],
QuantityPerUnit = (string)dr["QuantityPerUnit"]
})
.ToArray();
そのまんまだと、ただ結果取りたいだけなのに、物凄く行数を使うんですね。コマンドパラメータの追加なども大変面倒くさいし、結果セットの受け取りも大変。DbExecutorでは、この二つに対処するため、コマンドパラメータは匿名型で渡せるように、結果はIEnumerable<IDataRecord>の形で受け取ることができます。そのため、Selectした後は、ToArrayするなり、Linq to Objectsの操作にそのまま流れることが可能です。
この手のユーティリティでたまに見かける、SqlDataReaderだけを返すものは、あまり意味ないのではかな。やることなんて99%、グルッと回して行の値を取ることなので、それなら、そこまで面倒見てあげよう。IEnumerable<T>を返されても扱いようがなかった石器時代と違って、今はLinqがあるので、yield returnで返す。Stream的なものは全てIEnumerable<T>に変換する。そして全部Linqで処理する。それが現代の常識(ｷﾘｯ
なお、基本的には静的メソッドで(DbConnection, SQL文字列, パラメータ(匿名型))という形でメソッドを呼びます。DbConnectionは実行完了時にDisposeするため、usingで囲う必要はありません。
ExecuteReaderDynamic
C#3.0の鉄則がストリームっぽいものはyield returnで返す、ならば、C#4.0の鉄則は、動的っぽいものは全部dynamicで返す。ことです。実に色々と楽になります。
// ExecuteReaderDynamicはIDataRecordをdynamicで包んだものを列挙する
// dynamicであることにより、カラム名のアクセスが自然に、また、キャストが不要になる
var products3 = DbExecutor.ExecuteReaderDynamic(new SqlConnection(connStr), @"
select ProductName, QuantityPerUnit from Products
where SupplierID = @SupplierID and UnitPrice > @UnitPrice
", new { SupplierID = 1, UnitPrice = 10 })
.Select(d => new Product
{
ProductName = d.ProductName,
QuantityPerUnit = d.QuantityPerUnit
})
.ToArray();
ExecuteReaderのものとの違いはSelectの箇所だけです。ExecuteReaderDynamicはIEnumerable<dynamic>を返し、そのdynamicの中身はIDataRecordをDynamicObjectで包んだものです。このことにより、見た目が更に自然に、また、煩わしい型変換をdynamicが自動でやってくれるので、キャストが不要になり、書くのがとても楽になります。
更に嬉しい特典はデバッガでの表示。
列挙中にブレークポイントを張って観察すると、動的ビューでカラム名と値、型が見えるようになります。（おっと、これはカラム名にスペースが入っているので動かないというツッコミが、いやまあ、はは……）
簡単な出力ぐらいならSelectを通す必要すらないです。
var query = DbExecutor.ExecuteReaderDynamic(new SqlConnection(connStr), @"
select * from Products where UnitPrice < @UnitPrice
", new { UnitPrice = 20.0 });
foreach (var item in query)
{
Console.WriteLine(item.ProductName + ":" + item.UnitPrice);
}
大変シンプルに書けますね。C#はLLですから！
ExecuteNonQuery/ExecuteScalar
ExecuteNonQueryやExecuteScalarも同じノリで行けます。
// パラメータはプロパティから取得するので、別に匿名型でなく普通のクラスでも可(insertやupdateで便利)
DbExecutor.ExecuteNonQuery(new SqlConnection(connStr), @"
insert into Products(ProductName, QuantityPerUnit)
values (@ProductName, @QuantityPerUnit)
", new Product { ProductName = "何か", QuantityPerUnit = "QUQNQUN" });
// パラメータが不要な場合は省略可
var serverTime = DbExecutor.ExecuteScalar<DateTime>(new SqlConnection(connStr), @"
select GetDate()");
パラメータは匿名型でなくても、普通のクラスでも可なので、UpdateやInsertの際に便利に使えるかと思います。クエリ文の@に書かれてないパラメータは無視されるので、クラス側に余計なパラメータがある分には問題ありません。
Select/SelectDynamic/Insert/Update/Delete
今までの4つは、IDataReaderの基本的な操作をラップしただけのものでしたが、他に、少し手の入ったメソッドを5つ用意してあります。メソッド名通り、Select/Insert/Update/Deleteをシンプルに行うためのものです。
- Select
select文の結果をIDataRecordを触ることなく、指定した型に移します。
// IEnumerable<T>を返し、カラム名とプロパティ名を自動でマッピングする
var products4 = DbExecutor.Select<Product>(new SqlConnection(connStr), @"
select ProductName, QuantityPerUnit from Products
where SupplierID = @SupplierID and UnitPrice > @UnitPrice
", new { SupplierID = 1, UnitPrice = 10 })
.ToArray();
O/Rマッパーというには烏滸がましいというか別にそんな大仰なものではなく、単純にselect文をオブジェクトに転写するという、それだけ。それだけなんですが、それだけのシチュエーションって結構多いですよね？これだけで、生SQLの苦痛が随分と癒される。そう、生SQLを書くのが嫌なんじゃなくて、最後にオブジェクトに手作業で対応付けるのが嫌だったんだよ、と思える程度には。
パフォーマンスもほとんど問題ありません。一回目の実行時にデリゲートの動的生成＋キャッシュを行い、全てリフレクション経由ではなくデリゲート経由のアクセスを行うため、十分高速です。基本はneue cc - Expression Treeのこね方・入門編 - 動的にデリゲートを生成してリフレクションを高速化で書いたものですが、若干これ向けに修正してあります。
そうそう、今までのパラメータの匿名型渡しも同様にデリゲート生成していますので、匿名型渡しであることによる速度低下は全くありません。
- SelectDynamic
select文の結果をExpandoObjectに移します。
// IEnumerable<dynamic>で、dynamicの中身はExpandoObject
var products5 = DbExecutor.SelectDynamic(new SqlConnection(connStr), @"
select ProductName, QuantityPerUnit from Products
where SupplierID = @SupplierID and UnitPrice > @UnitPrice
", new { SupplierID = 1, UnitPrice = 10 })
.ToArray();
SelectDynamicもExecuteReaderDynamicも、共にIEnumerable<dynamic>なのですが、ExecuteReaderDynamicのdynamicは、あくまでIDataRecord、というよりもIDataReaderをdynamicでラップしたに過ぎないため、Selectで何かに射影するか、そうでなければシーケンシャルにしか値が取れません。SelectDynamicは、結果の一行一行をExpandoObjectに予め射影しているので、それだけで永続化されます。
使い勝手的にはDataTableが近い。実際、例えばASP.NETでは (Container.DataItem as dynamic).PropName とすることでデータバインドも行けます。また、ExpandoObjectは何気にINotifyPropertyChangedが実装されていたりするので、存外使い勝手は良いかもですね。
DataTableと違ってデバッガに非常に優しいのも嬉しい。動的ビューで中身が簡単に確認できます。
- Insert
Insertは指定したオブジェクトを元にInsertするというもの。
// テーブル名と対象オブジェクト(匿名型でも可)を渡すだけでInsert
DbExecutor.Insert(new SqlConnection(connStr), "Products",
new Product { ProductName = "何か2", QuantityPerUnit = "QOQOQUN" });
Insertって書くの面倒。table名(列名)values(@列名)。クソ単純なのに……。というわけで、テーブル名とオブジェクトを指定するだけで極々シンプルなinsert into valuesに変換されます(これはExecuteNonQueryの例で出したSQL文と同じものになります)。やりたいことがシンプルなとき、シンプルに書ける。そういうのがいいなって思っていて。
- Update
Insertと同じようなコンセプトです。
// where条件(複数の場合はand連結)とupdate対象を渡します
DbExecutor.Update(new SqlConnection(connStr), "Products",
new { ProductName = "何か!!!" }), // update対象
new { ProductName = "何か!", SupplierID = 100 }); // where条件
// 以下のようなSQLが発行されます
update Products set ProductName = @ProductName
where ProductName = @__extra__ProductName
and SupplierID = @__extra__SupplierID
第三引数にupdateする値を、第四引数にwhereの条件を、。whereの条件は必須なのと、また、複数の場合はandで連結されます。発行されるSQLに__extra__というのが付くのはupdate対象とプロパティ名が被っても大丈夫なようにするため、なので、特に気にしなくてもいいです。比較的そのまんまなSQLに変換される、とだけ分かってもらえれば。
- Delete
Updateと同じような(以下略)
// delete条件を渡します(複数の場合は例によってand連結)
DbExecutor.Delete(new SqlConnection(connStr), "Products",
new { ProductName = "何か2!" });
// 以下のSQLが発行される
delete from Products where ProductName = @ProductName
ちなみに、nullだけを削除といったようなことは出来ません。いや、isnullがコマンド渡しで書けないからね…… そういうのは普通にExecuteNonQueryで書いてくださいな。そういえばでそれと、Insert, Update, Deleteは内部的にはExecuteNonQueryを実行しているので、戻り値は影響された行の個数が返ってきます。
これらは、あくまで補助的なものとして用意したので、生SQL文を完全に代替することは最初から意識していません。それが当てはまるシンプルなシチュエーションで、シンプルに書けること。それが目的です。
接続を維持しての複数クエリ/トランザクション
今までの例は全て静的メソッドの、一接続一実行の例だけでしたが、接続をつなぎっぱなしにしたりトランザクションをかけたりも出来ます。usingで囲んでnewでインスタンス化すればOK。
// 静的メソッドではなくnewすればDisposeまで接続をCloseしないモード
using (var exec = new DbExecutor(new SqlConnection(connStr)))
{
// 今まで第一引数に渡していたコネクションが(当然)不要になる
var count = exec.ExecuteScalar<int>("select count(*) from Products");
// なお、ストアドプロシージャの実行は第三引数でCommandTypeを変更すればOK
var twoyears = exec.SelectDynamic("Sales by Year",
new { Beginning_Date = "1996-1-1", Ending_Date = "1997-12-31" },
CommandType.StoredProcedure)
.ToArray();
}
// 第二引数にIsolationLevelを渡すとTransactionがかかります
using (var exec = new DbExecutor(new SqlConnection(connStr), IsolationLevel.ReadCommitted))
{
// こんな露骨でなくても、配列上に沢山オブジェクトがあって
var products = Enumerable.Range(1, 10)
.Select(i => new
{
ProductName = "Test!",
SupplierID = i
});
// サクッと一気にInsertするとか、あったりなかったり
foreach (var product in products)
{
exec.Insert("Products", product);
}
exec.TransactionComplete(); // usingを抜ける前にこれを呼び出せばCommit、呼び出さなければRollback
}
new DbExecutorの際にIsolationLevelを渡すとトランザクションがかかります。TransactionScopeのように、確定させる際は最後にTransactionCompleteを。TransactionCompleteが実行されなかった場合はRollbackされます。なお、別に普通にTransactionScopeを使っても問題ありません。
まとめ
生SQLなんて、好きじゃない！どうやったって、異物だもの。生SQL文じゃないストアドプロシージャならいいかといえば勿論そんなわけはなく、呼び出し時のパラメータと値の受け取りが……。むしろ、実態のSQL文がコードと相当離れたところに置かれ、見通しが低下するわけで、それなら逐語的文字列リテラルでC#コード中に埋めたほうがいいよ。逐語的文字列リテラルのない言語だったら、悪夢すぎて考えたくないけれど。逐語的文字列リテラルの何がいいかって、コピペでSQL Server Management Studioに移せるところなんだよね。そして逆も然りで。XMLか何かに外出しも当然イマイチで、そんなことやるぐらいなら文字列埋め込みのほうがずっといい。んー、でもS2Daoの2Way SQLというのはいいですね。パラメータの修正などもせずManagement Studioでそのまま実行可能、という。Linq to Sql/EntitiesもLinq Padを使うことでそれらしいことは出来るかな？
と、まあ、なにはともあれで、SQLをどれだけ嫌ったところで、現実問題付き合っていかなければならない。ことはなくLinq to Hogeを使いたい。けど、無理なら、それならせめて軽やかに扱いたいよね、とは皆思うはずで、皆それぞれの俺々ユーティリティは用意されていると思いますが、私も作ってみました(一年ぶりに再チャレンジで)。
特徴はIEnumerableベース(Linq to Objectsに乗っかる基盤)であることと、匿名型を多用したパラメータの受け渡し、ExpressionTreeを用いた動的デリゲート生成による高速化、dynamicによるシンプルなアクセサ。C#3, 4の機能を満遍なく使って、軽快に書けるようにしたつもりです。生SQLを扱うわりには、かなりLL的な軽さは出せてるのではないかとー。とにかく簡素なAPIになるよう気を使いました。ついでに、今回はCode Contractsも全面的に導入しています。
とりあえず、SQLCEも4.0になってDLLのみでよくなって、更にはNuGetでサクッと用意できてと(EF CodeFirstも用意できる)、C#でもデータベースがもんのすごく身近に扱えるようになりました。とてもいい事です！というわけで、生SQLのお供に是非どうぞ。Linq to SqlやLinq to Entities使っていても普通に共存出来ますので～。
まあ私は生SQLよりもCodeFirstにしたいですが！生SQLなんてどうでもいいのでLinq to Entitiesでキャッキャウフフしたいです。そんなわけでDbExecutorのテストに使ったDBは、SQLCE4+EF CodeFirstで組んであったりして。Code FirstでDB組んだのにLinq to Entitiesではなく生SQLでアクセスするとか大変モニョる。
4/30追記
ver.2.0.0.1に。ExecuteScalarの契約で事後条件を!=nullとしていたのですが、大間違いで普通にnullりるので。DbNullが返ってくるものと勘違いしていてAssumeを足してわざわざ抑制してたんですが、全くもってダメダメな対応だった……。わざわざ（静的チェッカが）警告してくれたのを、深く考えずAssumeで消すとは、愚かすぎる。そして、1日で差し替えたのにStableリリースと言い張ったことを深く反省します。なお、リリースバイナリから事後条件は削除されているので、誤った契約による問題は、静的チェッカが正しく動作しない(nullではないとマークされる)ことだけになります。その程度の軽い障害なのだから良いかといえば、勿論全然よくはなく、本当にすみませんでした。
流れるようなインターフェイス vs 生SQL
で、思い出した。基本的には私は「流れるようなインターフェイス」自体が大嫌いというのもありますが、Seasar2 - S2JDBCのような仕掛けは全く無意味だと思いますね。Linqの式木のような深い解析が出来なければ、こういうのはただのファッションで、別に書きやすくも何ともないと思っていて。それなら生SQLのほうが遥かにマシだと。だから私はDbExecutorでは前段は生SQL、後段にLinq to Objectsという形体を取っています。「生SQLは避けられないもの」という認識が大前提のうえで、それを如何にサポートするかが主眼です（まあ、なので単純に比較しても意味のないところですが）。また、C#にはLinq to Entitesもあることですし、欠けてる部分を上手く補完出来ればというのが願うところです。