Miesięczne archiwum: Sierpień 2017

T-SQL SQL Server Wyzwalacze (ang. Triggers)

Wyzwalacze (ang. Triggers) są specjalnymi rodzajami procedur składowanych, które uruchamiają się automatycznie w wyniku zaistnienia jakiegoś zdarzenia na serwerze bazy danych. Wyzwalacze mogą służyć do różnych rzeczy np. obsługi błędów, sprawdzania poprawności danych, modyfikacji widoków itp.

W SQL Server rozróżniamy kilka typów wyzwalaczy w zależności od zdarzenia.

  • Wyzwalacze DML (od Data Manipulating Language) – chodzi tutaj o polecenia dzięki którym „manipulujemy”/zarządzamy danymi INSERT, UPDATE i DELETE. Wyzwalacze tego typu możemy zastosować na tabeli lub na widoku.
  • Wyzwalacze DDL (od Data Definiton Language) – tutaj chodzi o polecenia które służą do definiowania struktury danych CREATE, ALTER, DROP.
  • Wyzwalacze Logon – (istnieją na pewno od wersji SQL Server 2012) tutaj mamy do czynienia ze zdarzeniami Logon, kiedy użytkownik nawiązuje połączenie (sesje) z bazą danych.

Wyzwalacze rozróżniamy także w zależności od typu. W niniejszym wpisie skupimu się na Before i After:

  • Befor – trigger zostanie uruchomiony przed zdarzeniem
  • After – trigger zostanie uruchomiony po zdarzeniu

Przykład definicji prostego Triggera

Do czego służą wyzwalacze

Triggery możemy wykorzystać do bardzo wielu rzeczy, oto kilka z ich zastosowań:

  • wyzwalacze bardzo często wykorzystujemy do definiowania warunków integralności danych w tabelach
  • do kaskadowego wykonania pewnych operacji
  • są również wykorzystywane do kontroli danych
  • mogą służyć także do implementacji złożonych reguł biznesowych
  • możemy je także wykorzystać od generowania określonych wartości w tabelach
  • itp.

Przykład zastosowania Triggera na bazie AdventureWorks2008R2

Stwórzmy najprostszy Trigger jaki chyba tylko można stworzyć. Przy próbie modyfikacji danych w tabeli Person.Person za pomocą poleceń INSERT, UPDATE, DELETE ma się uruchomić trigger o nazwie TriggerInfo który wyświetli tylko komunikat „Brawo Twój pierwszy Trigger zadziałał”.

 Zgodnie z przykładową strukturą Triggera powyżej utworzyłem kod który utworzy nasz Trigger na tabeli Person.Person.

Teraz uruchamiamy nasz kod klawiszem F5 lub klikamy na przycisk „EXECUTE” !!! PAMIĘTAJ !!! że musisz być ustawiony w odpowiednim kontekście, czyli w bazie AdventureWorks2008R2. Jeżeli wszystko przebiegnie poprawnie powinniśmy uzyskać komunikat „Command(s) completed successfully”. Już w tym momencie w gałęzi Databases -> AdventureWorks2008R2 -> Tables  -> Person.Person  -> Triggers , powinien pojawić się nowy Trigger o nazwie TriggerInfo. Jeżeli go nie ma musimy kliknąć prawym przyciskiem myszy na gałęzi Triggers i z menu kontekstowego wybrać opcję Refresh.

t-sql sql server wyzwalacze triggers

Teraz sprawdzimy czy nasz trigger działa. W tym celu zmodyfikujemy jeden rekord w tabeli Person.Person i zobaczymy czy nasz trigger się uruchomi. Pamiętasz, że nasz trigger powiniem się uruchomić w przypadku zastosowania na tabeli Person.Person którejś z instrukcji INSERT, UPDATE, DELETE. My zastosujemy instrukcję UPDATE i zmodyfikujemy wiersz z osobą Ken Sanchez.

Poniżej zrzut tabeli sprzed modyfikacji.

t-sql sql server wyzwalacze triggers

Poniżej instrukcja UPDATE którą zmodyfikujemy imię z Ken na KEN, czyli zmienimy tylko rozmiar liter.

Wykonyjemy nasz kod F5 i jeżeli wszystko poszło poprawnie powinniśmy otrzymać dwa komunikaty. Jeden o tym, że zmodyfikowaliśmy jeden wiersz

(1 row(s) affected)

A drugi komunikat z naszego Triggera.

Brawo Twój pierwszy Trigger zadzialal

Co obrazuje poniższy zrzut.

t-sql sql server wyzwalacze triggers

Mamy więc pewność, że nasz Trigger działa poprawnie.

Zapraszam do kolejnych wpisów na Anonco.PL


  SQL tutorial. AdventureWorks exercises SQL SERVER

T-SQL SQL Server Procedury składowane (Stored Procedures)

