在自己的数据库里创建内置表来处理异常(译)

在自己的数据库里创建内置表来处理异常

翻译自： [url]http://rickie.cnblogs.com/[/url]

Apr. 24, 2006
本文将讲述在使用 Enterprise Library 2.0 中的 Logging Application block 时，如何在自己建立的数据库里创建内置的表来记录异常，而不是使用独立的一个日志数据库。同时，使用 Logging 和 usp_Logging 来分别做为表和存储过程的前缀，以避免和已经存在的库中的表和存储过程冲突。
 

完成这个步骤大体分以下三个步骤：
1. 在已经存在的库里建表

下面的 SQL 脚本在已经存在的库里创建3个表, Logging_Category, Logging_CategoryLog 和 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]

2. 在已经存在的库里建存储过程

下面的 SQL 脚本在上面的表里创建 4 个存储过程：usp_Logging_InsertCategoryLog, usp_Logging_AddCategory, usp_Logging_ClearLogs 和usp_Logging_WriteLog.

 

CREATE PROCEDURE usp_Logging_InsertCategoryLog

       @CategoryID INT,

       @LogID INT

AS

BEGIN

       SET NOCOUNT ON;

 

       DECLARE @CatLogID INT

       SELECT @CatLogID FROM Logging_CategoryLog WHERE CategoryID=@CategoryID and LogID = @LogID

       IF @CatLogID IS NULL

       BEGIN

              INSERT INTO Logging_CategoryLog (CategoryID, LogID) VALUES(@CategoryID, @LogID)

              RETURN @@IDENTITY

       END

       ELSE RETURN @CatLogID

END

 

CREATE PROCEDURE [dbo].[usp_Logging_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 Logging_Category WHERE CategoryName = @CategoryName

       IF @CatID IS NULL

       BEGIN

              INSERT INTO Logging_Category (CategoryName) VALUES(@CategoryName)

              SELECT @CatID = @@IDENTITY

       END

 

       EXEC usp_Logging_InsertCategoryLog @CatID, @LogID

 

       RETURN @CatID

END

 

CREATE PROCEDURE usp_Logging_ClearLogs

AS

BEGIN

       SET NOCOUNT ON;

 

       DELETE FROM Logging_CategoryLog

       DELETE FROM Logging_Log

    DELETE FROM Logging_Category

END

 

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

 

3. 配置app.config/web.config来使用新的存储过程

正因为进行了上面的改变，我们需要改变配置 logging application block 的默认值和存储过程的名字。只需要改变两个属性：AddCategoryStoredProcdure 和 WriteLogStoredProcedureName。可以通过下面的图示在你的应用中改变这两个值。