ADO.NET – podłączenie się do bazy SQL Server w C#

Tworzenie aplikacji w środowisku .NET, które wykorzystują bazy danych (a zwłaszcza SQL Server) jest stosunkowo proste. Na początek potrzebujemy Visual Studio (najlepiej darmowy express C#) oraz SQL Server (też najlepiej express) lub SQL Server Client (w przypadku gdy SQL Server nie jest zainstalowany na komputerze na którym prowadzimy prace developerskie).

Ten wpis zawiera dość długi wstęp dla osób o niskim obeznaniu ze środowiskiem VS, dlatego jeśli jesteś bardziej zaawansowanym użytkownikiem przewiń proszę tekst.

Gotową klasę można pobrać tutaj

Na początek musimy utworzyć projekt w VS, najlepiej wybrać Windows Form Application (jeśli korzystamy z VS innego niż Express proszę zaznaczyć język C#).

Poniższe Screenshoty pochodzą z wersji VS 2010 Professional.

Utworzenie nowego projektu:

Po utworzeniu projektu naszym oczom powinna ukazać się formatka (form1) nazywana skrótowo formą:

Z lewej strony znajduje się toolbox (tam znajdziemy różne kontrolki). Metodą przeciągnij upuść można na formę bezpośrednio z Toolboxa przeciągać wszystkie dostępne kontrolki. Proponuję zacząć od Button’a (guzika).

Button znajduje się nad zaznaczonym na screenshocie checkbox’em. Kontrolki są posortowane alfabetycznie.


Finalnie na formie powinien pojawić się button1 nasz guzik. Z prawej strony ekranu powinien być object(solution) explorer i properties.

Po jednokrotnym kliknięciu guzika na formie w properties zobaczymy jego właściwości. Wszystkie te właściwości są dostępne z poziomu kodu (do którego przejdziemy za chwilę). Proponuję zwrócić uwagę na właściwość (Name) jest to nazwa naszego obiektu. Możemy ją zmienić – co zalecam uczynić. W momencie gdy guzików pojawi się więcej będzie trudno nad tym zapanować. Ja mój guzik nazwę pobieracz. Jak widać po zmianie nazwy opis guzika w ogóle się nie zmienia, za napis na guziku odpowiada właściwość Text, można ją dowolnie zedytować. Zalecam także przejrzenie innych dostępnych właściwości.
Gdy już skonfigurujemy guzik tak by nam odpowiadał możemy w niego 2 razy kliknąć, otworzony nam się okno z kodem odpowiedzialnym za stworzenie formy, oraz pojawi się tam metoda obsługująca zdarzenie (event) kliknięcia myszą w guzik.
cały kod powinien wyglądać +/- tak:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{

}
}
}

Omawiając kod od samej góry:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

Najprościej rzecz ujmując jest poinformowanie jakich przestrzeni nazw chcemy używać, tworząc obiekt możemy napisać w kodzie System.Data.SqlClient.SqlCommand sqlc = new System.Data.SqlClient.SqlCommand(); albo możemy dodać przestrzeń nazw using System.Data.SqlClient i wtedy w kodzie napiszemy SqlCommand sqlc = new SqlCommand();. Tak więc using wykorzystujemy tylko dla skrócenia zapisów i sprawienia by były bardziej czytelne.
Kolejno mamy:

namespace WindowsFormsApplication2
{

jest to deklaracja naszej własnej przestrzeni nazw. Standardowo VS tworzy przestrzeń nazw tożsamą z nazwą aplikacji/projektu/solucji.

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

Teraz mamy deklarację publicznej klasy (public) o nazwie Form1 dziedziczącej (:) z ogólnej klasy Form. Pojawia się jeszcze słowo partial, które informuje kompilator że ta klasa może być deklarowana w wielu plikach. Ten zabieg pozwala zaprezentować nam kod który rzeczywiście jest dla nas istotny, podczas przeciągania kolejnych obiektów na formę VS tworzy wiele linii kodów które są przed nami ukrywane, dla zachowania czystości i pewnej czytelności.

public Form1() to konsturktor klasy Form1, konstruktor poznajemy zawsze po tym że nazywa się tak samo jak klasa oraz konstruktor nie ma definicji tego co zwraca (brak nazwy typu obiektu i słowa void przed nazwą metody). W konstruktorze mamy jedną funkcję która inicjuje nam kontrolki na formie.

Poniżej zostaje nam obsługa kliknięcia w guzik:

private void pobieracz_Click(object sender, EventArgs e)
{

}

moj guzik nazywa się pobierz więc VS automatycznie utworzyło funkcję według konwencji nazewnictwa ze środowiska .NET, pomiędzy klamrami nalezy dopisać kod który zostanie wykonany po kliknięciu guzika. Jako że poniższy post jest de facto o bazach danych a nie podstawach programowania w .NET przejdziemy do pisania kodu który umożliwi nam podłączenie się do bazy danych.

Dla zachowania czystości kodu proponuję utworzyć nową klasę, aby to zrobić należy z górnego menu wyrać project/add new item, albo add class.

VS utworzy nam klasę nazwaną tak samo jak plik w przestrzeni nazw naszej aplikacji:
namespace WindowsFormsApplication2
{
class jodb
{
}
}

Na samej górze w sekcji using warto (należy) dodać:

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;

Teraz musimy utworzyć konstruktor, warto się zastanowić w tym miejscu w jaki sposób chcemy łączyć się z bazą danych. Mam na myśli tutaj autentykację windows(domenową) lub autentykację SQL Server. Standardowo SQL Server instalowany jest z włączoną autoryzacją windows. Oznacza to że otwierając połączenie z SQL Serverem zostanie przekazany nasz login do komputera i na tej podstawie zostaną nam przyznane uprawnienia. Autoryzacja SQL Server standardowo jest wyłączona, można ją włączyć w dość prosty sposób (opiszę to w osobnym poście).
Kod który stworzę pozwala na połączenie się jedną i drugą metodą.

W klasie jodb należy stworzyć konstruktory, które w ostatecznym wyglądzie powinny obsłużyć podłączenie się do bazy. Na początek proponuję by kod klasy wyglądał tak:

class jodb
{

public jodb(string user, string pass, string instance, string dbdir)
{

}

public jodb(string instance, string dbdir)
{

}
}

Mimo iż on nic nie robi sensownego, możemy powiedzieć że mamy 2 konstruktory i zależnie od wyboru możemy podłączyć się za pomocą jednej z dwóch metod autentykacji. Warto pamiętać także o komentarzach które pozwalają później w łatwy sposób operować na wytworzonym kodzie. Dodająć /// linię nad deklaracją metody VS wygeneruje nam komentarze które uzupełnione o nasze dopiski będą wyświetlały się w intelisense (podpowiedzi podczas pisania kodu).
Gdy nasz kod wygląda +\- tak możemy przejść do sedna sprawy, musimy utworzyć połączenie do SQL Server. W obu przypadkach posłużymy się obiektem SQLConnection.

Proponuję stworzyć właściwość połączenie, która będzie widoczna dla całej klasy odpowiedzialnej za łączenie się SQL Serverem.

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

W tym celu po klamrze otwierającej definicję klasy dodajemy private (czyli obiekt/właściwość widoczna tylko wewnątrz klasy – SQLConnection -> typ opbiektu/właściwości klasy, oraz polaczenie czyli nazwa naszego obiektu.
W takim wypadku mamy tylko deklarację zmiennej/obiektu bez utworzenia instancji. Teraz w konstruktorach należy dodać utworzenie instancji obiektu SQLConnection oraz przypisać właściwość ConnectionString i otworzyć połączenie. Teraz nasz kod powinien zacząć wyglądać tak:

class jodb
{
private SqlConnection polaczenie; //obiekt polaczenia widoczny w calej klasie

///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji SQL Server
///

///uzytkownik ///haslo ///nazwa instancji ///nazwa bazy danych public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
///
/// Konstruktor do tworzenia polaczenia za pomoca autoryzacji windows
///

///nazwa instancji ///nazwa bazy danych public jodb(string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = “”;
polaczenie.Open();
}
}

Do pełni szczęścia brakują nam jeszcze connectionStringi, czyli ciągi wskazujące na konkretną bazę danych, można je znaleść na connectionstrings.com – ja poniżej przedstawiam te z których korzystam (dla mnie są wystarczające, jednakże istnieje możliwość zdefiniowania większej ilości parametrów).

Po uzupełnieniu ConnectionString nasz kontruktor powinien wyglądać mniej więcej tak:

public jodb(string user, string pass, string instance, string dbdir)
{
polaczenie = new SqlConnection();
polaczenie.ConnectionString = "user id=" + user + ";" +
"password=" + pass + ";Data Source=" + instance + ";" +
"Trusted_Connection=no;" +
"database=" + dbdir + "; " +
"connection timeout=3";
polaczenie.Open();
}

łańcuch do łączenia z bazą wygląda tak:
“user id=” + user + ;password=” + pass + “;Data Source=” + instance+ “;Trusted_Connection=no;database=” + dbdir + “;connection timeout=3”;

znaki + są konkatenacją (łączeniem łańcuchów) i parametryzują nasz łańcuch tak by podstawić zmienne które podajemy jako argumenty. Powyższy ciąg służy do łączenia za pomocą autentykacji SQL Server.

W drugim kostruktorze stosujemy trochę krótszy ConnectionString:
polaczenie.ConnectionString = "Data Source=" + instance + ";" +
"Trusted_Connection=yes;" +
"database=" + dbdir + "; " +
"connection timeout=3";

Jak widać główna różnica polega na braku użytkownika i hasła oraz zmianie na trusted_connection=yes.
Powyższy kod warto uzupełnić o obsługę błędów Try i catch, ale zrobimy to później, na tym etapie nie jest nam to potrzebne. Na tym etapie warto skompilować projekt (wciskając F5) lub zieloną strzałkę. Jeśli wszystko dobrze pójdzie ujrzymy naszą formatkę jako osobną aplikację. W przeciwnym wypadku musimy usunąć wszystkie błędy. Proszę pamiętać że konstruktory muszą się nazywać tak samo jak klasa więc jeśli klasa nazywa się inaczej niż jodb to konstruktory także. Warto też przeczytać ewentualne komunikaty błędów i sprawdzić w google co one mogą znaczyć. Jeśli na tym etapie coś nie działa proszę o komentarz pod postem, postaram się pomóc.

Jeśli projekt się nam kompiluje trzeba przetestować połączenie.
Przejdźmy do kodu w pliku form1.cs

Do góry na belce mamy listę otwartych plików:

Możemy też w solution explorer kliknąc dwa razy na form1.cs i jak pokaże się forma kliknąc 2 razy guzik lub wcisnąć klawisz F7.

Do funkcji/zdarzenia/metody odpowiedzialnej za kliknięcie guzikiem dopiszmy kod który utworzy nam obiekt do obsługi bazy danych, dodatkowo także wraz z tym obiektem zostanie ustanowione połączenie z bazą.


Jak widać przy dobrym opisaniu własnego kodu, łatwe jest jego późniejsze wykorzystanie.
Teraz nasz kod form1.cs powinien wyglądać podobnie do:
namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void pobieracz_Click(object sender, EventArgs e)
{
jodb baza = new jodb(“test”, “test”, “localhost\\s2012n”, “zajecia”);
}
}
}

