CODEWITHSUNDEEP
c#asp.netsql-serverdatabaseexception
Kyle Vassella
Kyle Vassella

Reputation: 2646

.NET - log exceptions to db using multiple SqlParameters and specify what goes in each db column

I'm following KudVenkat's tutorial for logging exceptions to the database in .NET: http://csharp-video-tutorials.blogspot.com/2012/12/logging-exception-to-database-part-75.html

His way is good for storing one thing - an ExceptionMessage string containing Exception Message, Exception Type, Stack Trace, etc - into one column - the @exceptionMessage column.

I'm trying to split this full Exception Message up into parts, such as ExceptionType (to the @exceptionType column), exceptionMessage (to the @exceptionMessage column), StackTrace (to the @stackTrace column), etc.

So far, I'm able to pass in multiple SqlParameters using:

        List<SqlParameter> sp = new List<SqlParameter>() {
            new SqlParameter("@exceptionType", log) { },
            new SqlParameter("@exceptionMessage", log) { }

        };

        cmd.Parameters.AddRange(sp.ToArray());

But I can't seem to split this data out into the columns I desire - the way I'm doing it, I have to call my logger function for each of my SqlParameters (see my code comments below), and they each create a new row with the same message on every column. Rather than Type going to Type column and Message going to Message column, it creates one row with Message inserted into both the Type and Message columns, then another row with Type inserted into both the Type and Message columns.

I believe this has to do with the nature of my LogToDB class:

public class storedProcedure : ApiController
{
    public static void Log(Exception exception)
    {

        // exception type
        StringBuilder sbExceptionType = new StringBuilder();

        // exception message
        StringBuilder sbExceptionMessage = new StringBuilder();

        do
        {
            // exception type
            sbExceptionType.Append("Exception Type" + Environment.NewLine);
            sbExceptionType.Append(exception.GetType().Name);
            sbExceptionType.Append(Environment.NewLine + Environment.NewLine);

            // exception message
            sbExceptionMessage.Append("Message" + Environment.NewLine);
            sbExceptionMessage.Append(exception.Message + Environment.NewLine + Environment.NewLine);
            sbExceptionMessage.Append("Stack Trace" + Environment.NewLine);
            sbExceptionMessage.Append(exception.StackTrace + Environment.NewLine + Environment.NewLine);

            exception = exception.InnerException;
        }
        while (exception != null);

////Calling this twice is a problem. How can I rebuild this to log both into the appropriate column?////
        LogToDB(sbExceptionMessage.ToString());
        LogToDB(sbExceptionType.ToString());
    }

    private static void LogToDB(string log)
    {

        string connectionString = ConfigurationManager.ConnectionStrings["redacted"].ConnectionString;

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand("redacted", con);

            cmd.CommandType = CommandType.StoredProcedure;

            List<SqlParameter> sp = new List<SqlParameter>() {
                new SqlParameter("@exceptionType", log) { },
                new SqlParameter("@exceptionMessage", log) { }

            };

            cmd.Parameters.AddRange(sp.ToArray());

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

Upvotes: 1

Views: 796

Answers (1)

tchelidze
tchelidze

Reputation: 8318

Calling LogToDb twice causes inserting 2 rows into the database.

Passing log as @exceptionType and @exceptionMessage parameter values causes same value to be inserted in Type and Message columns.

Modify LogToDB as 

private static void LogToDB(string exceptionType, string exceptionMessage)
{
   ....
   List<SqlParameter> sp = new List<SqlParameter>() {
            new SqlParameter("@exceptionType", exceptionType) { },
            new SqlParameter("@exceptionMessage", exceptionMessage) { }

        };
   ...

 }

And invoke LogToDB only once (Instead of twice as you do now) 

LogToDB(sbExceptionType.ToString(), sbExceptionMessage.ToString());

Upvotes: 2

Related Questions