• Artykuł

     

SQL CLR  Udostępnij na: Facebook

Autor: Wojciech Kalbarczyk

Opublikowano: 2011-02-25

SQL Server 2008, podobnie jak poprzednia wersja serwera z 2005 roku, udostępnia środowisko Common Language Runtime (CLR). Oznacza to wiele nowych możliwości dla programistów, którzy mogą tworzyć obiekty baz danych przy użyciu platformy .NET, korzystając z języków C# i Visual Basic. Korzystanie z CLR znacznie też rozszerza możliwości programistyczne w porównaniu z używaniem samego T-SQL. W artykule tym postaram się pokazać, jak korzystać z CLR, jak tworzyć obiekty bazodanowe oraz kiedy warto korzystać z tego środowiska.

Jak już wspomniałem na wstępie, SQLCLR pozwala nam wykonywać kod zarządzany w procesie bazy danych. CLR posiada wiele mechanizmów wspierających programowanie. W skład  środowiska CLR wchodzą:

  • nadzorca klas – zarządza metadanymi, wczytuje interfejsy klas,
  • kompilator JIT – konwertuje kod pośredni IL na maszynowy,
  • nadzorca kodu – nadzoruje uruchamianym kodem,
  • Garbage Collector – automatycznie zwalnia nieużywaną pamięć,
  • interfejs bezpieczeństwa – zapewnia mechanizmy bezpieczeństwa,
  • interfejs debugowania – pozwala na wyszukiwanie błędów i śledzenie stanu uruchamianego kodu,
  • nadzorca typów – nadzoruje proces konwersji typów,
  • nadzorca wyjątków – zarządza obsługą wyjątków,
  • interfejs wątków – dostarcza interfejs dla programowania wielowątkowego,
  • marszaler COM – dostarcza mechanizmy marszalingu do i z COM,
  • bazowa biblioteka klas – integruje kod z biblioteką .NET.

Mechanizmy te znacznie ułatwiają pracę, wykonują bowiem niektóre czynności za programistę, który może skupić się na tworzeniu samych obiektów w jednym z języków –  C# lub Visual Basic .NET. Pozwalają też na tworzenie obiektów takich jak:

  • funkcje skalarne i tablicowe,
  • procedury składowane,
  • wyzwalacze,
  • agregaty,
  • własne typy danych.

Dodatkowo .NET Framework oferuje wiele wbudowanych klas i metod, które mogą być łatwo implementowane po stronie serwera. Wiele zadań, które były niewygodne lub trudne do wykonania za pomocą kodu transakcyjnego SQL, można zrealizować za pomocą kodu zarządzanego. W wyniku kompilacji takiego kodu otrzymujemy pliki assemblies, które możemy podejrzeć w naszej bazie danych za pomocą funkcji sys.assemblies*.*

 Pracę ze środowiskiem CLR należy zacząć od włączenia tej usługi w SQL Server. Możemy to zrobić, korzystając z wbudowanej procedury sp_configure:

Przykład 1.

EXEC sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

Teraz możemy już przystąpić do stworzenia pierwszego projektu – prostej funkcji, która zwróci większą z dwóch podanych liczb. Oczywiście mało kto korzysta z SQLCLR do tak prostych rzeczy, jednak przykład ten ma na celu jedynie pokazanie, jak tworzyć takie obiekty. W tym celu uruchamiamy MS Visual Studio .NET, wybieramy opcję File -> New -> Project. Następnie rozwijamy menu Database i z zakładki SQL Server wybieramy projekt SQL CLR dla wybranego języka – C# lub Visual Basic. Ja będę korzystał z tego pierwszego języka:


Rys. 1. Tworzenie nowego projektu.

Nadajemy nazwę naszemu projektowi i klikamy OK. Następnie ukazuje się okienko, w którym musimy wybrać serwer (pole 'Server name') oraz bazę danych (pole 'Select or enter database name'),  z którymi chcemy się połączyć.


Rys. 2. Połączenie z bazą danych.

Możemy dodatkowo przeprowadzić test połączenia z naszą bazą, aby upewnić się, że wprowadzone przez nas dane są poprawne. Powinniśmy otrzymać następujący komunikat:


Rys. 3. Testowanie połączenia.

Po zatwierdzeniu wprowadzonych danych ukazuje nam się okno naszego projektu. Aby dodać nowy obiekt, przechodzimy do zakładki Solution Explorer i z menu kontekstowego wybieramy Add -> User-Defined Function.


Rys. 4. Dodawanie obiektu do projektu.

Następnie nadajemy nazwę naszej funkcji i dodajemy ją do projektu. Kolejnym krokiem jest zdefiniowanie naszej funkcji:

Przykład 2.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString max_function(int a, int b)

    {

        return new SqlString("max = " + Math.Max(a,b));

    }

};

Teraz wystarczy tylko utworzyć plik DDL oraz przenieść  go na serwer bazodanowy. W tym celu wybieramy z menu Build -> Build ProjectName oraz Build -> Deploy ProjectName.


Rys. 5. Tworzenie pliku DDL i przenoszenie go na serwer.

Aby sprawdzić działanie funkcji, uruchamiamy SQL Server Management Studio oraz wykonujemy następujące zapytanie:

Przykład 3.

SELECT dbo.max_function(3,1) AS 'max'

