Logging exception to database - Part 75

Suggested Videos
Part 72 - Windows event viewer
Part 73 - Logging exceptions to the windows eventviewer
Part 74 - Logging exceptions as information entry type in windows eventviewer

In this video we will discuss about loggin exceptions to a database table. The first step is to create the required table, to log the exceptions.

Script to create table - tblLog
Create table tblLog
(
[Id] int primary key identity(1,1),
[Date] DateTime,
[ExceptionMessage] nvarchar(max)
)



Stored procedure to log the exception
create procedure spInsertLog
@ExceptionMessage nvarchar(max)
as
begin
insert into tblLog([Date], [ExceptionMessage])
values (Getdate(), @ExceptionMessage)
end

Database connection string in web.config. Change it accordingly, to connect to your sql server.
<connectionStrings>
  <add name="DBConnectionString"
  connectionString="data source=.; database=Sample; Integrated Security=SSPI"
  providerName="System.Data.SqlClient" />
</connectionStrings>



Add a class file with name - Logger.cs.
public class Logger
{
    public static void Log(Exception exception)
    {
        StringBuilder sbExceptionMessage = new StringBuilder();

        do
        {
            sbExceptionMessage.Append("Exception Type" + Environment.NewLine);
            sbExceptionMessage.Append(exception.GetType().Name);
            sbExceptionMessage.Append(Environment.NewLine + Environment.NewLine);
            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);
        
        LogToDB(sbExceptionMessage.ToString());
    }

    private static void LogToDB(string log)
    {
        // ConfigurationManager class is in System.Configuration namespace
        string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        // SqlConnection is in System.Data.SqlClient namespace
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand("spInsertLog", con);
            // CommandType is in System.Data namespace
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter = new SqlParameter("@ExceptionMessage", log);
            cmd.Parameters.Add(parameter);

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

Add a webform with name - Errors.aspx.
<div style="font-family: Arial">
    <table style="border:1px solid black">
        <tr>
            <td style="color:Red">
                <h2>Application Error</h2>
            </td>
        </tr>
        <tr>
            <td>
                <h3>
                    An unkown error has occured. We are aware of it and the IT team is currently working
                    on this issue. Sorry for the inconvinience caused.</h3>
            </td>
        </tr>
        <tr>
            <td>
                <h5>
                    If you need further assistance, please contact our helpdesk at helpdesk@companyhelpdesk.com
                </h5>
            </td>
        </tr>
    </table>
</div>

Add a webform to the project. Drag and drop a gridview control. Copy and paste the following code in the code behind file, in the Page_Load() event.
//try
//{
    // DataSet is in System.Data namespace
    DataSet ds = new DataSet();
    // This line will throw an exception, as Data.xml 
    // file is not present in the project
    ds.ReadXml(Server.MapPath("~/Data.xml"));
    GridView1.DataSource = ds;
    GridView1.DataBind();
//}
//catch (Exception ex)
//{
//    Logger.Log(ex);
//    Server.Transfer("~/Errors.aspx");
//}

Global.asax code:
void Application_Error(object sender, EventArgs e)
{
    if (Server.GetLastError() != null)
    {
        // Log the exception
        Logger.Log(Server.GetLastError());
        // Clear the exception
        Server.ClearError();
        // Transfer the user to Errors.aspx page
        Server.Transfer("Errors.aspx");
    }
}

Run the application. The exception should be logged to the Database table - tblLog and the user will be redirected to Errors.aspx page.