Kompilujemy projekt, jeśli wszystko poszło ok po kliknięciu guzika nic się nie stanie. Jeśli coś jest nie tak albo projekt się nie skompiluje albo po kliknięciu guzika pojawi się wyjątek (exception) który poinformuje nas o błędzie. Warto zwrócić uwagę na podwójny \\ w nazwie instancji, \ jest znakiem wyjścia i znakiem wyjścia dla niego jest on sam stąd podwojenie. Proszę także pamiętać że SQL Server ma standardowo wyłączoną obsługę TCIP co oznacza że nie podłączymy się do niego za pomocą adresu IP ( tylko po nazwie hosta Windows) ew localhost.

To jest moment w którym zaczynają się schody i większośc ludzi ma problem z podłączeniem do bazy, najczęstszymi powodami są literówki w ConnectionString dlatego proszę go dokładnie sprawdzić w razie problemów, lub napisać komentarz postaram się pomóc.

Aby cały projekt miał sens brakuje nam metody która pozwoli pobrać nam dane z bazy. W poprzednich moich postach stworzona została baza zajęcia do której były dołączone dwa zestawy szkoleniowe, w dalszym kodzie będziemy się opierać na tej bazie.

Wracamy do pliku klasy do łączenia się z bazą danych, klikamy jego nazwę do góry na belce lub też klikamy go podwójnie w solution explorer.

Dopisujemy kolejną metodę publiczną, ja ją nazwę pobierz_dane.

///
/// Metoda do pobierana danych z SQL Server
///

///zapytanie sqL /// zwraca dane w obiekcie DataTable
public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable();

return dt;

}


Jak widać powyższy kod opatrzyłem komentarzami. Po słowie public mamy nazwę obiektu (DataTable) który oznacza co metoda zwróci, przygotowałem też wnętrze funkcji (wydmuszka) by było można skompilować projekt. W tym momencie ta metoda nie robi niczego sensownego po prostu za każdym razem zwróci pusty obiekt DataTable.
Za chwilę dopiszemy kod który pozwoli nam pobierać dane.

W tym celupotrzebujemy obiekt SQLCommand, któremu musimy wskazać z jakiego połączenia ma korzystać oraz jakie zapytanie ma wykonać, potrzebujemy także obiekt SQLDataReader, który to odczyta dane z bazy i na koniec trzeba te dane umieścić w obiekcie DataTable.

