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

Is there some reason that uncommenting the existing File.AppendAllText() call doesn't work? Are you receiving a specific error? — Diosjenin
– Diosjenin, Commented Jul 27, 2015 at 20:01
if I uncomment this, then the sqlConnection.InfoMessage variable will not allow me to pass a parameter to the void method it calls. How can I do this? — wk4997
– wk4997, Commented Jul 27, 2015 at 20:12
you can't do that as event handlers are specific methods.. you can try extensions maybe, or define a global variable and pass the path to it then use it the event handler — sertsedat
– sertsedat, Commented Jul 27, 2015 at 20:46

Martin Noreke · Accepted Answer · 2015-07-28 14:32:52Z

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.

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.

edited Jul 28, 2015 at 14:32

answered Jul 27, 2015 at 20:02

Martin Noreke

4,1661 gold badge24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Remus Rusanu Over a year ago

I second that, using a logging framework like Log4Net is much better.

wk4997 Over a year ago

Thank you for your suggestion but this is going to be ran internally by another department, and I want to keep it as simple as possible for anybody else to make edits to this. This is why I must remain with utilizing the existing log file.

Martin Noreke Over a year ago

See my update for an option to do it without log4net by storing the path in a variable within the class being called.

FirebladeDan · Accepted Answer · 2015-07-27 20:17:25Z

1

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

edited Jul 27, 2015 at 20:17

answered Jul 27, 2015 at 20:01

FirebladeDan

1,0696 silver badges14 bronze badges

1 Comment

wk4997 Over a year ago

I want the ability to have the log filename and path determined by the key in the App.config file. This will not work, see my new edit to show what I'm talking about

Diosjenin · Accepted Answer · 2015-07-27 20:37:22Z

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.

Collectives™ on Stack Overflow

C# output SQL Server Messages to text file

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related