public async Task Run()
{
try
{
_logger.LogDebug("Beginning exection");
var seenOn = DateTimeOffset.UtcNow;
// make sure the db connection is open
if (_db.State != System.Data.ConnectionState.Open && _db.State != System.Data.ConnectionState.Connecting)
{
await _db.OpenAsync();
}
var transaction = await _db.BeginTransactionAsync();
_logger.LogInformation("Getting License file");
var licenseFilePath = _client.GetLicenseFile();
_logger.LogInformation("Parsing License file");
var recordsUpserted = 0;
foreach (var license in _client.ParseLicenses(licenseFilePath))
{
await _service.Upsert(license.LicenseNumber, license.BusinessName, license.LegalName,
license.LocationAddress, license.City, license.LicenseType, license.OpenDate,
license.CloseDate, license.LbdWholesaler, seenOn, transaction);
recordsUpserted++;
if (recordsUpserted % 1000 == 0)
{
_logger.LogDebug($"Completing transaction. Total records: {recordsUpserted}");
await transaction.CommitAsync();
transaction = await _db.BeginTransactionAsync();
}
}
await transaction.CommitAsync();
}
catch (Exception e)
{
_logger.LogError(e, "Execution failed!");
throw;
}
_logger.LogInformation("Completed execution.");
}
/// <summary>
/// 开启事务
/// </summary>
/// <returns></returns>
public virtual async Task BeginTransactionAsync()
{
if (_DbTransaction?.Connection == null)
{
if (_DbConnection.State != ConnectionState.Open)
{
await _DbConnection.OpenAsync();
}
_DbTransaction = await _DbConnection.BeginTransactionAsync();
}
await _Db.Database.UseTransactionAsync(_DbTransaction);
_IsCommit = true;
}
private async Task UpdateDb(DbConnection conn)
{
var currentVersion = await GetDbVersion(conn);
var newVersions = GetLatestVersions(currentVersion);
if (newVersions.Any())
{
await using var trans = await conn.BeginTransactionAsync();
foreach (var v in newVersions)
{
_logger.LogInformation($"Update database to version {v}");
await ExecuteSqlsInDir(conn, trans, Path.Combine(_scriptsPath, UpdatesPath, v));
}
var newVersion = int.Parse(newVersions.Last());
var cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = $"UPDATE {VersionsTableName} SET Id={newVersion.ToString()}";
await cmd.ExecuteNonQueryAsync();
await trans.CommitAsync();
}
}
/// <summary>
/// Load rows into a temporary table in the database. The name of the table is automatically generated and can be accessed through <see cref="DbTempTableHandle.Name" />.
/// </summary>
protected async Task <DbTempTableHandle> TempTableAsync <TRow>(IEnumerable <TRow> rows, DbTempTableBuilder <TRow> builder, CancellationToken cancellationToken)
{
ThrowIf.Null(rows, nameof(rows));
ThrowIf.Null(builder, nameof(builder));
var tableName = $"__TempTable_{Guid.NewGuid():N}";
await DbConnection.EnsureOpenAsync(cancellationToken).ConfigureAwait(false);
await using (var transaction = await DbConnection.BeginTransactionAsync(cancellationToken).ConfigureAwait(false))
{
var dataTable = builder.ToDataTable(rows);
try
{
await ExecuteAsync
(
$"CREATE TEMPORARY TABLE {tableName}({string.Join(",", builder.Columns.Select(col => $"{col.Name} {col.DbType}"))});",
transaction,
cancellationToken
).ConfigureAwait(false);
var bulkCopy = new MySqlBulkCopy(DbConnection, transaction)
{
DestinationTableName = tableName,
NotifyAfter = 100
};
bulkCopy.MySqlRowsCopied += (sender, args) =>
{
Logger.LogTrace("Loaded {rowsCopied}/{totalRows} rows into {tableName}...", args.RowsCopied, dataTable.Rows.Count, tableName);
};
await bulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
}
catch (Exception ex)
{
await transaction.RollbackAsync(CancellationToken.None).ConfigureAwait(false);
Logger.LogError(ex, "Error loading rows into {tableName}", tableName);
throw;
}
Logger.LogDebug("Loaded {totalRows} rows into {tableName}({columnNames})",
dataTable.Rows.Count,
tableName,
string.Join(", ", builder.Columns.Select(col => col.Name)));
}
return(new DbTempTableHandle(tableName, async() =>
{
await ExecuteAsync($"DROP TEMPORARY TABLE {tableName};", cancellationToken).ConfigureAwait(false);
Logger.LogDebug("Dropped {tableName}", tableName);
}));
}
/// <summary>
/// Try to open connection and begin a new transaction on it.
/// </summary>
public static async Task <DbTransaction> OpenTransactionAsync(this DbConnection connection, IsolationLevel isolationLevel)
{
_ = connection ?? throw new ArgumentNullException(nameof(connection));
if (!connection.IsOpen())
{
await connection.OpenAsync();
}
return(await connection.BeginTransactionAsync(isolationLevel));
}
public async Task InsertAsync(DbConnection connection)
{
using (var trans = await connection.BeginTransactionAsync().ConfigureAwait(false))
{
await connection.ExecuteAsync(
$@"INSERT INTO {nameof(TestTable)}(Id, Message) VALUES (@id, @message);",
new { id = Id, message = Message }).ConfigureAwait(false);
await trans.CommitAsync().ConfigureAwait(false);
}
}
/// <summary>
/// 对数据库连接开启事务
/// </summary>
/// <param name="context">数据上下文</param>
/// <param name="cancellationToken">异步取消标记</param>
/// <returns></returns>
public virtual async Task BeginOrUseTransactionAsync(IDbContext context, CancellationToken cancellationToken = default)
{
if (_contextDict.IsEmpty)
{
return;
}
foreach (KeyValuePair <DbConnection, List <DbContextBase> > pair in _contextDict)
{
DbContextBase dbContext = pair.Value.FirstOrDefault(m => m.Equals((DbContextBase)context));
if (dbContext == null)
{
continue;
}
DbConnection connection = pair.Key;
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync(cancellationToken);
}
if (!_transDict.TryGetValue(connection, out DbTransaction transaction) || transaction.Connection == null)
{
transaction = await connection.BeginTransactionAsync(cancellationToken);
_transDict[connection] = transaction;
_logger.LogDebug($"在上下文 {context.GetType()}[标识：[{context.GetHashCode()}] 创建事务，事务标识：{transaction.GetHashCode()}");
}
if (dbContext.Database.CurrentTransaction != null && dbContext.Database.CurrentTransaction.GetDbTransaction() == transaction)
{
continue;
}
if (dbContext.IsRelationalTransaction())
{
await dbContext.Database.UseTransactionAsync(transaction, cancellationToken : cancellationToken);
_logger.LogDebug($"在上下文 {context.GetType()}[标识：[{context.GetHashCode()}] 上应用现有事务，事务标识：{transaction.GetHashCode()}");
}
else
{
await dbContext.Database.BeginTransactionAsync(cancellationToken);
}
break;
}
HasCommitted = false;
}
async Task InsertUsersIntoDatabase(IEnumerable <string> emails)
{
await db.OpenAsync();
using (var tx = await db.BeginTransactionAsync())
{
foreach (string email in emails)
{
await db.ExecuteAsync("insert into [User] (Email) values (@Email)", new { email }, tx);
}
await tx.CommitAsync();
}
}
public async Task DbConnectionCommitAsync()
{
DbConnection connection = m_connection;
using (var trans = await connection.BeginTransactionAsync().ConfigureAwait(false))
{
await connection.ExecuteAsync("insert into transactions_test values(1), (2)", transaction : trans).ConfigureAwait(false);
await trans.CommitAsync().ConfigureAwait(false);
}
var results = await connection.QueryAsync <int>(@"select value from transactions_test order by value;").ConfigureAwait(false);
Assert.Equal(new[] { 1, 2 }, results);
}
public async Task SetupDatabaseAsync()
{
using (var tx = await Connection.BeginTransactionAsync())
{
var tables = TestCaseExcel.Database.ToArray();
await Task.WhenAll(
tables.Reverse().Select(m
=> SqlRawHelper.ExecuteSqlAsync(Connection, $"delete from {m.TableName}"))
);
await Task.WhenAll(
tables.Select(table =>
SqlRawHelper.ImportDataTableAsync(Connection, table)));
await tx.CommitAsync();
}
}
public Task <ITransaction> GenerateAsync(CancellationToken cancellationToken)
{
lock (lockObject)
{
var dbTransaction = connection.BeginTransactionAsync(cancellationToken)
.GetAwaiter().GetResult();
foreach (var context in dbContexts)
{
if (context.Database.CurrentTransaction != null)
{
throw new InvalidOperationException("Transaction already open");
}
context.Database.UseTransactionAsync(dbTransaction, cancellationToken)
.GetAwaiter().GetResult();
}
return(Task.FromResult(new Transaction(dbTransaction) as ITransaction));
}
}
private async Task InitDb(DbConnection conn)
{
var version = 0;
var newVersions = GetLatestVersions(version);
if (newVersions.Any())
{
version = int.Parse(newVersions.Last());
}
await using var trans = await conn.BeginTransactionAsync();
await ExecuteSqlsInDir(conn, trans, Path.Combine(_scriptsPath, CreatesPath));
var cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = $"INSERT INTO {VersionsTableName} VALUES ({version})";
await cmd.ExecuteNonQueryAsync();
await trans.CommitAsync();
}
/// <summary>
/// 对数据库连接开启事务
/// </summary>
/// <param name="cancellationToken">异步取消标记</param>
/// <returns></returns>
public virtual async Task BeginOrUseTransactionAsync(CancellationToken cancellationToken = default(CancellationToken))
{
if (_dbContexts.Count == 0)
{
return;
}
if (_transaction?.Connection == null)
{
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync(cancellationToken);
}
#if NETSTANDARD2_0
_transaction = _connection.BeginTransaction();
#else
_transaction = await _connection.BeginTransactionAsync(cancellationToken);
#endif
_logger.LogDebug($"在连接 {_connection.ConnectionString} 上开启新事务，事务标识：{_transaction.GetHashCode()}");
}
foreach (DbContextBase context in _dbContexts)
{
if (context.Database.CurrentTransaction != null && context.Database.CurrentTransaction.GetDbTransaction() == _transaction)
{
continue;
}
if (context.IsRelationalTransaction())
{
await context.Database.UseTransactionAsync(_transaction, cancellationToken);
_logger.LogDebug($"在上下文 {context.GetType()} 上应用现有事务");
}
else
{
await context.Database.BeginTransactionAsync(cancellationToken);
}
}
HasCommitted = false;
}
private static async Task <T> ExecuteCommandAsync <T>(
DbConnection connection, Func <DbCommand, Task <T> > executeAsync, string sql, bool useTransaction,
IReadOnlyList <object> parameters)
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
await connection.OpenAsync();
try
{
using (var transaction = useTransaction
? await connection.BeginTransactionAsync()
: null)
{
T result;
using (var command = CreateCommand(connection, sql, parameters))
{
result = await executeAsync(command);
}
if (transaction != null)
{
await transaction.CommitAsync();
}
return(result);
}
}
finally
{
if (connection.State != ConnectionState.Closed)
{
await connection.CloseAsync();
}
}
}
public async Task <DbTransaction> TransactionStartAsync()
{
return(await connection.BeginTransactionAsync(IsolationLevel.Serializable));
}
static async ValueTask <IDbTransaction> doAsync(DbConnection c, CancellationToken ct) => await c.BeginTransactionAsync(ct).ConfigureAwait(false);
/// <summary>
/// Connects to the database and starts a transaction
/// </summary>
/// <returns>A task that represents the asynchronous operation.</returns>
public async Task ConnectAsync()
{
await _dbConnection.OpenAsync();
_dbTransaction = await _dbConnection.BeginTransactionAsync();
}
public async Task <DbTransaction> GenerateTransactionAsync(DbConnection conn, System.Data.IsolationLevel isolationLevel)
{
return(await conn.BeginTransactionAsync());
}
public async Task OpenAsync(IsolationLevel isolationLevel)
{
await OpenAsync();
Transaction = await connection.BeginTransactionAsync(isolationLevel);
}
public async Task BeginTransactionAsync(IsolationLevel?isolationLevel = null, CancellationToken token = default)
{
OpenConnection();
this.DbTransaction = await DbConnection.BeginTransactionAsync(isolationLevel ?? defaultIsolationLevel, token).ConfigureAwait(false);
}
/// <summary>
/// Asynchronously gets an instace of the <see cref="DbTransaction"/> object based on the <see cref="DbConnection"/> object passed in
/// </summary>
/// <param name="level">The transaction locking level for the passed in <paramref name="connection"/></param>
/// <param name="connection">An instance of <see cref="DbConnection"/></param>
/// <param name="token">Structure that propogates a notification that an operation should be cancelled</param>
/// <returns>An instance of the <see cref="DbTransaction"/> object</returns>
public async ValueTask <DbTransaction> GetDbTransactionAsync(DbConnection connection, IsolationLevel level, CancellationToken token = default)
{
//Return this back to the caller
return(await connection.BeginTransactionAsync(level, token).ConfigureAwait(false));
}
public ITransaction BeginTransaction(IsolationLevel isolationLevel)
{
return(new Transaction(dbConnection.BeginTransactionAsync(isolationLevel).Result));
}
public async IAsyncEnumerable <__IRow> ExecuteRecordsAsync(bool schemaOnly, Type connectionType, string connectionString, bool transactional, IsolationLevel isolationLevel, CommandType commandType, string commandText, IEnumerable <DbParameter> commandParameters, int?commandTimeout = null, bool commandPrepare = false, Action <long> resultCallback = null)
{
DbTransaction dbTransaction;
const bool OPEN = true;
//IList<__Row> rows;
CommandBehavior commandBehavior;
long resultIndex = 0;
ReadOnlyCollection <DbColumn> dbColumns;
DbColumn dbColumn;
PropertyInfo[] propertyInfos;
PropertyInfo propertyInfo;
if ((object)connectionType == null)
{
throw new ArgumentNullException(nameof(connectionType));
}
if ((object)connectionString == null)
{
throw new ArgumentNullException(nameof(connectionString));
}
using (DbConnection dbConnection = (DbConnection)Activator.CreateInstance(connectionType))
{
if (OPEN)
{
dbConnection.ConnectionString = connectionString;
await dbConnection.OpenAsync();
if (transactional)
{
dbTransaction = await dbConnection.BeginTransactionAsync(isolationLevel);
}
else
{
dbTransaction = null;
}
}
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.Transaction = dbTransaction;
dbCommand.CommandType = commandType;
dbCommand.CommandText = commandText;
if ((object)commandTimeout != null)
{
dbCommand.CommandTimeout = (int)commandTimeout;
}
// add parameters
if ((object)commandParameters != null)
{
foreach (DbParameter commandParameter in commandParameters)
{
if ((object)commandParameter.Value == null)
{
commandParameter.Value = DBNull.Value;
}
dbCommand.Parameters.Add(commandParameter);
}
}
if (commandPrepare)
{
await dbCommand.PrepareAsync();
}
//rows = new List<__Row>();
commandBehavior = schemaOnly ? CommandBehavior.SchemaOnly : CommandBehavior.Default;
using (DbDataReader dbDataReader = await dbCommand.ExecuteReaderAsync(commandBehavior))
{
__Row row;
string key;
object value;
if (!schemaOnly)
{
do
{
if ((object)resultCallback != null)
{
resultCallback(resultIndex++);
}
while (await dbDataReader.ReadAsync())
{
row = new __Row(StringComparer.OrdinalIgnoreCase);
for (int fieldIndex = 0; fieldIndex < dbDataReader.FieldCount; fieldIndex++)
{
key = dbDataReader.GetName(fieldIndex);
value = dbDataReader.GetValue(fieldIndex);
value = this.DataTypeFascade.ChangeType <object>(value);
if (row.ContainsKey(key) || (key ?? string.Empty).Length == 0)
{
key = string.Format("Field_{0:0000}", fieldIndex);
}
row.Add(key, value);
}
//rows.Add(row);
yield return(row);
}
}while (await dbDataReader.NextResultAsync());
}
else
{
if (!dbDataReader.CanGetColumnSchema())
{
throw new NotSupportedException(string.Format("The connection command type '{0}' does not support schema access.", dbDataReader.GetType().FullName));
}
dbColumns = dbDataReader.GetColumnSchema();
{
if ((object)dbColumns != null)
{
for (long recordIndex = 0; recordIndex < dbColumns.Count; recordIndex++)
{
dbColumn = dbColumns[(int)recordIndex];
propertyInfos = dbColumn.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
row = new __Row(StringComparer.OrdinalIgnoreCase);
row.Add(string.Empty, dbColumn);
if ((object)propertyInfos != null)
{
for (int fieldIndex = 0; fieldIndex < propertyInfos.Length; fieldIndex++)
{
propertyInfo = propertyInfos[fieldIndex];
if (propertyInfo.GetIndexParameters().Any())
{
continue;
}
key = propertyInfo.Name;
value = propertyInfo.GetValue(dbColumn);
value = this.DataTypeFascade.ChangeType <object>(value);
row.Add(key, value);
}
}
yield return(row);
}
}
}
}
}
//return rows;
}
}
}
