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>
/// Extracts the <see cref="SqlAttribute.ConnectionStringSetting"/> in attribute and uses it to establish a connection
/// to the SQL database. (Must be virtual for mocking the method in unit tests)
/// </summary>
/// <param name="attribute">
/// The binding attribute that contains the name of the connection string app setting and query.
/// </param>
/// <returns></returns>
public virtual async Task <string> BuildItemFromAttributeAsync(SqlAttribute attribute)
{
using SqlConnection connection = SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, this._configuration);
// Ideally, we would like to move away from using SqlDataAdapter both here and in the
// SqlAsyncCollector since it does not support asynchronous operations.
// There is a GitHub issue open to track this
using var adapter = new SqlDataAdapter();
using SqlCommand command = SqlBindingUtilities.BuildCommand(attribute, connection);
adapter.SelectCommand = command;
await connection.OpenAsync();
var dataTable = new DataTable();
adapter.Fill(dataTable);
this._logger.LogInformation($"{dataTable.Rows.Count} row(s) queried from database: {connection.Database} using Command: {command.CommandText}");
return(JsonConvert.SerializeObject(dataTable));
}
/// <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>
/// Builds a SqlCommand using the query/stored procedure and parameters specifed in attribute.
/// </summary>
/// <param name="attribute">The SqlAttribute with the parameter, command type, and command text</param>
/// <param name="connection">The connection to attach to the SqlCommand</param>
/// <exception cref="InvalidOperationException">
/// Thrown if the CommandType specified in attribute is neither StoredProcedure nor Text. We only support
/// commands that refer to the name of a StoredProcedure (the StoredProcedure CommandType) or are themselves
/// raw queries (the Text CommandType).
/// </exception>
/// <returns>The built SqlCommand</returns>
public static SqlCommand BuildCommand(SqlAttribute attribute, SqlConnection connection)
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = attribute.CommandText;
if (attribute.CommandType == CommandType.StoredProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
else if (attribute.CommandType != CommandType.Text)
{
throw new ArgumentException("The Type of the SQL attribute for an input binding must be either CommandType.Text for a plain text" +
"SQL query, or CommandType.StoredProcedure for a stored procedure.");
}
SqlBindingUtilities.ParseParameters(attribute.Parameters, command);
return(command);
}
/// <summary>
/// Attempts to grab the next row of the SQL query result.
/// </summary>
/// <returns>
/// True if there is another row left in the query to process, or false if this was the last row
/// </returns>
private async Task <bool> GetNextRowAsync()
{
if (this._reader == null)
{
using SqlCommand command = SqlBindingUtilities.BuildCommand(this._attribute, this._connection);
await command.Connection.OpenAsync();
this._reader = await command.ExecuteReaderAsync();
}
if (await this._reader.ReadAsync())
{
this.Current = JsonConvert.DeserializeObject <T>(this.SerializeRow());
return(true);
}
else
{
return(false);
}
}
/// <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))
{
string fullDatabaseAndTableName = 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()}-{fullDatabaseAndTableName}";
ObjectCache cachedTables = MemoryCache.Default;
TableInformation tableInfo = cachedTables[cacheKey] as TableInformation;
if (tableInfo == null)
{
tableInfo = await TableInformation.RetrieveTableInformationAsync(connection, fullDatabaseAndTableName);
CacheItemPolicy policy = new CacheItemPolicy
{
// Re-look up the primary key(s) after 10 minutes (they should not change very often!)
AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(10)
};
_logger.LogInformation($"DB and Table: {fullDatabaseAndTableName}. Primary keys: [{string.Join(",", tableInfo.PrimaryKeys.Select(pk => pk.Name))}]. SQL Column and Definitions: [{string.Join(",", tableInfo.ColumnDefinitions)}]");
cachedTables.Set(cacheKey, tableInfo, policy);
}
int batchSize = 1000;
await connection.OpenAsync();
foreach (IEnumerable <T> batch in rows.Batch(batchSize))
{
GenerateDataQueryForMerge(tableInfo, batch, out string newDataQuery, out string rowData);
var cmd = new SqlCommand($"{newDataQuery} {tableInfo.MergeQuery};", connection);
var par = cmd.Parameters.Add(RowDataParameter, SqlDbType.NVarChar, -1);
par.Value = rowData;
await cmd.ExecuteNonQueryAsync();
}
await connection.CloseAsync();
}
}
/// <summary>
/// Extracts the <see cref="SqlAttribute.ConnectionStringSetting"/> in attribute and uses it to establish a connection
/// to the SQL database. (Must be virtual for mocking the method in unit tests)
/// </summary>
/// <param name="attribute">
/// The binding attribute that contains the name of the connection string app setting and query.
/// </param>
/// <returns></returns>
public virtual async Task <string> BuildItemFromAttributeAsync(SqlAttribute attribute)
{
using (SqlConnection connection = SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, _configuration))
{
// Ideally, we would like to move away from using SqlDataAdapter both here and in the
// SqlAsyncCollector since it does not support asynchronous operations.
// There is a GitHub issue open to track this
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = SqlBindingUtilities.BuildCommand(attribute, connection))
{
adapter.SelectCommand = command;
await connection.OpenAsync();
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return(JsonConvert.SerializeObject(dataTable));
}
}
}
}
/// <summary>
/// Attempts to grab the next row of the SQL query result.
/// </summary>
/// <returns>
/// True if there is another row left in the query to process, or false if this was the last row
/// </returns>
private async Task <bool> GetNextRowAsync()
{
if (_reader == null)
{
using (SqlCommand command = SqlBindingUtilities.BuildCommand(_attribute, _connection))
{
await command.Connection.OpenAsync();
_reader = await command.ExecuteReaderAsync();
}
}
if (await _reader.ReadAsync())
{
_currentRow = JsonConvert.DeserializeObject <T>(SerializeRow());
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Creates a SqlTriggerBinding using the information provided in "context"
/// </summary>
/// <param name="context">
/// Contains the SqlTriggerAttribute used to build up a SqlTriggerBinding
/// </param>
/// <exception cref="ArgumentNullException">
/// Thrown if context is null
/// </exception>
/// <exception cref="InvalidOperationException">
/// If the SqlTriggerAttribute is bound to an invalid Type. Currently only IEnumerable<SqlChangeTrackingEntry<T>>
/// is supported, where T is a user-defined POCO representing a row of their table
/// </exception>
/// <returns>
/// Null if "context" does not contain a SqlTriggerAttribute. Otherwise returns a SqlTriggerBinding{T} associated
/// with the SqlTriggerAttribute in "context", where T is the user-defined POCO
/// </returns>
public Task <ITriggerBinding> TryCreateAsync(TriggerBindingProviderContext context)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
ParameterInfo parameter = context.Parameter;
SqlTriggerAttribute attribute = parameter.GetCustomAttribute <SqlTriggerAttribute>(inherit: false);
if (attribute == null)
{
return(Task.FromResult <ITriggerBinding>(null));
}
if (!IsValidType(parameter.ParameterType))
{
throw new InvalidOperationException($"Can't bind SqlTriggerAttribute to type {parameter.ParameterType}. Only IEnumerable<SqlChangeTrackingEntry<T>>" +
$" is supported, where T is a user-defined POCO that matches the schema of the tracked table");
}
Type type = parameter.ParameterType.GetGenericArguments()[0].GetGenericArguments()[0];
Type typeOfTriggerBinding = typeof(SqlTriggerBinding <>).MakeGenericType(type);
ConstructorInfo constructor = typeOfTriggerBinding.GetConstructor(new Type[] { typeof(string), typeof(string), typeof(ParameterInfo), typeof(ILogger) });
return(Task.FromResult <ITriggerBinding>((ITriggerBinding)constructor.Invoke(new object[] { attribute.TableName,
SqlBindingUtilities.GetConnectionString(attribute.ConnectionStringSetting, _configuration), parameter, _logger })));
}
/// <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;
}
}
/// <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)
{
return(SqlBindingUtilities.BuildCommand(attribute, SqlBindingUtilities.BuildConnection(
attribute.ConnectionStringSetting, _configuration)));
}
IAsyncEnumerable <T> IConverter <SqlAttribute, IAsyncEnumerable <T> > .Convert(SqlAttribute attribute)
{
return(new SqlAsyncEnumerable <T>(SqlBindingUtilities.BuildConnection(
attribute.ConnectionStringSetting, _configuration), attribute));
}
/// <summary>
/// Serializes the reader's current SQL row into JSON
/// </summary>
/// <returns>JSON string version of the SQL row</returns>
private string SerializeRow()
{
return(JsonConvert.SerializeObject(SqlBindingUtilities.BuildDictionaryFromSqlRow(this._reader, this._cols)));
}
