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