Tworzenie funkcji CLR bez drogiego środowiska Visual Studio

Dziś po chwili prób udało mi się stworzyć zewnętrzną funkcję dla bazy danych MSSQL 2005 za pomocą notatnika i kompilatora obsługiwanego z linii poleceń VBC.EXE. O ile samo stworzenie funkcji rozszerzającej możliwości bazy danych nie jest zbyt skomplikowane to zrobienie tego bez Visual Studio jest nieco karkołomne, gdyż w dzisiejszych czasach wszechobecnych kreatorów i szablonów możemy czuć się trochę zagubieni gdy ich nam zabraknie.

Proces tworzenia rozpocząłem standardowo w Visual Studio i za pomocą kreatora stworzyłem sobie prostą funkcję która bez problemu mi się skompilowała i dała się umieścić na serwerze za pomocą guzika Deploy. Kod funkcji jest dosyć prosty:

Imports System.Data.SqlTypes
Imports Microsoft.VisualBasic

Partial Public Class UserDefinedFunctions
     _
    Public Shared Function formatowanie(ByVal data As SqlDateTime) As SqlInt32
        Dim s As Integer
        Dim d As DateTime

        d = data.Value

        s = DatePart(DateInterval.WeekOfYear, d, FirstDayOfWeek.Monday, FirstWeekOfYear.FirstFullWeek)

        Return New SqlInt32(s)
    End Function
End Class

'

Zadaniem tej funkcji jest zwrócenie numeru tygodnia zgodnie z ustalonymi warunkami to jest: liczymy od pierwszego pełnego tygodnia oraz tydzień rozpoczyna się od poniedziałku.
Kolejnym krokiem jaki uczyniłem było skopiowanie kodu źródłowego do pliku plik.vb umieszczonego w katalogu D:\Praca\Net . Jest to o tyle istotne że później ta ścieżka będzie wykorzystana w poleceniach T-SQL-a.
Tak przygotowany plik możemy skompilować za pomocą komendy vbc.exe dostępnej wraz z .NET Framework SDK (do ściągnięcia ze strony Microsoftu np. tu). Sama komenda jest dziecinnie prosta i wygląda następująco:

D:\Praca\NET>C:\WINDOWS\Microsoft.NET\Framework\v3.5\vbc.exe /target:library plik.vb

Jak widzimy polecenie to uruchamiamy w katalogu z plikiem źródłowym. Wynikiem zaś działania tej komendy będzie plik.dll czyli biblioteka z funkcją.
Kolejnym krokiem jest podlinkowanie biblioteki w MSSQL-u oraz stworzenie funkcji. Możemy to wykonać za pomocą Menagment Studio, a w wersji bardziej Hardcore za pomocą narzędzia sqlcmd.exe.
Kod SQL z którego skorzystałem to:

use kosz

-- Włączamy obsługę CLR

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1;
RECONFIGURE;
GO

EXEC SP_CONFIGURE 'CLR ENABLED', 1
RECONFIGURE
GO

-- usuwamy funkcję jeżeli istnieje
IF OBJECT_ID (N'formatowanie', N'FS') IS NOT NULL
 DROP FUNCTION DBO.[FORMATOWANIE]
go 

-- usuwamy Assemby jeżeli istnieje 
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'plik')
 DROP ASSEMBLY [plik]
go

-- tworzymy Assemby
CREATE ASSEMBLY [plik]
AUTHORIZATION [dbo]
FROM 'D:\Praca\NET\plik.dll'
WITH PERMISSION_SET = SAFE
go

-- tworzymy funkcję
CREATE FUNCTION [dbo].[formatowanie](@data [datetime])
RETURNS [int] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [plik].[UserDefinedFunctions].[formatowanie]
go

-- sprawdzenie
SELECT [dbo].[formatowanie] (getdate()) as NumOfWeek

Umieściłem go w pliku plik.sql i wykonałem komendą:

D:\Praca\NET>sqlcmd -S .\SQLEXPRESS -E -i plik.sql

Jak widzimy plik jest uruchamiany na serwerze zainstalowanym na lokalnej maszynie
z wykorzystaniem wbudowanego logowania Windows. Wynik widoczny po uruchomieniu polecenia to aktualny numer tygodnia zgodnie z przyjętymi regułami.

I teraz najważniejsze. Jeżeli chcielibyśmy stworzyć inne obiekty typu procedurę, funkcję agregującą lub inną musimy zmienić dyrektywę obecną przed deklaracją obiektu. Dyrektywa ta informuje kompilator o tym co chcemy osiągnąć. Pełna lista dyrektyw to:

- Microsoft.SqlServer.Server.SqlFunction()
- Microsoft.SqlServer.Server.SqlProcedure()
- Microsoft.SqlServer.Server.SqlTrigger()
- Microsoft.SqlServer.Server.SqlUserDefinedAggregate()
- Microsoft.SqlServer.Server.SqlUserDefinedType()

Opisywana metoda może być niezwykle użyteczna w miejscach w których nie mamy do dyspozycji Visual Studio lub też gdy nie mamy dostępu do trybu graficznego a jedynie możemy wykonywać komendy za pomocą zdalnego commandline.