在nlog中以编程方式创建数据库以启用使用DatabaseTarget [英] Creating a database programatically in nlog to enable using DatabaseTarget

问题描述

我在C＃中创建一个DatabaseTarget对象，并使用它将数据记录到NLog数据库中。

I'm creating a DatabaseTarget object in C# and using it to log data into an NLog database.

如果数据库不存在，nlog目标失败。我想检查DB是否存在，如果它不创建它和一个日志表。

If the database does not exist, the nlog target fails. I want to check to see if the DB exists, and if it doesn't create it and a log table.

我可以看到targetDB.Install（installationContext）函数似乎能够做到这一点，但找不到任何例子。有很多使用配置文件的示例。我想把它放在代码中，而不是在使用包含此代码的DLL的所有应用程序中部署配置文件。

I can see the targetDB.Install(installationContext) function appears to be able to do the job but cannot find any examples. There are numerous examples using a config file. I want to put this in code and not have to deploy a config file in all applications that use the DLL that will contain this code.

如何检查和创建数据库？

How do I check for and create the database?

推荐答案

此代码使用Install（）方法创建一个日志记录DB和表，

This code uses the Install() method to create a logging DB and table if they do not already exist:

private static void GetDBLogger(string strConnectionString)
    {
        StringBuilder sb = new StringBuilder();
        InstallationContext installationContext = new InstallationContext();

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.ConnectionString = strConnectionString;
        string strDatabase = builder.InitialCatalog;

        NLog.Targets.DatabaseTarget targetDB = new NLog.Targets.DatabaseTarget();

        targetDB.Name = "db";
        targetDB.ConnectionString = strConnectionString;

        NLog.Targets.DatabaseParameterInfo paramDB;

        paramDB = new NLog.Targets.DatabaseParameterInfo();
        paramDB.Name = string.Format("@Message");
        paramDB.Layout = string.Format("${{message}}");
        targetDB.Parameters.Add(paramDB);
        targetDB.CommandText = string.Format("INSERT INTO Logs(Message) VALUES (@message);");

        // Keep original configuration
        LoggingConfiguration config = LogManager.Configuration;
        if (config == null)
            config = new LoggingConfiguration();

        config.AddTarget(targetDB.Name, targetDB);

        LoggingRule rule = new LoggingRule("*", LogLevel.Debug, targetDB);
        config.LoggingRules.Add(rule);

        LogManager.Configuration = config;

        SqlConnectionStringBuilder builder2 = new SqlConnectionStringBuilder();
        builder2.ConnectionString = strConnectionString;
        builder2.InitialCatalog = "master";

        // we have to connect to master in order to do the install because the DB may not exist
        targetDB.InstallConnectionString = builder2.ConnectionString;

        sb.AppendLine(string.Format("IF NOT EXISTS (SELECT name FROM master.sys.databases WHERE name = N'{0}')", strDatabase));
        sb.AppendLine(string.Format("CREATE DATABASE {0}", strDatabase));

        DatabaseCommandInfo createDBCommand = new DatabaseCommandInfo();
        createDBCommand.Text = sb.ToString();
        createDBCommand.CommandType = System.Data.CommandType.Text;
        targetDB.InstallDdlCommands.Add(createDBCommand);

        // create the database if it does not exist
        targetDB.Install(installationContext);

        targetDB.InstallDdlCommands.Clear();
        sb.Clear();
        sb.AppendLine("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Logs')");
        sb.AppendLine("RETURN");
        sb.AppendLine("");
        sb.AppendLine("CREATE TABLE [dbo].[Logs](");
        sb.AppendLine("[LogId] [int] IDENTITY(1,1) NOT NULL,");
        sb.AppendLine("[Message] [nvarchar](max) NULL,");
        sb.AppendLine(" CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ");
        sb.AppendLine("(");
        sb.AppendLine("[LogId] ASC");
        sb.AppendLine(")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
        sb.AppendLine(") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]");

        DatabaseCommandInfo createTableCommand = new DatabaseCommandInfo();
        createTableCommand.Text = sb.ToString();
        createTableCommand.CommandType = System.Data.CommandType.Text;
        targetDB.InstallDdlCommands.Add(createTableCommand);

        // we can now connect to the target DB
        targetDB.InstallConnectionString = strConnectionString;

        // create the table if it does not exist
        targetDB.Install(installationContext);
    }

这篇关于在nlog中以编程方式创建数据库以启用使用DatabaseTarget的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！