C# output SQL Server Messages to text file

Question

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

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.

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!

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

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

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);
          }
    }

Answer 1

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.

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.

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.

Answer 2

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

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

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

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