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