/// <summary>
/// CTOR
/// </summary>
/// <param name="databaseConfiguration"></param>
public AuditProvider(DatabaseAuditingConfiguration databaseConfiguration)
{
var databaseDataProvider = new DatabaseDataProvider(databaseConfiguration);
CreateAuditTableIfNotExist(databaseConfiguration);
Configuration.DataProvider = databaseDataProvider;
}
/// <summary>
/// Ensures that table for the audit logs exists in the database
/// </summary>
/// <param name="databaseConfiguration"></param>
private void CreateAuditTableIfNotExist(DatabaseAuditingConfiguration databaseConfiguration)
{
using (var connection = new SqlConnection(databaseConfiguration.ConnectionString))
{
connection.Open();
var sql =
$@"
IF NOT EXISTS
(
SELECT *
FROM sys.tables t
WHERE t.name = '{databaseConfiguration.TableName}'
)
CREATE TABLE [{databaseConfiguration.TableName}]
(
[Id] INT IDENTITY(1,1) NOT NULL,
[Process] NVARCHAR(MAX) NOT NULL,
[ProcessInstanceId] NVARCHAR(MAX) NOT NULL,
[EventType] NVARCHAR(MAX) NULL,
[Originator] NVARCHAR(MAX) NOT NULL,
[StartDate] datetime2(7) NOT NULL,
[EndDate] datetime2(7) NULL,
[Data] NVARCHAR(MAX) NULL,
[Payload] NVARCHAR(MAX) NULL,
[EpicId] NVARCHAR(38) NOT NULL
CONSTRAINT [PK_{databaseConfiguration.TableName}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
";
var command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
}
public void Audit_With_Existing_DatabaseConfiguration_Should_Insert_Valid_Audit_Log()
{
var databaseConfiguration = new DatabaseAuditingConfiguration
{
TableName = "AuditLogTest2",
ConnectionString = "Server=[SERVER];Database=[DB];User Id=[USR];Password=[PWD];"
};
using (var connection = new SqlConnection(databaseConfiguration.ConnectionString))
{
connection.Open();
// Make sure the table exists
var createTableSql =
$@"
IF NOT EXISTS
(
SELECT *
FROM sys.tables t
WHERE t.name = '{databaseConfiguration.TableName}'
)
BEGIN
CREATE TABLE [{databaseConfiguration.TableName}]
(
[Id] INT IDENTITY(1,1) NOT NULL,
[Process] NVARCHAR(MAX),
[ProcessInstanceId] NVARCHAR(MAX),
[Payload] NVARCHAR(MAX),
[EventType] NVARCHAR(MAX),
[Originator] NVARCHAR(MAX),
[StartDate] datetime2(7),
[EndDate] datetime2(7),
[Data] NVARCHAR(MAX)
)
END
DELETE FROM [{databaseConfiguration.TableName}]
";
var command = new SqlCommand(createTableSql, connection);
command.ExecuteNonQuery();
var auditProvider = new AuditProvider(databaseConfiguration);
var auditEntry = auditProvider.Entry();
auditEntry.Originator = "Originator";
auditEntry.Process = "RegisterUser";
auditEntry.EventType = "Register user";
auditEntry.Payload = "Payload";
auditEntry.Data.Process["ProcessInfo"] = "Some process info";
auditEntry.Data.Source["SourceInfo"] = "Some source info";
auditProvider.Audit(auditEntry, () =>
{
Thread.Sleep(100);
});
// Retrieve the audit from the database
var getAuditSql =
$@"
SELECT * FROM [{databaseConfiguration.TableName}]
";
command = new SqlCommand(getAuditSql, connection);
var reader = command.ExecuteReader();
// Make sure there is at least one audit entry
Assert.True(reader.HasRows);
while (reader.Read())
{
Assert.Equal("Payload", reader["Payload"].ToString());
Assert.Equal("Originator", reader["Originator"].ToString());
Assert.Equal("RegisterUser", reader["Process"].ToString());
Assert.Equal("Register user", reader["EventType"].ToString());
// Deserialize the Data object and check its values
var content = reader["Data"].ToString();
var entryData = JsonConvert.DeserializeObject <AuditEntryData>(content);
Assert.Equal("Some process info", entryData.Process["ProcessInfo"]);
Assert.Equal("Some source info", entryData.Source["SourceInfo"]);
}
reader.Close();
// Cleanup
var cleanUpSql = $@"DELETE FROM {databaseConfiguration.TableName}";
command = new SqlCommand(cleanUpSql, connection);
command.ExecuteNonQuery();
connection.Close();
}
}
/// <summary>
/// CTOR
/// </summary>
/// <param name="databaseConfiguration"></param>
public DatabaseDataProvider(DatabaseAuditingConfiguration databaseConfiguration)
{
_databaseConfiguration = databaseConfiguration;
}
