Help us understand the problem. What is going on with this article?

DbCommand 型の拡張 (SQL 実行)

More than 1 year has passed since last update.

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

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

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));
        });
    }
}
Apeworks
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした