DbCommand 型の拡張 (SQL 実行)

System.Data.Common.DbCommand 型のユーティリティ

拡張メソッド用に DbCommandExtensions クラスを用意

SQL 実行メソッドをラップ

DbCommand クラスには、SQL を実行するメソッドが 3 つあるが、実行したクエリをログ出力したい場合など、それぞれのメソッドをラップしておけば便利。

• ExecuteNonQuery
• ExecuteScalar
• ExecuteReader

ログ出力

まずはログ出力用のメソッド（中身は適当に変更）

static void Log(this DbCommand command)
{
    Trace.WriteLine(command.CommandText);
}

ExecuteNonQuery

結果を伴わないクエリの実行

public static void Execute(this DbCommand command)
{
    if (command == null) throw new ArgumentNullException(nameof(command));
    try
    {
        command.Log();
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Attempt to ExecuteNonQuery failed.", ex);
    }
}

ExecuteScalar

結果を１つだけ取得するクエリの実行

public static TResult Execute<TResult>(this DbCommand command)
{
    Func<Type, bool> isNullable = type => type.IsGenericType && (type.GetGenericTypeDefinition() == typeof(Nullable<>));
    Func<Type, Type> getBaseOrSelf = type => isNullable(type) ? Nullable.GetUnderlyingType(type) : type;

    if (command == null) throw new ArgumentNullException(nameof(command));
    try
    {
        command.Log();
        var result = command.ExecuteScalar();
        if (result == null) return default(TResult);
        if (result is DBNull) return default(TResult);
        return (TResult)Convert.ChangeType(result, getBaseOrSelf(typeof(TResult)));
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Attempt to ExecuteScalar failed.", ex);
    }
}

ExecuteReader

テーブル情報を取得するクエリの実行
- IDataRecord から任意の型に変換するメソッドを渡す

public static IEnumerable<TResult> Execute<TResult>(this DbCommand command, Func<IDataRecord, TResult> parse)
{
    if (command == null) throw new ArgumentNullException(nameof(command));
    if (parse == null) throw new ArgumentNullException(nameof(parse));
    try
    {
        command.Log();
        return command.ExecuteYield(parse);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Attempt to ExecuteReader failed.", ex);
    }
}

private static IEnumerable<TResult> ExecuteYield<TResult>(this DbCommand command, Func<IDataRecord, TResult> parse)
{
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read()) yield return parse(reader);
    }
}

使用例

using (var connection = new SqlConnection("接続文字列"))
{
    connection.Open();

    using (var command1 = connection.CreateCommand())
    {
        command1.CommandText = "UPDATE xxx SET value = 'test' WHERE key = 1";
        command1.Execute();
    }

    using (var command2 = connection.CreateCommand())
    {
        command2.CommandText = "SELECT value FROM xxx WHERE key = 1";
        var value = command2.Execute<string>();
    }

    using (var command3 = connection.CreateCommand())
    {
        command3.CommandText = "SELECT * FROM xxx";
        var records = command3.Execute(record =>
        {
            return new KeyValuPair<int, string>(record.GetInt32(0), record.GetString(1));
        });
    }
}