private async Task <IEnumerable <TEntity> > Query <TEntity>(string sql, CommandType commandType, object parameters, ISqlTransaction transaction) { if (transaction == null) { using IDbConnection connection = NewOpenConnection(); return(await QueryInternal <TEntity>(sql, commandType, parameters, connection)); } else { SqlTransaction sqlTransaction = transaction as SqlTransaction; return(await QueryInternal <TEntity>(sql, commandType, parameters, sqlTransaction.Connection, sqlTransaction.CurrentTransaction)); } async Task <IEnumerable <TEntity> > QueryInternal <TEntityInternal>(string sql, CommandType commandType, object parameters, IDbConnection connection, IDbTransaction dbTransaction = null) { return((await _queryAsyncPolicy.ExecuteAsync(() => connection.QueryAsync <TEntity>(sql, parameters ?? new { }, commandType: commandType, transaction: dbTransaction))) .ToArray()); } }
protected async Task <TEntity> QuerySingleSql <TEntity>(string sql, object parameters = null, ISqlTransaction transaction = null) => await QuerySingle <TEntity>(sql, CommandType.Text, parameters, transaction);
protected int ExecuteNonQuery(string sql, ISqlTransaction transaction = null) { if (transaction == null) { using IDbConnection connection = NewOpenConnection(); return(ExecuteInternal(sql, connection)); } else { SqlTransaction sqlTransaction = transaction as SqlTransaction; return(ExecuteInternal(sql, sqlTransaction.Connection)); } int ExecuteInternal(string sql, IDbConnection connection, IDbTransaction dbTransaction = null) { IDbCommand command = connection.CreateCommand(); command.CommandText = sql; command.CommandType = CommandType.Text; command.CommandTimeout = 120; // ReSharper disable once AccessToDisposedClosure return(_executePolicy.Execute(() => command.ExecuteNonQuery())); } }
/// <param name = "isolationLevel"> /// Only SQL standard isolation levels are supported, except /// <see cref = "IsolationLevel.Snapshot" />. /// </param> /// <exception cref = "SqlException" /> /// <exception cref = "OperationCanceledException" /> public static ValueTask SetIsolationLevel (this ISqlTransaction transaction, IsolationLevel isolationLevel, CancellationToken ct = default) { var sql = isolationLevel switch { IsolationLevel.ReadUncommitted => setReadUncommittedSql, IsolationLevel.ReadCommitted => setReadCommittedSql, IsolationLevel.RepeatableRead => setRepeatableReadSql, IsolationLevel.Serializable => setSerializableSql, IsolationLevel.Unspecified => throw new ArgumentException( "Transaction has no specified isolation level.", nameof(transaction)), IsolationLevel.Snapshot => throw new ArgumentException( "Snapshot isolation level cannot be set by this method.", nameof(transaction)), _ => throw new ArgumentException( $"Isolation level {isolationLevel} is not part of SQL standard.", nameof(transaction)) }; return(transaction.ExecuteAsync(sql, ct)); } }
public void transaction_controller_implicitly_opens_the_connection() { using (var ctx = new SqlTransactionCallContext(TestHelper.Monitor)) { var controller = ctx.GetConnectionController(TestHelper.GetConnectionString()); BeginTranAndCommit(controller); controller.Connection.State.Should().Be(ConnectionState.Closed); // Explicit openening. using (controller.ExplicitOpen()) { BeginTranAndCommit(controller); controller.Connection.State.Should().Be(ConnectionState.Open); } controller.Connection.State.Should().Be(ConnectionState.Closed); } void BeginTranAndCommit(ISqlConnectionTransactionController controller) { ISqlTransaction tran = controller.BeginTransaction(); controller.Connection.State.Should().Be(ConnectionState.Open); tran.IsNested.Should().BeFalse(); tran.Status.Should().Be(SqlTransactionStatus.Opened); tran.Commit(); tran.Status.Should().Be(SqlTransactionStatus.Committed); tran.Invoking(t => t.Dispose()).Should().NotThrow(); } }
protected async Task <IEnumerable <TEntity> > Query <TEntity>(string sql, object parameters = null, ISqlTransaction transaction = null) => await Query <TEntity>(sql, CommandType.StoredProcedure, parameters, transaction);
bool RegistrateCrawlerId(IDbConnection connection) { ISqlTransaction transaction = connection.BeginTransaction(); string selectQuery = "SELECT COUNT(*) FROM crawler_id WHERE mac_address='" + Helpers.GetMacAddress() + "'"; List <object>[] data = connection.ExecuteReadQuery(selectQuery); bool hasNoCrawlersWithThisMac = data.Length != 0 && data[0].Count > 0 && (long)data[0][0] == 0; if (hasNoCrawlersWithThisMac) { connection.ExecuteNonQuery("INSERT INTO crawler_id(mac_address) VALUES('" + Helpers.GetMacAddress() + "')"); data = connection.ExecuteReadQuery(selectQuery); bool hasOnlyOneCrawlerWithThisMac = data.Length != 0 && data[0].Count > 0 && (long)data[0][0] == 1; if (!hasOnlyOneCrawlerWithThisMac) { transaction.Rollback(); return(false); } transaction.Commit(); return(true); } transaction.Rollback(); return(false); }
/// <inheritdoc /> public async ValueTask <String?> Test(ISqlTransaction transaction, CancellationToken cancellationToken) { var sql = @"select jsonb_agg(jsonb_build_object('timestamp', timestamp, 'content', content)) from posts where author is null" ; return(await transaction.QueryFirstRowOrDefault <String?>(sql, cancellationToken)); }
/// <exception cref = "SqlException" /> /// <exception cref = "OperationCanceledException" /> public static ValueTask SetAccessAsync (this ISqlTransaction transaction, SqlAccess access, CancellationToken ct = default) { var sql = access switch { SqlAccess.Rw => setRwSql, SqlAccess.Ro => setRoSql }; return(transaction.ExecuteAsync(sql, ct)); } const String setReadUncommittedSql = "set transaction isolation level read uncommitted";
protected async Task <bool> Delete <TEntity>(TEntity entity, ISqlTransaction transaction = null) where TEntity : class { if (transaction == null) { using IDbConnection connection = NewOpenConnection(); return(await connection.DeleteAsync(entity)); } else { SqlTransaction sqlTransaction = transaction as SqlTransaction; return(await sqlTransaction.Connection.DeleteAsync(entity, sqlTransaction.CurrentTransaction)); } }
private void Dispose(bool disposing) { if (!_disposed) { if (disposing) { // Transaction Dispose (wenn noch da...) SqlTransaction?.Dispose(); SqlTransaction = null; // Connection Dispose Console.WriteLine("dispose session"); Connection?.Close(); Connection?.Dispose(); Connection = null; Console.WriteLine("==="); } _disposed = true; } }
public ITransaction <SqlConnection> Open(bool childOnly) { var parent = m_threadTransaction.Value; ISqlTransaction child = null; //parent == null ? (ISqlTransaction)new SqlTransaction(ConnectionFactory, this) : new ChildSqlTransaction(parent, this); if (parent != null) { child = new ChildSqlTransaction(parent, this); } else { child = new SqlTransaction(ConnectionFactory, this, childOnly); } m_threadTransaction.Value = child; return(child); }
internal ChildSqlTransaction(ISqlTransaction parent, SqlTransactionManagerBase owner) { Parent = parent; m_owner = owner; }
public void Begin() { SqlTransaction = new SqlTransaction(Connection); SqlTransaction.Begin(); }
public static ValueTask SetAccessAsync (this ISqlTransaction transaction, SqlAccess access, CancellationToken cancellationToken = default) => transaction.ExecuteAsync(
/// <exception cref = "SqlException" /> /// <exception cref = "OperationCanceledException" /> public static ValueTask SetRoAsync (this ISqlTransaction transaction, CancellationToken cancellationToken = default) => transaction.ExecuteAsync(setRoSql, cancellationToken);
/// <exception cref = "SqlException" /> /// <exception cref = "OperationCanceledException" /> public static ValueTask SetSerializable(this ISqlTransaction transaction, CancellationToken ct = default) => transaction.ExecuteAsync(setSerializableSql, ct);
/// <exception cref = "SqlException" /> /// <exception cref = "OperationCanceledException" /> public static ValueTask SetRepeatableRead(this ISqlTransaction transaction, CancellationToken ct = default) => transaction.ExecuteAsync(setRepeatableReadSql, ct);