Write Errorlog in SQL Server in ASP.Net Application

Today I discuss how to write application errors in SQL Server.

SQL Server Table schema :-

CREATE TABLE [dbo].[exceptionLogger](

[Counter] [int] IDENTITY(1,1) NOT NULL,

[pageName] [varchar](200) NULL,

[method] [varchar](200) NULL,

[line_Number] [int] NULL,

[errorMessage] [varchar](max) NULL,

[createdDate] [datetime] NULL

)

Stored Procedure to Insert in Errorlog table :-

Create proc [dbo].[SP_INSERT_EXCEPTION_LOGGER]

@pagename varchar(200),

@method varchar(200),

@line_number int ,

@error_msg varchar(max)

as begin

Insert into exceptionLogger(pageName,method,line_Number,errorMessage)

values (@pagename,@method,@line_number,@error_msg)

end

Write below code in Any class file :-

    public static void WriteToEventLog(Exception excep)

    {

        string ErrorMessgage = excep.Message;

        System.Diagnostics.StackTrace trace = new System.Diagnostics.StackTrace(excep, true);

        string pagename = trace.GetFrame((trace.FrameCount - 1)).GetFileName();

        string method = trace.GetFrame((trace.FrameCount - 1)).GetMethod().ToString();

        Int32 lineNumber = trace.GetFrame((trace.FrameCount - 1)).GetFileLineNumber();

        SqlConnection con = new SqlConnection(Common.GetConnectionString());

        con.Open();

        SqlCommand cmd = new SqlCommand("SP_INSERT_EXCEPTION_LOGGER", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@pagename", pagename);

        cmd.Parameters.AddWithValue("@method", method);

        cmd.Parameters.AddWithValue("@line_number", lineNumber);

        cmd.Parameters.AddWithValue("@error_msg", ErrorMessgage);

        cmd.ExecuteNonQuery();

        con.Close();

    }

Now , To call above method easily in catch  block of every method.

catch (Exception ex)
{
Logger.WriteToEventLog(ex);
}

Logger is my class file.

By using above code. When any exception occurs code will write every details in sql server table like 

PageName - on which page error occurs.
Methodname - which method hit error.
LineNumber - on which line error occur on the page.
ErrorMessage - error message.

I think this is very important thing to record our exception list in database or any txt file.