How to configure Exception Handling and Logging Application Block(Database Logging).
1. Execute the below DB scripts to create Tables and Storced procedures in the database.
Create Table Scripts :-
CREATE TABLE [dbo].[Log](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NULL,
[Priority] [int] NOT NULL,
[Severity] [nvarchar](32) NOT NULL,
[Title] [nvarchar](256) NOT NULL,
[Timestamp] [datetime] NOT NULL,
[MachineName] [nvarchar](32) NOT NULL,
[AppDomainName] [nvarchar](512) NOT NULL,
[ProcessID] [nvarchar](256) NOT NULL,
[ProcessName] [nvarchar](512) NOT NULL,
[ThreadName] [nvarchar](512) NULL,
[Win32ThreadId] [nvarchar](128) NULL,
[Message] [nvarchar](1500) NULL,
[FormattedMessage] [ntext] NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](64) NOT NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[CategoryLog](
[CategoryLogID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[LogID] [int] NOT NULL,
CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED
(
[CategoryLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Category]
GO
ALTER TABLE [dbo].[CategoryLog] WITH CHECK ADD CONSTRAINT [FK_CategoryLog_Log] FOREIGN KEY([LogID])
REFERENCES [dbo].[Log] ([LogID])
GO
ALTER TABLE [dbo].[CategoryLog] CHECK CONSTRAINT [FK_CategoryLog_Log]
Create Stored Procedures Script :-
CREATE PROCEDURE [dbo].[AddCategory]
-- Add the parameters for the function here
@CategoryName nvarchar(64),
@LogID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CatID INT
SELECT @CatID = CategoryID FROM Category WHERE CategoryName = @CategoryName
IF @CatID IS NULL
BEGIN
INSERT INTO Category (CategoryName) VALUES(@CategoryName)
SELECT @CatID = @@IDENTITY
END
EXEC InsertCategoryLog @CatID, @LogID
RETURN @CatID
END
CREATE PROCEDURE [dbo].[ClearLogs]
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM CategoryLog
DELETE FROM [Log]
DELETE FROM Category
END
CREATE PROCEDURE [dbo].[InsertCategoryLog]
@CatID int,
@LogID int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO CATEGORYLOG(CATEGORYID,LOGID) VALUES(@CatID,@LogID)
END
CREATE PROCEDURE [dbo].[WriteLog]
(
@EventID int,
@Priority int,
@Severity nvarchar(32),
@Title nvarchar(256),
@Timestamp datetime,
@MachineName nvarchar(32),
@AppDomainName nvarchar(512),
@ProcessID nvarchar(256),
@ProcessName nvarchar(512),
@ThreadName nvarchar(512),
@Win32ThreadId nvarchar(128),
@Message nvarchar(1500),
@FormattedMessage ntext,
@LogId int OUTPUT
)
AS
INSERT INTO [Log] (
EventID,
Priority,
Severity,
Title,
[Timestamp],
MachineName,
AppDomainName,
ProcessID,
ProcessName,
ThreadName,
Win32ThreadId,
Message,
FormattedMessage
)
VALUES (
@EventID,
@Priority,
@Severity,
@Title,
@Timestamp,
@MachineName,
@AppDomainName,
@ProcessID,
@ProcessName,
@ThreadName,
@Win32ThreadId,
@Message,
@FormattedMessage)
SET @LogID = @@IDENTITY
RETURN @LogID
After creating the tables and stored procedures, please make sure that you have provided the execution grants to the Stored procedures properly.
please open the web.config using Enterprise Library configuration tool.
1. Make sure that the web.config already has the connectionstring tag set.
2. Add Logging Application Block to the config as mentioned below.
3. As we are planning to do the database logging, add the database trace listener as mentioned below.
4. Select the corresponding database instance name.
5. Add a new category and name it as DBCategory
6. Add a new Listener reference to the category.
7. Add the reference to Database Trace listener.
8. Add Exception Handling Application block to the config todo set the exception policies.
9. Add a exception Policy and exception type as mentioned below and give a proper name to exception policy.
10. Add the logging handler to the exception.
11. Select the log category and select t the Format type as textexceptionformat.
12. Open the .NET application add the below references to the application.
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging
Microsoft.Practices.EnterpriseLibrary.Logging
Microsoft.Practices.EnterpriseLibrary.Logging.Database
13. Add the below sample code in button click or page load event and run the application.
try
{
throw(new Exception("Test Logging Application Block"));
}
catch (Exception ex)
{
ExceptionPolicy.HandleException(ex, "Policy1");
}
No comments:
Post a Comment