C＃将SQL Server消息输出到文本文件

I am new to C#, so please bear with me as I have inherited a script that I'm attempting to tweak.

我是C#的新手,所以请耐心等待,因为我继承了一个我试图调整的脚本。

I want to get the output of SQL PRINT/RAISERROR statements to show up in a log file that has been declared in another part of the script.

我想得到SQL PRINT / RAISERROR语句的输出显示在已在脚本的另一部分中声明的日志文件中。

This is my method I'm calling:

这是我打电话的方法:

public void ProcessData(string StoredProcedure, int StartDate, int EndDate, string Directory, string LogFileNameAndPath)
    {
        SqlConnection sqlConnection = null;
        SqlCommand sqlCommand = null;
        SqlParameter sqlParameter = null;
       // String outputText = null;

        try
        {
            sqlConnection = new SqlConnection(_ConnectionString);
            sqlConnection.Open();

            sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = StoredProcedure;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandTimeout = 0;

            sqlParameter = new SqlParameter("@StartDt", SqlDbType.Int);
            sqlParameter.Value = StartDate;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlParameter = new SqlParameter("@EndDt", SqlDbType.Int);
            sqlParameter.Value = EndDate;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlParameter = new SqlParameter("@stringDirs", SqlDbType.VarChar);
            sqlParameter.Value = Directory;
            sqlCommand.Parameters.Add(sqlParameter);

            sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
            sqlCommand.ExecuteNonQuery();

        }
        catch (SqlException sqlEx)
        {
            throw sqlEx;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString());
        }
        finally
        {
            if (sqlConnection != null)
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }
    }

This is the info handler method:

这是信息处理程序方法:

    public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)//, String LogFileNameAndPath)
    {
        foreach (SqlError err in args.Errors)
        {

            //File.AppendAllText(LogFileNameAndPath, err.Message);
            Console.WriteLine("{0}", err.Message);
            //return err.Message;
          //"The {0} has received a severity {1}, state {2} error number {3}\n" +
         // "on line {4} of procedure {5} on server {6}:\n{7}",
         //  err.Source, err.Class, err.State, err.Number, err.LineNumber,
         //  err.Procedure, err.Server, err.Message);
        }
    }

Instead of outputting to the Console, I want to write it to the LogFileNameAndPath variable via the "File.AppendAllText(LogFileNameAndPath, err.Message)"; however, I have looked at many posts over the web and NOBODY provides a solution. Is there a way to do this? Please be kind. Thanks!

我想通过“File.AppendAllText(LogFileNameAndPath,err.Message)”将其写入LogFileNameAndPath变量,而不是输出到Console。但是,我通过网络查看了很多帖子,NOBODY提供了解决方案。有没有办法做到这一点?请善待。谢谢!

===========================================================================

[ADDED 2015-07-27 1606 EDT] If I change this line from:

[ADDED 2015-07-27 1606 EDT]如果我更改此行:

sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);

... to ...

... 至 ...

sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage(LogFileNameAndPath));

... it fails to compile. How does this get passed to the method?

......它无法编译。这如何传递给方法?

Here's the new method:

这是新方法:

    public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args, String LogFileNameAndPath)
    {
        foreach (SqlError err in args.Errors)
        {

            File.AppendAllText(@LogFileNameAndPath, err.Message);
            //Console.WriteLine("{0}", err.Message);
          }
    }

3 个解决方案

#1

Rather than try to pass the log file name into the event handler, you should have the log file name be defined elsewhere, and visible inside the event handler. For example:

您应该将日志文件名定义在别处,并在事件处理程序中可见,而不是尝试将日志文件名传递给事件处理程序。例如:

public class SqlHandler
{
    private readonly string m_LogFileNameAndPath = @"C:\log.txt";
    // or get it from AppConfig, as noted by FirebladeDan

    public void ProcessData() { /*...*/ }

    public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
    {
        foreach(var err in args.Errors)
        {
            File.AppendAllText(m_LogFileNameAndPath, err.Message);
        }
    }
}

It's also worth noting that you can still use a logging library (Log4Net, NLog, etc.) with a custom file, by telling the library which file to log to.

还值得注意的是,您仍然可以使用自定义文件来记录库(Log4Net,NLog等),方法是告知库要记录哪个文件。

#2

While writing straight to a file may work for you (see @FirebladeDan's answer for that), I would highly recommend using an existing logging library for performance reasons.

虽然直接写入文件可能对您有用(请参阅@ FirebladeDan的答案),但强烈建议您出于性能原因使用现有的日志记录库。

Log4Net is a common suggestion, and this link should be a good start for you. Log4Net has some nice features that help you such as the ability via the App.Config/Web.Config files to configure logging at run-time, so that you can change the logging level (how detailed) based upon the configuration without a recompile.

Log4Net是一个常见的建议,这个链接应该是一个很好的开始。 Log4Net有一些很好的功能可以帮助您,例如通过App.Config / Web.Config文件在运行时配置日志记录的功能,这样您就可以根据配置更改日志记录级别(详细程度)而无需重新编译。

Hopefully this gets you on the right path, and good luck to you!

希望这能让你走上正确的道路,祝你好运!

EDIT:

Based upon your updated statements, this option may be what you need.

根据您更新的声明,此选项可能是您所需要的。

// use a variable to store the path.
private string logFileNameAndPath;

public void ProcessData(string StoredProcedure, int StartDate, int EndDate, string Directory, string LogFileNameAndPath)
{
   // Store the parameter that was passed in as the variable.
   this.logFileNameAndPath = LogFileNameAndPath;
   /* Do query setup work here*/
   sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
   sqlCommand.ExecuteNonQuery();
}

public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
    foreach (SqlError err in args.Errors)
    {
        // Use the variable to indicate where to log to.
        File.AppendAllText(this.logFileNameAndPath, err.Message);
    }
}

Hopefully this helps get you across the finish line.

希望这有助于您跨越终点线。

#3

File.AppendAllText(@"pathgoeshere", err.Message);

Added: Below is how to get the value using a key from your AppConfig

补充:以下是如何使用AppConfig中的密钥获取值

String LogFileNameandPath = ConfigurationManager.AppSettings["keynamegoeshere"]);

Pass LogFileNameandPath into your method **Don't forget to add your references

将LogFileNameandPath传递给您的方法**不要忘记添加引用

#1