Monthly Archives: Maj 2012

Nowości w SQL Server 2012 – WITH RESULT SETS

2012-05-30 

W SQL Server 2012 polecenie EXECUTE rozszerzone zostało o dodatkową opcję WITH RESULT SETS. Pozwala ona na określenie oczekiwanej struktury danych zwracanych przez zapytanie lub procedurę składowaną. Jeżeli zwrócony wynik nie będzie zgodny z określoną definicją wygenerowany zostanie wyjątek. Omawiana opcja umożliwia także nadanie własnych nazw kolumnom wynikowego zbioru danych. Klauzula WITH RESULT SETS może przyjąć trzy opcje:

  • WITH RESULT SETS UNDEFINED – nieokreślona struktura danych wynikowych, jest to ustawienie domyślne
  • WITH RESULT SETS NONE – brak wynikowego zbioru danych, jeżeli operacja zwróci jakikolwiek zbiór wygenerowany zostanie wyjątek
  • WITH RESULT SETS <result_sets_definition> – definicja struktury wynikowego zbioru danych

Definicja wyniku (<result_sets_definition>) może być określona na cztery sposoby:

  • AS OBJECT – struktura danych wynikowych musi pokrywać się z danym obiektem (tabelą, widokiem, funkcją zwracającą tabelę)
  • AS TYPE – struktura danych wynikowych musi pokrywać się z danym typem tabelarycznym
  • AS FOR XML – dane zwracane przez polecenie muszą mieć format XML
  • <custom_definition> – własna struktura danych wynikowych (lista kolumn wraz z typami oraz dodatkowymi informacjami: null/not null, collate)

W wyniku wykonania polecenia EXECUTE może zostać zwróconych kilka zbiorów danych. Za pomocą WITH RESULT SETS możemy określić ich liczbę oraz strukturę każdego z nich. Co więcej, możemy w takim przypadku stosować różne typy definicji. Można na przykład określić, że dana procedura składowana musi zwrócić dwa zbiory danych, z czego pierwszy będzie miał strukturę zgodną z typem tabelarycznym, a drugi będzie w formacie XML.

W celu zaprezentowania działania opcji WITH RESULT SETS stworzyłem tabelę Obiekty, typ tabelaryczny tObiekty oraz procedurę składowaną PobierzObiekty. Procedura przyjmuje parametr typu int i w zależności od jego wartości zwraca różne wyniki (0 – brak zbioru wynikowego, 1 – jeden zbiór danych, 2 – dwa zbiory danych, 3 – wynik w formacie XML):

--Utworzenie tabeli Obiekty
create table Obiekty
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Wstawienie 10-ciu rekordów
insert into Obiekty (Id, Nazwa)
select top 10 object_id, name
from sys.objects
order by object_id

--Utworzenie typu tabelarycznego tObiekty
create type tObiekty
as table
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Utworzenie procedury PobierzObiekty
create procedure PobierzObiekty
    @wynik int
as
begin
    --0 - brak wyników
    if @wynik = 0
        return
    --1 - jeden zbiór wynikowy, 2 - dwa zbiory wynikowe
    if @wynik in (1, 2)
    begin
        select Id, Nazwa
        from Obiekty

        if @wynik  = 2
            select object_id, name, create_date
            from sys.objects

        return
    end
    --3 - wynik w formacie XML
    if @wynik = 3
    begin
        select Id, Nazwa
        from Obiekty as Obiekt
        for xml auto, root('Obiekty')

        return
    end
end

Przykłady użycia WITH RESULT SETS dla zapytania oraz procedury składowanej:

--Definicja wyniku dla zapytania
exec ('select * from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Definicja wyniku dla procedury
exec PobierzObiekty 1
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Procedura nie zwracająca danych
exec PobierzObiekty 0
with result sets none

--Dowolny rodzaj wyniku lub jego brak
exec PobierzObiekty 1
with result sets undefined

--Definicja wyniku na podstawie tabeli
exec PobierzObiekty 1
with result sets
(
    as object Obiekty
)

--Definicja wyniku na podstawie typu tabelarycznego
exec PobierzObiekty 1
with result sets
(
    as type tObiekty
)

--Wynik w formacie XML
exec PobierzObiekty 3
with result sets
(
    as for xml
)

Przykłady użycia WITH RESULT SETS dla kilku zbiorów danych zwracanych przez procedurę składowaną z jednoczesną zmianą nazw kolumn tych zbiorów:

--Własne definicje dwóch wynikowych zbiorów danych
exec PobierzObiekty 2
with result sets
(
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50)
    ),
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

