/// <summary> /// Upserts the rows specified in "rows" to the table specified in "attribute" /// If a primary key in "rows" already exists in the table, the row is interpreted as an update rather than an insert. /// The column values associated with that primary key in the table are updated to have the values specified in "rows". /// If a new primary key is encountered in "rows", the row is simply inserted into the table. /// </summary> /// <param name="rows"> The rows to be upserted </param> /// <param name="attribute"> Contains the name of the table to be modified and SQL connection information </param> /// <param name="configuration"> Used to build up the connection </param> private async Task UpsertRowsAsync(IEnumerable <T> rows, SqlAttribute attribute, IConfiguration configuration) { using SqlConnection connection = SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, configuration); await connection.OpenAsync(); Dictionary <string, string> props = connection.AsConnectionProps(); string fullTableName = attribute.CommandText; // Include the connection string hash as part of the key in case this customer has the same table in two different Sql Servers string cacheKey = $"{connection.ConnectionString.GetHashCode()}-{fullTableName}"; ObjectCache cachedTables = MemoryCache.Default; var tableInfo = cachedTables[cacheKey] as TableInformation; if (tableInfo == null) { TelemetryInstance.TrackEvent(TelemetryEventName.TableInfoCacheMiss, props); tableInfo = await TableInformation.RetrieveTableInformationAsync(connection, fullTableName, this._logger); var policy = new CacheItemPolicy { // Re-look up the primary key(s) after 10 minutes (they should not change very often!) AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(10) }; this._logger.LogInformation($"DB and Table: {connection.Database}.{fullTableName}. Primary keys: [{string.Join(",", tableInfo.PrimaryKeys.Select(pk => pk.Name))}]. SQL Column and Definitions: [{string.Join(",", tableInfo.ColumnDefinitions)}]"); cachedTables.Set(cacheKey, tableInfo, policy); } else { TelemetryInstance.TrackEvent(TelemetryEventName.TableInfoCacheHit, props); } IEnumerable <string> extraProperties = GetExtraProperties(tableInfo.Columns); if (extraProperties.Any()) { string message = $"The following properties in {typeof(T)} do not exist in the table {fullTableName}: {string.Join(", ", extraProperties.ToArray())}."; var ex = new InvalidOperationException(message); TelemetryInstance.TrackException(TelemetryErrorName.PropsNotExistOnTable, ex, props); throw ex; } TelemetryInstance.TrackEvent(TelemetryEventName.UpsertStart, props); var transactionSw = Stopwatch.StartNew(); int batchSize = 1000; SqlTransaction transaction = connection.BeginTransaction(); try { SqlCommand command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; SqlParameter par = command.Parameters.Add(RowDataParameter, SqlDbType.NVarChar, -1); int batchCount = 0; var commandSw = Stopwatch.StartNew(); foreach (IEnumerable <T> batch in rows.Batch(batchSize)) { batchCount++; GenerateDataQueryForMerge(tableInfo, batch, out string newDataQuery, out string rowData); command.CommandText = $"{newDataQuery} {tableInfo.Query};"; par.Value = rowData; await command.ExecuteNonQueryAsync(); } transaction.Commit(); var measures = new Dictionary <string, double>() { { TelemetryMeasureName.BatchCount.ToString(), batchCount }, { TelemetryMeasureName.TransactionDurationMs.ToString(), transactionSw.ElapsedMilliseconds }, { TelemetryMeasureName.CommandDurationMs.ToString(), commandSw.ElapsedMilliseconds } }; TelemetryInstance.TrackEvent(TelemetryEventName.UpsertEnd, props, measures); this._logger.LogInformation($"Upserted {rows.Count()} row(s) into database: {connection.Database} and table: {fullTableName}."); } catch (Exception ex) { try { TelemetryInstance.TrackException(TelemetryErrorName.Upsert, ex, props); transaction.Rollback(); } catch (Exception ex2) { TelemetryInstance.TrackException(TelemetryErrorName.UpsertRollback, ex2, props); string message2 = $"Encountered exception during upsert and rollback."; throw new AggregateException(message2, new List <Exception> { ex, ex2 }); } throw; } }