w wyniku którego powinniśmy otrzymać:


Rys. 6. Wynik działania funkcji max_function.

Udało nam się więc stworzyć za pomocą SQLCLR pierwszą funkcję. Pozostałe obiekty tworzymy  podobnie. Stwórzmy więc jeszcze jeden przykład – procedurę składowaną, która wyświetli osoby o określonym wieku. Najpierw tworzymy tabelę, którą uzupełniamy przykładowymi danymi:

Przykład 4.

CREATE TABLE osoba

(

                imie varchar(30),

                nazwisko varchar(30),

                wiek int

)

INSERT INTO osoba VALUES('Tomasz','Rybacki',31),('Agata','Mroz',25),('Roman','Jankowski',15),('Karolina','Kosecka',31)

Następnie przechodzimy do MS Visual Studio, gdzie tworzymy naszą procedurę:

Przykład 5.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void wiek(SqlInt32 wiek)

    {

      using (SqlConnection conn = new SqlConnection("context connection=true"))

            {

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"SELECT * FROM osoba WHERE wiek= @wiek";

                cmd.Parameters.AddWithValue("@wiek",wiek);

                conn.Open();

                SqlContext.Pipe.ExecuteAndSend(cmd);

            }

    }

};

Wywołując stworzoną przez nas procedurę

Przykład 6.

EXEC dbo.wiek 31

otrzymujemy:


Rys. 7.  Wynik wykonania procedury dbo.wiek.

Wiemy już zatem, jak należy tworzyć obiekty w SQLCLR, pozostaje więc jeszcze pytanie: kiedy warto korzystać ze środowiska .NET, a kiedy pozostać przy T-SQL? Generalnie T-SQL oferuje szybszy dostęp do danych i efektywniej wykonuje operacje na zbiorach rekordów. CLR z kolei polecany jest głównie do wszelkich obliczeń numerycznych oraz operacji z minimalnym dostępem do danych. O ile bardziej wydajny może być CLR od T-SQL? Nie ma na ten temat żadnych oficjalnych statystyk, zatem sprawdźmy sami, czy faktycznie warto korzystać z tego środowiska. W tym celu stwórzmy tabelę:

Przykład 7.

CREATE TABLE liczby

(

                liczba float

)

oraz uzupełnijmy ją przykładowymi danymi:

Przykład 8.

DECLARE @counter int =1

WHILE @counter <= 1200

BEGIN

                INSERT INTO liczby

                VALUES(@counter)

                SET @counter+=1;

END

Następnie stwórzmy funkcje liczące ciąg Fibonacciego dla podanej liczby – jedną w T-SQL, a drugą w CLR.

T-SQL:

Przykład 9.

CREATE FUNCTION fib1 (@liczba float)

RETURNS float

AS

BEGIN

                DECLARE @l1 float=0

                DECLARE @l2 float=1

                DECLARE @wynik float=0

                WHILE @liczba>0

                               BEGIN

                               SET @wynik=@l1 + @l2

                               SET @l1=@l2

                               SET @l2=@wynik

                               SET @liczba-=1

                               END

                RETURN(@wynik)

END

CLR:

Przykład 10.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDouble fib2(float a)

    {

        double l1 = 0;

        double l2 = 1;

        double wynik=0;

        while (a > 0)

        {

            wynik = l1 + l2;

            l1 = l2;

            l2 = wynik;

            a--;

        }

        return new SqlDouble(wynik);

    }

};

I na koniec porównajmy, ile czasu zajmuje wykonie się poszczególnych funkcji.

T-SQL:

Przykład 11.

DECLARE @time TIME(7)= SYSDATETIME();

SELECT liczba, dbo.fib1(liczba) AS ciag_Fibonacciego

FROM liczby

DECLARE @t TIME(7)= SYSDATETIME();

SELECT  DATEDIFF(MILLISECOND,@time,@t) AS 'czas_wykoniania(ms)'


Rys. 8. Wynik wykonania funkcji fib1.

CLR:

Przykład 12.

DECLARE @time TIME(7)= SYSDATETIME();

SELECT liczba, dbo.fib2(liczba) AS ciag_Fibonacciego

FROM liczby

DECLARE @t TIME(7)= SYSDATETIME();

SELECT  DATEDIFF(MILLISECOND,@time,@t) AS 'czas_wykoniania_CLR(ms)'


Rys. 9. Wynik wykonania funkcji fib2.

Czasy wykonania:

            T-SQL – 7110 ms.

**            CLR – 170 ms.**

Widzimy tu miażdżącą wręcz przewagę dla SQLCLR. Zapytanie zostało wykonane prawie 42 razy szybciej niż z zastosowaniem Transact-SQL! Widzimy więc, że umiejętne dokonanie wyboru między CLR a T-SQL może przynieść ogromne korzyści zarówno programistom, jak i administratorom baz danych.

Udostępnienie CLR w SQL Server znacznie rozszerzyło możliwości programowania baz danych. Pozwoliło to na implementowanie funkcji, procedur, wyzwalaczy czy agregatów w środowisku .NET. Jeżeli jeszcze dodamy, że stosowanie tego środowiska może znacznie skrócić czas wykonywania niektórych operacji, to myślę, że każda osoba zajmująca się bazami danych powinna dość dokładnie zapoznać się z tym środowiskiem.