public DataTable pobierz_dane(string q)
{
DataTable dt= new DataTable(); // deklaracja i utworzenie instancji obiektu DataTable o nazwie dt
SqlDataReader dr; // deklaracja obiektu SqlDataReader o nazwie dr
SqlCommand sqlc; // Deklaracja obiektu SqlCOmmand

sqlc = new SqlCommand(q);
// utworzenie instancji SQLCommand ktora ma wykonac zapytanie podane jako parametr
// w zmiennej q

sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
dr =sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

cała funkcja do pobierania danych powinna wyglądać jak powyżej.

Teraz należy utworzyć obiekt w którym możemy zaprezentować pobrane dane, do tego celu możemy skorzystać z obiektu DataGridView. Można go utworzyć z poziomu kodu, lub też przeciągnąć z toolbox’a. W tym celu klikamy w solution explorer form1.cs dwa razy i jak pojawi się forma klikamy toolbox i przeciągamy DataGridView.
Proszę zwrócić uwagę iż kontrolka DataGridView może nie znajdować się w zakładce common controls.

Gdy już mamy datagridview, (domyślnie VS utworzy nam obiekt o nazwie datagridview1), wchodzimy do kodu form1.cs (przycisk F7 lub podwójny klik na guzik). Przy tworzeniu datagridview VS chce wymusić na nas ustawienia DataSource, ale my z tego świadomie rezygnujemy(nie klikamy w nic na formie co się pojawia wokół Datagridview).

W kodzie form1.cs w funkcji która wykonuje się po kliknięciu guzikiem dopisujemy linijkę kodu:

dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");

Z lewej strony mamy obiekt datagridview1 i jego właściwość Datasource, przypisujemy do niej wynik zwrócony przez metodę pobierz_dane obiektu baza_win (wcześniej był pokazywany obiekt baza, jednak dodałem sobie także obiekt baza_win który łączy się z SQLServer poprzez logowanie windows).
Metoda pobierz_dane przyjmuje jeden argument typu string i jest nim zapytanie SQL.
Cała metoda wygląda tak:

poniżej jeszcze kod:
private void pobieracz_Click(object sender, EventArgs e)
{
// jodb baza = new jodb("test", "test", "localhost\\s2012n", "zajecia");
jodb baza_win = new jodb("localhost\\s2012n", "zajecia");
dataGridView1.DataSource = baza_win.pobierz_dane("select * from klienci");
}

Teraz zostaje nam tylko skompilować i uruchomić projekt (F5).
Po uruchomieniu programu powinno się pokazać coś podobnego do:

Po kliknięciu guzika powinny w DatagridView pojawić się wyniki:

Klucz obcy – informacje praktyczne | foreign key – practical

By dokładniej omówić jak działa klucz obcy posłużę się przykładem. Weźmy po uwagę 2 tabele: Klienci i ich adresy.
Załóżmy że zbieramy informację o adresach do korespondencji i adresach zameldowania. Możemy to zrealizować na 2 sposoby. Albo utworzymy tabelę klienci która będzie zawierała odpowiednie kolumny (odpowiedzialne za adres do korespondencji i adres zameldowania) lub też stworzymy 2 tabele, gdzie w jednej będziemy trzymać dane klienta a w drugiej dane adresowe. Drugi przypadek jest o tyle ciekawszy że w momencie jak będziemy chcieli zbierać więcej niż 2 adresy nie musimy przebudowywać aplikacji i bazy danych by dodać taką możliwość. Jednak taka struktura wymaga( a właściwie stwarza możliwość) wykorzystania klucza podstawowego i obcego. Dla zainteresowanych teorią tego zagadnienia jak prawidłowo rozdzielać kolumny pomiędzy tabelami i jak tworzyć relację polecam hasło: 3 postać normalna (3NF), zapraszam także do lektury moich postów o modelu relacyjnym.

Wracając do praktyki, poniższe skrypty są tworzone pod SQL Server, jednak po niewielkich przeróbkach mogą aplikowane w innych bazach danych.


create table klienci
(klient_id int not null primary key identity(1,1),
klient_imie varchar(255),
klient_data datetime default getdate()
)

create table adresy (
adres_id int not null primary key identity(1,1),
adres_ulica varchar(255),
adres_miejscowosc varchar(255),
adres_klient_id int,
adres_typ int
)

insert into klienci (klient_imie) values ('Krzys');
insert into klienci (klient_imie) values ('Adam');
insert into klienci (klient_imie) values ('Andrzej');
insert into klienci (klient_imie) values ('Marek');

insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Poznan','Poznanska',1,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Warszawa','Warszawska',2,1);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gniezno','Dworcowa',1,2);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Krakow','Glowna',1,3);
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Gdansk','Bursztynowa',1,4)
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,5)

Uruchomienie powyższych zapytań tworzy nam dwie tabele. Tabela z danymi klientów (celowo uproszczona) oraz prosta tabela z adresami. Jak widać w tabeli adresy jest kolumna wskazująca na klienta (adres_klient_id). Jest to kolumna na której powinniśmy założyć klucz obcy.

Najpierw jednak przyjrzyjmy się danym w tabelach.

Jak widać w tabeli adresów znajduje się jedno wskazanie na id klienta który nie istnieje w tabeli klientów. Do takiej sytuacji łatwo doprowadzić w sytuacji gdy baza nie posiada więzów integralności (czyli właśnie kluczy obcych i podstawowych). Taki efekt możemy uzyskać np w sytuacji gdy ktoś po
prostu usunął klient z klient_id=5.

Możemy spróbować założyć klucz obcy na tabelę adresy by uniknąć takich sytuacji:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

Składnia polecenia jest dość prosta:
Alter table nazwa_tabeli – wskazanie tabeli na której chcemy założyć klucz.
add constraint – rodzaj operacji.
adresy_klient_fk – nazwa naszego klucza, wybieramy ją sami, proszę pamiętać że nazwa klucza nie może powielać się z innymi obiektami w bazie.
foreign key (adres_klient_id) – typ klucza, constraint – więzu integralności, w nawiasie nazwa kolumny na którą zakładamy klucz.
references klienci(klient_id) – wskazanie na tabelę i kolumnę do której klucz obcy ma się odnosić.
Typ klucza podstawowego i obcego w sql server zawsze muszą być identyczne (uwzględniając długość łańcuchów varchar).

Nie mniej ze względu na nasz błąd w danych dostaniemy komunikat błędu:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.

Aby poradzić sobie z taką sytuacją mamy dwa wyjścia. Możemy stwierdzić że nie jesteśmy wstanie naprawić bałaganu, ale nie chcemy go też powiększać. Dlatego chcielibyśmy utworzyć klucz który będzie działał na nowo wprowadzone dane:

alter table adresy with nocheck
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)

w tej wersji zapytania pojawiły się słowa with nocheck, jest to informacja dla bazy danych że ma nie weryfikować już wprowadzonych danych.

Gdy utworzyliśmy taki klucz, polecenie:
insert into adresy (adres_miejscowosc,adres_ulica,adres_typ,adres_klient_id) values ('Olsztyn','Polska',1,6)

zwróci nam:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.klienci”, column ‘klient_id’.
The statement has been terminated.

klient_id = 6 nie istnieje w tabeli klienci więc baza nie pozwoli nam na zapis takiej informacji.

Drugi scenariusz jaki możemy przeprowadzić to próba posprzątania bałaganu, tutaj mamy dwie opcje. Możemy usunąć kłopotliwy wiersz, lub też możemy ustawić wartość adres_klient_id na null. Czyli na wartość nieokreśloną. W ten sposób zachowamy adres i zweryfikujemy poprawność pozostałych danych.

By przeprowadzić scenariusz numer 2, usuniemy na chwilę klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

teraz ustawmy wartosc null dla danych które nie spełniają zależności klucza obcego:

update adresy set adres_klient_id = null where adres_klient_id not in (select klient_id from klienci)

Teraz możemy założyć klucz obcy według standardowych ustawień:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id)
.

Możemy zauważyć że relacja która właśnie stworzyliśmy jest typu jeden do wiele. W tabeli klienci mamy zawsze wartości unikalne (wymóg klucza podstawowego). W tabeli adresy możemy mieć wiele adresów przypisanych do 1 klienta. Jest to najpopularniejszy typ relacji.

Spróbujmy teraz usunąć klienta z naszej bazy:

delete from klienci where klient_id = 2

Baza zwróci nam komunikat:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “adresy_klient_fk”. The conflict occurred in database “zajecia”, table “dbo.adresy”, column ‘adres_klient_id’.
The statement has been terminated.

W ten sposób już nie osierocimy adresów, jednak czasem mimo wszystko ktoś może chcieć usunąć dane z bazy. Wtedy musimy usuwać dane w kierunku relacji. Czyli zaczynamy od tabel które nie mają na siebie wskazań (ich klucz podstawowy nie jest nigdzie kluczem obcym). W naszym przykładzie taką tabelą jest tabela adresy.

delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2

Taki kod wykonany w takiej kolejności zadziała prawidłowo. Warto jednak dodać iż warto go objąć tak zwaną transakcją by nie osierocić klienta z adresu. Możemy bowiem wyobrazić sobie sytuację w które adres udaje nam się usunąć a dane klienta już nie.

begin transaction
delete from adresy where adres_klient_id = 2
delete from klienci where klient_id = 2
commit

Powyższy kod jest najbardziej poprawną wersją.

Istnieje jeszcze możliwość kaskadowego usuwania danych.
Usuńmy na chwilę nasz klucz obcy:

alter table adresy
drop constraint adresy_klient_fk

dodajmy go z powrotem, tym razem z opcją kaskadowego usuwania danych.

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete cascade

teraz wykonanie polecenia:
delete from klienci where klient_id=1

Niestanowi żadnego problemu, możemy też zauważyć że automatycznie został usunięty wiersz z tabeli adresy ( w naszym wypadku nawet dwa wiersze).

Możemy też tak skonfigurować nasz klucz by podczas usuwania danych z tabeli klienci, ustawiał wartość null w kolumnie adres_klient_id. Aby tego dokonać w naszym przykładzie usuńmy po raz kolejny nasz klucz:

alter table adresy
drop constraint adresy_klient_fk

Teraz dodajmy klucz z opcją on delete set null:

alter table adresy
add constraint adresy_klient_fk foreign key (adres_klient_id) references klienci(klient_id) on delete set null

Po ustawieniu klucza, wykonujemy delete z tabeli klientów:

delete from klienci where klient_id = 3

Po wykonaniu zapytania, widać że baza ustawiła nam wartość null dla adresu z adres_klient_id=3.

Metodyka porównywania wydajności operacji SQL Insert w wybranych RDBMS

Poniżej polska wersja artykułu opublikowanego w PIMR,
oryginalna wersja dostępna tutaj

Wprowadzenie
Kluczową kwestią nabierającą coraz większego znaczenia, braną pod uwagę na etapie projektowania, wytwarzania i eksploatowania bazodanowych systemów informatycznych jest problem wydajności i skalowalności aplikacji[1]. W procesie wieloetapowego projektowania danych generalnie nasza uwaga skupiona jest na ich poprawnym odwzorowaniu zgodnie z rozpoznanymi wymaganiami i z przyjętym architektonicznym modelem danych. Zagadnienie wydajności samo w sobie jest zagadnieniem złożonym albowiem możemy go rozpatrywać w kontekście różnych operacji dokonywanych na bazie produkcyjnej lub też w kontekście procesów tworzenia na jej podstawie baz analitycznych[1][7][8]. Z tego obszaru, dość szerokiego, nakreślonego powyżej autorów zainteresował wpływ istnienia klucza podstawowego oaz sposób jego generowania na wydajność zapisu danych w RDBMS. W podejściu badawczym przyjęto perspektywę, interesującą użytkowników gotowego produktu, jaki jest RDBMS, w związku z tym świadomie pominięto zagadnienia związane z mechanizmami fizycznego rozmieszczenia danych na dysku.
Metodyka

Próba odpowiedzi na pytanie na ile obecność klucza podstawowego oraz sposób jego tworzenia wpływa na zapis danych w relacyjnych wymagało opracowania metodyki i wytworzenia niezbędnych narzędzi programistycznych.
Na etapie początkowym przyjęto szereg założeń, których zachowanie powinno eliminować wpływ czynników zakłócających pomiar. Między innymi zdecydowano się, iż aplikacja powinna działać całkowicie po stronie serwera bazodanowego. Tym samym wyeliminowano by wpływ interfejsów programistycznych dostępu do danych typu ADO, ADO.NET itp. na czas zapisu rekordów. Konsekwencją tej decyzji było zwrócenie naszej uwagi tylko na te RDBMS, których wewnętrzny język jest językiem proceduralnym.
Wytworzona procedura, względnie procedury stanowiące podstawowe narzędzie badawcze, powinny tworzyć strukturę tabeli, generować rekordy, a następnie je zapisywać. Równocześnie należałoby rejestrować czas rozpoczęcia i zakończenia zapisu danych z jednoczesnym odnotowywaniem liczby rekordów osadzonych w bazie. Powyższa druga porcja informacji, potrzebna do analiz, gromadzona byłaby w oddzielnej tabeli.
Skoro podstawowym celem autorów było uzyskanie odpowiedzi na pytanie na ile obecność klucza głównego i sposób jego generowania ma wpływ na szybkość zapisu zdecydowano się na utworzenie szeregu niezależnych procedur, osobno dla każdego przewidywanego wariantu. Wspólnym elementem byłby zapis wyników eksperymentu do tej samej tabeli. Przyjęto następujące przypadki, które wiązały się rodzajem informacjami zapisywanych w bazie:
• dane bez klucza podstawowego,
• dane z kluczem głównym, którego wartości generowane są przez RDBMS,
• dane z kluczem głównym, gdzie wartości są generowane na podstawie algorytmu użytkownika.
Procedury to realizujące powinny być zaimplementowane w co najmniej w dwóch RDBMS. Podjęto decyzję, iż będzie to SQL Server 2008, Oracle XE i MySQL. Podstawą wspomnianej decyzji była popularność wykorzystania tych narzędzi informatycznych. I tak dla przykładu na polskim rynku darmowa wersja MSSQL jest wykorzystywana między innymi w znanych programach: Płatnik, MicroSubiekt. Z kolei jego większy brat (płatny) stosowany jest między innymi w jednym z najpopularniejszych programów typu ERP na naszym rynku tj. CDN XL. Baza Mysql skolei dominuje na rynku aplikacji interneotwych, większość skryptów obsługujących fora to tandem Apache, PHP wraz z MySQL. Oprogramowanie bazodanowe dostarczane przez Oracle ma jedną z najlepszych opinii w środowisku IT, dlatego też darmowa wersja (XE) została poddana analogicznym testom jak MSSQL i MySQL.
W prezentowanej metodyce przyjęto jeszcze jedno założenie, z którym w praktyce bazodanowej bardzo często mamy do czynienia, polegające na tym, iż klucz podstawowy jest jedno elementowy, przyjmujący wartości typu int.
Mimo, że zaproponowana metodyka całościowo nie pokrywa się z operacjami przebiegającymi w rzeczywistości, to zdaniem autorów jest dobrym narzędziem do realizacji badań porównawczych. Świadomie zrezygnowano w rozważaniach i badaniach z specjalistycznych narzędzi służących do ładowania danych (typu ETL) wykorzystywanych w tworzeniu hurtowni danych, skupiając się głównie na rozwiązaniach bazodanowych OLTP.

Narzędzie badawcze – procedury

