BETA
Aby się zalogować, najpiew wybierz portal.
Aby się zarejestrować, najpiew wybierz portal.
Podaj słowa kluczowe
Słowa kluczowe muszą mieć co najmniej 3 sąsiadujące znaki alfanumeryczne
Pole zawiera niedozwolone znaki

Baza wiedzy











CRL i SQL 2005

28-02-2006 22:54 | kazikkuta
Czy Transact SQL zniknie? Raczej nie prędko, ale możliwości płynące z integracji SQL Server 2005 z .NET Framework są dla programistów ogromne…

SQLCRL

Integracja SQL Server 2005 z Microsoft .NET Framework jest chyba najbardziej intrygującą z nowości wprowadzonych w tej wersji. Przynajmniej dla programistów. Dotyczy ona zarówno administracji bazą (poprzez SQL Managment Objects, .NETowy odpowiednik opartych na modelu COM SQL Distributet Managment Objects) jak i czysto programistycznych, takich jak możliwość pisania procedur w językach .NET. I właśnie tą ostatnią możliwością zajmiemy się w tym artykule.

Bez Visual Studio 2005…

Jeśli posiadasz Visual Studio 2005 to możesz od razu opuścić ten rozdział. Jeśli natomiast nie jesteś szczęśliwym posiadaczem kopii powyższego oprogramowania, albo jesteś fanem programowania przy użyciu Notatnika to zapraszam do lektury.

Aby móc używać SQLCRL należy najpierw go uruchomić na serwerze, gdyż jest on domyślnie wyłączony. Zdecydowano się na takie posunięcie, aby zminimalizować obciążenie serwera oraz zwiększyć bezpieczeństwo. Uruchamiamy go następującym skryptem.

[Kod SQL]

EXEC sp_configure ‘clr enabled’, 1
RECONFIGURE WITH OVERRIDE
GO

Jeśli chcemy wyłączyć środowisko uruchomieniowe .NET na serwerze wystarczy zamienić 1 w skrypcie na 0. Kiedy już serwer potrafi obsługiwać programy środowiska .NET uruchamiamy nieśmiertelne narzędzie programisty, jakim jest Notatnik i wpisujemy następujący kod. Po całej operacji zapisujemy plik pod nazwą Hello.cs

