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

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)
        {

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

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

// ただの入れ物
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)
                });
        }
    }
}

// 実装(一部抜粋)
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;
        }
    }
}

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

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