20. Użycie .NET CLR w SQL Server 2005

O wykorzystaniu CLR w SQL Server 2005 można by napisać wiele książek (i nie wątpię, że w najbliższym czasie tak się stanie). My zajmiemy się dość zasadniczymi zagadnieniami, które będą mogły przedstawić rewolucyjny charakter usprawnień. Pierwszym krokiem będzie napisanie funkcji do formatowania daty. Drugim nieco bardziej skomplikowanym zagadnieniem będzie napisanie własnej funkcji agregującej.

Funkcje bazodanowe

Pierwszym krokiem, jakiego musimy dokonać jest uruchomienie MS Visual Studio .NET 2005. Gdy już to będziemy mieli za sobą, musimy utworzyć odpowiedni projekt. W tym celu musimy wykonać następujące czynności. W głównym menu wybieramy File -> New -> Project. Następnie z lewego okna rozwijamy Visual C# zaznaczamy Database i naciskamy OK.

Po utworzeniu projektu pojawi się okienko dialogowe z możliwością wyboru serwera, z jakim chcemy się połączyć i bazy danych na nim istniejącym. My uzupełnimy to tak jak na zdjęciu poniżej.

Gdy projekt się uruchomi, odnajdujemy zakładkę Solution Explorer i klikamy prawym guzikiem myszy na projekt w celu dodania naszej funkcji. Wybieramy Add -> User Defined Funcition po otworzeniu okna dialogowego wpisujemy nazwę funkcji DateFormater i naciskamy OK.

Pojawiło nam się okno z wygenerowaną przykładową funkcją. Przechodzimy teraz do napisania funkcji, która jako argument będzie pobierać datę oraz format daty w jakim chcemy ją przedstawić. Jak pamiętamy z wcześniejszych artykułów, standardowo zwracana data wygląda następująco: 2005-08-20 17:19:22.977. Co jednak, jeśli chcemy, aby data zwracana była w formacie 20.08.2005 lub 20//08//2005? Aby rozwiązać ten problem wystarczy tylko jedna linia kodu w C#. Zmodyfikujmy wygenerowaną funkcję do takiej postaci:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString DateFormater(SqlDateTime myDate, SqlString dateFormat)
{
        return (SqlString)string.Format((string)("{0:" + dateFormat + "}"), myDate.Value);
}

Metoda pobiera standardowo dwa argumenty: datę oraz format, w jakim chcemy datę przedstawić. Format daty przedstawiany jest w następujący sposób: „y” reprezentuje rok, „M” miesiąc, a „d” dzień. Pomiędzy podane znaczniki można podstawiać dowolne znaki. Teraz wystarczy tylko utworzyć plik DDL i przerzucić go na serwer bazodanowy. Z menu wybieramy Build  -> Build Solution, a następnie Bulid- > Deploy Solution. Jeśli wszystko poszło dobrze możemy teraz przejść do serwera SQL i sprawdzić czy nasza funkcja działa poprawnie. Musimy tylko jeszcze włączyć na MS SQL Server 2005 opcje korzystania z CLR. Funkcja ta ze względów bezpieczeństwa jest domyślnie wyłączona. Aby ją uruchomić, wystarczy uruchomić następującą linię kodu.

sp_configure 'clr enabled', 1
go
reconfigure
go

Teraz możemy przetestować naszą funkcję. Poniższy skrypt pokazuje w jaki sposób można ją uruchomić i wyniki jakie otrzymujemy.

USE AdventureWorks
GO

SELECT dbo.DateFormater(GetDate(), 'yy.MM.dd')          05.08.20
SELECT dbo.DateFormater(GetDate(), 'dd///MM///yyyy')    20///08///2005
SELECT dbo.DateFormater(GetDate(), 'yy**MM**dd')        05**08**20
SELECT dbo.DateFormater(GetDate(), 'yy:-)MM:-)dd')      05:-)08:-)20

Funkcje agregujące

Funkcjom agregującym poświęciliśmy jeden z artykułów. I od tego czasu mam nadzieję, że wszyscy zdają sobie sprawę, jak ważnym zagadnieniem są agregaty. A co jeśli moglibyśmy napisać własne funkcje agregujące, z własnymi zasadami agregacji? Taką właśnie możliwość daje nam połączenie SQL Server 2005 z .NET Framework.

