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