Sygnalizowane powyżej procedury, stanowiące narzędzie badawcze, wytworzono wpierw w języku T-SQL, który jest językiem wbudowanym w SQL Server 2008R2. Pozwala on nie tylko na manipulowanie danymi (składowe języka DML – Data modyfication Language) ale również na definiowanie struktury (składowe języka DDL – Data definition Language). W zaprezentowanej poniżej procedurze, dotyczące przypadku zapisu danych w tabeli bez zdefiniowanego klucza podstawowego, wykorzystano obie wspomniane możliwości języka T-SQL. Podjęto również decyzję o zapisie wyników cząstkowych eksperymentu w dodatkowej tabeli. Do utworzenia agregatów, które tworzą wyniki cząstkowe, odnotowane w tabeli wykorzystano pytanie grupujące wraz z funkcją DateDiff. Grupę tworzą rekordy, które zostały zapisane do bazy w tym samym przedziale czasowym wynoszącym jedna sekunda. Ten sposób grupowania jest możliwy, albowiem jedno z pól tabeli podstawowej (simple_insert_table) zawiera datę i czas systemowy zwróconą przez funkcję GetDate.

CREATE PROCEDURE [dbo].[simple_insert]
@numerow int=1000;
@numtries int= 100;
AS
BEGIN
SET NOCOUNT ON;
declare @testb datetime;
declare @tests datetime;

create table simple_insert_table (
a int, b varchar(10), c datetime );

declare @numtries1 int=@numtries;
declare @numerow1 int=@numerow;

begin transaction;
set @testb = GETDATE();
while @numtries >0
begin
set @numerow = @numerow1;
while @numerow >0
begin
insert into simple_insert_table values (1,’1234567890’,GETDATE());
set @numerow = @numerow -1;
end;
set @numtries = @numtries -1;
end;
set @tests=GETDATE();

insert into test_results (test_name,start_d,stop_d,param1,param2)
values (‘simple insert test’,@testb,@tests,@numerow1,@numtries1);

set @numerow = @@IDENTITY;
insert into test_subresult (test_id, ins_num, time_agr)
select @numerow,count (DATEDIFF(S, ‘19700101’, c)) e, DATEDIFF(S, ‘19700101’, c) from
simple_insert_table
group by DATEDIFF(S, ‘19700101’, c);
insert into test_results (test_name,param1,param2,param3,param4,param5)
select ‘statistic for simple insert: ‘+ cast(@numerow as varchar),exec_per,max_ins,avg_ins,min_ins,stddev_ins from dbo.simple_insert_stat;
drop table simple_insert_table;
commit transaction;
END

Niewielkie zmiany w prezentowanym kodzie pozwoliły na zbudowanie nowych procedur, które stanowiły narzędzie testujące dla dwóch kolejnych przypadków. W pierwszym wariancie klucz był generowany przez algorytm zawarty w procedurze. Zmienna wykorzystywana do przekazywania wartości generowanego klucza do zapytania, stanowiła równocześnie składową warunku, decydującego o liczbie wykonanych pętli. W drugim przypadku do generowania klucza wykorzystano odpowiednie narzędzia serwera bazodanowego. W przypadku SQL Server 2008R2 wykorzystano mechanizm identity, natomiast jego odpowiednikiem na poziomie MySQL był autoincrement. Z kolei w RDBMS Oracle wykorzystano mechanizm sekwencji. Ingerencja w wspomniane narzędzia dotyczyła tylko ustawień początkowych, obejmujących wartość startową oraz skok, które we wszystkich przypadkach były identyczne. Celem zapewnienia możliwości dokonania uogólnień omawiany algorytm został zaimplementowany w języku PL/SQL oraz w proceduralnym rozszerzeniu języka SQL dla bazy MySQL 5.5.8, co pozwoliło na przeprowadzenie testów z wykorzystaniem innych RDBMS.
W trakcie podjętych przez autorów badań pojawiła się, co prawda nowa wersja SQL Server 2011 wyposażona tym razem w dwa mechanizmy, pozwalające na automatyczne tworzenie klucza podstawowego, lecz nie jest to wersja ostateczna, w związku z powyższym nie stała się ona przedmiotem eksperymentów. Tym dodatkowym narzędziem umożliwiającym automatyczne generowanie klucza na poziomie wspomnianego RDBMS jest mechanizm sekwencji [8].
Badania i wyniki

Badania przeprowadzono w wirtualnym środowisku, opartym o system operacyjny Windows XP Home Edition wraz z najnowszymi aktualizacjami. Instalacja miała charakter standardowy podobnie, jak późniejsza instalacja RDBMS (zaaprobowano wszystkie ustawienia kreatora). Po zakończeniu testów danego RDBMS następowała reinstalacja środowiska badawczego. Każdy z testów został także wykonany na maszynie serwerowej podpiętej do macierzy w środowisku Windows Server 2008 ( z wyłączeniem testów bazy Oracle). Procedury dla bazy Oracle zostały przetestowane na serwerze z systemem operacyjnym Ubuntu Server 11 także podpiętym do wydajnej macierzy. Wyniki z środowisk serwerowych były analogiczne do maszyny badawczej (relacje wyników testów dla danego RDBMS).
Powszechnie uważa się iż wyłączenie ograniczeń (np. Klucza podstawowego) podczas wstawiania dużej ilości danych pozwala zwiększyć wydajność zapisu. Opisywany eksperyment pozwala ocenić czy wskazana operacja jest wstanie przynieść realne zyski.
W celu przeprowadzenia testów potrzebne było utworzenie w każdym badanym RDBMS pustej bazy danych, również zgodnie z ustawieniami sugerowanymi przez kreatora. Dało to podstawy do osadzenia w niej procedur testujących z jednoczesnym utworzeniem struktury relacyjnej do gromadzenia wyników badań.
Test przeprowadzono dla każdego z RDBMS dla wyszczególnianych poniżej wariantów, podczas których wprowadzono do bazy 106 rekordów:
• wstawiano dane bez klucza głównego,
• wstawiano dane tworzące klucz główny z poziomu procedury
• wstawiono dane zawierające klucz główny utworzony przy użyciu mechanizmów RDBMS (autoincrement, identity, sequence).

Efekty przeprowadzonych badań zaprezentowano w formie tabelarycznej tab. 1, 2, 3 i 4. Zawierają one wielkości względne, a punktem ich odniesienia są wyniki uzyskane dla pierwszego wariantu badań. Powyższy sposób postępowania zastosowano do rezultatów uzyskiwanych dla każdego z badanych RDBMS. W przypadku MySQL 5.5.8 badania przeprowadzono dla dwóch różnych silników INNODB oraz MyISAM, dostępnych dla tego RDBMS. Natomiast niecelowym, zdaniem autorów było umieszczenie względnych wyników badań uzyskanych dla przypadku zapisu rekordów pozbawionych klucza podstawowego.

Omówienie wyników

