/// <summary>
/// 根据 <see cref="QueryAttribute"/> 获取数据库执行信息
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
private SqlCommandDescription GetDescriptionByQuery(SqlCommandGenerateContext context)
{
string querySelector = NpiConfig.QuerySelector;
IEnumerable <SqlAttribute> queryAttributes = context.QueryAttributes.Where(x => x.Selector == querySelector);
if (queryAttributes.Count() > 1)
{
throw new ApplicationException("匹配到多个 QueryAttribute");
}
SqlAttribute queryAttribute = queryAttributes.FirstOrDefault();
SqlCommandDescription description = new SqlCommandDescription()
{
SqlCommand = queryAttribute.Sql,
Mode = queryAttribute.Mode
};
foreach (var ps in this.sqlParameterFinder.Find(queryAttribute.Sql))
{
description.AddParameter(new SqlParameterInfo(ps));
}
return(description);
}
public void TestNullCollectorConstructorArguments()
{
var arg = new SqlAttribute(string.Empty);
Assert.Throws <ArgumentNullException>(() => new SqlAsyncCollector <string>(config.Object, null, NullLoggerFactory.Instance));
Assert.Throws <ArgumentNullException>(() => new SqlAsyncCollector <string>(null, arg, NullLoggerFactory.Instance));
}
private void SetValue(PropertyInfo prop, SqlDataReader reader, T instance)
{
SqlAttribute sqlAttribute = (SqlAttribute)Attribute.GetCustomAttribute(prop, typeof(SqlAttribute));
if (sqlAttribute == null)
{
if (reader.HasColumn(prop.Name))
{
var value = reader[prop.Name] == DBNull.Value ? default(T) : reader[prop.Name];
Type propertyType = prop.PropertyType;
var targetType = IsNullableType(propertyType) ? Nullable.GetUnderlyingType(propertyType) : propertyType;
var propertyVal = value == null ? null : Convert.ChangeType(value, targetType);
prop.SetValue(instance, propertyVal, null);
}
}
else
{
if (reader.HasColumn(sqlAttribute.ColumnName))
{
var value = reader[sqlAttribute.ColumnName] == DBNull.Value ? default(T) : reader[sqlAttribute.ColumnName];
prop.SetValue(instance, value, null);
}
}
}
/// <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);
}
public void TestInvalidArgumentsBuildConnection()
{
var attribute = new SqlAttribute("");
Assert.Throws <ArgumentException>(() => SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, config.Object));
attribute = new SqlAttribute("");
attribute.ConnectionStringSetting = "ConnectionStringSetting";
Assert.Throws <ArgumentNullException>(() => SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, null));
}
public async void TestMalformedDeserialization()
{
var arg = new SqlAttribute(string.Empty);
var converter = new Mock <SqlGenericsConverter <TestData> >(config.Object, logger.Object);
// SQL data is missing a field
string json = "[{ \"ID\":1,\"Name\":\"Broom\",\"Timestamp\":\"2019-11-22T06:32:15\"}]";
converter.Setup(_ => _.BuildItemFromAttributeAsync(arg)).ReturnsAsync(json);
var list = new List <TestData>();
var data = new TestData
{
ID = 1,
Name = "Broom",
Cost = 0,
Timestamp = new DateTime(2019, 11, 22, 6, 32, 15)
};
list.Add(data);
IEnumerable <TestData> enActual = await converter.Object.ConvertAsync(arg, new CancellationToken());
Assert.True(enActual.ToList().SequenceEqual(list));
// SQL data's columns are named differently than the POCO's fields
json = "[{ \"ID\":1,\"Product Name\":\"Broom\",\"Price\":32.5,\"Timessstamp\":\"2019-11-22T06:32:15\"}]";
converter.Setup(_ => _.BuildItemFromAttributeAsync(arg)).ReturnsAsync(json);
list = new List <TestData>();
data = new TestData
{
ID = 1,
Name = null,
Cost = 0,
};
list.Add(data);
enActual = await converter.Object.ConvertAsync(arg, new CancellationToken());
Assert.True(enActual.ToList().SequenceEqual(list));
// Confirm that the JSON fields are case-insensitive (technically malformed string, but still works)
json = "[{ \"id\":1,\"nAme\":\"Broom\",\"coSt\":32.5,\"TimEStamp\":\"2019-11-22T06:32:15\"}]";
converter.Setup(_ => _.BuildItemFromAttributeAsync(arg)).ReturnsAsync(json);
list = new List <TestData>();
data = new TestData
{
ID = 1,
Name = "Broom",
Cost = 32.5,
Timestamp = new DateTime(2019, 11, 22, 6, 32, 15)
};
list.Add(data);
enActual = await converter.Object.ConvertAsync(arg, new CancellationToken());
Assert.True(enActual.ToList().SequenceEqual(list));
}
public void TestInvalidCommandType()
{
// Specify an invalid type
var attribute = new SqlAttribute("");
attribute.CommandType = System.Data.CommandType.TableDirect;
Assert.Throws <ArgumentException>(() => SqlBindingUtilities.BuildCommand(attribute, null));
// Don't specify a type at all
attribute = new SqlAttribute("");
Assert.Throws <ArgumentException>(() => SqlBindingUtilities.BuildCommand(attribute, null));
}
public async Task TestAddAsync()
{
// Really a pretty silly test. Just confirms that the SQL connection is only opened when FlushAsync is called,
// because otherwise we would get an exception in AddAsync (since the SQL connection in the wrapper is null)
var arg = new SqlAttribute(string.Empty);
var collector = new SqlAsyncCollector <TestData>(config.Object, arg, NullLoggerFactory.Instance);
var data = new TestData
{
ID = 1,
Name = "Data",
Cost = 10,
Timestamp = new DateTime(2019, 11, 22, 6, 32, 15)
};
await collector.AddAsync(data);
}
/// <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>
/// 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>
/// 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>
/// 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;
}
}
public void TestValidCommandType()
{
var query = "select * from Products";
var attribute = new SqlAttribute(query);
attribute.CommandType = System.Data.CommandType.Text;
var command = SqlBindingUtilities.BuildCommand(attribute, null);
Assert.Equal(System.Data.CommandType.Text, command.CommandType);
Assert.Equal(query, command.CommandText);
var procedure = "StoredProceudre";
attribute = new SqlAttribute(procedure);
attribute.CommandType = System.Data.CommandType.StoredProcedure;
command = SqlBindingUtilities.BuildCommand(attribute, null);
Assert.Equal(System.Data.CommandType.StoredProcedure, command.CommandType);
Assert.Equal(procedure, command.CommandText);
}
/// <summary>
/// 根据反射获取repalce sql语句
/// </summary>
/// <param name="obj"></param>
/// <param name="tableName"></param>
/// <param name="containKey">生成的sql是否包含主键</param>
/// <returns></returns>
public static String GetReplaceIntoSql(Object obj, String tableName, bool containKey = false)
{
StringBuilder filedSb = new StringBuilder();
StringBuilder valueSb = new StringBuilder();
Type type = obj.GetType();
//获取所有公有属性
PropertyInfo[] info = type.GetProperties();
bool myFiledName = false;
foreach (var p in info)
{
myFiledName = false;
//取得属性的特性标签，false表示不获取因为继承而得到的标签
Object[] attr = p.GetCustomAttributes(false);
if (attr.Length > 0)
{
//从注解数组中取第一个注解(一个属性可以包含多个注解)
SqlAttribute myattr = attr[0] as SqlAttribute;
if (myattr.primaryKey == true && containKey == true)
{
continue;
}
//如果使用了自定义字段名
if (!String.IsNullOrEmpty(myattr.fieldName))
{
filedSb.Append(myattr.fieldName + ",");
myFiledName = true;
}
}
//如果没用自定义字段名
if (!myFiledName)
{
filedSb.Append(FiledToLower(p.Name) + ",");
}
valueSb.Append("'" + p.GetValue(obj, null) + "',");
}
String sql = $"replace into {tableName} ({filedSb.ToString().Substring(0, filedSb.ToString().Length-1)}) values ({valueSb.ToString().Substring(0, valueSb.ToString().Length-1)})";
return(sql);
}
/// <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));
}
}
}
}
public async void TestWellformedDeserialization()
{
var arg = new SqlAttribute(string.Empty);
var converter = new Mock <SqlGenericsConverter <TestData> >(config.Object, logger.Object);
string json = "[{ \"ID\":1,\"Name\":\"Broom\",\"Cost\":32.5,\"Timestamp\":\"2019-11-22T06:32:15\"},{ \"ID\":2,\"Name\":\"Brush\",\"Cost\":12.3," +
"\"Timestamp\":\"2017-01-27T03:13:11\"},{ \"ID\":3,\"Name\":\"Comb\",\"Cost\":100.12,\"Timestamp\":\"1997-05-03T10:11:56\"}]";
converter.Setup(_ => _.BuildItemFromAttributeAsync(arg)).ReturnsAsync(json);
var list = new List <TestData>();
var data1 = new TestData
{
ID = 1,
Name = "Broom",
Cost = 32.5,
Timestamp = new DateTime(2019, 11, 22, 6, 32, 15)
};
var data2 = new TestData
{
ID = 2,
Name = "Brush",
Cost = 12.3,
Timestamp = new DateTime(2017, 1, 27, 3, 13, 11)
};
var data3 = new TestData
{
ID = 3,
Name = "Comb",
Cost = 100.12,
Timestamp = new DateTime(1997, 5, 3, 10, 11, 56)
};
list.Add(data1);
list.Add(data2);
list.Add(data3);
IEnumerable <TestData> enActual = await converter.Object.ConvertAsync(arg, new CancellationToken());
Assert.True(enActual.ToList().SequenceEqual(list));
}
IAsyncEnumerable <T> IConverter <SqlAttribute, IAsyncEnumerable <T> > .Convert(SqlAttribute attribute)
{
return(new SqlAsyncEnumerable <T>(SqlBindingUtilities.BuildConnection(
attribute.ConnectionStringSetting, _configuration), attribute));
}
/// <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, ILoggerFactory loggerFactory)
{
_configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
_attribute = attribute ?? throw new ArgumentNullException(nameof(attribute));
_logger = loggerFactory?.CreateLogger(LogCategories.Bindings) ?? throw new ArgumentNullException(nameof(loggerFactory));
}
/// <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)
{
string json = await BuildItemFromAttributeAsync(attribute);
return(JsonConvert.DeserializeObject <IEnumerable <T> >(json));
}
IAsyncCollector <T> IConverter <SqlAttribute, IAsyncCollector <T> > .Convert(SqlAttribute attribute)
{
return(new SqlAsyncCollector <T>(_configuration, attribute, _loggerFactory));
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlAsyncEnumerator<typeparamref name="T"/>"/> class.
/// </summary>
/// <param name="connection">The SqlConnection to be used by the enumerator</param>
/// <param name="attribute">The attribute containing the query, parameters, and query type</param>
/// <exception cref="ArgumentNullException">
/// Thrown if either connection or attribute is null
/// </exception>
public SqlAsyncEnumerator(SqlConnection connection, SqlAttribute attribute)
{
this._connection = connection ?? throw new ArgumentNullException(nameof(connection));
this._attribute = attribute ?? throw new ArgumentNullException(nameof(attribute));
this._cols = new List <string>();
}
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)
{
return(await BuildItemFromAttributeAsync(attribute));
}
/// <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;
}
}
IAsyncCollector <T> IConverter <SqlAttribute, IAsyncCollector <T> > .Convert(SqlAttribute attribute)
{
return(new SqlAsyncCollector <T>(this._configuration, attribute, this._logger));
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlAsyncEnumerable<typeparamref name="T"/>"/> class.
/// </summary>
/// <param name="connection">The SqlConnection to be used by the enumerator</param>
/// <param name="attribute">The attribute containing the query, parameters, and query type</param>
/// <exception cref="ArgumentNullException">
/// Thrown if either connection or attribute is null
/// </exception>
public SqlAsyncEnumerable(SqlConnection connection, SqlAttribute attribute)
{
this._connection = connection ?? throw new ArgumentNullException(nameof(connection));
this._attribute = attribute ?? throw new ArgumentNullException(nameof(attribute));
}