public void DatabaseLogger_LogsError_DatabaseLogs()
{
var message = "Some entry";
var config = new DatabaseLoggingConfiguration();
config.Host = "sql.emitknowledge.com";
config.Database = "app.db";
config.Username = "******";
config.Password = "******";
config.DataProvider = DataProvider.SqlClient;
config.TableName = "Log2";
Aspects.Logging.ILogger logger = new NLogger(config);
logger.Error(new Exception(message));
string connectionString = $"Data Source={config.Host};Initial Catalog={config.Database}; User Id={config.Username}; Password={config.Password}";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlDataReader reader = new SqlCommand(@"SELECT * FROM LogEntry;", connection).ExecuteReader();
while (reader.Read())
{
Assert.Contains(message, reader["Payload"].ToString());
}
reader.Close();
var command = new SqlCommand("DELETE FROM LogEntry", connection);
command.ExecuteNonQuery();
}
}
public DatabaseConfiguration()
{
LogWith = new DatabaseLoggingConfiguration(this, (l) =>
{
if (l == null)
{
throw new ArgumentNullException($"{nameof(l)} cannot be null.");
}
_logger = l;
});
}
/// <summary>
/// Initiate logging in database
/// </summary>
/// <param name="configuration"></param>
/// <param name="levels"></param>
public NLogger(DatabaseLoggingConfiguration configuration, params LogLevel[] levels)
{
LoggingConfiguration = configuration;
using (var connection = new SqlConnection(configuration.ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction($"Create{configuration.TableName}"))
{
var sql =
$@"
IF NOT EXISTS
(
SELECT *
FROM sys.tables t
WHERE t.name = '{configuration.TableName}'
)
BEGIN
CREATE TABLE [{configuration.TableName}]
(
[Id] INT IDENTITY(1,1) NOT NULL,
[CreatedOn] DATETIME2(7) NOT NULL,
[Level] NVARCHAR(MAX) NULL,
[ErrorGroup] NVARCHAR(MAX) NULL,
[ErrorCode] NVARCHAR(MAX) NULL,
[Origin] NVARCHAR(MAX) NULL,
[ProcessName] NVARCHAR(MAX) NULL,
[Action] NVARCHAR(MAX) NULL,
[ActionFilePath] NVARCHAR(MAX) NULL,
[ActionSourceLineNumber] NVARCHAR(MAX) NULL,
[Message] NVARCHAR(MAX) NULL,
[ExceptionMessage] NVARCHAR(MAX) NULL,
[UserIdentifier] NVARCHAR(MAX) NULL,
[Payload] NVARCHAR(MAX) NULL
CONSTRAINT [PK_{configuration.TableName}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [{configuration.TableName}] ADD CONSTRAINT [DF_{configuration.TableName}_CreateOn] DEFAULT (getutcdate()) FOR [CreatedOn];
END
";
var command = new SqlCommand(sql, connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
}
}
var dbTarget = new DatabaseTarget("database")
{
DBHost = configuration.Host,
DBDatabase = configuration.Database,
DBPassword = configuration.Password,
DBUserName = configuration.Username,
DBProvider = configuration.DataProvider.GetDescription(),
CommandText = $@"
insert into {configuration.TableName} (
Level,
ErrorGroup,
ErrorCode,
Origin,
Action,
ProcessName,
ActionFilePath,
ActionSourceLineNumber,
Message,
ExceptionMessage,
UserIdentifier,
Payload
) values (
@Level,
@ErrorGroup,
@ErrorCode,
@Origin,
@Action,
@ProcessName,
@ActionFilePath,
@ActionSourceLineNumber,
@Message,
@ExceptionMessage,
@UserIdentifier,
@Payload
);"
};
dbTarget.Parameters.Add(new DatabaseParameterInfo("@level", Layout.FromString(@"${event-properties:item=Level}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@errorGroup", Layout.FromString(@"${event-properties:item=ErrorGroup}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@errorCode", Layout.FromString(@"${event-properties:item=ErrorCode}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@origin", Layout.FromString(@"${event-properties:item=Origin}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@action", Layout.FromString(@"${event-properties:item=Action}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@processName", Layout.FromString(@"${event-properties:item=ProcessName}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@actionFilePath", Layout.FromString(@"${event-properties:item=ActionFilePath}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@actionSourceLineNumber", Layout.FromString(@"${event-properties:item=ActionSourceLineNumber}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@message", Layout.FromString(@"${event-properties:item=Message}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@exceptionMessage", Layout.FromString(@"${event-properties:item=ExceptionMessage}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@userIdentifier", Layout.FromString(@"${event-properties:item=UserIdentifier}")));
dbTarget.Parameters.Add(new DatabaseParameterInfo("@payload", Layout.FromString(@"${event-properties:item=Payload}")));
InitTarget(dbTarget, levels?.ToList());
}