Aby pracować z własnymi User-Defined Aggregates (UDA), należy zapoznać się z czterema podstawowymi zasadami rządzącymi ich działaniem. Entering, accumulating, mering i existing. Kiedy uruchamiamy funkcję agregującą na serwerze, musimy zainicjalizować wartość początkowe. Jeśli robilibyśmy operacje dodawania, odejmowania - wartością startową byłoby zero. Jeśli jednak chcielibyśmy przeprowadzić operacje mnożenia wartością pierwotną byłaby wartość jeden.

Ponieważ nasz przykład nie będzie nazbyt wyrachowany i ograniczy się do powielenia agregatu SUM, wartość domyślną inicjalizujemy zerem.

public void Init()
{
        result = 0;
}

Następnym krokiem jest zdefiniowanie tego, co się dzieje z kolejną wartością, jaka jest pobierana. Czyli co się dzieje, jeśli podczas procesu przechodzenia po kolejnych wierszach w tabeli dostajemy nowe wartości. Jak mają się zachować w stosunku do wartości, które już mamy.

public void Accumulate(SqlInt32 Value)
{
        result += (int)Value;
}

W naszym przypadku odpowiedź jest dość prosta. Należy dodać wartość, która została podana do wartości, które już zsumowaliśmy.

Metoda Terminale zwraca wynik po wykonaniu metody Accumulate. Jest również odpowiedzialna za zwrócenie wyniku do kodu wywołującego. W naszym przypadku zwróci sumę wyniku z całej tabeli.

public SqlInt32 Terminate()
{
        return new SqlInt32(result);
}

Merge jest specjalną metodą, która pomaga w utrzymaniu spójności na poziomie obiektowym.

public void Merge(MyIntegerCalculate Group)
{
        result += Group.result;
}

W ten sposób napisaliśmy funkcję agregującą SUM, która w naszym przypadku nazywa się MyIntegerCalculate i używamy jej dokładnie w ten sam sposób, jak SUM z tą różnicą, że wstawiamy przed nazwę agregata dbo.

Przykład ten miał za zadanie zademonstrować, jak działają funkcje agregujące i przedstawić cztery podstawowe zasady tworzenia własnych agregatów. Następnym i wydaje się ostatnim krokiem na drodze tworzeniem własnych efektywnych i poprawnie działających funkcji są atrybuty.

Atrybuty agregatów

Atrybuty są specjalnymi znacznikami na klasach w .NET, za pomocą których możemy definiować dodatkową funkcjonalność dla danej klasy. Przykładem mogą być Web serwisy, przy których należy do każdej z metod dodać atrybut [WebMethod], aby umożliwić korzystanie z niej za pośrednictwem stron WWW.
W przypadku funkcji agregujących, które z definicji pracują na bazach danych, gdzie  najważniejsza jest wydajność, atrybuty są wyjątkowo ważne. Pomagają one SQL Server Query Optimizer efektywniej budować zapytania.

Format – mówi SQL czy powinien serializować dane

IsInvariantToNull – jeśli jest ustawione na true, serwer SQL ignoruje wartości NULL. W naszym przypadku nie ma potrzeby pobierać wartości nieokreślonych, więc możemy bez problemu ustawić na true. Jeśli jednak tworzylibyśmy funkcję wyliczającą wartość średnią musielibyśmy również uwzględnić wartości NULL.

IsInvariantToOrder – jeśli ustawimy na true, oznacza to, że nie zważamy na kolejność w jakiej dane są pobierane. Ponieważ suma jest operacją przemienną, nie ma potrzeby zachowywać kolejności.

IsNullIfEmpty – jeśli jest ustawione na true, zwraca wartość nieokreśloną NULL, gdy nie ma żadnych wartości. W naszym przypadku ogranicza się to do tego, że jeśli tabela będzie pusta, lub wiersze, które podaliśmy do sumowania posiadają wartość NULL to wynik również będzie NULL.

Podsumowanie

Wydaje mi się, że wszystkich zachęciłem do zagłębienie się w CLR i nowe możliwości jakie stoją przed SQL Server 2005 i jego integracją z .NET Framework. Mimo wielkich możliwości, jakie zostały dane zarówno administratorom baz danych, jak i developerom powinniśmy pamiętać, iż operacje wykonywane za pomocą Frameworka są znacznie bardziej kosztowne i czasochłonne, niż alternatywna operacja w Transact-SQL. Przy użyciu CLR powinniśmy najpierw sprawdzić, czy korzyści wynikające z łatwiejszego zakodowania są na tyle duże, aby zrezygnować z SQL.