Phil Factor (2/5/2009)

---

I'd be interested to see if anyone has a solution to the problem of preventing a barf when an invalid path is passed to DirectoryInfo. The obvious idea would be to send a RAISERROR through the Pipe to the SQLExecutionContext via ExecuteAndSend(). The error should be caught by the outer exception handling of the TSQL TRY....CATCH block so you can then do something sensible. However, in SQL Server 2005, the error is thrown back to the CLR and handled as SQLExpection. If the SQLException is caught within the CLR and nothing is done within the CLR CATCH block, then the execution will fail in the T-SQL context with an error, instead of being caught by the TSQL-TRY/CATCH Block. (Jens Suessmeyer in http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251376)

    public static IEnumerable os_directory_info(SqlString path, SqlString filter)
    {
        DirectoryInfo di = new DirectoryInfo(path.Value);
        if (di.Exists)
        {
            if (filter.IsNull || filter.Value == string.Empty)
                return di.GetFileSystemInfos();
            else
                return di.GetFileSystemInfos(filter.Value);
        }
        else
        {
            return null; 
        }
    }

noeld (2/5/2009)

---

Although SQLCLR has great power I think it is very important to tell the reader that it also brings MANY undesirable things in.
- Bad Exception handling is just one of the examples.

BEGIN TRY

select * 
from dbo.os_directory_info('f:\Start', null)

END TRY
BEGIN CATCH

SELECT error_message()

END CATCH

- Bad routines could cause stability problems even knowing that is a "hosted" CLR

- There is a very high risk of introducing code that is not Server-Side friendly.

- Problems caused by CLR objects are a lot harder to deal with.

Phil Factor (2/5/2009)

---

Yes, Agreed! However, I've recently started using NET Reflector with an add-in that allows you to see the source of all the CLR routines currently in the database.
http://www.denisbauer.com/NETTools/SQL2005Browser.aspx
It is a joy to use and it means that the D**ned developers can't hide their code from you, particularly as you can decompile it into VB or C#. and save the source to a file. Power to the DBA!

declare @content varchar(max)

select @content= cast(content as varchar(max)) 
from sys.assembly_files
where name = 'os_directory_info.cs'

print @content