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.