SQL CLR To jest pierwszy artykuł na temat pisania procedur,funkcji i innych rzeczy w Common Language Runtime (CLR). Ten artykuł koncentruje się na podstawach budowania procedury SQL w Visual Studio 2010 za pomocą C#.

Tak można pisać procedury i funkcję w kodzie w C#, które zostaną później wykorzystane przez SQL Server jako biblioteka *.dll.

Nie ma sensu przerabiać podstawowych zapytań SQL na C# i na .NET-owe *.dll-ki.

Kiedy ma to sens?
Funkcjonalność CLR ma sens wtedy gdy musimy napisać zawansowany algorytm, który musi wykonać się po stronie serwera. Jako programista .NET i SQL zapewne też rozumiesz ,że język zapytań SQL nie został stworzony z myślą o obliczaniu zawansowanych równań matematycznych.

Sam muszę przyznać ,że napisanie metody łączącej tekst na wiele sposób przyjdzie mi dużo łatwiej w C# niż w SQL. Więc jest to też kolejny powód.

CLR także szybciej wykonuje swoje operację niż SQL.

SQL powinien wyciągać tylko dane ,ale dzisiaj ten język i jego mutację zależne od systemu baz danych (T-SQL) starają się robić dużo więcej niż to. Niestety języki stworzone do takich operacji jak C# po kompilacji do biblioteki będą wykonywały takie operację szybciej.

Piszemy projekt CLR

Aby stworzyć procedurę w C# musimy naturalnie stworzyć projekt w Visual Studio.

Po uruchomieniu Visual Studio wybierz z menu File –> New Project.

W oknie dialogowy z menu po lewej wybierz zakładkę Database ,a potem wybierz SQL Server.

image

Po prawej stronie wybierz projekt “Visual C# SQL CLR Database Project”.

Na dole istnieją pole tekstowe w których określasz gdzie twój projekt wyląduje oraz jak będzie on się nazywał.Ja swój projekt nazwałem “CSharSQLCLR”.

Po utworzeniu projektu Visual Studio poprosi ciebie o referencje do bazy danych do której chcesz zainstalować swoje procedury napisane w C#.

Najpierw wybierasz nazwę swojego serwera SQL (domyślnie po instalacji Visual Studio powinieneś mieć lokalny serwer SQL Server ) ,a potem swoją bazę danych.

Ja na potrzeby tego wpisu utworzyłem bazę danych o nazwie “CLRDataBase”.

image

Po wyborze bazy danych Visual Studio może ciebie zapytać o aktywację debugowania CLR dla tego połączenia. Warto zaznaczyć ,że w trakcie debugowania wszystkie wątki SQL Server zostaną zatrzymane co oznaczy ,że nie ma mowy o debugowaniu kodu na serwerze produkcyjnym chyba ,że chcesz stracić pracę.

Jest to aplikacja testowa uruchomiana na lokalny serwerze napisana dla potrzeby wpisu blogowego dlatego w tym przypadku zaznaczę opcję tak.

SQL server Project_04

Jeśli wybrałeś właściwy projekt w Visual Studio jego struktura powinna być następująca.

image

Zaznacz ikonkę projektu prawym przyciskiem myszki i wybierz z menu Add –> Stored Procedure…

SQL server Project_05

Do projektu powinna zostać dodana klasa StoredProcedures. Jak widzisz ta klasa ma słowo kluczowe patrial co oznacza ,że mimo iż możesz rozbijać swoje różne procedury na różne pliki .cs w rzeczywistości wszystkie twoje metody/procedury będą w tej samej klasie.

Na potrzeby tego wpisu stworzę prostą metodę zwracają obecną datę.

using System.Globalization;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PrintToday()
    {
        SqlPipe pipe;
        pipe = SqlContext.Pipe;

        pipe.Send(System.DateTime.Now.ToString
        (CultureInfo.InvariantCulture));
    }
};

Do przesłania prostej informacji do SQL Server wystarczy klasa SqlPipe i jej metoda Send. Obiekt tej klasy ma wiele innych alternatywnych metody Send ,ale skoncentrujemy się na razie na tej najprostszej wersji. Ta wersja spełni dokładnie to samo zadanie co metoda PRINT w T-SQL. Wydrukuje ona przesłany rezultat.

Mając napisaną metodę następny naszym krokiem jest umieszczenie *.dll-ki do danej bazy danych.

W meni wybierz opcję Build –> Deploy Solution.

image

Jeśli wcześniej nie wybrałeś swoje bazy danych bądź chcesz ją zmienić wystarczy zaznaczyć prawym przyciskiem myszki ikonkę projektu ,a potem wybrać opcję “Properties”.

W zakładkach po lewej stronie wybierz “Database” po czym z edytuj opcję “Connection String”.

image

Wciąż jednak możesz mieć problem z umieszczeniem swojego kodu do bazy danych.

Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification".
W przypadku błędu w trakcie wdrażania biblioteki istnieje duża szansa ,że główną przyczyną jest za wysoka wersja .NET Framework-a. Instancja SQL Server 2008 akceptuje biblioteki do wersji 3.5. Nie sprawdzałem tego ,ale zapewne wersja SQL Server-a 2012 może odtwarzać biblioteki w wersji 4.0 i 4.5. Myśląc tym tropem zapewne SQL Server 2005 akceptuje tylko biblioteki do wersji 2.0.

SQL server Project_07

Aby rozwiązać ten problem wystarczy w opcjach projektu zmienić wersję .NET Framework-a.

Teraz gdy nasza procedura istnieje w bazie danych sprawdźmy czy możemy ją wywołać.

Twoja C# procedura powinna znajdować się w folderze Programmability –> Stored Procedures. Jak widzisz procedura ta jest oznaczona kłódką. Właśnie wielką wadą CLR jest fakt ,że nie możesz edytować już raz skompilowanego kodu w SQL Server-ze.

image

Kod wywołujący procedurę wygląda tak.

EXEC [dbo].[PrintToday]

SQL server Project_08

Chyba jednak jeszcze o czymś zapomnieliśmy. No tak domyślnie SQL Server ma wyłączoną egzekucję kodu CLR.

Oto prosty kod który zmieni to ustawienie. Nie trzeba tego kodu wykonywać za każdy razem.

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

EXEC [dbo].[PrintToday]

W sumie to wszystko. Debugowanie kodu CLR jak i inne ciekawostki zostawię na inny wpis.

Pobierz Kod