[Kod C#]

using Microsoft.SqlServer.Server
namespace TestNamespace
{
  public class TestClass
  {
    public static void Hello(string name, ref string output)
    {
      output = “Hello” + name;
    }
  }
}

Zarówno klasa jak i metoda muszą być publiczne. Ponadto metoda musi być statyczna, gdyż SQL nie będzie tworzył instancji klasy. Definiowanie przestrzeni nazw nie jest obowiązkowe jednak zdecydowałem się na umieszczenie jej, żeby pokazać jak można zarejestrować procedurę, która znajduje się w takiej przestrzeni. Kiedy mamy już nasz plik możemy go skompilować przy pomocy csc?

[Kod C#]

css /t:liblary Hello.cs

Kompilator utworzy nam plik nam bibliotekę Hello.dll. Teraz należy ją zarejestrować na serwerze. Pozwoli to na nadanie nazwy bibliotece wewnątrz SQL Servera oraz ustawienie praw. Zauważmy, że biblioteka zostanie zainstalowana w bazie, do której jesteśmy aktualnie podłączeni. Jak już wspomniałem podczas rejestracji biblioteki mamy możliwość nadania praw. Domyślnie podczas instalacji biblioteka dostaje prawa SAFE, więc nie musimy tego robić w skrypcie.

§         SAFE – zarejestrowana w ten sposób biblioteka będzie miała dostęp tylko do kodu CLR oraz do bazy

§         EXTERNAL_ACCESS – zarejestrowana biblioteka będzie miała dostęp do zewnętrznych systemów, takich jak system plików, logi systemowe, sieć czy też inne serwery baz danych.

§         UNSAFE – zarejestrowana biblioteka będzie miała dostęp do wszystkiego. Oczywiście ustawienie to nie jest rekomendowane, jakkolwiek niekiedy może okazać się konieczne.

Poniższy skrypt instaluje bibliotekę na serwerze, ponieważ nie będziemy w kodzie próbować uruchamiać jakichkolwiek zewnętrznych funkcji zarejestrujemy ją jako SAFE.

[Kod SQL]

CREATE ASSEMBLY CsPorcedures FROM ‘D:\Hello.dll’
WITH PREMISSION_SET = SAFE
GO

Teraz jedyne, co nam zostało, to zarejestrować samą procedurę. Dzięki temu nadamy procedurze nazwę oraz wskażemy parametry. SQL nie potrafi sobie poradzić ze znakiem kropki oddzielającym nazwę przestrzeni nazw od nazwy klasy. Radzimy sobie z tym umieszczając całą nazwę w nawiasach kwadratowych.

[Kod SQL]

CREATE PROCEDURE HelloWorld
@name nvarchar(50)
@output nvarchar(100) OUTPUT
AS EXTERNAL NAME CsPorcedures.[TestNamespace.TestClass].Hello
GO

Procedury mogą zwracać wartość void albo int. Jeśli mimo to utworzymy funkcję, która zwraca inną wartość (na przykład string) plik się nam skompiluje, ale podczas próby rejestracji biblioteki na serwerze zostanie wyrzucony wyjątek. Jeśli funkcja ma zwracać wartość typu string, trzeba przekazać do funkcji wartość jako parametr typu ref. Potem, podczas rejestracji funkcji czy też procedury, wskazujemy ten parametr jako parametr wyjściowy (OUTPUT).Wywołujemy tak napisaną i zarejestrowaną procedurę tradycyjnie

[Kod SQL]

DECLARE @result nvarchar(100)
EXEC HelloWorld ‘Kazik’, @result OUTPUT
PRINT @result

Czego użyliśmy?

Jeśli przeczytaliście poprzedni rozdział to pewnie zauważyliście już, że używaliśmy klasy SqlContext. Leży ona w przestrzeni nazw Microsoft.SqlServer.Server i jest zdefiniowana w pliku System.Data.dll (podczas kompilacji przy pomocy csc nie musimy wskazywać tego pliku, gdyż kompilator C# doda referencję do niego automatycznie). Daje nam ona bezpośredni dostęp do środowiska bazy danych, w którym CLRSQL został uruchomiony.

… oraz z Visual Studio 2005

Chyba nikogo nie zaskoczę mówiąc, że Visual Studio 2005 znakomicie usprawnia tworzenie procedur serwera dla SQL 2005. Doceni to ten, kto przeszedł do tego miejsca w artykule używając Notatnika. Visual Studio 2005 Professional Edition oferuje programistom projekt typu Database. Podczas uruchomienia zostaniemy poproszeni o połączenie się z bazą danych. Dla poniższych przykładów użyłem bazy Nortwind.

02-28-200606-15-28  - [1].Jpg
Po wybraniu bazy powinien nam się ukazać następujący kod

[Kod C#]

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.sqlServer.Server;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void HelloCs()
  {
    // Put your code here
  }
}

Zauważmy atrybut SqlProcedure, który mówi Visual Studio jak ma zarejestrować naszą funkcję na serwerze. Można też przy pomocy tego atrybutu nadać procedurze inna nazwę niż nazwa funkcji. Robi się to przy pomocy właściwości Name tego atrybutu. Niestety Visual Studio nie potrafi włączyć CLRSQL na serwerze, należy w tym celu uruchomić na serwerze skrypt

[Kod SQL]

EXEC sp_configure ‘clr enabled’, 1
RECONFIGURE WITH OVERRIDE
GO

Coś łatwego

Dopiszmy do utworzonej przez Visual Studio klasy kod, który wyświetli nam powitanie. Nasza metoda powinna wyglądać tak:

[Kod C#]

[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloCs()
{
  SqlContext.Pipe.Send(“Hello World!”);
}

Jak widzimy SqlContext udostępnia między innymi właściwość Pipe, która pozwala na przesłanie danych do klienta. Jest ona odpowiednikiem funkcji PRINT z Tranzact-SQL’a. Jeśli spróbujemy teraz zbudować i uruchomić nasz projekt używając na przykład klawisza F5 projekt się zbuduje, ale nic się nie wyświetli. Skąd zatem mamy wiedzieć, czy utworzona przez nas procedura działa tak, jak się tego spodziewamy? Otóż podczas uruchomienia naszego programu faktycznie są uruchamiane skrypty SQL znajdujące się w katalogu Test Scripts. Na początku znajduje się tam tylko jeden skrypt o nazwie Test.sql. Otwórzmy istniejący skrypt i dodajmy tam wywołanie naszej procedury.

[Kod SQL]

EXEC HelloCs

Po tej operacji powinniśmy po naciśnięciu klawisza F5 ujrzeć komunikat Hello Word!

Operacje na danych

Spróbujmy czegoś trudniejszego.

[Kod C#]

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.sqlServer.Server;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetWords(string sentence)
  {
    SqlDataRecord rec = SqlDataRecord(
      new SqlMetaData(“Index”, SqlDbType.Int),
      new SqlMetaData(“Word”, SqlDbType.NVarChar, 50));
    SqlContext.Pipe.SendResultsStart(rec);

    int i =0;
    foreach (string word in sentence.Split(‘ ‘))
    {
      rec.SetInt32(0, i++);
      rec.SetString(1, word);
      SqlContext.Pipe.SendresultsRow(rec);
    }
    SqlContext.Pipe.SendResultsEnd();
  }
}

Powyższa procedura, jak jej nazwa wskazuje, wylicza słowa w podanej frazie. Na początku deklarujemy SqlDataRecord, który będzie reprezentował pojedynczy wiersz tablicy rekordów. Poszczególne kolumny w rekordzie definiuje się przy pomocy obiektów typu SqlMetaData, podając ich nazwę oraz typ zawieranych danych. Zauważmy, że typ string musi być zadeklarowany w sensie SQL, czyli wraz z podaną długością.

Potem otwieramy Pipe przy pomocy metody SendResultsStart podając jako parametr wcześniej utworzoną reprezentacje wiersza w tablicy. Po kolei dokładamy do wyjścia kolejne wiersze przy pomocy metody SendresultsRow, aby na końcu zwrócić całą tablicę wywołując metodę SendResultsEnd. Jako parametr SendresultsRow używamy naszego rekordu (SqlDataRecord) z ustawionymi odpowiednimi danymi.

Jak dotąd operowaliśmy na danych, ale nie pobieraliśmy danych z bazy. Aby pobrać dane z bazy możemy wykorzystać nowy typ połączenia, którym jest połączenie kontekstowe. Spójrzmy na poniższy przykład kodu, pochodzący z ksiązki Glenna Johnsona Programing Microsoft ADO.NET 2.0 Applications

[Kod C#]

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.sqlServer.Server;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void LastEmployeeOrders(string sentence)
  {
    SqlDataRecord rec = SqlDataRecord(
    new SqlMetaData(“EmployeeID”, SqlDbType.Int),
    new SqlMetaData(“Last3Orders”, SqlDbType.NVarChar, 50));
    DataTAble employees = new DataTable(“Employees”);

    using(SqlConnection cn = new SqlConnection())
    {
      cn.ConnectionString = “context connection = true”;
      using (SqlCommand cmd = cn.CreateCommand())
      {
        cmd.CommandText = “Select EmployeeID From Employees ORDER BY EmployeeID ASC”;
        cn.Open();
        using (SqlReader rdr = cmd.ExecuteReader())
        {
          employees.Load(rdr);
        }
      }
      SqlContext.Pipe.SendResultsStart(rec);

      foreach (DataRow dr in employees.Rows)
      {
        int empId = (int)dr[“EmployeeID”];
        using (SqlCommand cmd = cn.CreateConnection())
        {
          cmd.CommandText = string.Format(“Select TOP 3 From Orders WHERE EmployeeID = {0}" +
               " ORDER BY OrderDate DESC”, empID);
          using (SqlDataReader rdr = cmd.Executrreader())
          {
            string orders = string.Empty;
            while (rdr.Read())
            {
              if (orders.Length > 0) orders += “, ”;
              orders += rdr[0].ToString();
            }
            rec.SetInt32(0, empId);
            rec.SetString(1, orders);
            SqlContext.Pipe.SendResultsRow(rec);
          }
        }
      }
    }
    SqlContext.Pipe.SendResultsEnd();
  }
}

Na początku musimy pokazać, jaką tablicę będzie zwracać nasza procedura. Potem wyciągamy z bazy przy użyciu połączenia kontekstowego wszystkich pracowników i w pętli wybieramy trzy ostatnie zamówienia wykonane przez każdego pracownika. Potem wystarczy skonkatenować je i wysłać jako jeden string.

Context Connection

Dla danych znajdujących się w bazie, dla której piszemy procedurę możemy używać połączenia kontekstowego. Komunikuje się ono bezpośrednio z SQL Server, pomijając protokoły sieciowe, dzięki czemu zyskujemy na wydajności. Czy zatem nie będziemy używać połączeń ‘tradycyjnych’, kiedy używamy CLRSQL? Oczywiście, że będziemy! Zawsze, kiedy chcemy się połączyć z inną bazą trzeba będzie użyć tradycyjnej metody. Poza tym połączenie kontekstowe ma pewnie ograniczenia. Do nich należą między innymi brak wsparcia dla SqlNotyficationRequest czy też SqlCommand.Cancel. Tym nie mniej jest ono wystarczające do większości zastosowań. Po więcej informacji odsyłam do ksiązki SQL Server 2005 For Developers Peter'a DeBetty.

Słowo na zakończenie

Kiedy warto sięgać po możliwość napisania kodu w języku .NET , a kiedy tradycyjnie użyć T-SQL? Postanowiłem podać trzy reguły, choć nie jestem ich całkowicie pewny. Poddaję je raczej jako zachętę do konstruktywnej krytyki i wyłonienia lepszych, bardziej szczegółowych reguł.

Po pierwsze, jeśli kod ma za zadanie dostarczać lub modyfikować dane, powinieneś użyć T-SQL. CLRSQL pomimo używania połączenia kontekstowego, które nie wykorzystuje protokołów sieciowych, nadal łączy się z bazą, a to w moim mniemaniu nie przyśpiesza całej operacji.

Po drugie, jeśli kod ma wykonywać dużo obliczeń (na przykład kodowanie czy inne operacje matematyczne) powinieneś użyć .NET.

Po trzecie, ale to chyba jest oczywiste, jeśli chcesz użyć jakiejś funkcjonalności platformy .NET powinieneś użyć języka tejże.

Artykuł nie wyczerpuje wszystkich możliwości CLRSQL. Tak naprawdę są one o wiele większe. Używając CLRSQL można pisać wyzwalacze czy tez funkcje agregujące. Można też pisać własne typy (User Defined Types) z poziomu języka C# czy tez VB.NET. Mam nadzieję, że tym ostatnim akapitem udało mi się zachęcić was do samodzielnych poszukiwań.

tagi: SQL

Komentarze 1

User 131717
User 131717
25 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
jak dla mnie zabraklo informacji o tablicach, strukturach ups........ rekordach.... Mam nadzieje, ze sie doczekam na taki artykul!?
pkt.

Zaloguj się lub Zarejestruj się aby wykonać tę czynność.