Dzisiaj zajmiemy się tematem procedury składowane, w literaturze niekiedy nazywane osadzonymi. Najprostsza definicja jaka przychodzi mi do głowy to taka, że procedury składowane to zbiór instrukcji SQL, które zapisujemy pod jedną nazwą. Następnie możemy odwołać się do danej procedury (wywołać ją) za pomocą tej nazwy wraz z parametrami wejściowymi które wcześniej definiujemy w procedurze. Struktura procedury składowanej (najprostsza jaką znalazłem) poniżej.

Definicja procedury składowanej (najprostszej jaką znalazłem).

ale np. przy próbie utworzenia nowej procedury składowanej w SQL Server otrzymamy automatycznie kod (bez kometarzy).

 

Przykład

Utwórz procedurę która będzie pobierała dwa parametry Imię , Nazwisko i w zależności od tych parametrów będzie podawała dane teleadresowa danej osoby: Adres, E-mail, Telefon, Typ Telefonu.

Uruchamiamy nasz kod F5. I jeżeli wszystko jest w porządku to otrzymamy komunikat „Command(s) completed successfully.”. W tym momencie w gałęzi Databases -> AdventureWorks2008R2 -> Programmability -> Stored Procedures powinna pojawić się procedura o nazwie ProcOsobyInformacje.

t-sql sql server procedury składowane stored procedures

Teraz możemy przetestować naszą procedurę, czy rzeczywiście zwraca poprawne wyniki. Wyszukamy informacje dotyczące osoby(lub osób) Terri Duffy. W tym celu klikamy prawym przyciskiem myszy (dalej PPM) i wybieramy opcję Execute Stored Procedure….

t-sql sql server procedury składowane stored procedures

W oknie Execute Procedure w tabelce ze zmiennymi (parametrami) w kolumnie Value wpisujemy odpowiednie wartości, dla imienia Terri, a dla nazwiska Duffy i nasz wybór potwierdzamy przyciskiem OK.

t-sql sql server procedury składowane stored procedures

Poniżej efekt wykonania naszej procedury.

t-sql sql server procedury składowane stored procedures

Widzimy, że SQL Server znalazł dwie osoby o takim samym imieniu i nazwisku, różniące się drugim imieniem. Teraz możemy sprawdzić czy jest to prawda w tabeli Person.Person zapytaniem wyszukującym osoby o tym imieniu i nazwisku.

Wynik uruchomienia naszego zapytania.

t-sql sql server procedury składowane stored procedures

Widzimy, że wszystko się zgadza.

Wywołać procedurę składowaną możemy także „ręcznie” wpisując w okno zapytania następujący kod.

Efekt będzie identyczny

SQL Server posiada także procedury już wbudowane oto najbardziej użyteczne z nich: sys.sp_addrolemember, sys.sp_adduser, sys.sp_catalogs, sys.sp_columns, sys.sp_databases itd. ale o tym w kolejnych wpisach.

Zapraszam do lektury kolejnych wpisów na Anonco.Pl

Pamiętaj:

  • Nie możesz utworzyć dwóch obiektów w bazie o takiej samej nazwie np. widok i procedura składowa. Jeżeli zdarzy się taka sytuacja w momencie próby utworzenia drugiego obiektu o tej samej nazwie co pierwszy otrzymasz komunikat „There is already an object named ‘tutaj_nazwa_obiektu’ in the database”. Dobrą praktyką jest nazywanie obiektów np. z przedrostkami np. Proc_ dla procedur lub View_ dla widoków itd.

  SQL tutorial. AdventureWorks exercises SQL SERVER

T-SQL SQL Server Korzystanie z widoków.

W jednym z poprzednich wpisów (link tutaj) utworzyliśmy widok który prezentuje listę osób z adresami, adresami e-mail i numerami telefonów (baza: AdventureWorks2008R2). Teraz napiszemy zapytanie które będzie korzystało z widoku jako źródła danych.

Przypomnę nasz widok  o nazwie OsobyInformacje zawiera dane z kilku tabel: Person.Person, Person.BusinessEntityAddress, Person.Address,  Person.EmailAddress, Person.PersonPhone, Person.PhoneNumberType. My napiszemy zapytanie które „wyciągnie” nam z naszego widoku: Imię, Nazwisko, Numer Telefonu i Typ Telefonu. Widzimy więc, że możemy dowolnie obrabiać dane z widoku i pobierać dane jakie tylko chcemy.

Kod zapytania poniżej

 

Zwróć uwagę, że w sekcji FROM jako źródło danych wskazujemy właśnie nasz widok o nazwie OsobyInformacje.

Poniżej efekt uruchomienia naszego zapytania.

t-sql sql server view use widok zapytanie

Jak widzimy uzyskaliśmy oczekiwany efekt.

Zapraszam do kolejnych wpisów.


  SQL tutorial. AdventureWorks exercises SQL SERVER

T-SQL SQL Server Tworzenie nowego widoku CREATE VIEW

Dzisiaj zajmiemy się poleceniem CreateView za pomocą którego tworzymy widoki. Widok to wirtualna tabela utworzona za pomocą zapytania mogąca zawierać dane z jednej lub wielu tabel. Z widoków możemy skorzystać w wielu sytuacjach oto kilka z nich:

  • Często korzystamy ze danych z wielu tabel (czynność powtarza się często i cyklicznie). W celu uproszczenia i przyspieszenia pracy z bazą danych możemy stworzyć sobie widok, który będzie „wirtualną tabelą” początkową do dalszej obróbki.
  • Wielu użytkowników korzysta z jednakowego początkowego zestawu danych (połączonych danych z wielu kolumn) ale później inaczej je obrabia.
  • Widok jest idealnym rozwiązaniem kiedy nie chcemy nadawać użytkownikom dostępu do tabel bazy, wtedy tworzymy potrzebny dla użytkownika widok i gotowe, użytkownicy mają dostęp do potrzebnego im widoku ale nie mogą nic zmodyfikować w tabelach źródłowych.

Składnia polecenia CREATEVIEW

 

Przykłady zastosowania polecenia CREATEVIEW

Przykład 1.
Utwórz widok o nazwie osoby na podstawie jednej tabeli Person.Person z bazy AdventureWorks 2008 R2.

Po uruchomieniu naszego zapytania przyciskiem F5 w oknie Messages  powinniśmy uzyskać komunikat Command(s) completed successfully. Teraz efekt naszego zapytania (utworzenia widoku), możemy także zobaczyć  w gałęzi Views w bazie AdventureWorks2008R2, powinien tak pojawić się nowy widok o nazwie Osoby (zrzut poniżej).

t-sql sql server create view

Jeżeli chcemy zobaczyć co zwraca nam nasz widok klikamy na nim prawym przyciskiem myszy (dalej PPM) i wybieramy opcję Select Top 1000 Rows.

t-sql sql server create view

I widzimy, że to jest dokładnie ten sam zestaw danych co w tabeli Person.Person.

t-sql sql server create view

Przykład 2.
Teraz stworzymy widok z danych z kilku tabel. Wyświetlimy dane dotyczące osób w bazie z ich adresami, adresami e-mail, telefonami (wraz z typem). W tym celu musimy złączyć dane z kilku tabel a mianowicie: Person.Person, Person.BusinessEntityAddress, Person.Address,  Person.EmailAddress, Person.PersonPhone, Person.PhoneNumberType. Ale nie chcemy wyświetlać wszystkich danych z powyższych kolumn. W naszym widoku ograniczymy się tylko do kolumn: ID osoby, Imię, Drugie Imię, Nazwisko, Miasto, Adres, E-mail, Telefon, Typ Telefonu.

Poniżej kod który utworzy nasz widok który nazwiemy OsobaInformacje

Analogicznie jak w przykładzie 1, po uruchomieniu naszego zapytania przyciskiem F5 w oknie Messages  powinniśmy uzyskać komunikat Command(s) completed successfully. W gałęzi Views w bazie AdventureWorks2008R2, powinien tak pojawić się nowy widok o nazwie OsobyInformacje (zrzut poniżej).

t-sql sql server create view

Jeżeli chcemy zobaczyć co zwraca nam nasz widok klikamy na nim prawym przyciskiem myszy (dalej PPM) i wybieramy opcję Select Top 1000 Rows. Poniżej efekt naszego zapytania.

t-sql sql server create view

 Teraz możemy korzystać z naszych widoków jak ze zwykłych tabel.

Zapraszam do kolejnych wpisów.


  SQL tutorial. AdventureWorks exercises SQL SERVER

ASP.NET MVC Przesyłanie (przekazywanie) danych z kontrolera do widoku z użyciem funkcji ViewBag

ASP.NET MVC Przesyłanie (przekazywanie) danych z kontrolera do widoku z użyciem funkcji  ViewBag

W niniejszym wpisie pokaże jak, za pomocą funkcji ViewBag, można przesłać (przekazać) dane z kontrolera do widoku. Stworzymy listę kilku pracowników firmy i prześlemy ją do widoku w celu wyświetlenia.

W tym celu tworzymy nowy projekt ASP.NET Web Application (jak to zrobić przeczytasz <<<tutaj>>>)

Kolejny krok to stworzenie modelu danych. Czyli musimy określić jakie dane i jakiego typu będziemy przechowywać. W tym celu klikamy prawym przyciskiem myszy (dalej PPM) na katalogu Models w oknie Solution Explorer i wybieramy opcję Add ® Class

asp.net mvc kontroler widok viewbag

Następnie w oknie Add New Item wybieramy opcję Class, w polu Name wprowadzamy nazwę pracownik i potwierdzamy przyciskiem Add.

asp.net mvc kontroler widok viewbag

Teraz określimy jakie dane o pracowniku będziemy przechowywać. W oknie solution explorer rozwijamy gałąź Models gdzie powinien znajdować się plik pracownik.cs, klikamy na ten plik. W ognie głównym Visual Studio otworzy się nam nowa zakładka pracownik.cs w którym zobaczymy następujący kod.

Uzupełniamy klasę pracownik zgodnie z poniższym listingiem

Widzimy tutaj, że będziemy przechowywać takie informacje jak imię, nazwisko, stanowisko, dzial, miejscowość i pensja. Oczywiście wszystkie pola „nie liczbowe” są typu string a pole pensja ma przechowywać liczby.

OK w tym momencie mamy gotowy model danych.

Teraz musimy oprogramować metodę w kontrolerze która będzie przekazywała dane do widoku.

W oknie Solution Explorer przechodzimy do katalogu Controllers, rozwijamy całą gałąź i klikamy na HomeController.cs. W oknie głównym Visual Studio pojawi się nowa zakładka HomeControllers.cs. Odnajdujemy akcję ActionResult i w niej będziemy tworzyć naszą listę pracowników. Oczywiście dane do listy powinniśmy „zaciągać” z bazy danych ale nie to jest tematem tego wpisu i żeby nie zaciemniać głównego wątku krótką listę utworzymy ręcznie. Utworzymy listę 4 pracowników z przykładowymi danymi co przedstawia kod poniżej.

W tym momencie mamy już model danych, zgodnie z którym utworzyliśmy listę pracowników. Teraz wystarczy tylko przekazać te dane do widoku i wyświetlić ale wcześniej należy przypisać naszą listę jako obiekt do funkcji ViewBag.

Teraz obiekt ViewBag.pracownicy zawiera naszą listę pracowników.

Żeby wyświetlić tą listę zajmiemy się widokiem  Index więc zaraz po uruchomieniu aplikacji powinniśmy otrzymać listę pracowników na głównym widoku aplikacji. W oknie Solution Explorer klikamy w katalog Views następnie w katalog Home (bo w tym kontrolerze tworzyliśmy naszą listę) i wybieramy widok Index.cshtml. Nasz kod umieścimy na początku strony. Żeby wyświetlić listę najlepiej jest zrobić to w pętli a najlepiej do naszego zadania nadaje się pętla foreach (o której możesz przeczytać <<<TUTAJ>>>).  Przydatną cechą tej pętli jest to, że nie trzeba myśleć o warunku wyjścia z pętli. Foreach przeskoczy po wszystkich elementach naszej listy i zakończy działanie. Na początek stworzymy więc kod.

@{ foreach (var Item in Collection) { } }

Utworzymy więc element <div> w którym zdefiniujemy pętlę. Pamiętajmy o tym że żeby Visual Studio wiedziało, że teraz chemy korzystać z C# to musimy mu o tym powiedzieć i do tego służy kod.

Teraz zajmijmy się samą pętlą. Aby przyspieszyć tworzenie pętli wpisujemy słówko foreach i przyciskamy dwa razy przycisk tab. W tym momencie powinna nam się utworzyć automatycznie struktura pętli foreach (jak poniżej).

Naszą kolekcją jest lista pracownicy, a ta lista została przekazana do ViewBag.pracownicy więc słówko Collection w naszym wzorze musimy zastąpić ViewBag.pracownicy. Natomiast żeby „dobrać” się do poszczególnych elementów listy musimy posłużyć się modelem pracownik, zmieniamy więc słówko Item we wzorze na pracownik. Teraz wewnątrz pętli wyświetlimy poszczególnych pracowników w tagach paragraf. Jeżeli chcemy wyświetlić jakieś pole np. imię pracownika posługujemy się taką składnią @pracownik.Imie, czyli odwołujemy się, za każdym przebiegiem pętli, do pola Imię w obiekcie  pracownik. Oczywiście nie musimy wyświetlać wszystkich informacji o pracowniku które są w naszej liście. Dla celów tego przykładu wyświetlimy tylko imię i nazwisko pracowników z listy, kod poniżej.

@pracownik.Imie, @pracownik.Nazwisko

} }

Nasz projekt uruchamiamy wciskając przycisk F5. Jeżeli nie zrobiliśmy żadnego błędu zostanie uruchomiona nasza domyśla przeglądarka internetowa i naszym oczom ukaże się strona, jak na załączonym obrazku, z listą naszych pracowników.

asp.net mvc kontroler widok viewbag

Dziękujemy za czytanie naszych wpisów, mamy nadzieję, że przyczyniamy się choć troszeczkę do pogłębiania przez Was wiedzy. Pozdrawiamy i zachęcamy do czytania innych naszych wpisów.