public async Task AddTrailsAsync <T>(AuditOperation auditOperation, string user, AuditableObjects <T> auditableObjects, IEnumerable <string> namesOfPropertiesToIgnore) where T : class
{
if (!auditConfigurationSqlServer.Enable.Value)
{
return;
}
var trails = auditableObjects.Select(o => new AuditTrail(
auditableObjects.TransactionId,
auditableObjects.Timestamp,
auditConfigurationSqlServer.ApplicationId,
auditOperation,
user,
httpContextAccessor.HttpContext?.Connection?.RemoteIpAddress?.ToString(),
o.Key,
o.Value,
namesOfPropertiesToIgnore));
var transactionBuilder = new StringBuilder();
foreach (var trail in trails)
{
transactionBuilder.Append($@"
insert into
[{auditConfigurationSqlServer.SqlServer.DatabaseName}].
[{auditConfigurationSqlServer.SqlServer.SchemaName}].
[{auditConfigurationSqlServer.SqlServer.TableName}]
(
[{nameof(AuditTrail.Id)}],
[{nameof(AuditTrail.TransactionId)}],
[{nameof(AuditTrail.Timestamp)}],
[{nameof(AuditTrail.ApplicationId)}],
[{nameof(AuditTrail.Operation)}],
[{nameof(AuditTrail.User)}],
[{nameof(AuditTrail.Origin)}],
[{nameof(AuditTrail.ObjectAssembly)}],
[{nameof(AuditTrail.ObjectId)}],
[{nameof(AuditTrail.ObjectSerial)}]
)
values
(
'{trail.Id}',
'{trail.TransactionId}',
'{trail.Timestamp.ToString("yyyy-MM-dd HH:mm:ss.fff") }',
'{trail.ApplicationId}',
'{trail.Operation}',
'{trail.User}',
{(!string.IsNullOrWhiteSpace(trail.Origin) ? $"'{trail.Origin}'" : "NULL")},
'{trail.ObjectAssembly}',
'{trail.ObjectId}',
{(!string.IsNullOrWhiteSpace(trail.ObjectSerial) ? $"'{trail.ObjectSerial}'" : "NULL")}
);");
}
await auditContext.ExecuteWithTransactionAsync(transactionBuilder.ToString());
}
public void Initialize()
{
if (!auditConfigurationSqlServer.Enable.Value)
{
logger.LogInformation("Audit with SqlServer and Dapper disabled.");
return;
}
try
{
logger.LogInformation($"Performing query in 'INFORMATION_SCHEMA' in order to check if audit table '{auditConfigurationSqlServer.SqlServer.TableName}' already exists...");
var dmlTableExists = $@"
SELECT
[TABLE_NAME]
FROM
[{auditConfigurationSqlServer.SqlServer.DatabaseName}].
[INFORMATION_SCHEMA].
[TABLES]
WHERE
TABLE_NAME = @tableName";
var tableExists = auditContext
.QueryAsync <string>(dmlTableExists, new { tableName = auditConfigurationSqlServer.SqlServer.TableName })
.GetAwaiter()
.GetResult()
.Any();
logger.LogInformation($"Audit table '{auditConfigurationSqlServer.SqlServer.TableName}' exists: {tableExists}");
if (tableExists)
{
return;
}
else if (!auditConfigurationSqlServer.EnableInitializer)
{
throw new NotImplementedException($"Audit table '{auditConfigurationSqlServer.SqlServer.TableName}' does not exist in the supplied database '{auditConfigurationSqlServer.SqlServer.DatabaseName}'.");
}
logger.LogInformation($"Attempting to create audit table '{auditConfigurationSqlServer.SqlServer.TableName}'...");
var ddlCreateTable = $@"
CREATE TABLE
[{auditConfigurationSqlServer.SqlServer.DatabaseName}].
[{auditConfigurationSqlServer.SqlServer.SchemaName}].
[{auditConfigurationSqlServer.SqlServer.TableName}]
(
[{nameof(AuditTrail.Id)}] uniqueidentifier NOT NULL,
[{nameof(AuditTrail.TransactionId)}] uniqueidentifier NOT NULL,
[{nameof(AuditTrail.Timestamp)}] datetime NOT NULL,
[{nameof(AuditTrail.ApplicationId)}] varchar(100) NOT NULL,
[{nameof(AuditTrail.Operation)}] varchar(50) NOT NULL,
[{nameof(AuditTrail.User)}] varchar(50) NOT NULL,
[{nameof(AuditTrail.Origin)}] varchar(500) NULL,
[{nameof(AuditTrail.ObjectAssembly)}] varchar(500) NOT NULL,
[{nameof(AuditTrail.ObjectId)}] varchar(200) NOT NULL,
[{nameof(AuditTrail.ObjectSerial)}] text NULL,
CONSTRAINT [PK_{auditConfigurationSqlServer.SqlServer.TableName}] PRIMARY KEY ([{nameof(AuditTrail.Id)}])
);
CREATE INDEX
[IX_{auditConfigurationSqlServer.SqlServer.TableName}_ViewHistory]
ON
[{auditConfigurationSqlServer.SqlServer.SchemaName}].
[{auditConfigurationSqlServer.SqlServer.TableName}]
([{nameof(AuditTrail.ApplicationId)}], [{nameof(AuditTrail.ObjectAssembly)}], [{nameof(AuditTrail.ObjectId)}]);
CREATE INDEX
[IX_{auditConfigurationSqlServer.SqlServer.TableName}_{nameof(AuditTrail.User)}]
ON
[{auditConfigurationSqlServer.SqlServer.SchemaName}].
[{auditConfigurationSqlServer.SqlServer.TableName}]
([{nameof(AuditTrail.User)}]);";
auditContext.ExecuteWithTransactionAsync(ddlCreateTable).GetAwaiter();
logger.LogInformation($"Audit table '{auditConfigurationSqlServer.SqlServer.TableName}' created.");
}
catch (Exception e)
{
logger.LogError(e, "An internal error occurred while trying to initialize the audit schema.");
}
}