Zaprezentowane wyniki badań zapisu danych zawierających i niezawierających klucz podstawowy niewiele się różnią się między sobą w przypadku SQL Server 2008 R2. Dotyczy to zarówno sytuacji, gdy klucz jest tworzony przez RDBMS, jak i przez algorytm zaszyty w procedurze testującej. Opóźnienie zapisu danych wyposażonych w klucz podstawowy, przy przyjęciu prędkości zapisu 5000 wierszy na sekundę oraz liczbie rekordów 106, wynosi 10 sekund. Ważnym odnotowania jest również fakt, iż w trakcie badań nie zaobserwowano spadku prędkości zapisu wierszy wraz ze wzrostem liczby rekordów zawartych w tabeli. Zbliżoną prawidłowość zauważono w przypadku MySQL 5.5.8 wyposażonego w silnik InnoDB z tym, że okazał się on bardziej wrażliwy na sposób generowania klucza podstawowego. Drugą dość zaskakującą zależnością, wynikającą z otrzymanych wyników dla tego RDBMS jest wzrost względnej prędkości zapisu danych, zawierających klucz podstawowy. Dotyczy to zarówno sytuacji gdy klucz główny jest generowany przez system bazodanowy, jak i procedurę. Równoległym faktem, wymagającym wyjaśnienia są zerowe wartości prędkości minimalnej uzyskiwane w trakcie badań MySQL 5.5.8[5]. Skutkowało to znacznym wzrostem odchylenia standardowego oraz tym, że minimalna prędkość względna przyjmowała wartości nieokreślone.
Tego rodzaju prawidłowości nie stwierdzono przy użyciu wcześniejszego silnika MyISAM MySQL 5.5.8 [4]. Odnotowane w tym przypadku tendencje, będę konsekwencjami pomiarów są zgodne z rezultatami uzyskanymi dla Oracle XE, które potwierdzają dotychczasowe przekonanie, że wprowadzenie klucza spowalnia zapis nowych wierszy do tabeli. Należy jednak przypomnieć o istotnych wadach silnika MyISAM, jakim jest brak możliwość tworzenia transakcji i definiowania więzów integralności referencyjnej [6].
Podsumowanie

Współczesne RDBMS bardzo się różnią pod względem implementacji modelu relacyjnego, co utrudniania formułowanie uogólnień i reguł dotyczących wydajności tych systemów informatycznych. W pewnych wypadkach rozwiązania intuicyjne, czy będące efektem dotychczasowych doświadczeń z RDBMS mogą się okazać nieefektywne przy pracy z nowymi systemami bazodanowymi, dlatego autorzy zalecają testowanie proponowanych rozwiązań zwłaszcza przed rozpoczęciem prac rozwojowych tworzonego oprogramowania (zwłaszcza w przypadku tworzenia struktur OLAP). Przeprowadzone badania wraz z dokonaną analizą wyników, z użyciem trzech różnych RDBMS skłoniły autorów do sformułowania następujących uwag i wniosków:
• Wyposażenie danych w klucz podstawowy ogranicza z reguły wydajność zapisu rekordów w każdym badanym RDBMS z wyłączaniem MySQL 5.5 zawierającego silnik InnoDB. Spadek tej wydajności jest zróżnicowany lecz z perspektywy SQL Server 2008R2 jest on mało znaczący.
• Mechanizm generowania klucza podstawowego wbudowany w RDBMS z perspektywy wydajności zapisu nowych wierszy jest generalnie rozwiązaniem lepszym od własnych rozwiązań programistycznych zaszytych w procedurach.
• Wskazanym wydaje się podjęcie dalszych wysiłków poznawczych, zmierzających do wyjaśnienia nietypowego zachowania się MySQL 5.5.8 z silnikiem InnoDB z perspektywy prędkości zapisu danych pozbawianych i wyposażonych w klucz podstawowy.
• Uzyskane całkowite czasy zapisy danych w analizowanych wariantach i w różnych RDBMS i przy zaproponowanej metodyce badań wskazują, że z perspektywy aplikacji OLTP korzyści wydajnościowe, wynikające z niestosowania jednoelementowego klucza podstawowego są mało znaczące.
Bibliografia

[1] Beynon-Davies – Database Systems 2003
[2] Joe Celko – SQL for Smarties, Advanced SQL Programming, 3 Edition 2005
[3] MySQL Reference Manual – http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html 2011
[4] MySQL MyISAM Storage Engine Manual – http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html
[5] MySQL InnoDB – http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[6] MySQL refman – http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html
[7] Paulraj Ponniah – Data Warehousing Fundamentals for IT Professionals 2010
[8] SQL Server perfomance – http://www.sql-server-performance.com/articles/dev/sequence_sql_server_2011_p1.aspx 2011
[9] Chris Todman – Designing a data warehouse: supporting customer relationship management 2003

T-SQL: Numer Pesel bez tajemnic

Pracując w Polsce z bazami danych, prędzej czy później spotkamy się z problemem walidacji różnych numerów (NIP,REGON,PESEL,numer konta bankowego, itd). Na przykładzie numeru PESEL postaram się pokazać jak sobie z tym radzić po stronie Bazy danych. Niestety na większość z podanych numerów nie można nałożyć twardej walidacji (np. Niektóre numery PESEL były błędnie wygenerowane, obcokrajowcy nie posiadają PESEL, itd). Twarda walidacja oznacza iż nie możemy zabronić zapisania niepoprawnej wartości w systemie. Reperkusją takiego zachowania jest dopuszczenie błędnie wprowadzonych numerów na podstawie prawidłowych danych. Z kolei raportowanie,obróbka powinny być oparte o dane poprawne. Numery PESEL mają stosunkowo prostą strukturę: Składają się z samych cyfr, mają określoną długość,niektóre cyfry mają określone znaczenie.
Zaczynając od początku wiemy iż numer PESEL winien miec 11 znaków(cyfr). w TSQL mamy do dyspozycji funkcję isnumeric oraz len.

select isnumeric('11111111111'),len('11111111111')

W ten sposób możemy określić czy ciąg jest liczbą/składa się z cyfr oraz czy ma długość 11 znaków.

Niestety nie jest to wystarczające sprawdzenie. Osoby które projektowały numer PESEL, wymyśliły także pewien mechanizm ochronny, tak zwaną cyfrę kontrolną. W numerze PESEL jest to ostatni znak, który jest obliczany na podstawie pierwszych 10 znaków. Algorytm jest prosty każda kolejna cyfra posiada swoją wagę przez która jest mnożona. Z sumy iloczynów obliczane jest modulo (reszta z dzielenia przez 10) wynik jest liczbą kontrolną.
Poniżej kawałek funkcji która sprawdza obliczoną sumę kontrolną z tą podaną. Jako że taki kod w zapytaniu już wygląda dość kiepsko, warto skorzystać z funkcji w TSQL i zdefiniować swoją własną funkcję do walidacji.
if (
( ( cast(substring(@psl,1,1) as bigint)*9)
+(cast(substring(@psl,2,1) as bigint)*7)
+(cast(substring(@psl,3,1) as bigint)*3)
+(cast(substring(@psl,4,1) as bigint)*1)
+(cast(substring(@psl,5,1) as bigint)*9)
+(cast(substring(@psl,6,1) as bigint)*7)
+(cast(substring(@psl,7,1) as bigint)*3)
+(cast(substring(@psl,8,1) as bigint)*1)
+(cast(substring(@psl,9,1) as bigint)*9)
+(cast(substring(@psl,10,1) as bigint)*7) ) % 10
= right(@psl,1) )
begin
return 1;
end

A cała funkcja może wyglądać następująco:

create FUNCTION [dbo].[is_pesel]
(@PESEL varchar(255))
RETURNS int
AS
BEGIN
declare @psl varchar(255);
set @psl=0;
if @PESEL='00000000000' begin return 0 end
if @PESEL='12345678910' begin return 0 end
if @PESEL='11111111116' begin return 0 end
if @PESEL='11111111123' begin return 0 end
if SUBSTRING(@pesel,3,1) not in ('0','1','2','3') begin return 0 end
if (isnumeric(@PESEL) =1 )
begin
set @psl = cast(@PESEL as varchar(255));
end
else
begin
return 0;
end;

