Create built-in tables in your own database to handle exceptions, Part 1

Create built-in tables in your own database to handle exceptions, Part 1

 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Posted by: Rickie Blog: http://rickie.cnblogs.com

Apr. 24, 2006

This post will show you how to create built-in tables in your existing database to log exceptions when using Logging Application block of Enterprise Library 2.0, instead of creating an isolated LOGGING database. In the meanwhile, the prefix name – “Logging” and “usp_Logging” have been used for table name and stored procedure name respectively, in order not to conflict with the existing tables and stored procedures in the existing database.

 

For more detail information about Logging Application block of Enterprise Library 2.0, please go to Enterprise Library 2.0 web site.

 

There are about three steps to finish this process.

1. Create tables in the existing database.

2. Create stored procedures in the existing database

3. Change app.config/web.config configuration file to refer to new stored procedures.

 

Please follow the following steps.

1. Create tables in the existing database.

The following SQL script is used to created three tables in your own existing database respectively, Logging_Category, Logging_CategoryLog and Logging_Log.

 

CREATE TABLE [dbo].[Logging_Category](

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

       [CategoryName] [nvarchar](64) NOT NULL,

 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED

(

       [CategoryID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Logging_CategoryLog](

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

       [CategoryID] [int] NOT NULL,

       [LogID] [int] NOT NULL,

 CONSTRAINT [PK_CategoryLog] PRIMARY KEY CLUSTERED

(

       [CategoryLogID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[Logging_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

) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

To be continued.