public async Task PersistSubmissionAndFundingValuesAsync(IEnumerable <NcsSubmission> ncsSubmissions, IEnumerable <FundingValue> fundingValues, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (SqlConnection ncsConnection = new SqlConnection(_dataServiceConfiguration.NcsDbConnection))
{
await ncsConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
_logger.LogDebug("Starting NCS submission and funding data Transaction");
using (SqlTransaction ncsTransaction = ncsConnection.BeginTransaction())
{
try
{
await _clearService.ClearSubmissionDataAsync(ncsJobContextMessage, ncsConnection, ncsTransaction, cancellationToken);
await _bulkInsert.Insert(DatabaseConstants.NcsSubmissionTable, ncsSubmissions, ncsConnection, ncsTransaction, cancellationToken);
await _clearService.ClearFundingDataAsync(ncsJobContextMessage, ncsConnection, ncsTransaction, cancellationToken);
await _bulkInsert.Insert(DatabaseConstants.FundingValuesTable, fundingValues, ncsConnection, ncsTransaction, cancellationToken);
ncsTransaction.Commit();
}
catch (Exception ex)
{
ncsTransaction.Rollback();
_logger.LogError($"NCS Transaction failed rolling back - {ex.Message}");
throw;
}
_logger.LogDebug("NCS Transaction complete");
}
}
}
public async Task StoreAsync(FM35Data model, SqlConnection sqlConnection, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(FM35Constants.FM35_global, model.Globals, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM35Constants.FM35_Learner, model.Learners, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM35Constants.FM35_LearningDelivery, model.LearningDeliveries, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM35Constants.FM35_LearningDelivery_Period, model.LearningDeliveryPeriods, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM35Constants.FM35_LearningDelivery_PeriodisedValues, model.LearningDeliveryPeriodisedValues, sqlConnection, cancellationToken);
}
public async Task StoreAsync(FM25Data model, SqlConnection sqlConnection, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(FM25Constants.FM25_global, model.Globals, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM25Constants.FM25_Learner, model.Learners, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM25Constants.FM25_FM35_global, model.Fm25Fm35Globals, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM25Constants.FM25_FM35_Learner_Period, model.Fm25Fm35LearnerPeriods, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM25Constants.FM25_FM35_Learner_PeriodisedValues, model.Fm25Fm35LearnerPeriodisedValues, sqlConnection, cancellationToken);
}
public async Task PersistReportDataAsync <T>(List <T> models, int ukPrn, int returnPeriod, string tableName, string connectionString, CancellationToken cancellationToken)
where T : AbstractReportModel
{
AbstractReportModel.ReturnPeriodSetter = returnPeriod;
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
await sqlConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
using (SqlTransaction sqlTransaction = sqlConnection.BeginTransaction())
{
try
{
using (SqlCommand command = new SqlCommand($"DELETE FROM {tableName} WHERE ukPrn = {ukPrn} and returnPeriod = {returnPeriod}", sqlConnection, sqlTransaction))
{
command.ExecuteNonQuery();
}
await _bulkInsert.Insert(tableName, models, sqlConnection, sqlTransaction, cancellationToken);
sqlTransaction.Commit();
}
catch (Exception ex)
{
_logger.LogError($"Persisting {tableName} failed attempting to rollback - {ex.Message}");
sqlTransaction.Rollback();
_logger.LogDebug($"Persisting {tableName} successfully rolled back");
throw;
}
}
}
}
public static void Insert <TEntity>(this IBulkInsert bulkInsert, IEnumerable <TEntity> list)
{
var dataTable = list.ToDataTable();
bulkInsert.Table = dataTable;
bulkInsert.Insert();
}
public async Task Save(IList <SummarisedActual> summarisedActuals, int collectionReturnId, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
foreach (var item in summarisedActuals)
{
item.CollectionReturnId = collectionReturnId;
}
await _bulkInsert.Insert(SummarisedActualsConstants.SummarisedActuals, summarisedActuals, sqlConnection, sqlTransaction, cancellationToken);
}
public async Task StoreAsync(FM36HistoryData model, SqlConnection sqlConnection, CancellationToken cancellationToken)
{
if (cancellationToken.IsCancellationRequested)
{
return;
}
await _bulkInsert.Insert(FM36HistoryConstants.AppEarnHistory, model.AppsEarningsHistories, sqlConnection, cancellationToken);
}
private void SaveResults()
{
var existingResults = db.Results.AsNoTracking().ToList();
var newResults = inboundResults.Except(existingResults, new ResultComparer())
.GroupBy(x => new { x.HomeTeam, x.AwayTeam, x.Date }).Select(g => g.First()).ToList();
resultsAdded = newResults.Count;
bulkInsert.Insert(newResults, "dbo.Results", ResultSqlMap.ColumnMapping());
}
private void CreateDatasets(List <Result> datasetsToCreate)
{
List <Dataset> newDatasets = new List <Dataset>();
for (int i = 0; i < datasetsToCreate.Count; i++)
{
Dataset dataset = SetupDataset(datasetsToCreate[i]);
newDatasets.Add(dataset);
}
bulkInsert.Insert(newDatasets, "dbo.Datasets", DatasetSqlMap.ColumnMapping());
}
private static void InsertValuesIntoTempTable <TKey, TValue>(
IDatabase database,
IDictionary <TKey, TValue> values,
string tempTableName)
{
database.ExecuteNonQuery(
$"CREATE TABLE {tempTableName}([Key] {typeof(TKey).ToSqlDataType()}, [Value] {typeof(TValue).ToSqlDataType()})");
using IBulkInsert bulkInsert = database.CreateBulkInsert();
bulkInsert.DestinationTableName = tempTableName;
using var reader = new EnumerableDataReader <KeyValuePair <TKey, TValue> >(values, new string[] { "Key", "Value" });
bulkInsert.Insert(reader);
}
public async Task StoreAsync(FM36Data model, SqlConnection sqlConnection, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(FM36Constants.FM36_global, model.Globals, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_Learner, model.Learners, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_LearningDelivery, model.LearningDeliveries, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_LearningDelivery_Period, model.LearningDeliveryPeriods, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_LearningDelivery_PeriodisedValues, model.LearningDeliveryPeriodisedValues, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_LearningDelivery_PeriodisedTextValues, model.LearningDeliveryPeriodisedTextValues, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_PriceEpisodes, model.ApprenticeshipPriceEpisodes, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_PriceEpisode_Period, model.ApprenticeshipPriceEpisodePeriods, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM36Constants.FM36_PriceEpisode_PeriodisedValues, model.ApprenticeshipPriceEpisodePeriodisedValues, sqlConnection, cancellationToken);
}
public async Task StoreAsync(FM70Data model, SqlConnection sqlConnection, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(FM70Constants.FM70_global, model.Globals, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_Learner, model.Learners, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_DPOutcome, model.DPOutcomes, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_LearningDelivery, model.LearningDeliveries, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_LearningDeliveryDeliverable, model.LearningDeliveryDeliverables, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_LearningDeliveryDeliverable_Period, model.LearningDeliveryDeliverablePeriods, sqlConnection, cancellationToken);
await _bulkInsert.Insert(FM70Constants.FM70_LearningDeliveryDeliverable_PeriodisedValues, model.LearningDeliveryDeliverablePeriodisedValues, sqlConnection, cancellationToken);
}
private static void InsertValuesIntoTempTable <TValue>(
IDatabase database,
IEnumerable <TValue> values,
string tempTableName)
{
TableInfo tableInfo = Database.DatabaseMapper.GetTableInfo <TValue>();
string columns = GetColumnsWithSqlTypes(tableInfo, typeof(TValue));
database.ExecuteNonQuery($"CREATE TABLE {tempTableName} ( {columns} )");
using IBulkInsert bulkInsert = database.CreateBulkInsert();
bulkInsert.DestinationTableName = tempTableName;
using var reader = new EnumerableDataReader <TValue>(values, GetColumns(tableInfo, typeof(TValue)));
bulkInsert.Insert(reader);
}
public async Task PersistAsync(IReportServiceContext reportServiceContext, IEnumerable <T> reportModels, CancellationToken cancellationToken)
{
if (!reportServiceContext.DataPersistFeatureEnabled)
{
return;
}
using (var connection = _sqlConnectionFunc())
{
await connection.OpenAsync(cancellationToken);
//check if anything exists to be deleted
var dataExists = await connection.ExecuteScalarAsync <bool>(string.Format(ExistsSql, _tableName), new { reportServiceContext.Ukprn, reportServiceContext.ReturnPeriod });
using (var transaction = connection.BeginTransaction())
{
try
{
if (dataExists)
{
_logger.LogInfo($"Clean up previous data in {_tableName} for ukprn {reportServiceContext.Ukprn}");
await connection.ExecuteAsync(string.Format(CleanUpSql, _tableName), new { reportServiceContext.Ukprn, reportServiceContext.ReturnPeriod }, transaction);
}
else
{
_logger.LogInfo($"No existing data found for {reportServiceContext.Ukprn} in {_tableName}, no cleanup needed");
}
_logger.LogInfo($"Persisting report data into {_tableName}");
await _bulkInsert.Insert(_tableName, reportModels, connection, transaction, cancellationToken);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
_logger.LogError(e.Message);
throw;
}
}
}
}
public async Task SaveAsync(IEnumerable <SummarisedActualBAU> summarisedActuals, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(SummarisedActualsConstants.SummarisedActuals, summarisedActuals, sqlConnection, sqlTransaction, cancellationToken);
}
public async Task BulkCopy <T>(string tableName, IEnumerable <T> data, SqlConnection connection, SqlTransaction transaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(tableName, data, connection, transaction, cancellationToken);
}
public async Task WriteValidationRules(IEnumerable <Rule> rules, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert("Rules", rules, sqlConnection, sqlTransaction, cancellationToken);
}
private async Task LogValidationErrorsAsync(SqlConnection sqlConnection, SqlTransaction sqlTransaction, IEnumerable <EasValidationError> validationErrors, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(TableNameConstants.ValidationError, validationErrors, sqlConnection, sqlTransaction, cancellationToken);
}
public async Task PersistValidationDataAsync(IDataStoreCache dataStoreCache, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
await _bulkInsert.Insert(TableNameConstants.ValidationError, dataStoreCache.Get <ValidationError>(), sqlConnection, sqlTransaction, cancellationToken);
}