if (
( ( cast(substring(@psl,1,1) as bigint)*9)
+(cast(substring(@psl,2,1) as bigint)*7)
+(cast(substring(@psl,3,1) as bigint)*3)
+(cast(substring(@psl,4,1) as bigint)*1)
+(cast(substring(@psl,5,1) as bigint)*9)
+(cast(substring(@psl,6,1) as bigint)*7)
+(cast(substring(@psl,7,1) as bigint)*3)
+(cast(substring(@psl,8,1) as bigint)*1)
+(cast(substring(@psl,9,1) as bigint)*9)
+(cast(substring(@psl,10,1) as bigint)*7) ) % 10
= right(@psl,1) )
begin
return 1;
end

return 0;
END

Funkcja z założenia wraca wartość 1 w przypadku gdy numer jest prawidłowy(a właściwie jego suma kontrolna jest prawidłowa) w przeciwnym wypadku zwraca 0. Plus dodatkowo weryfikuje kilka dodatkowych założeń. Sprawdza czy podana wartość nie należy do tak zwanych wartości skompromitowanych, znanych kombinacji z poprawną sumą kontrolną. W dużych zbiorach wartości skompromitowane można łatwo wykryć odpytując bazę i sortując po ilości wystąpień danego numeru. Kolejne założenie jakie zostało zweryfikowane to iż w bazie powinny być PESELe osób żyjących (czyli takich które się już urodziły) dodatkowo też założono że w bazie nie znajdują się PESELe osób urodzonych przed 1900 rokiem.

if SUBSTRING(@pesel,3,1) not in ('0','1','2','3') begin return 0 end

Ten fragment kodu pozwala nam zweryfikować czy pierwsza liczba miesiąca urodzenia to wartości 0,1,2,3
Dla osób urodzonych od roku 2000 do liczby oznaczającej numer miesiąca dodaje się wartość 20, tak więc można by jeszcze dodatkowo ograniczyć ten zbiór… Można jeszcze dodatkowo sprawdzić czy 5 i 6 cyfra razem mieszczą się pomiędzy 01 a 31.

Kolejną informacją zawartą w numerze PESEL jest płeć, aby pobrać płeć z numeru PESEL będziemy potrzebować następującego kodu:

create FUNCTION [dbo].[pesel_plec]
(
@PESEL varchar(255)
)
RETURNS int
AS
BEGIN
declare @plec int;
declare @psl varchar(255);
set @psl=0;
if (is_pesel(@PESEL) =1 )
begin set @psl = cast(@PESEL as varchar(255)); end
else
begin return -1; end;
if (cast((SUBSTRING(@psl,10,1))as bigint) in (0,2,4,6,8))
begin
-- kobieta
set @plec= 0; end else
begin
--facet
set @plec = 1; end;
return @plec;
END

Najpierw sprawdzamy czy numer PESEL jest prawidłowy, kolejno sprawdzamy 10 cyfrę w numerze PESEL jeśli cyfra jest parzysta (0,2,4,6,8) to numer należy do kobiety, w przeciwnym wypadku mamy do czynienia z mężczyzną. Funkcja zwraca 0 – kobieta lub 1 – mężczyzna.

Jak wiadomo pierwsze 6 cyfr to datau rodzenia w formacie rrmmdd. Znając tą datę możemy obliczyć wiek osoby. By to uczynić można użyć poniższego kodu T-SQL:

CREATE FUNCTION [dbo].[get_wiek_from_pesel]
( @pesel varchar(11))
RETURNS varchar(255)
AS
BEGIN

if (dbo.is_pesel(@pesel) != 1 )
begin return -1 end
declare @data varchar(255);
declare @wynik int;

select @data = case
when substring(i,3,1)=2 then
(substring(i,5, 2) + '-'+'0' +substring(i,4,1)+'-'+'20'+substring(i,1,2))
when substring(i,3,1)=3 then
(substring(i,5, 2)+'-'+'1' + substring(i,4,1)+'-'+'20'+substring(i,1,2))
else
(substring(i,5, 2)+'-'+substring(i,3,2) + '-'+'19' +substring (i,1,2))
end
from (select @pesel as i ) e;
SELECT @wynik= FLOOR(DATEDIFF(DAY, convert(date,@data,105), getdate()) / 365.25)
return @wynik
END

Tradycyjnie najpierw sprawdzamy poprawność numeru PESEL, Tutaj przyda się weryfikacja wartości skompromitowanych oraz weryfikacja pierwszych 6 cyfr czy rzeczywiście reprezentują one datę urodzenia.
Powyższa funkcja ‘skleja’ datę na podstawie znanych informacji. Kolejno jest obliczana liczba dni pomiędzy dziś a złożoną datą urodzenia, wynik dzielony jest przez 365 i 1/4 dnia (uwzględnienie lat przestępnych). W ten prosty sposób z dość dużą dokładnością mamy obliczony wiek.

Tworzenie baz danych w SQL SERVER

W RDBMS które obsługują wiele baz danych na jednej instancji, mamy do dyspozycji polecenie create database, które to tworzy pustą bazę danych:

create database nowa_pusta;

gdy chcemy zacząć korzystać z nowo utworzonej bazy należy się na nią przełączyć ( wybrać ją).
W przypadku MySQL oraz SQLServer wykorzystujemy do tego celu polecenie use.
use nowa_pusta -- by korzystac z bazy utworzonej

Oczywiście mamy możliwość dodawania wielu dodatkowych parametrów wpływających na ustawienia bazy.
W przypadku SQL Server, za pomocą języka T-SQL możemy takie parametry odpowiednio modyfikować:


