在自己的数据库里创建内置表来处理异常
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。可以通过下面的图示在你的应用中改变这两个值。