/// <summary>
/// Initializes a new instance of the <see cref="SqlAsyncCollector<typeparamref name="T"/>"/> class.
/// </summary>
/// <param name="connection">
/// Contains the SQL connection that will be used by the collector when it inserts SQL rows
/// into the user's table
/// </param>
/// <param name="attribute">
/// Contains as one of its attributes the SQL table that rows will be inserted into
/// </param>
/// <param name="loggerFactory">
/// Logger Factory for creating an ILogger
/// </param>
/// <exception cref="ArgumentNullException">
/// Thrown if either configuration or attribute is null
/// </exception>
public SqlAsyncCollector(IConfiguration configuration, SqlAttribute attribute, ILogger logger)
{
this._configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
this._attribute = attribute ?? throw new ArgumentNullException(nameof(attribute));
this._logger = logger;
TelemetryInstance.TrackCreate(CreateType.SqlAsyncCollector);
}
IAsyncEnumerable <T> IConverter <SqlAttribute, IAsyncEnumerable <T> > .Convert(SqlAttribute attribute)
{
TelemetryInstance.TrackConvert(ConvertType.IAsyncEnumerable);
try
{
return(new SqlAsyncEnumerable <T>(SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, this._configuration), attribute));
}
catch (Exception ex)
{
var props = new Dictionary <string, string>()
{
{ TelemetryPropertyName.Type.ToString(), ConvertType.IAsyncEnumerable.ToString() }
};
TelemetryInstance.TrackException(TelemetryErrorName.Convert, ex, props);
throw;
}
}
/// <summary>
/// Opens a SqlConnection, reads in the data from the user's database, and returns it as a JSON-formatted string.
/// </summary>
/// <param name="attribute">
/// Contains the information necessary to establish a SqlConnection, and the query to be executed on the database
/// </param>
/// <param name="cancellationToken">The cancellationToken is not used in this method</param>
/// <returns>
/// The JSON string. I.e., if the result has two rows from a table with schema ProductID: int, Name: varchar, Cost: int,
/// then the returned JSON string could look like
/// [{"productID":3,"name":"Bottle","cost":90},{"productID":5,"name":"Cup","cost":100}]
/// </returns>
async Task <string> IAsyncConverter <SqlAttribute, string> .ConvertAsync(SqlAttribute attribute, CancellationToken cancellationToken)
{
TelemetryInstance.TrackConvert(ConvertType.Json);
try
{
return(await this.BuildItemFromAttributeAsync(attribute));
}
catch (Exception ex)
{
var props = new Dictionary <string, string>()
{
{ TelemetryPropertyName.Type.ToString(), ConvertType.Json.ToString() }
};
TelemetryInstance.TrackException(TelemetryErrorName.Convert, ex, props);
throw;
}
}
/// <summary>
/// Initializes the SQL binding rules
/// </summary>
/// <param name="context"> The config context </param>
/// <exception cref="ArgumentNullException">
/// Thrown if context is null
/// </exception>
public void Initialize(ExtensionConfigContext context)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
ILogger logger = this._loggerFactory.CreateLogger(LogCategories.Bindings);
TelemetryInstance.Initialize(this._configuration, logger);
#pragma warning disable CS0618 // Fine to use this for our stuff
FluentBindingRule <SqlAttribute> inputOutputRule = context.AddBindingRule <SqlAttribute>();
var converter = new SqlConverter(this._configuration);
inputOutputRule.BindToInput(converter);
inputOutputRule.BindToInput <string>(typeof(SqlGenericsConverter <string>), this._configuration, logger);
inputOutputRule.BindToCollector <OpenType>(typeof(SqlAsyncCollectorBuilder <>), this._configuration, logger);
inputOutputRule.BindToInput <OpenType>(typeof(SqlGenericsConverter <>), this._configuration, logger);
}
/// <summary>
/// Adds an item to this collector that is processed in a batch along with all other items added via
/// AddAsync when <see cref="FlushAsync"/> is called. Each item is interpreted as a row to be added to the SQL table
/// specified in the SQL Binding.
/// </summary>
/// <param name="item"> The item to add to the collector </param>
/// <param name="cancellationToken">The cancellationToken is not used in this method</param>
/// <returns> A CompletedTask if executed successfully </returns>
public async Task AddAsync(T item, CancellationToken cancellationToken = default)
{
if (item != null)
{
await this._rowLock.WaitAsync(cancellationToken);
TelemetryInstance.TrackEvent(TelemetryEventName.AddAsync);
try
{
this._rows.Add(item);
}
finally
{
this._rowLock.Release();
}
}
}
/// <summary>
/// Creates a SqlCommand containing a SQL connection and the SQL query and parameters specified in attribute.
/// The user can open the connection in the SqlCommand and use it to read in the results of the query themselves.
/// </summary>
/// <param name="attribute">
/// Contains the SQL query and parameters as well as the information necessary to build the SQL Connection
/// </param>
/// <returns>The SqlCommand</returns>
public SqlCommand Convert(SqlAttribute attribute)
{
TelemetryInstance.TrackConvert(ConvertType.SqlCommand);
try
{
return(SqlBindingUtilities.BuildCommand(attribute, SqlBindingUtilities.BuildConnection(
attribute.ConnectionStringSetting, this._configuration)));
}
catch (Exception ex)
{
var props = new Dictionary <string, string>()
{
{ TelemetryPropertyName.Type.ToString(), ConvertType.SqlCommand.ToString() }
};
TelemetryInstance.TrackException(TelemetryErrorName.Convert, ex, props);
throw;
}
}
/// <summary>
/// Opens a SqlConnection, reads in the data from the user's database, and returns it as a list of POCOs.
/// </summary>
/// <param name="attribute">
/// Contains the information necessary to establish a SqlConnection, and the query to be executed on the database
/// </param>
/// <param name="cancellationToken">The cancellationToken is not used in this method</param>
/// <returns>An IEnumerable containing the rows read from the user's database in the form of the user-defined POCO</returns>
public async Task <IEnumerable <T> > ConvertAsync(SqlAttribute attribute, CancellationToken cancellationToken)
{
TelemetryInstance.TrackConvert(ConvertType.IEnumerable);
try
{
string json = await this.BuildItemFromAttributeAsync(attribute);
return(JsonConvert.DeserializeObject <IEnumerable <T> >(json));
}
catch (Exception ex)
{
var props = new Dictionary <string, string>()
{
{ TelemetryPropertyName.Type.ToString(), ConvertType.IEnumerable.ToString() }
};
TelemetryInstance.TrackException(TelemetryErrorName.Convert, ex, props);
throw;
}
}
/// <summary>
/// Processes all items added to the collector via <see cref="AddAsync"/>. Each item is interpreted as a row to be added
/// to the SQL table specified in the SQL Binding. All rows are added in one transaction. Nothing is done
/// if no items were added via AddAsync.
/// </summary>
/// <param name="cancellationToken">The cancellationToken is not used in this method</param>
/// <returns> A CompletedTask if executed successfully. If no rows were added, this is returned
/// automatically. </returns>
public async Task FlushAsync(CancellationToken cancellationToken = default)
{
await this._rowLock.WaitAsync(cancellationToken);
try
{
if (this._rows.Count != 0)
{
TelemetryInstance.TrackEvent(TelemetryEventName.FlushAsync);
await this.UpsertRowsAsync(this._rows, this._attribute, this._configuration);
this._rows.Clear();
}
}
catch (Exception ex)
{
TelemetryInstance.TrackException(TelemetryErrorName.FlushAsync, ex);
throw;
}
finally
{
this._rowLock.Release();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlGenericsConverter<typeparamref name="T"/>"/> class.
/// </summary>
/// <param name="configuration"></param>
/// <param name="logger">ILogger used to log information and warnings</param>
/// <exception cref="ArgumentNullException">
/// Thrown if the configuration is null
/// </exception>
public SqlGenericsConverter(IConfiguration configuration, ILogger logger)
{
this._configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
this._logger = logger;
TelemetryInstance.TrackCreate(CreateType.SqlGenericsConverter);
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlConverter/>"/> class.
/// </summary>
/// <param name="configuration"></param>
/// <exception cref="ArgumentNullException">
/// Thrown if the configuration is null
/// </exception>
public SqlConverter(IConfiguration configuration)
{
this._configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
TelemetryInstance.TrackCreate(CreateType.SqlConverter);
}
/// <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;
}
}