--Definicja na podstawie typu oraz własna
exec PobierzObiekty 2
with result sets
(
    as type tObiekty,
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

Jeżeli struktura zwracanych danych nie będzie zgodna z określoną definicją wygenerowany zostanie wyjątek (sprawdzenie nie dotyczy nazw kolumn):

--EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #1 in result set #1, and the corresponding value sent at run time was null.
exec ('select null as Id, Nazwa from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
exec ('select * from Obiekty')
with result sets none

W pierwszym przypadku problemem są wartości NULL w kolumnie Id, w drugim zapytanie zwraca zbiór danych mimo, iż definicja na to nie pozwala.

Więcej informacji o poleceniu EXECUTE oraz opcji WITH RESULT SETS: MSDN

Reklamy

Nowości w SQL Server 2012 – sekwencje

2012-05-23 

W najnowszej wersji SQL Server pojawił się mechanizm sekwencji. Sekwencje przypominają Identity, z tą różnicą, że ich działanie nie ogranicza się do pojedynczej tabeli. Utworzona sekwencja jest globalnym obiektem bazy danych i może być wykorzystywana w wielu tabelach, ponadto można w łatwy sposób zarządzać nią z poziomu języka T-SQL. Na poszczególnych przykładach pokażę w jaki sposób korzystać z obiektów sekwencji.

Tworzenie/usuwanie

Najprostszy sposób utworzenia sekwencji:

create sequence dbo.Numerator

Przy tworzeniu sekwencji możemy określić dodatkowe parametry:

create sequence dbo.Numerator
    as int
    start with 1
    increment by 1
    minvalue 1
    maxvalue 100
    cycle
    cache 10
  • AS – typ wartości (tinyint, smallint, int, bigint, numeric), domyślnym typem jest bigint
  • START WITH – wartość początkowa, domyślnie minimalna/maksymalna wartość wybranego typu danych
  • INCREMENT BY – wartość o jaką sekwencja jest zwiększana/zmniejszana przy pobieraniu kolejnej wartości, domyślnie 1
  • MINVALUE – minimalna wartość sekwencji
  • MAXVALUE – maksymalna wartość sekwencji
  • CYCLE – opcja określa, czy po osiągnieciu wartości MAXVALUE/MINVALUE licznik sekwencji jest „przekręcany”
  • CACHE – liczba kolejnych wartości, które przy pobieraniu są buforowane w pamięci w celu ograniczenia operacji odczytu/zapisu z dysku, zwiększa to wydajność ale w przypadku wyłączenia serwera dochodzi do powstania luk w numeracji (dane z cache zostają utracone)

Usunięcie sekwencji:

drop sequence dbo.Numerator

Sekwencjami można również zarządzać z poziomu Management Studio, są one widoczne w gałęzi Programmability -> Sequences:

Resetowanie wartości

Istnieje możliwość zresetowania sekwencji do danej wartości początkowej:

alter sequence dbo.Numerator
restart with 3

Podgląd aktualnej wartości

W celu sprawdzenia aktualnej wartości sekwencji bez jej zwiększania/zmniejszania należy posłużyć się widokiem sys.sequences (kolumna current_value):

select current_value
from sys.sequences
where name = 'Numerator'

Pobieranie wartości

Do pobrania i jednoczesnej zmiany wartości sekwencji służy polecenie NEXT VALUE FOR:

select next value for dbo.Numerator

Przykłady zastosowania:

create table #Test (Id int, Nazwa nvarchar(128))

--Bezpośrednie pobranie wartości
insert into #Test (Id, Nazwa)
values (next value for dbo.Numerator, 'Nazwa_1')

--Pobranie wartości do zmiennej
declare @Numer int = next value for dbo.Numerator
insert into #Test (Id, Nazwa)
values (@Numer, 'Nazwa_2')

--Pobranie wartości dla każdego rekordu zapytania zgodnie z porządkiem w opcjonalnej klauzuli OVER
insert into #Test(Id, Nazwa)
select next value for dbo.Numerator over(order by name), name
from sys.objects

Możliwe jest również pobranie zakresu wartości za pomocą procedury sp_sequence_get_range:

declare
    @firstValue sql_variant,
    @lastValue sql_variant

--Pobranie 10 wartości
exec sp_sequence_get_range
    @sequence_name = 'dbo.Numerator',
    @range_size = 10,
    @range_first_value = @firstValue output,
    @range_last_value = @lastValue output

select @firstValue as PierwszyNumer, @lastValue as OstatniNumer

Transakcje

W przypadku pobrania wartości sekwencji w ramach rozpoczętej transakcji, a następnie odwołaniu tej transakcji, wartość sekwencji nie jest przywracana do stanu początkowego:

select current_value
from sys.sequences
where name = 'Numerator'

--Rozpoczęcie transakcji
begin tran
select next value for dbo.Numerator
--Wycofanie transakcji
rollback tran

select current_value
from sys.sequences
where name = 'Numerator'

Parametry MINVALUE, MAXVALUE, CYCLE

Poniższy kod tworzy sekwencję rozpoczynającą się od 4 z minimalną wartością 1 i maksymalną 5. Następnie trzykrotnie pobierana jest jej wartość:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Ostatnia próba pobrania zakończyła się błędem, ponieważ sekwencja osiągnęła maksymalną wartość. Jeżeli przy tworzeniu sekwencji dodamy parametr CYCLE, po osiągnięciu MAXVALUE jej wartość zostanie ustawiona na MINVALUE:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5
    cycle

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Warto zauważyć, że w powyższej sytuacji wartość sekwencji nie jest ustawiana na parametr START WITH tylko na MINVALUE.

Jeżeli parametr INCREMENT BY miałby wartość ujemną, sekwencja po osiągnięciu MINVALUE zostałaby ustawiona na MAXVALUE.

Default kolumny

Sekwencji można użyć jako wartości DEFAULT dla kolumny tabeli:

create table Test
(
    Id int default(next value for dbo.Numerator),
    Nazwa nvarchar(128)
)

Więcej informacji o sekwencjach w SQL Server 2012: MSDN

Kolekcja obiektów i plik XML – zapis, odczyt i modyfikacja danych przy użyciu LINQ to XML

2012-05-18

Na platformie .NET od wersji 3.5 dostępna jest technologia LINQ oferująca uniwersalny mechanizm zadawania zapytań do obiektów. W tym wpisie pokażę w jaki sposób przy użyciu LINQ to XML przenieść dane z kolekcji obiektów do dokumentu XML, wczytać dane z pliku XML do kolekcji oraz zmodyfikować zawartość XML-a. Posłużę się w tym celu klasą System.Xml.Linq.XDocument.

Załóżmy, że mamy klasę Osoba o następującej strukturze:

class Osoba
{
    public string Pesel { get; set; }
    public string Imie { get; set; }
    public string Nazwisko { get; set; }
    public int Wzrost { get; set; }

    public Osoba(string pesel, string imie, string nazwisko, int wzrost)
    {
        Pesel = pesel;
        Imie = imie;
        Nazwisko = nazwisko;
        Wzrost = wzrost;
    }
}

Poniższy kod tworzy kolekcję trzech obiektów typu Osoba, po czym dane z tej kolekcji (posortowane po nazwisku i imieniu) przenosi do dokumentu XML. Na końcu dokument zapisywany jest do pliku Osoby.xml:

List<Osoba> listaOsob = new List<Osoba>();
listaOsob.Add(new Osoba("70010123456", "Jan", "Kowalski", 180));
listaOsob.Add(new Osoba("75061598765", "Janina", "Kowalska", 160));
listaOsob.Add(new Osoba("80122012398", "Marian", "Kowalski", 185));

XDocument xml = new XDocument(
    new XDeclaration("1.0", "utf-8", "yes"),
    new XComment("Lista osób z kolekcji"),
    new XElement("osoby",
        from osoba in listaOsob
        orderby osoba.Nazwisko, osoba.Imie
        select new XElement("osoba",
            new XAttribute("pesel", osoba.Pesel),
            new XElement("imie", osoba.Imie),
            new XElement("nazwisko", osoba.Nazwisko),
            new XElement("wzrost", osoba.Wzrost)
            )
        )
    );

xml.Save("Osoby.xml");

Zawartość utworzonego pliku XML:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Lista osób z kolekcji-->
<osoby>
  <osoba pesel="75061598765">
    <imie>Janina</imie>
    <nazwisko>Kowalska</nazwisko>
    <wzrost>160</wzrost>
  </osoba>
  <osoba pesel="70010123456">
    <imie>Jan</imie>
    <nazwisko>Kowalski</nazwisko>
    <wzrost>180</wzrost>
  </osoba>
  <osoba pesel="80122012398">
    <imie>Marian</imie>
    <nazwisko>Kowalski</nazwisko>
    <wzrost>185</wzrost>
  </osoba>
</osoby>

Jeżeli mamy plik XML, możemy na podstawie jego zawartości utworzyć kolekcję obiektów typu Osoba:

XDocument xml = XDocument.Load("Osoby.xml");

List<Osoba> listaOsob = (
    from osoba in xml.Root.Elements("osoba")
    select new Osoba(
        osoba.Attribute("pesel").Value,
        osoba.Element("imie").Value,
        osoba.Element("nazwisko").Value,
        int.Parse(osoba.Element("wzrost").Value)
        )
    ).ToList<Osoba>();

Ostatnią operacją jest modyfikacja danych w istniejącym pliku XML. Pokażę dwa sposoby jej realizacji. Załóżmy, że osobie o numerze pesel 75061598765 chcemy zmienić nazwisko na Nowak.

Przykład 1:

XDocument xml = XDocument.Load("Osoby.xml");

var osoby = xml.Root.Elements("osoba").Where(
    osoba => osoba.Attribute("pesel").Value == "75061598765");
if (osoby.Any())
    osoby.First().Element("nazwisko").Value = "Nowak";

xml.Save("Osoby.xml");

Przykład 2:

XDocument xml = XDocument.Load("Osoby.xml");

var osoby = from osoba in xml.Root.Elements("osoba")
            where osoba.Attribute("pesel").Value == "75061598765"
            select osoba;
if (osoby.Any())
    osoby.First().Element("nazwisko").Value = "Nowak";

xml.Save("Osoby.xml");

W obu przykładach w pliku XML zmodyfikowane zostało nazwisko odpowiedniej osoby:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Lista osób z kolekcji-->
<osoby>
  <osoba pesel="75061598765">
    <imie>Janina</imie>
    <nazwisko>Nowak</nazwisko>
    <wzrost>160</wzrost>
  </osoba>
  <osoba pesel="70010123456">
    <imie>Jan</imie>
    <nazwisko>Kowalski</nazwisko>
    <wzrost>180</wzrost>
  </osoba>
  <osoba pesel="80122012398">
    <imie>Marian</imie>
    <nazwisko>Kowalski</nazwisko>
    <wzrost>185</wzrost>
  </osoba>
</osoby>

W przypadku zapytań na dokumentach XML, do kolekcji ich elementów mamy dostęp poprzez odpowiednie właściwości i metody:

  • Root – element główny wraz z całą zawartością dokumentu
  • Elements – elementy-dzieci bieżącego elementu z możliwością podania ich nazwy
  • Nodes – węzły-dzieci bieżącego elementu (w odróżnieniu od Elements zwracane są także komentarze i tekst)
  • Descendants – elementy-dzieci bieżącego elementu na dowolnym poziomie z możliwością podania ich nazwy
  • Parent – rodzic bieżącego elementu
  • Ancestors – rodzice bieżącego elementu na dowolnym poziomie z możliwością podania ich nazwy
  • ElementsBeforeSelf – elementy o tym samym rodzicu znajdujące się przed bieżącym elementem z możliwością podania ich nazwy
  • ElementsAfterSelf – elementy o tym samym rodzicu znajdujące się po bieżącym elemencie z możliwością podania ich nazwy
  • Attributes – atrybuty bieżącego elementu z możliwością podania ich nazwy

Więcej informacji o technologii LINQ to XML: MSDN

Nowości w SQL Server 2012 – obsługa błędów i polecenie THROW

2012-05-14 

Jedną z nowości w SQL Server 2012 jest polecenie THROW służące do wywoływania wyjątków. Działa ono podobnie do znanej wcześniej funkcji RAISERROR. Zgodnie z MSDN w najnowszej wersji języka T-SQL zalecane jest stosowanie THROW zamiast RAISERROR. Omawiane polecenie ma dwa zastosowania. Pierwsze z nich polega na wywołaniu THROW z określonymi parametrami w celu wygenerowania własnego wyjątku. Drugim zastosowaniem jest wywołanie THROW bez argumentów w sekcji CATCH (tylko w tym miejscu jest to dozwolone) co pozwala na ponowne zgłoszenie przechwyconego wyjątku (propagowanie wyjątku).

THROW error_number, error_message, error_state

error_number – numer błędu (wartość INT nie mniejsza od 50000), w odróżnieniu od RAISERROR nie musi znajdować się w sys.messages
error_message – komunikat błędu
error_state – stan, wartość z przedziału 0 – 255

W przypadku polecenia THROW nie mamy wpływu na parametr severity (Level), zawsze jest on ustawiony na 16.

Przykład użycia:

THROW 50001, 'Własny komunikat błędu', 3;

Dotychczas propagowanie wyjątku z użyciem polecenia RAISERROR realizowane było w następujący sposób:

begin try
    select 1 / 0
end try
begin catch
    --Obsługa błędów

    --Propagowanie wyjątku
    declare
        @ErrorMessage nvarchar(4000) = ERROR_MESSAGE(),
        @ErrorSeverity int = ERROR_SEVERITY(),
        @ErrorState int = ERROR_STATE()

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
end catch

Dzięki poleceniu THROW operacja ta staje się znacznie prostsza:

begin try
    select 1 / 0
end try
begin catch
    --Obsługa błędów

    --Propagowanie wyjątku
    THROW
end catch

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Przesyłanie plików w systemach rozproszonych – streaming w WCF

2012-05-10

Często spotykanym wyzwaniem przy budowie systemów rozproszonych jest przesyłanie plików o znacznych rozmiarach (np. rzędu kilkuset MB). Tego typu rozwiązanie w łatwy sposób można zaimplementować w technologii WCF, która pozwala na tworzenie wszechstronnych i wydajnych usług sieciowych. Na przykładowym serwisie WCF pokażę w jaki sposób radzić sobie z wysyłaniem i odbieraniem dużych plików pomiędzy klientem a usługą.

Domyślnym trybem przesyłania danych w WCF jest tryb buforowany (Buffered) co oznacza, że dane przechowywane są w pamięci do czasu odebrania ich w całości i dopiero po zakończeniu transferu stają się dostępne do dalszych operacji (np. ich odczytu przez odbiorcę). Przy operacjach przesyłania dużych plików takie podejście ma negatywny wpływ na wykorzystanie zasobów i wydajność całego rozwiązania. W takim przypadku znacznie lepszym wyborem jest tryb przesyłania strumieniowego (Streamed) i właśnie jemu poświęcę dalszą część tematu. W tym trybie dane mogą być przetwarzane już w trakcie ich przesyłania, nie ma wówczas konieczności oczekiwania na zakończenie transferu.

Prezentowane w tym wpisie rozwiązanie składa się z trzech projektów: serwisu WCF (StreamingService), aplikacji hostującej (StreamingHosting) i klienta korzystającego z metod serwisu (StreamingClient).

Serwis WCF

StreamingService to projekt WCF Service Library będący usługą udostępniającą dwie metody: DownloadFile (po przekazaniu nazwy pliku metoda zwraca klientowi taki plik o ile istnieje) oraz UploadFile (metoda pozwala na wysłanie przez klienta pliku o podanej nazwie). Dodatkowo obie metody zwracają Result typu int (0 – wystąpił błąd, 1 – brak błędów) oraz Message typu string (komunikat w przypadku wystąpienia błędu). Głównymi elementami projektu są:

  • IService.cs – definicje kontraktów serwisu i danych
  • Service.cs – implementacja metod serwisu
  • App.config – konfiguracja serwisu

Żeby skorzystać z trybu przesyłania strumieniowego bardzo ważną rzeczą jest, aby przy definiowaniu kontraktów wiadomości (MessageContract) przesyłany plik (parametr typu Stream) umieszczony był w sekcji Body i był jedyną zawartością tej sekcji. Jeżeli w sekcji Body oprócz pliku znajdzie się jeszcze inny parametr to tryb przesyłania zostanie automatycznie przełączony na buforowanie. W związku z powyższym, aby działał tryb Streamed wszystkie dodatkowe parametry muszą być przekazywane w nagłówku wiadomości (MessageHeader), natomiast sam obiekt Stream w treści wiadomości (MessageBodyMember).

Jeszcze jedną ważną rzeczą jest implementacja interfejsu IDisposable przez klasy kontraktów wiadomości (MessageContract), które są wynikiem metod przesyłających pliki z serwisu do klientów. Metoda Dispose() ma na celu zwolnienie uchwytu do przesyłanego pliku po zakończeniu transferu (zamknięciu wiadomości).

Kod pliku IService.cs:

namespace StreamingService
{
    [ServiceContract]
    public interface IService
    {
        [OperationContract]
        DownloadFileResponse DownloadFile(DownloadFileRequest request);

        [OperationContract]
        UploadFileResponse UploadFile(UploadFileRequest request);
    }

    #region DownloadFile Contracts

    [MessageContract]
    public class DownloadFileRequest
    {
        [MessageHeader]
        public DownloadFileRequestHeader downloadFileRequestHeader;
    }

    [DataContract]
    public class DownloadFileRequestHeader
    {
        [DataMember]
        public string FileName;
    }

    [MessageContract]
    public class DownloadFileResponse : IDisposable
    {
        [MessageHeader]
        public DownloadFileResponseHeader downloadFileResponseHeader;

        [MessageBodyMember]
        public Stream File;

        public void Dispose()
        {
            if (File != null)
            {
                File.Close();
                File = null;
            }
        }
    }

    [DataContract]
    public class DownloadFileResponseHeader
    {
        [DataMember]
        public int Result;

        [DataMember]
        public string Message;
    }

    #endregion

    #region UploadFile Contracts

    [MessageContract]
    public class UploadFileRequest
    {
        [MessageHeader]
        public UploadFileRequestHeader uploadFileRequestHeader;

        [MessageBodyMember]
        public Stream File;
    }

    [DataContract]
    public class UploadFileRequestHeader
    {
        [DataMember]
        public string FileName;
    }

    [MessageContract]
    public class UploadFileResponse
    {
        [MessageHeader]
        public UploadFileResponseHeader uploadFileResponseHeader;
    }

    [DataContract]
    public class UploadFileResponseHeader
    {
        [DataMember]
        public int Result;

        [DataMember]
        public string Message;
    }

    #endregion
}

Implementacja metod serwisu znajduje się w pliku Service.cs:

namespace StreamingService
{
    public class Service : IService
    {
        public DownloadFileResponse DownloadFile(DownloadFileRequest request)
        {
            int result = 1;
            string message = "";
            DownloadFileResponse response = new DownloadFileResponse();
            DownloadFileResponseHeader responseHeader = new DownloadFileResponseHeader();

            string filePath = ConfigurationManager.AppSettings["FilePath"].ToString() +
                request.downloadFileRequestHeader.FileName;

            if (File.Exists(filePath))
                response.File = File.OpenRead(filePath);
            else
            {
                response.File = Stream.Null;
                result = 0;
                message = "Nie odnaleziono pliku!";
            }

            responseHeader.Result = result;
            responseHeader.Message = message;
            response.downloadFileResponseHeader = responseHeader;

            return response;
        }

        public UploadFileResponse UploadFile(UploadFileRequest request)
        {
            int result = 1;
            string message = "";
            UploadFileResponse response = new UploadFileResponse();
            UploadFileResponseHeader responseHeader = new UploadFileResponseHeader();

            string fileName = request.uploadFileRequestHeader.FileName;

            if (string.IsNullOrWhiteSpace(fileName))
            {
                result = 0;
                message = "Nie podano nazwy pliku!";
            }
            else
            {
                try
                {
                    string filePath =
                        ConfigurationManager.AppSettings["FilePath"].ToString() + fileName;
                    using (FileStream fs = new FileStream(filePath, FileMode.Create))
                    {
                        //1MB buffer
                        int bufferSize = 1048576;
                        byte[] buffer = new byte[bufferSize];
                        int bytes;

                        while ((bytes = request.File.Read(buffer, 0, bufferSize)) > 0)
                        {
                            fs.Write(buffer, 0, bytes);
                            fs.Flush();
                        }
                    }
                }
                catch (Exception e)
                {
                    result = 0;
                    message = e.Message;
                }
            }

            responseHeader.Result = result;
            responseHeader.Message = message;
            response.uploadFileResponseHeader = responseHeader;

            return response;
        }
    }
}

Ostatnim elementem projektu usługi jest plik konfiguracyjny. Najważniejsze ustawienia znajdują się w sekcji <binding>:

  • transferMode – tryb przesyłania danych (Buffered/Streamed)
  • receiveTimeout – maksymalny czas działania metody odbierającej dane od klienta (metoda UploadFile)
  • sendTimeout – maksymalny czas działania metody wysyłającej dane do klienta (metoda DownloadFile)
  • maxReceivedMessageSize – maksymalny rozmiar wiadomości w bajtach, przy trybie Streamed może być ustawiony na maksymalną wartość ponieważ wiadomość nie jest buforowana w całości
  • maxBufferSize – maksymalna ilość pamięci w bajtach jaką WCF przeznacza na buforowanie, ważne jest aby wartość ta nie była mniejsza niż wielkość paczek w jakich odczytywane są dane (metoda UploadFile, zmienna bufferSize)

Ważnym parametrem w przypadku udostępniania usługi poprzez np. IIS jest maxRequestLength w sekcji <httpRuntime>. Kontroluje on maksymalną wielkość wiadomości jaką klient może wysłać do serwisu (podawana w kilobajtach).

Plik konfiguracyjny usługi App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="FilePath" value="D:\Streaming\Data\" />
  </appSettings>
  <system.web>
    <httpRuntime maxRequestLength="512000" />
  </system.web>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="BasicHttpBinding_IService"
                 transferMode="Streamed"
                 closeTimeout="00:10:00" openTimeout="00:10:00"
                 receiveTimeout="01:00:00" sendTimeout="01:00:00"
                 maxBufferSize="2147483647" maxBufferPoolSize="2147483647"
                 maxReceivedMessageSize="2147483647"
                 messageEncoding="Text" textEncoding="utf-8"
                 allowCookies="false" bypassProxyOnLocal="false"
                 hostNameComparisonMode="StrongWildcard" useDefaultWebProxy="true">
          <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647"
                        maxArrayLength="2147483647" maxBytesPerRead="2147483647"
                        maxNameTableCharCount="2147483647" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None" realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>
    <services>
      <service name="StreamingService.Service">
        <endpoint address="" binding="basicHttpBinding"
                  bindingConfiguration="BasicHttpBinding_IService"
                  contract="StreamingService.IService" />
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:8732/StreamingService.Service" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior>
          <!-- To avoid disclosing metadata information,
          set the value below to false and remove the metadata endpoint above before deployment -->
          <serviceMetadata httpGetEnabled="True"/>
          <!-- To receive exception details in faults for debugging purposes,
          set the value below to true.  Set to false before deployment
          to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="False" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

Hosting

W systemach produkcyjnych serwisy udostępniane są np. poprzez IIS, jednak aby nie komplikować prezentowanego rozwiązania ja w tym celu stworzyłem prostą aplikację konsolową (StreamingHosting). Jedyne co trzeba zrobić to dodać  referencje do System.ServiceModel, referencje do naszego projektu usługi WCF (StreamingService) oraz skopiować plik konfiguracyjny usługi. Plik Program.cs zawiera następujący kod:

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Uruchamianie usługi...");
        using (ServiceHost host = new ServiceHost(typeof(Service)))
        {
            try
            {
                host.Open();
                Console.WriteLine("Usługa uruchomiona. Naciśnij Enter aby zakończyć.");
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
        Console.WriteLine("Usługa zatrzymana.");
        Console.ReadLine();
    }
}

Po uruchomieniu aplikacji nasz serwis będzie dostępny pod adresem wskazanym w pliku konfiguracyjnym w sekcji <baseAddresses>.

Może się zdarzyć, że próba uruchomienia aplikacji zakończy się następującym błędem:
"HTTP could not register URL http://+:8732/StreamingService.Service. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details)."
Błąd ten oznacza brak możliwości nasłuchiwania żądań przychodzących po http. Rozwiązaniem tego problemu jest wywołanie następującej instrukcji z poziomu wiersza poleceń uruchomionego z prawami administratora (należy podać właściwą nazwę użytkownika):

netsh http add urlacl url=http://+:8732/StreamingService.Service/ user=UserName

Jeżeli nasłuchiwanie nie jest nam już potrzebne, poniższe polecenie przywraca stan pierwotny:

netsh http delete urlacl url=http://+:8732/StreamingService.Service/

Klient

Ostatnim elementem realizowanego rozwiązania jest aplikacja kliencka (StreamingClient) korzystająca z metod usługi. Aby możliwa była współpraca z udostępnionym serwisem konieczne jest dodanie do niego referencji (Service References) i jej skonfigurowanie zgodnie z poniższym screenem:

Poniżej znajduje się implementacja metod pozwalających na pobieranie i wysyłanie plików z/do serwisu WCF z wykorzystaniem jego metod DownloadFile i UploadFile:

public void DownloadFileFromService(string fileName)
{
    StreamingService.DownloadFileRequest request =
        new StreamingService.DownloadFileRequest();
    StreamingService.DownloadFileRequestHeader requestHeader =
        new StreamingService.DownloadFileRequestHeader();
    StreamingService.DownloadFileResponse response;

    using (StreamingService.ServiceClient sc = new StreamingService.ServiceClient())
    {
        requestHeader.FileName = fileName;
        request.downloadFileRequestHeader = requestHeader;
        response = sc.DownloadFile(request);
    }

    if (response.downloadFileResponseHeader.Result == 1)
    {
        try
        {
            string filePath =
                ConfigurationManager.AppSettings["ClientFilePath"].ToString() + fileName;
            using (FileStream fs = new FileStream(filePath, FileMode.Create))
            {
                //1MB buffer
                int bufferSize = 1048576;
                byte[] buffer = new byte[bufferSize];
                int bytes;

                while ((bytes = response.File.Read(buffer, 0, bufferSize)) > 0)
                {
                    fs.Write(buffer, 0, bytes);
                    fs.Flush();
                }
            }
            MessageBox.Show("Zakończono pobieranie pliku.");
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }
    else
        MessageBox.Show(response.downloadFileResponseHeader.Message);
}

public void UploadFileToService(string fileName)
{
    StreamingService.UploadFileRequest request =
        new StreamingService.UploadFileRequest();
    StreamingService.UploadFileRequestHeader requestHeader =
        new StreamingService.UploadFileRequestHeader();
    StreamingService.UploadFileResponse response;
    string filePath =
        ConfigurationManager.AppSettings["ClientFilePath"].ToString() + fileName;

    if (!File.Exists(filePath))
    {
        MessageBox.Show("Nie odnaleziono pliku!");
        return;
    }

    requestHeader.FileName = fileName;
    request.uploadFileRequestHeader = requestHeader;

    using (FileStream fs = new FileStream(filePath, FileMode.Open))
    {
        request.File = fs;
        using (StreamingService.ServiceClient sc = new StreamingService.ServiceClient())
        {
            response = sc.UploadFile(request);
        }
    }

    if (response.uploadFileResponseHeader.Result == 1)
        MessageBox.Show("Wysłano plik!");
    else
        MessageBox.Show(response.uploadFileResponseHeader.Message);
}

Plik konfiguracyjny aplikacji klienckiej wygląda następująco:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="ClientFilePath" value="D:\Streaming\ClientData\" />
  </appSettings>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="BasicHttpBinding_IService"
                 transferMode="Streamed"
                 closeTimeout="00:10:00" openTimeout="00:10:00"
                 receiveTimeout="01:00:00" sendTimeout="01:00:00"
                 maxBufferSize="2147483647" maxBufferPoolSize="2147483647"
                 maxReceivedMessageSize="2147483647"
                 messageEncoding="Text" textEncoding="utf-8"
                 allowCookies="false" bypassProxyOnLocal="false"
                 hostNameComparisonMode="StrongWildcard" useDefaultWebProxy="true">
          <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647"
                        maxArrayLength="2147483647" maxBytesPerRead="2147483647"
                        maxNameTableCharCount="2147483647" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None"
                realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
    </bindings>
    <client>
      <endpoint address="http://localhost:8732/StreamingService.Service"
          binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IService"
          contract="StreamingService.IService" name="BasicHttpBinding_IService" />
    </client>
  </system.serviceModel>
</configuration>

Podsumowanie

Przedstawione rozwiązanie pozwala na efektywne przesyłanie plików w systemach rozproszonych, a dzięki zastosowaniu trybu Streamed ich rozmiar nie stanowi problemu. W połączeniu z mechanizmem ładowania do bazy dużych zbiorów danych (omawianym przeze mnie w tym wpisie) możliwe jest tworzenie wydajnych rozwiązań zdalnego pobierania/wysyłania danych. Więcej o WCF i trybie streamingu można przeczytać na MSDN: link1, link2, link3.

Nowości w SQL Server 2012 – funkcje logiczne i tekstowe

2012-05-03 

W tym wpisie omówię kolejne cztery funkcje, które pojawiły się w najnowszej wersji języka T-SQL. Będą to dwie funkcje logiczne (IIF i CHOOSE) oraz dwie funkcje operujące na ciągach znaków (CONCAT i FORMAT).

IIF

Funkcja w swoim działaniu przypomina wyrażenie CASE. W zależności od wyniku warunku logicznego zwraca jedną z przekazanych wartości, przy czym mogą one być różnych typów. Typ wartości zwracanej przez funkcję określany jest na podstawie hierarchii typów, co oznacza, że będzie on przejęty z parametru o wyższym priorytecie typu. W związku z tym, przekazane wartości parametrów muszą pozwalać na konwersję pomiędzy ich typami. Szczegóły dotyczące hierarchii typów: Data Type Precedence.

IIF(boolean_expression, value_if_true, value_if_false)

boolean_expression – warunek logiczny
value_if_true – wartość zwracana w przypadku spełnienia warunku logicznego
value_if_false – wartość zwracana w przypadku nie spełnienia warunku logicznego

--Prawda
select IIF(1 < 2, 'Prawda', 'Fałsz')

--2
declare @x int = 1, @y int = 2
select IIF(@x > @y, @x, @y)

--Conversion failed when converting the varchar value 'Prawda' to data type int.
select IIF(1 < 2, 'Prawda', 1)

W przypadku bezpośredniego przekazania wartości NULL w miejsce obu zwracanych wartości, wstąpi błąd związany z brakiem możliwości określenia typu wynikowego funkcji:

--At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
select IIF(1 > 2, null, null)

Taki zapis jest już prawidłowy, ponieważ znany jest typ przynajmniej jednego z parametrów:

declare @n int = null
--NULL
select IIF(1 > 2, @n, null)

CHOOSE

Funkcja jako parametr przyjmuje wartość indeksu (typ int) oraz listę elementów w postaci kolejnych parametrów różnych typów. W wyniku zwraca wartość parametru przekazanego na pozycji określonej przez indeks. W przypadku podania indeksu większego niż liczba parametrów zwrócona zostanie wartość NULL. Podobnie jak w funkcji IIF, typ wartości zwracanej przez funkcję CHOOSE określany jest na podstawie elementu o najwyższym priorytecie typu, w związku z czym wartości pozostałych parametrów muszą pozwalać na konwersję do niego.

CHOOSE(index, value_1 [... , value_n])

index – pozycja elementu, którego wartość ma być zwrócona, pozycje liczone od 1 (typ int)
value_1 ... value_n – lista kolejnych parametrów (elementów)

--103
select CHOOSE(3, 101, 102, '103', '104', '105')

--Test1
select CHOOSE(1, 'Test1', 'Test2', 'Test3')

--NULL
select CHOOSE(5, 1, 2, 3)

--Conversion failed when converting the varchar value 'Test1' to data type int.
select CHOOSE(1, 'Test1', 2)

CONCAT

Funkcja pozwala na łączenie kilku ciągów znaków w jeden łańcuch. Jej główną zaletą jest obsługa wartości NULL, które zamieniane są na puste ciągi znaków dzięki czemu nie ma potrzeby używania funkcji ISNULL.

--123456
select CONCAT('123', null, '456')

--''
select CONCAT(null, null)

declare @s1 varchar(3) = '123', @s2 varchar(3) = null, @s3 varchar(3) = '456'
--NULL
select @s1 + @s2 + @s3
--123456
select ISNULL(@s1, '') + ISNULL(@s2, '') + ISNULL(@s3, '')
--123456
select CONCAT(@s1, @s2, @s3)

FORMAT

Funkcja służy do konwersji wartości różnych typów na odpowiednio sformatowany łańcuch znaków. Właśnie możliwość sformatowania ciągu wynikowego jest największą zaletą tej funkcji. W przypadku prostych konwersji, nie wymagających określonego formatu wyjściowego powinno się używać dotychczasowych funkcji CAST lub CONVERT.

FORMAT(value, format [, culture])

value – wartość do skonwertowania
format – format wynikowego łańcucha znaków (typ nvarchar)
culture – opcjonalne wskazanie ustawień regionalnych w jakich sformatowany będzie łańcuch znaków, domyślnie używane są ustawienia bieżącej sesji

Parametr określający format łańcucha znaków musi być poprawnie zdefiniowanym wyrażeniem formatującym znanym z platformy .NET, więcej na ten temat: Formatting Types.

W przypadku błędu podczas formatowania zwracana jest wartość NULL, w przypadku błędnie określonego parametru ustawień regionalnych zgłaszany jest błąd.

declare @d date = '20120402', @m money = 1512845.15

--1 512 845,15 zł
select FORMAT(@m, 'C', 'Pl-PL')

--2 kwietnia 2012
select FORMAT(@d, 'D', 'Pl-PL')

--2012-04
select FORMAT(@d, 'yyyy-MM')

--Pn, kwi 2, 2012
select FORMAT(@d, 'ddd, MMM d, yyyy', 'Pl-PL')

--poniedziałek, 02 kwietnia, 2012
select FORMAT(@d, 'dddd, dd MMMM, yyyy', 'Pl-PL')

Format możemy także przekazać w postaci zmiennej lub kolumny tabeli w zapytaniu:

declare @d date = '20120402', @format varchar(50) = 'd MMMM, yyyy roku (dddd)'

--2 kwietnia, 2012 roku (poniedziałek)
select FORMAT(@d, @format, 'Pl-PL')

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Nowości w SQL Server 2012 – funkcje daty i czasu

2012-05-02

W SQL Server 2012 pojawiło się sześć nowych funkcji związanych z operacjami na typach daty i czasu. Są to funkcje:

  • DATEFROMPARTS
  • SMALLDATETIMEFROMPARTS
  • DATETIMEFROMPARTS
  • DATETIME2FROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • EOMONTH

DATEFROMPARTS

Funkcja na podstawie trzech parametrów wejściowych typu int (rok, miesiąc, dzień) zwraca wartość typu date.

--2012-05-02
select DATEFROMPARTS(2012, 5, 2)

SMALLDATETIMEFROMPARTS

Funkcja na podstawie pięciu parametrów wejściowych typu int (rok, miesiąc, dzień, godzina, minuta) zwraca wartość typu smalldatetime.

--2012-05-02 12:15:00
select SMALLDATETIMEFROMPARTS(2012, 5, 2, 12, 15)

DATETIMEFROMPARTS

Funkcja na podstawie siedmiu parametrów wejściowych typu int (rok, miesiąc, dzień, godzina, minuta, sekunda, milisekunda) zwraca wartość typu datetime.

--2012-05-02 12:15:30.010
select DATETIMEFROMPARTS(2012, 5, 2, 12, 15, 30, 10)

DATETIME2FROMPARTS

Funkcja na podstawie ośmiu parametrów wejściowych typu int (rok, miesiąc, dzień, godzina, minuta, sekunda, część sekundy, precyzja) zwraca wartość typu datetime2. Znaczenie przedostatniego parametru zależy od podanej precyzji, np. dla precyzji 3 będą to milisekundy.

--2012-05-02 12:15:30.4567
select DATETIME2FROMPARTS(2012, 5, 2, 12, 15, 30, 4567, 4)

DATETIMEOFFSETFROMPARTS

Funkcja na podstawie dziesięciu parametrów wejściowych typu int (rok, miesiąc, dzień, godzina, minuta, sekunda, część sekundy, przesunięcie godzin, przesunięcie minut, precyzja) zwraca wartość typu datetimeoffset. Znaczenie siódmego parametru zależy od podanej precyzji, np. dla precyzji 3 będą to milisekundy.

--2012-05-02 12:15:30.4567 +02:15
select DATETIMEOFFSETFROMPARTS(2012, 5, 2, 12, 15, 30, 4567, 2, 15, 4)

EOMONTH

Funkcja jako parametr przyjmuje datę, dla której zwraca ostatni dzień danego miesiąca (typ date). Opcjonalnie można przekazać drugi parametr (typu int), który określa liczbę miesięcy jaka będzie dodana do daty podanej w pierwszym parametrze.

--2012-05-31
select EOMONTH(GetDate())

--2012-06-30
select EOMONTH(GetDate(), 1)

--2012-02-29
select EOMONTH(GetDate(), -3)

Więcej informacji o nowościach w SQL Server 2012: MSDN