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.