Anixe.IO.Appmetrics.Extensions.SqlDB 1.1.0

Anixe.IO.Appmetrics.Extensions.SqlDB

Anixe.IO.Appmetrics.Extensions.SqlDB allows you to quickly integrate any System.Data.Common based driver database with graylog

How to integrate

Please follow the instructions in appmetrics , then add in csproj as a package reference

<PackageReference Include="Anixe.IO.Appmetrics.Extensions.SqlDB" Version="1.0.6" />

register appmetrics and extension in DI container in Startup.cs. The goal is to provide DbProviderFactory pattern as UseSqlDBWithAnixeAppmetrics argument. The factory will be store as static variable inside.

    public void ConfigureServices(IServiceCollection services)
    {
      services.UseAnixeAppmetrics(this.config, opts => // use this to provide metrics for inbound http requests (middlewaere) and unhandled exceptions (filter)
      {
        // override any option here
        opts.Middleware.HostName = "my machine"; /*providing machine name is on your own*/
        opts.Middleware.ServerIp = "127.0.0.1"; /*providing machine name is on your own*/
      })

      services.UseSqlDBWithAnixeAppmetrics(NpgsqlFactory.Instance); // register dependencies with custom factory from Npgsql driver.
    }

then use it by executing commands injected from DI. It is mandatory to use provided objects from DI container. If you don't use it then no db app metrics will be recorded. You are able to use Func<DbConnection, DbCommand> or Func<DbTransaction, DbCommand> dependencies. Each call on one of these functions will create new DbCommand instance with provided DbConnection or DbTransaction so you can control the connection scope via Dispose pattern.

Here is my example of DatabaseService which executes various db operations using injected dependencies from Appmetrics.

  public class DatabaseService : IDatabaseService
  {
    private readonly Func<DbConnection, DbCommand> dbCommandFunc;
    private readonly Func<DbTransaction, DbCommand> dbCommandWithinTransactionFunc;

    public DatabaseService(Func<DbConnection, DbCommand> dbCommandFunc, Func<DbTransaction, DbCommand> dbCommandWithinTransactionFunc)
    {
      this.dbCommandFunc = dbCommandFunc;
      this.dbCommandWithinTransactionFunc = dbCommandWithinTransactionFunc;
    }


    // create new connection ,run command and close the connection
    public async Task<T> ExecuteScalar<T>(string sqlQuery, DbParameter[] cmdParms, CancellationToken token = default)
    {
      using (var connection = CreateConnection())
      using (var cmd = dbCommandFunc(connection))
      {
        PrepareCommand(cmd, CommandType.Text, sqlQuery, cmdParms); // write SELECT here
        return (T)await cmd.ExecuteScalarAsync(token);
      }
    }


    // reused connection from argument with ExecuteReaderAsync
    public async Task<IDataReader> ExecuteQueryAsync(string sqlQuery, DbParameter[] cmdParms, DbConnection conn)
    {      
      EnsureOpen(conn);
      using (var cmd = dbCommandFunc(conn))
      {
        PrepareCommand(cmd, CommandType.Text, sqlQuery, cmdParms); // write SELECT here
        return await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
      }
    }


    // command with transaction
    public async Task<IDataReader> ExecuteQueryAsync(string sqlQuery, DbParameter[] cmdParms, DbTransaction tr)
    {
      EnsureOpen(tr.Connection);
      using (var cmd = dbCommandWithinTransactionFunc(tr))
      {
        PrepareCommand(cmd, CommandType.Text, sqlQuery, cmdParms); // write SELECT here
        return await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess);
      }
    }

  }

Example message

{
  "version": "1.1",
  "host": "wm-anx-macbook.local",
  "level": 6,
  "timestamp": 1563458762.351234,
  "_application_name": "wheels_admin_api",
  "_transaction_name": "api/v1/export_ion/sales_discount/test",
  "_src": "wheels_admin_api",
  "_rid": "5486539052653189219",
  "_mid": "5189252136311428311",
  "_st": "OK",
  "_p": "CAR",
  "_env": "development",
  "_platform": "wheels",
  "_host_ip": "192.168.195.81",
  "_rq_mth": "GET",
  "_rq_url": "http://localhost:5001/api/v1/export_ion/sales_discount/test?ionUrl=%2fapi%2fv1%2fsales_discounts%2fion%3ftenant%3dtest%26key%3dapi_secret_key",
  "_rs_code": 200,
  "_client_ip": "127.0.0.1",
  "_tt": 34.039,
  "_ttp": 8.383,
  "_ttc": 25.656,
  "_db_sel_customers_tt": 25.656,
  "_db_sel_customers_count": 1,
  "short_message": "-"
}

custom fields from db appmetrics are those with _db prefix, so: _db_sel_customers_tt and _db_sel_customers_count. The format beneath is _prefix_operation_tablename_property, where:

• _prefix - always _db for database communication related data
• _operation - can be sel for SELECT, ins for INSERT, upd for UPDATE, del for DELETE, fetch for FETCH cursor.
• _tablename - SQL table name of the operation
• _tt - time taken in miliseconds, this is sum of all the same operations on the same table
• _count - number of the same operations on the same table

Example for multiple operations (4 selects from customers and one update of customers table)

{
  ...
  "_db_sel_customers_tt" : 234.23,
  "_db_sel_customers_count": 4,
  "_db_upd_customers_tt" : 24.3,
  "_db_upd_customers_count": 1,
  ...
}