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
結果を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));
});
}
}
Author And Source
この問題について(DbCommand 型の拡張 (SQL 実行)), 我々は、より多くの情報をここで見つけました https://qiita.com/Apeworks/items/f5cee1e0946c0a31d8cc著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .