Moi drodzy,
Postanowiłem utworzyć nowy cykl zawierający serię "skrótów tematycznych" dla wybranych platform, zawierających podręczne "ściągawki".
Seria będzie dostępna na osobnych podstronach bloga, a w zawartości będzie można znaleźć krótki wstęp do wybranej technologii oraz wybrane, najczęściej stosowane funkcje wraz ze składnią oraz przykładami ich użycia
Cykl będzie regularnie rozbudowywany. Jeśli macie jakieś propozycje dodania nowych elementów - piszcie w komentarzach.
Na pierwszy ogień: T-SQL .Zapraszam - zawsze i wszędzie!
Blog poświęcony wszelkim zagadnieniom z dziedziny IT (głównie z zakresu programowania i baz danych).
środa, 20 sierpnia 2014
wtorek, 19 sierpnia 2014
T-SQL: Tabele tymczasowe, zmienne typu TABLE, różnice
Język T-SQL umożliwia tworzenie tabel tymczasowych oraz zmiennych typu TABLE.
Tabele tymczasowe dzielimy na:
#lokalne - do których dostęp posiadamy w ramach otwartej sesji
##globalne - dostępne w ramach SERWERA (czyli będzie ona dostępna również w ramach innej bazy).
@zmienne typu TABLE - dostępne od chwili wywołania do zakończenia działania skryptu
Pierwsza różnica pomiędzy tabelami tymczasowymi a zmiennymi: tabele tymczasowe TWORZYMY, a zmienne DEKLARUJEMY.
Składnia tworzenia tabel tymczasowych:
CREATE TABLE #NAZWA_TABELI
(
kolumna1 typ,
kolumna2 typ,
...
kolumnaN, typ )
warto też pamiętać, że możemy od razu przekazać dane z zapytania do tabeli tymczasowej, bez konieczności podawania jej definicji, dodając przed FROM magiczne "INTO #TABELA", np
CREATE TABLE #TEMP
(
NAME NVARCHAR(100)
)
SELECT
name, surname, age
FROM PERSON
możemy zastąpić
SELECT
name, surname, age
INTO #TEMP
FROM PERSON
Wówczas tabela tymczasowa utworzy się sama, a jej kolumny będą odpowiadały nazwom kolumn zwracanych przez kwerendę. Należy wówczas pamiętać o dwóch podstawowych zasadach:
W przypadku deklarowania zmiennej typu TABLE:
DECLARE @NAZWA_TABELI TABLE
(
kolumna1 typ,
kolumna2 typ,
...
kolumnaN, typ
)
Tabele tymczasowe wprost świetnie nadają się do importów i migracji danych pomiędzy środowiskami oraz analizy danych, gdzie operujemy już na jednym, stałym, wstępnie ograniczonym "wiaderku danych", do których możemy co jakiś czas później się ponownie odwołać.
Zmienne natomiast mają dość krótki żywot (do zakończenia działania skryptu) :) Niemniej jednak są w teorii bardziej wydajne, ponieważ w przypadku SQL SERVER przechowywane są w pamięci operacyjnej do chwili zakończenia działania skryptu. Jeśli zaraz po wykonaniu zapytania dane nie będą nam już potrzebne, zmienna może okazać się niezłym pomysłem. Są one szczególnie fajne przy mniejszych zbiorach, np. gdy nie chcemy operować korzystać z kursorów do poruszania się po danych (a kysz!) - wówczas odpowiednia pętla po naszym "mikro zbiorze" i po krzyku.
Przy tabelach tymczasowych powinniśmy dodatkowo pamiętać o konieczności ich usunięcia, gdy nie są już potrzebne.
Jeśli chcielibyśmy posiadać dostęp do naszej tabeli tymczasowej globalnie (np. migracja danych, głębsze analizy itd), oczywistym jest, że powinniśmy stosować ##, jednakże tylko w sytuacji, kiedy faktycznie będziemy korzystali z tabeli tymczasowej później.
Warto też pamiętać, że tabele tymczasowe możemy wykorzystać do złączeń (JOIN). Możemy dzięki temu łączyć już przefiltrowany zbiór pewnych danych z tabeli/tabel (np słowników) w formie #tabeli_tymczasowej, z głównym źródłem - praktyczne przy dużych słownikach, z których interesuje nas niewielki ich fragment.
Zmienne przechowywane są w pamięci (chyba że przekroczą określony rozmiar - wówczas trafią do tempdb), więc w teorii powinny być szybsze.
Nie przeprowadzałem wystarczającej ilości prób, aby jednoznacznie powiedzieć, kiedy stosować @, a kiedy #.
Osobiście uważam, że optymalnym rozwiązaniem będzie przetestowanie "na żywo" i po prostu - wybranie szybszego rozwiązania per-case. Osobiście wielokrotnie byłem zaskoczony rezultatem działania. Od razu przypomina mi się cudowny cytat, który kiedyś gdzieś usłyszałem:
Tabele tymczasowe dzielimy na:
#lokalne - do których dostęp posiadamy w ramach otwartej sesji
##globalne - dostępne w ramach SERWERA (czyli będzie ona dostępna również w ramach innej bazy).
@zmienne typu TABLE - dostępne od chwili wywołania do zakończenia działania skryptu
Pierwsza różnica pomiędzy tabelami tymczasowymi a zmiennymi: tabele tymczasowe TWORZYMY, a zmienne DEKLARUJEMY.
Składnia tworzenia tabel tymczasowych:
CREATE TABLE #NAZWA_TABELI
(
kolumna1 typ,
kolumna2 typ,
...
kolumnaN, typ )
warto też pamiętać, że możemy od razu przekazać dane z zapytania do tabeli tymczasowej, bez konieczności podawania jej definicji, dodając przed FROM magiczne "INTO #TABELA", np
CREATE TABLE #TEMP
(
NAME NVARCHAR(100)
)
SELECT
name, surname, age
FROM PERSON
możemy zastąpić
SELECT
name, surname, age
INTO #TEMP
FROM PERSON
Wówczas tabela tymczasowa utworzy się sama, a jej kolumny będą odpowiadały nazwom kolumn zwracanych przez kwerendę. Należy wówczas pamiętać o dwóch podstawowych zasadach:
- Wszystkie zwracane kolumny w kwerendzie muszą posiadać nazwy
- Nazwy kolumn muszą być unikatowe (nie mogą się powtórzyć)
W przypadku deklarowania zmiennej typu TABLE:
DECLARE @NAZWA_TABELI TABLE
(
kolumna1 typ,
kolumna2 typ,
...
kolumnaN, typ
)
Zastosowanie
Zapewne niejednokrotnie pojawiała się potrzeba tworzenia tabel "na chwilę". Możemy oczywiście tworzyć pewne tabele, nazwać je "tymczasowe" i wierzyć, że kiedyś je usuniemy :) Niemniej jednak takie rozwiązanie jest mało eleganckie, niejednokrotnie później pozostają dziwne byty, których boimy się usunąć, a które w rzeczywistości utworzyliśmy na 5 minut...Tabele tymczasowe wprost świetnie nadają się do importów i migracji danych pomiędzy środowiskami oraz analizy danych, gdzie operujemy już na jednym, stałym, wstępnie ograniczonym "wiaderku danych", do których możemy co jakiś czas później się ponownie odwołać.
Zmienne natomiast mają dość krótki żywot (do zakończenia działania skryptu) :) Niemniej jednak są w teorii bardziej wydajne, ponieważ w przypadku SQL SERVER przechowywane są w pamięci operacyjnej do chwili zakończenia działania skryptu. Jeśli zaraz po wykonaniu zapytania dane nie będą nam już potrzebne, zmienna może okazać się niezłym pomysłem. Są one szczególnie fajne przy mniejszych zbiorach, np. gdy nie chcemy operować korzystać z kursorów do poruszania się po danych (a kysz!) - wówczas odpowiednia pętla po naszym "mikro zbiorze" i po krzyku.
Który rodzaj wybrać?
Zmienne typu TABLE powinniśmy stosować, gdy przekazujemy tablicę jako parametr do procedury czy funkcji. Ponadto zmienna "znika" po wykonaniu skryptu.Przy tabelach tymczasowych powinniśmy dodatkowo pamiętać o konieczności ich usunięcia, gdy nie są już potrzebne.
Jeśli chcielibyśmy posiadać dostęp do naszej tabeli tymczasowej globalnie (np. migracja danych, głębsze analizy itd), oczywistym jest, że powinniśmy stosować ##, jednakże tylko w sytuacji, kiedy faktycznie będziemy korzystali z tabeli tymczasowej później.
Warto też pamiętać, że tabele tymczasowe możemy wykorzystać do złączeń (JOIN). Możemy dzięki temu łączyć już przefiltrowany zbiór pewnych danych z tabeli/tabel (np słowników) w formie #tabeli_tymczasowej, z głównym źródłem - praktyczne przy dużych słownikach, z których interesuje nas niewielki ich fragment.
Wydajność
Pod względem wydajności, nie istnieje uniwersalne uzasadnienie, kiedy powinniśmy korzystać z tabel tymczasowych # / ##, a kiedy ze zmiennych. W google można odnaleźć masę rankingów na temat szybkości działania.Zmienne przechowywane są w pamięci (chyba że przekroczą określony rozmiar - wówczas trafią do tempdb), więc w teorii powinny być szybsze.
Nie przeprowadzałem wystarczającej ilości prób, aby jednoznacznie powiedzieć, kiedy stosować @, a kiedy #.
Osobiście uważam, że optymalnym rozwiązaniem będzie przetestowanie "na żywo" i po prostu - wybranie szybszego rozwiązania per-case. Osobiście wielokrotnie byłem zaskoczony rezultatem działania. Od razu przypomina mi się cudowny cytat, który kiedyś gdzieś usłyszałem:
W teorii, teoria i praktyka to to samo.
W praktyce, tak nie jest.
poniedziałek, 18 sierpnia 2014
#Quick: zabawa kursorem (myszki), oraz o rozdzielczości ekranu
info
Poniższy artykuł powstał wyłącznie do celów edukacyjnych, a autor (czyli ja) nie ponosi odpowiedzialności za wykorzystanie informacji zawartych w poniższym tekście niezgodnie z prawem lub etyką zawodową.
Położenie kursora
Aby odczytać lub zmienić położenie kursora, wystarczy odwołać się do Cursor.Position :
//odczytanie położenia
int px = Cursor.Position.X;
int py = Cursor.Position.Y;
//zmiana położenia, trololo :)
Cursor.Position = new Point(100, 100);
Ograniczamy wolność kursora ;)
Ciekawym atrybutem przy Cursor jest Clip.
Umożliwia on ustalenie granic, w których możemy operować naszym kursorem. W Windows taką granicą domyślnie jest ustalona rozdzielczość. Spróbujmy ograniczyć możliwość poruszania się naszego kursora myszki wyłącznie do obszaru naszego okna (uwzględniając, aby nie możliwe było zamknięcie/zmiana rozmiaru naszego okna), jak na poniższym zrzucie:
Oczywiście możemy zmienić atrybut formatki form.FormBorderStyle na odpowiedni, ale wówczas tracimy na wyglądzie okienka i nie ma zabawy, rozwiązanie stałoby się mniej uniwersalne.
przepis:
//pobieramy szybko wymiar naszego okna:
Rectangle currentwindow = this.Bounds;
//zmniejszamy obszar do zabawy oknie:
//35 - przybliżony rozmiar górnego paska
currentwindow.Height = this.Height - 35;
//10: aby uniemożliwić zmianę rozmiaru okna
currentwindow.Width = this.Width - 10;
/* Ustawiamy położenie początkowe
* (przesunięte o kilka pikseli,
* aby nie można było zmieniać rozmiaru
* z lewej/góry */
currentwindow.Location = new Point(currentwindow.Location.X+5, currentwindow.Location.Y + 30);
//podpinamy punkt początkowy granic i rozmiar
Cursor.Clip = new Rectangle(currentwindow.Location, currentwindow.Size);
możemy pobrać sobie gotowe granice np do rozdzielczości ekranu głównego:
Rectangle resolution = Screen.PrimaryScreen.Bounds;
..lub wskazując na dowolny, poruszając się po kolekcji AllScreens:
Rectangle r2 = Screen.AllScreens[1].Bounds;
Zastosowanie
Zmianę/odczyt położenia możemy wykorzystać m.in. przy:- podpowiedzi użytkownikowi, gdzie w danym momencie powinien kliknąć użytkownik
- tworzeniu aplikacji do zdalnego sterowania komputerem (a'la TeamViewer)
- grach / programach graficznych
- programach egzaminacyjnych
- aplikacjach dla osób niepełnosprawnych, ułatwiających pracę
- POS
- ...z okazji 1 kwietnia :)
Ograniczenia
Granice kursora zostaną "zresetowane" m.in. po:- zamknięciu programu
- zmianie okna wiodącego
- zmianie podświetlenia okna (np przycisk start, alt+tab, etc)
- zmianie rozmiaru/położenia okna
środa, 6 sierpnia 2014
DataView - ciekawa zabawka
Tworząc aplikacje bazodanowe (pomijam wszelkiego rodzaju "ulepszacze") praktycznie zawsze pobieramy dane, które wcześniej czy później wyświetlimy w tabelce (np dataGridView).
Dane możemy pobrać wygodnie np za pomocą SQLDataAdaptera do DataTable lub DataSet, a następnie przerzucić na nasz dataGrid.
Jeśli chcemy filtrować zbiór, możemy zrealizować to na wiele sposobów, m.in.:
Jedno i drugie rozwiązanie jest jak najbardziej akceptowalne (zależnie oczywiście od sytuacji). Natomiast mimo wszystko, fajnym wynalazkiem jest DataView.
Zastosowanie:
Do pobrania danych (wszystkich) stworzyliśmy sobie procedurę SQL:
getServerList
w aplikacji pobraliśmy sobie dane i "wrzuciliśmy" do datagrida:
private void GetServers()
{
SqlConnection con = new SqlConnection(/*nasz ConnectionString*/);
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("exec getServerList", con);
DataTable dt = new DataTable();
//wypełniamy DataTable danymi za pomocą metody Fill:
da.Fill(dt);
//wypełniamy (wskazujemy źródło) naszym dataTable
dataGridView1.DataSource=dt;
}
Działa, pobraliśmy dane. Ale chcielibyśmy móc filtrować wyniki po miejscowości i obsłużmy zdarzenie
Dodajemy Textbox, po drodze dorzucamy DataView i metodę do obsługi filtrowania i obsługujemy zdarzenie na TextChanged...
DataView dataViewServers;
private void GetServers()
{
SqlConnection con = new SqlConnection(/*nasz ConnectionString*/);
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("exec getServerList", con);
DataTable dt = new DataTable();
da.Fill(dt);
//upakujmy nasz zbiór danych DataView...
dataViewServers = new DataView(dt);
//...i podłączmy DataView zamiast DataTable dataGridView1.DataSource=dataViewServers;
}
//utwórzmy dla porządku metodę odpowiedzialną za filtrowanie
private void FilterServers()
{
//filtrowanie jak w SQL...
dataViewServers.RowFilter = "Location_City like '"+textBoxCity.Text+"%'";
}
//obsłużymy zdarzenie naszego textboxa, aby filtrowało "na żywo":
private void textBoxCity.TextChanged(object sender, EventArgs e)
{
filterServers();
}
Rezultat: mamy bardzo fajną, szybką i dynamiczną wyszukiwarkę. przy czym samo wyszukiwanie odbywa się po stronie aplikacji - operując na pobranej już kolekcji. Zawsze możemy dodać kolejne warunki do filtrowania danych.
DataView jest bardzo wygodnym dodatkiem, umożliwiającym szybkie filtrowanie danych po stronie klienta, na pobranym już zbiorze danych. Nie musimy ręcznie filtrować danych, czy też pobierać na nowo zbioru z serwera, co w mniejszym lub większym stopniu wpływa na wydajność pracy.W dodatku DataView działa zaskakująco szybko, a sama metoda filtrowania jest po prostu - wygodna.
Dane możemy pobrać wygodnie np za pomocą SQLDataAdaptera do DataTable lub DataSet, a następnie przerzucić na nasz dataGrid.
Jeśli chcemy filtrować zbiór, możemy zrealizować to na wiele sposobów, m.in.:
- Za każdym razem pobierać "świeże", przefiltrowane dane z bazy - tylko wtedy dodatkowo obciążamy system, a w chwili kiedy pobieramy dane z dość statecznego zbioru (np słownika), nie zawsze będzie to "eleganckie" rozwiązanie.
- Filtrować dane w naszej kolekcji (np DataTable, Combobox, DataGridView, etc) - wszystko fajnie, ale musimy to ręcznie oprogramować, co bywa męczące, a w dodatku możemy być gorsi z algorytmów, ale dobrzy z SQL'a... co wtedy?
Jedno i drugie rozwiązanie jest jak najbardziej akceptowalne (zależnie oczywiście od sytuacji). Natomiast mimo wszystko, fajnym wynalazkiem jest DataView.
System.Data.DataView
DataView osobiście nazywam "nakładką" na zbiór danych (sam w sobie nie przechowuje danych). Umożliwia m.in. sortowanie, edycję wyszukiwanie/filtrowanie, etc (więcej informacji pod tym linkiem ) W artykule opowiem trochę o filtrowaniu (oczywiście na przykładzie) :)Zastosowanie:
- Pobranie kompletu danych (czasami działa szybciej niż przy przefiltrowanych zbiorach - o tym również wspomnę :) )
- upakowanie danych naszym DataView (amulet szczęścia :) )
- Filtrowanie danych po stronie klienta (Serwer odpoczywa)
Koniec teorii, czas na praktykę.
załóżmy, że mamy prostą tabelkęTBLServers |
---|
ID |
ServerName |
OperatingSystem |
IP_Address |
Location_City |
Do pobrania danych (wszystkich) stworzyliśmy sobie procedurę SQL:
getServerList
w aplikacji pobraliśmy sobie dane i "wrzuciliśmy" do datagrida:
private void GetServers()
{
SqlConnection con = new SqlConnection(/*nasz ConnectionString*/);
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("exec getServerList", con);
DataTable dt = new DataTable();
//wypełniamy DataTable danymi za pomocą metody Fill:
da.Fill(dt);
//wypełniamy (wskazujemy źródło) naszym dataTable
dataGridView1.DataSource=dt;
}
Działa, pobraliśmy dane. Ale chcielibyśmy móc filtrować wyniki po miejscowości i obsłużmy zdarzenie
Dodajemy Textbox, po drodze dorzucamy DataView i metodę do obsługi filtrowania i obsługujemy zdarzenie na TextChanged...
DataView dataViewServers;
private void GetServers()
{
SqlConnection con = new SqlConnection(/*nasz ConnectionString*/);
SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("exec getServerList", con);
DataTable dt = new DataTable();
da.Fill(dt);
//upakujmy nasz zbiór danych DataView...
dataViewServers = new DataView(dt);
//...i podłączmy DataView zamiast DataTable dataGridView1.DataSource=dataViewServers;
}
//utwórzmy dla porządku metodę odpowiedzialną za filtrowanie
private void FilterServers()
{
//filtrowanie jak w SQL...
dataViewServers.RowFilter = "Location_City like '"+textBoxCity.Text+"%'";
}
//obsłużymy zdarzenie naszego textboxa, aby filtrowało "na żywo":
private void textBoxCity.TextChanged(object sender, EventArgs e)
{
filterServers();
}
Rezultat: mamy bardzo fajną, szybką i dynamiczną wyszukiwarkę. przy czym samo wyszukiwanie odbywa się po stronie aplikacji - operując na pobranej już kolekcji. Zawsze możemy dodać kolejne warunki do filtrowania danych.
Podsumowanie
DataView jest bardzo wygodnym dodatkiem, umożliwiającym szybkie filtrowanie danych po stronie klienta, na pobranym już zbiorze danych. Nie musimy ręcznie filtrować danych, czy też pobierać na nowo zbioru z serwera, co w mniejszym lub większym stopniu wpływa na wydajność pracy.W dodatku DataView działa zaskakująco szybko, a sama metoda filtrowania jest po prostu - wygodna.
poniedziałek, 4 sierpnia 2014
Quick: #naskróty Wyszukiwanie tabel wg nazw kolumn
Każdy, kto rozpoczyna pracę z nieznaną bazą danych, często czuje się jak poszukiwacz karteczek w lesie (gdzie Slenderem jest szef oczekujący szybkich rezultatów "na wczoraj" :) ).
Załóżmy, że nasz szef chciałby abyśmy ("migusiem") odnaleźli informację na temat klienta, a przed nos dostaliśmy jego adres e-mail... i do roboty. Ok, tylko że to jest nasz pierwszy dzień w pracy, a baza liczy kilka tysięcy tabel....
Na podstawie zadania wiemy, że powinniśmy znaleźć tabelę, która posiada kolumnę z adresem e-mail. Ale przecież SQL Management Studio sam w sobie umożliwia wyszukiwanie TABEL, a instalowanie czegokolwiek na komputerze jest monitorowane, poza tym i tak nie posiadamy uprawnień...
...Ale posiadamy fajny skrypt, który przeszuka strukturę w tabelach systemowych naszej bazy:
SELECT
sysobjects.name as [tabela],
syscolumns.name as [nazwa kolumny],
systypes.name as [typ]
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'
and syscolumns.name like '%mail%'
ORDER BY sysobjects.name,syscolumns.colid
Email
E_mail
contactemail
ContactEMail
EmailAddress
etc...
A czasami po prostu "mail".Różne szkoły.
Gotowiec wyszukuje tabele, które zawierają kolumnę o wybranej nazwie.
Później wystarczy już sprawdzić w bardziej sensownych tabelach, czy znajdujemy to czego szukamy i w optymalnym (z reguły) przypadku, przesłać maila z wynikami. Oczywiście wszystko zależy od struktury bazy; jeśli np parametry rozwijane są "w dół" (wierszami), albo przechowywany jest XML, wynalazek nie zadziała (chyba że poszukamy po XML, Param, Value, etc).
...Nie ma za co... :]
Załóżmy, że nasz szef chciałby abyśmy ("migusiem") odnaleźli informację na temat klienta, a przed nos dostaliśmy jego adres e-mail... i do roboty. Ok, tylko że to jest nasz pierwszy dzień w pracy, a baza liczy kilka tysięcy tabel....
Na podstawie zadania wiemy, że powinniśmy znaleźć tabelę, która posiada kolumnę z adresem e-mail. Ale przecież SQL Management Studio sam w sobie umożliwia wyszukiwanie TABEL, a instalowanie czegokolwiek na komputerze jest monitorowane, poza tym i tak nie posiadamy uprawnień...
...Ale posiadamy fajny skrypt, który przeszuka strukturę w tabelach systemowych naszej bazy:
SELECT
sysobjects.name as [tabela],
syscolumns.name as [nazwa kolumny],
systypes.name as [typ]
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'
and syscolumns.name like '%mail%'
ORDER BY sysobjects.name,syscolumns.colid
Czemu '%mail%'?
Ponieważ powinniśmy założyć, że element którego szukamy może nazywać się:E_mail
contactemail
ContactEMail
EmailAddress
etc...
A czasami po prostu "mail".Różne szkoły.
Gotowiec wyszukuje tabele, które zawierają kolumnę o wybranej nazwie.
Później wystarczy już sprawdzić w bardziej sensownych tabelach, czy znajdujemy to czego szukamy i w optymalnym (z reguły) przypadku, przesłać maila z wynikami. Oczywiście wszystko zależy od struktury bazy; jeśli np parametry rozwijane są "w dół" (wierszami), albo przechowywany jest XML, wynalazek nie zadziała (chyba że poszukamy po XML, Param, Value, etc).
...Nie ma za co... :]
X3
Subskrybuj:
Posty (Atom)