create database nowa_pusta on
( NAME = N'nowa_pusta', FILENAME = N'C:\temp\nowa_pusta.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )

W powyższym przykładzie zostanie utworzona baza danych z plikiem o rozmiarze 168MB, W ten sposób system alokuje odpowiednią ilość miejsca na dysku. Wspomaga to wydajność zapisu danych. Pozostawienie MAXSIZE z wartością unlimited może skutkować wyczerpaniem miejsca na dysku.

create database nowa_pusta on
( NAME = N'nowa_pusta', FILENAME = N'C:\temp\nowa_pusta.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'nowa_pusta_log', FILENAME = N'C:\temp\nowa_pusta.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )

powyższy przykład utworzy bazę danych ze ściśle zdefiniowanymi plikami danych oraz logów. w Pliku LDF przechowywane są informację odnośnie wykonywanych(i wykonanych) transakcji. W przypadku problemów przy zatwierdzaniu transakcji RDBMS (SQL Server) wspomaga się tym plikiem by wykonać rollback oraz przywrócić bazę do stanu spójności.

Można utworzyć bazę z wieloma plikami przechowującymi dane.

create database nowa_pusta on primary
( NAME = N'nowa_pusta', FILENAME = N'C:\temp\nowa_pusta.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
,FILEGROUP [nowa_pusta2] ( NAME = N'nowa_pusta2', FILENAME = N'C:\temp\nowa_pusta2.mdf' , SIZE = 50MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB )
LOG ON
( NAME = N'nowa_pusta_log', FILENAME = N'C:\temp\nowa_pusta.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )

teraz tworząc tabele możemy je utworzyć w 2 różnych plikach, możemy w ten sposób np rozmieszczać mniej istotne dane na wolniejszych napędach.

create table nazwa (id int,nazwa varchar(255)) on primary

wykonanie tego polecenia będzie skutkować utworzeniem tabeli w pliku który został podany jako pierwszy podczas tworzenia bazy danych. W przypadku pominięcia informacji gdzie ma być utworzona tabela, będzie to równoznaczne z utworzeniem jej w pierwszym pliku. Warto dodać iż standardowo jest tworzony jeden plik bazy danych.


create table nazwa (id int,nazwa varchar(255)) on nowa_pusta2

skutkuje utworzeniem tabeli w pliku należącym do grup nowa_pusta2. Mimo iż każda grupa(filegroup) może zawierać wiele plików, w poleceniach SQL, można przypisywać tylko do poziomu grupy plików(przynajmniej z tego co mi wiadomo).

Przestrzeń Tabel Oracle

Przestrzeń Tabel Oracle (TableSpace) jest logiczną jednostką w Systemach bazodanowych firmy Oracle. Przestrzeń tabel zawiera przynajmniej jeden plik (datafile). Każdy obiekt przechowywany w bazie danych Oracle jest przypisany do konkretnej przestrzeni tabel. Można powiedzieć że przestrzeń tabel jest pewnego rodzaju mostem pomiędzy systemem plików a RDBMS.

widok – perspektywa

Widok (perspektywa) to logiczny byt (obiekt), osadzony na serwerze baz danych. Umożliwia dostęp do podzbioru kolumn i wierszy tabel lub tabeli na podstawie zapytania w języku SQL, które stanowi część definicji tego obiektu. Przy korzystaniu z widoku jako źródła danych należy odwoływać się identycznie jak do tabeli. Operacje wstawiania, modyfikowania oraz usuwania rekordów nie zawsze są możliwe ( np. w sytuacji gdy widok udostępnia część kolumn dwóch tabel tb_A oraz tb_B bez kolumny z kluczem głównym tabeli tb_B ). W niektórych SZBD widok służy tylko i wyłącznie do pobierania wyników i ograniczania dostępu do danych.

Encja

Encja (ang. entity) w bazach danych to reprezentacja obiektu (grupy obiektów) Formalnie jest to pojęcie niedefiniowalne, a podstawową cechą encji jest to, że jest rozróżnialna od innych encji (założeniem modelu relacyjnego jest unikalność encji).
Przykłady encji (i atrybuty w encji):
• Osoba (imię, nazwisko, PESEL)
• Pojazd (wysokość, szerokość, długość, sposób poruszania się)
Charakterystyczną cechą encji jest to, że włącza ona do swojego obszaru znaczeniowego obok obiektów fizycznych również obiekty niematerialne. Encja może stanowić pojęcie, fakt, wydarzenie (np. konto bankowe, którego atrybuty to np. numer, posiadacz, dopuszczalny debet itp.; konferencja, której atrybuty to np. temat, data, organizator itp.; wypożyczenie książki, z atrybutami np. imię i nazwisko wypożyczającego, numer karty bibliotecznej, data wypożyczenia itp.).
W pewnych kontekstach encja ma znaczenie bliższe tabeli przechowującej dane, a czasem jest utożsamiana z wystąpieniem danego obiektu w tabeli(instancja obiektu).

Na podstawie polskiej wiki, z moimi drobnymi zmianami.

Wstęp do T-SQL – zmienne

By korzystać w T-SQL ze zmiennych należy najpierw je zadeklarować:

declare @zmienna typ;

Po słowie kluczowym następuje nazwa zmiennej ze znakiem @. Należy także zadeklarować typ zmiennej. W T-SQL mamy dostępne te same typy danych co są dostępne do przechowywania w SQL Server, istnieją także typy złożone (np typ tabelaryczny).

aby zadeklarować zmienną z ustaloną wartością początkową:

declare @liczba int=0;

Aby ustawić wartość zadeklarowanej zmiennej:

set @liczba = 1;

Aby zwrócić wartość zmiennej:

select @liczba;

możemy także wynik zapytania zwrócić do zmiennej:

select @liczba=liczba from tabela

Oczywiście zmienne możemy wykorzystać także do innych celów

select * from tabela where kol_1=@liczba

W przypadku zmiennych typu varchar należy zwrócić uwagę na to jaką długość deklarujemy, bynajmniej nie dlatego że Sql Server zwróci błąd w przypadku próby przypisania wartości o większej długości, on po prostu obetnie łańcuch. Jest to dość upierdliwe w przypadku

Niestety w T-SQL nie ma zmiennych typu rowtype, jest tylko typ table.

DECLARE @jakastabela TABLE
(
id int,
opis varchar(255)
)

do zmiennej typu tablicowego możemy się dodawać wiersze:


INSERT INTO @jakastabela (ID, opis)
SELECT 1,'jakis opis'

także w sposób tradycyjny:

INSERT INTO @jakastabela (ID, opis) values (1,'jakis tam opis')

tak samo można usuwać oraz modyfikować wiersze.

Zmienne tabelaryczne różnią się od tabel(także tymczasowych), posiadają pewne ograniczenia (brak możliwości alter Table), pewnie niedogodności z indeksami(Nie można założyć indeksu nieklastrowanego przed klastrowanym).

Sekwenecje w Sql Server – Mssql

W Sql server 2011 Microsoft udostępnia mechanizm sekwencji. Sekwencja jest mechanizmem zasadniczo podobnym do mechanizmu Identity lub autoincrement. Zasadniczą różnicą pomiędzy wspomnianymi mechanizmami jest fakt iż sekwencje nie są związane z tabelą. Z sekwencji możemy w dowolnym momencie pobrać kolejną wartość. Osoby mające doświadczenia z produktami Oracle z pewnością ucieszy obsługa omawianego mechanizmu w produkcie z Redmont.

Aby utworzyć sekwencję w MSSQL DENALI:

CREATE SEQUENCE dbo.nowa_sek
AS INT
MINVALUE 1
NO MAXVALUE
START WITH 1;

pobranie wartości z Sekwencji:

SELECT NextOrderID = NEXT VALUE FOR dbo.nowa_sek

wstawianie danych przy uzyciu sekwencji:

create table testowa (id int not null primary key,opis varchar(255))

insert into testowa values (NEXT VALUE FOR dbo.nowa_sek,'jakis opis');

Kolejną cechą sekwencji jest możliwość jej cofnięcia do dowolnej wartości

ALTER SEQUENCE dbo.nowa_sek
RESTART WITH 20;

Można także określić maksymalną wartość sekwencji, jednak podczas tworzenia możemy otrzymać ostrzeżenie:

The sequence object ‘nowa_sek’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

W skrócie oznacza to ustawienie odpowiedniej wielkości Cache.

CREATE SEQUENCE dbo.nowa_sek
MINVALUE 1
MAXVALUE 20
START WITH 1;
GO

Gdy sekwencja dojdzie do wartości 20 przy próbie pobrania kolejnej wartości otrzymamy:

The sequence object ‘nowa_sek’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Gdy chcemy by sekwencja się zapętlała:
CREATE SEQUENCE dbo.nowa_sek
AS INT
MINVALUE 1
MAXVALUE 2
cycle
START WITH 1;

wtedy jedynymi wartościami z sekwencji będzie 1 i 2.

To co jest istotne, podobnie jak w przypadku Identity i sekwencji oraclowych – istnieje duże prawdopodobieństwo wystąpienia dziur w numeracji. W przypadku pobrania wartości i wykonania rollback transakcji, wartość zostanie utracona.

Niestety Microsoft nie stworzył funkcji zwracającej current value (aktualna wartość sekwencji).

Aby pobrać informację o wartości sekwencji należy wykonać zapytanie sql:
SELECT current_value
FROM sys.sequences
WHERE name = ‘nowa_sek’;