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);
