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

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

Posted by: Rickie  Apr. 25, 2006
http://rickie.cnblogs.com

The previous article shows you how to create built-in tables in the existing database, which will be used to handle logs. This post will continue the same topic.

2. Create stored procedures<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

The following SQL script is adopted to create four stored procedures in the same database with the above tables respectively. These stored procedures are usp_Logging_InsertCategoryLog, usp_Logging_AddCategory, usp_Logging_ClearLogs and 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. Change app.config/web.config configuration file to refer to new stored procedures.

The default values for logging application block should be updated because in the above step, the names of stored procedures have been updated too. Only two properties are required to change. One is AddCategoryStoredProcdure, another is WriteLogStoredProcedureName. Please reference to following image when changing these properties in your case.