EntLib and SQLCLR?

Question

I have quite a few SQLCLR functions and sprocs in my MSSQL 2008 DB. Debugging and logging is always a problem. I have used Microsoft Enterprise Library Logging Application Block a lot in straight C# apps, and was wondering if it was (1) crazy or (2) impossible to layer that into SQLCLR stuff. I really like using a config file to define a rolling text log, Event Log, and SMTP output for different events, so if there is another way to do so, I'm all ears...

Thanks.

Answer 1

It seems like it is possible. I don't know if it's advisable.

One alternative would be defining a trace listener in code, which of course can read the configuration from the database. Another would simple be logging messages to a SQL table and using triggers to enable notifications.

I also do have to question whether the real mistake is that you have CLR stored procs in your database that have so much business logic that they require logging. I'm a big fan of business logic in my database, but I'm wary of CLR stored procs.

If you wanted to use the enterprise application blocks, the config file to edit woulds be C:\Program Files\Microsoft SQL Server\MSSQL10.INSTANCE_NAME\MSSQL\Binn\sqlservr.config. However, sql server does not seem to write values to this file when you restart it. I arrived at this conclusion with the following CLF PROC and UDFs:

using System;
using System.Configuration;
using Microsoft.SqlServer.Server;

namespace LoggedClr
{

    public static class AppDomainInfo
    {

        [SqlFunction]
        public static string GetConfigFileName()
        {
            return AppDomain.CurrentDomain.SetupInformation.ConfigurationFile;
        }

        [SqlFunction]
        public static string GetAppConfigValue(string key)
        {
            return ConfigurationManager.AppSettings[key];
        }

        [SqlProcedure]
        public static void SetAppConfigValue(string key, string value)
        {
            ConfigurationManager.AppSettings[key] = value;

        }
    }
}

Which I loaded and ran using the following T-SQL:

CREATE DATABASE TestAssembly
GO

USE TestAssembly
GO

ALTER DATABASE TestAssembly SET TRUSTWORTHY ON;
GO

ALTER AUTHORIZATION ON DATABASE::TestAssembly TO test
GO

DROP ASSEMBLY LoggedClr 
GO

CREATE ASSEMBLY LoggedClr 
    from 'C:\justin''s projects\TestClr\LoggedClr\LoggedClr\bin\Debug\LoggedClr.dll' 
    WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE FUNCTION GetConfigFileName () RETURNS NVARCHAR(MAX) AS 
    EXTERNAL NAME LoggedClr.[LoggedClr.AppDomainInfo].GetConfigFileName
GO

CREATE FUNCTION GetAppConfigValue (@key nvarchar(max)) RETURNS nvarchar(max) AS 
    EXTERNAL NAME LoggedClr.[LoggedClr.AppDomainInfo].GetAppConfigValue
GO

CREATE PROCEDURE SetAppConfigValue (@key nvarchar(max), @value nvarchar(max)) AS 
    EXTERNAL NAME LoggedClr.[LoggedClr.AppDomainInfo].SetAppConfigValue
GO

SELECT dbo.GetConfigFileName()
EXEC dbo.SetAppConfigValue 'justin', 'is a developer'
SELECT dbo.GetAppConfigValue('justin')

This gave me the following results:

------------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL10.INSTANCE_NAME\MSSQL\Binn\sqlservr.config

(1 row(s) affected)

-------------------------------------------------------------------------
is a developer

(1 row(s) affected)