Set the static Audit.Core.Configuration.DataProvider property to set the Sql Server data provider, or call the UseSqlServer method on the fluent configuration. This should be done before any AuditScope creation, i.e. during application startup.
For example:
Audit.Core.Configuration.DataProvider = new SqlDataProvider()
{
ConnectionString = "data source=localhost;initial catalog=Audit;integrated security=true;",
Schema = "dbo",
TableName = "Event",
IdColumnName = "EventId",
JsonColumnName = "JsonData",
LastUpdatedDateColumnName = "LastUpdatedDate",
CustomColumns = new List<CustomColumn>()
{
new CustomColumn("EventType", ev => ev.EventType)
}
};
ConnectionString: The SQL Server connection string.
DbConnection: The DbConnection to use, alternative to ConnectionString.
DbContextOptions: To set custom options to use with the default Audit EF DbContext. This setting is ignored if a DbContext instance is provided.
DbContext: Specifies a custom EntityFramework DbContext for querying and inserting audit events. When this option is provided, the ConnectionString, DbConnection, and DbContextOptions settings are ignored.
Schema: The SQL schema for the table. (optional)
TableName: The audit events table name.
IdColumnName: The column name of the event identifier (the primary key).
JsonColumnName: The column name of the event table where the audit event JSON will be stored. (optional)
LastUpdatedDateColumnName: The datetime column name to update when replacing events. (optional)
CustomColumn: Additional columns to store information from the audit event. (optional)
Query events
This provider implements GetEvent and GetEventAsync methods to obtain an audit event by id:
var event = sqlDataProvider.GetEvent(1000);
Table constraints
The table should exists.
The table should have a single ID column (Unique or Primary key).
The type of the ID column should be convertible to NVARCHAR.
For example:
CREATE TABLE [Event]
(
[EventId] BIGINT IDENTITY(1,1) NOT NULL,
[InsertedDate] DATETIME NOT NULL DEFAULT(GETUTCDATE()),
[LastUpdatedDate] DATETIME NULL,
[JsonData] NVARCHAR(MAX) NOT NULL,
[EventType] NVARCHAR(100) NOT NULL,
CONSTRAINT PK_Event PRIMARY KEY (EventId)
)
GO
If you use Azure SQL Server or Sql Server 2016, you can create indexes on the JSON fields, for example creating a schemabinded view:
CREATE VIEW dbo.[v_Event] WITH SCHEMABINDING
AS
SELECT EventId,
InsertedDate,
CAST(JSON_VALUE(JsonData, '$.EventType') AS NVARCHAR(255)) AS [EventType],
CAST(JSON_VALUE(JsonData, '$.ReferenceId') AS NVARCHAR(255)) AS [ReferenceId],
JSON_VALUE(JsonData, '$.Target.Type') As [TargetType],
COALESCE(JSON_VALUE(JsonData, '$.Target.Old'), JSON_QUERY(JsonData, '$.Target.Old')) AS [TargetOld],
COALESCE(JSON_VALUE(JsonData, '$.Target.New'), JSON_QUERY(JsonData, '$.Target.New')) AS [TargetNew],
JSON_QUERY(JsonData, '$.Comments') AS [Comments],
[JsonData]
FROM dbo.[Event]
GO
CREATE UNIQUE CLUSTERED INDEX PK_V_EVENT ON [v_Event] (EventId)
GO
CREATE INDEX IX_V_EVENT_EventType_ReferenceId ON [v_Event] (EventType, ReferenceId)
GO
