/// <summary>
/// DbCommand オブジェクトを作成します。
/// </summary>
/// <param name="trans">DbTransaction オブジェクト。</param>
/// <param name="commandText">クエリ。</param>
/// <param name="commandType">クエリの種類。</param>
/// <param name="bindByName">名前付きパラメーターの使用を行うかどうか。</param>
/// <returns>DbCommand オブジェクト。</returns>
public static DbCommand CreateCommand(this DbTransaction trans, string commandText = "", CommandType commandType = CommandType.Text, bool bindByName = true)
{
var command = trans.CreateCommand(bindByName);
command.CommandText = commandText;
command.CommandType = commandType;
return command;
}
public static Task StoreReceived(this DbTransaction tx, Guid id, string messageType)
{
return(tx.CreateCommand(
"insert into sender.received_track (id, message_type) values (@id, @type)")
.With("id", id)
.With("type", messageType)
.ExecuteNonQueryAsync());
}
// Try to get a global lock with transaction scoping
public static async Task <bool> TryGetGlobalTxLock(this DbConnection conn, DbTransaction tx, int lockId,
CancellationToken cancellation = default(CancellationToken))
{
var c = await tx.CreateCommand("SELECT pg_try_advisory_xact_lock(:id);")
.With("id", lockId)
.ExecuteScalarAsync(cancellation);
return((bool)c);
}
/// <summary>
/// Removes the provided version from the database log table.
/// </summary>
/// <param name="transaction">The transaction to execute the command in</param>
/// <param name="version">The version to log</param>
private static void UpdateSchemaVersionDown(DbTransaction transaction, long version)
{
const string sql = "DELETE FROM [schema_migrations] WHERE version = {0}";
using (DbCommand cmd = transaction.CreateCommand())
{
cmd.CommandText = string.Format(sql, version);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Updates the database with the version provided
/// </summary>
/// <param name="transaction">The transaction to execute the command in</param>
/// <param name="version">The version to log</param>
private static void UpdateSchemaVersionUp(DbTransaction transaction, long version)
{
const string sql = "INSERT INTO [schema_migrations] ([version]) VALUES ({0})";
using (DbCommand cmd = transaction.CreateCommand())
{
cmd.CommandText = string.Format(sql, version);
cmd.ExecuteNonQuery();
}
}
///<summary>Creates a DbDataAdapter.</summary>
///<param name="factory">The factory used to create the DbDataAdapter.</param>
///<param name="transaction">The transaction for the adapter.</param>
///<param name="selectSql">The SQL for the adapter's select command.</param>
public static DbDataAdapter CreateDataAdapter(this DbProviderFactory factory, DbTransaction transaction, string selectSql)
{
if (factory == null)
{
throw new ArgumentNullException("factory");
}
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (String.IsNullOrEmpty(selectSql))
{
throw new ArgumentNullException("selectSql");
}
var adapter = factory.CreateDataAdapter();
adapter.SelectCommand = transaction.CreateCommand(selectSql);
return(adapter);
}
//public OleDbTransaction OleDbTransaction
//{
// get { return (OleDbTransaction)_transactionImpl.Transaction; }
//}
public OleDbCommand CreateOleCommand()
{
return((OleDbCommand)_transactionImpl.CreateCommand());
}
/// <summary>
/// プロシージャ発行用の DbCommand オブジェクトを作成します。
/// </summary>
/// <param name="trans">DbTransaction オブジェクト。</param>
/// <param name="commandText">プロシージャー名。</param>
/// <returns>DbCommand オブジェクト。</returns>
public static DbCommand CreateProcedureCommand(this DbTransaction trans, string commandText)
{
return trans.CreateCommand(commandText, CommandType.StoredProcedure, true);
}
/// <summary>
/// 通常クエリ発行用の DbCommand オブジェクトを作成します。
/// </summary>
/// <param name="trans">DbTransaction オブジェクト。</param>
/// <param name="commandText">クエリ。</param>
/// <returns>DbCommand オブジェクト。</returns>
public static DbCommand CreateQueryCommand(this DbTransaction trans, string commandText)
{
return trans.CreateCommand(commandText, CommandType.Text, true);
}
public static T ExecuteScalar <T>(this DbTransaction transaction, string sql)
{
using (var command = transaction.CreateCommand(sql)) return(command.ExecuteScalar().NullableCast <T>());
}
public static DbDataReader ExecuteReader <TParameters>(this DbTransaction transaction, string sql, TParameters parameters) where TParameters : class
{
return(transaction.CreateCommand(sql, parameters).ExecuteReader());
}
///<summary>Executes a SQL statement against a connection.</summary>
///<typeparam name="TParameters">A type containing public properties to add as parameters.</typeparam>
///<param name="transaction">The transaction to the database. The connection is not closed.</param>
///<param name="sql">The SQL to execute.</param>
///<param name="parameters">An object containing the values of the parameters to add.</param>
///<returns>The number of rows affected by the statement.</returns>
public static int ExecuteNonQuery <TParameters>(this DbTransaction transaction, string sql, TParameters parameters) where TParameters : class
{
using (var command = transaction.CreateCommand(sql, parameters)) return(command.ExecuteNonQuery());
}
///<summary>Creates a DbCommand.</summary>
///<param name="transaction">The connection to create the command for.</param>
///<param name="sql">The SQL of the command.</param>
public static DbCommand CreateCommand(this DbTransaction transaction, string sql)
{
return(transaction.CreateCommand <object>(sql, null));
}
/// <summary>
/// Shortcut for creating a stored procedure DbCommand from any DbTransaction.
/// </summary>
/// <param name="transaction">The transaction to create a command from.</param>
/// <param name="procedureName">The command text or stored procedure name to use.</param>
/// <param name="secondsTimeout">The number of seconds to wait before the command times out.</param>
/// <returns>The created SqlCommand.</returns>
public static DbCommand CreateStoredProcedureCommand(this DbTransaction transaction,
string procedureName, int secondsTimeout = CommandTimeout.DEFAULT_SECONDS)
=> transaction.CreateCommand(CommandType.StoredProcedure, procedureName, secondsTimeout);
/// <summary>
/// Shortcut for creating a text DbCommand from any DbTransaction.
/// </summary>
/// <param name="transaction">The transaction to create a command from.</param>
/// <param name="commandText">The command text or stored procedure name to use.</param>
/// <param name="secondsTimeout">The number of seconds to wait before the command times out.</param>
/// <returns>The created SqlCommand.</returns>
public static DbCommand CreateTextCommand(this DbTransaction transaction,
string commandText, int secondsTimeout = CommandTimeout.DEFAULT_SECONDS)
=> transaction.CreateCommand(CommandType.Text, commandText, secondsTimeout);
// Global lock with transaction scoping, this would block
public static Task GetGlobalTxLock(this DbConnection conn, DbTransaction tx, int lockId,
CancellationToken cancellation = default(CancellationToken))
{
return(tx.CreateCommand("SELECT pg_advisory_xact_lock(:id);").With("id", lockId)
.ExecuteNonQueryAsync(cancellation));
}