ado.net tworzenie elastycznych aplikacji cz. 1

Wiadomo powszechnie że programista jest stworzeniem leniwym. Visual studio udostępnia mnóstwo różnych kreatorów które pozwalają na wyklikanie wielu różnych rzeczy. Niestety minusem tego podejścia jest kompletna niewiedza na temat tego jak pewne rzeczy działają. Dodatkowo często natrafimy na różne ograniczenia, które w pewnym momencie sprawią że albo będzie trzeba mocno się nakombinować, albo wręcz trzeba będzie napisać własne rozwiązanie.

Przykład który prezentuje tym razem jest raczej banalny i jest przeznaczony dla początkujących programistów. Podobnie jak udostępniona klasa jodb, jest mocno spolszczony i dodatkowo jest opisana praktycznie każda linia kodu.

Wyobraźmy sobie sytuację że mamy napisać aplikację do wprowadzania danych (na początku załóżmy że te dane nie muszą być walidowane w jakiś sensowny sposób). By sprawę maksymalnie uprościć załóżmy że będziemy dodawać klientów do bazy danych. Ale jak wiadomo wymagania mogą z czasem się zmienić i chcielibyśmy by nasza aplikacja była dość elastyczna.

Stwórzmy na początek bazę danych i tabelę gdzie będziemy trzymać nasze dane:

create database elasticApp;
use elasticApp;
go

create table klienci (
kli_id int not null primary key identity(1,1),
kli_imie varchar(255),
kli_nazwisko varchar(255),
kli_mail varchar(255),
kli_dataDodania datetime default getdate(),
kli_aktywny int default 1
)

Jak widać mamy prostą tabelę. Możemy napisać teraz prostą aplikację która będzie korzystała z jodb,
napiszemy sql z parametrami i można by powiedzieć że jest ok. Jednak jeśli ktoś chciałby byśmy zbierali jeszcze jedno pole np miejsce urodzenia, to musimy dokonać zmiany w bazie danych oraz zmiany w aplikacji (zmiana sql, dodanie kolejnych kontrolek itd).

Możemy też napisać aplikację, która sama zareaguje na zmiany w bazie i nie będziemy musieli nic zmieniać.

Nasza aplikacja może pobierać definicje tabeli z bazy i na tej podstawie generować pola tekstowe i później dynamicznie tworzyć zapytanie SQL by te dane zapisać.


string q = "SELECT top 1 column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name= @nazwaTabeli"
+ " ORDER BY ordinal_position";

powyższe zapytanie wklejone w C# i wykonane pozwoli nam na pobranie listy kolumn.

Później na podstawie listy kolumn generujemy textboxy:


//iterowanie po wszystich wierszach w obiektu datatable
// kazdy wiersz odpowiadna jednej kolumnie
//nazwa kolumny jest w column_name
foreach (DataRow dr in opisTabel.AsEnumerable())
{
if (!dr["column_name"].ToString().ToLower().Contains("id") && !dr["column_name"].ToString().ToLower().Contains("datadodania"))
{
Label l = new Label(); // stworzenie nowej labelki (opis tekstowy);
TextBox t = new TextBox(); // stworzenie nowego textboxa (pole tekstowe).
l.Name = "label_" + dr["column_name"].ToString(); //nadanie nazwy labelki
l.Text = dr["column_name"].ToString(); // ustawienie wyswietlanego tekstu
l.Top = 10 + a; // polozenie od gory
l.Left = 10; // polezenie od lewego brzegu formy
l.Show(); // pokazanie labelki*

t.Name = dr["column_name"].ToString(); //nadanie nazwy dla textboxa
t.Top = 10 + a; // polozenie od gory
t.Left = 160; // polezenie od lewego brzegu formy
t.Show();// pokazanie textboxa*

this.Controls.Add(t); // dodanie textboxa do listy kontrolek na formie
this.Controls.Add(l); // dodanie labelki do kontrolki na formie

a += 25; //dodanie 12 do ostatniej wartosci a
}
}
// po petli stworzymy guzik ktory bedzie zapisywał dane do bazy
Button b = new Button(); // stworzenie obiektu guzika
b.Left = 160; // polozenie od lewej
b.Top = 10 + 25 + a; //polozenie od gory
b.Text = "zapisz"; // nadanie etykiety guzika
b.Click += b_Click; //przypisanie eventu klikniecia do metody (ponizej)
b.Show(); //pokazanie guzika*
this.Controls.Add(b); // dodanie go do formy

//* obiekt zostanie pokazany na formie dopiero po tym jak zostanie dodany do kontrolek
// jesli zostala uzyta metoda show, a obiekt nie znajduje sie w kolekcji, nic sie nie pojawi.
}

Mając przygotowaną formatkę musimy jeszcze przygotować event który obsłuży nam zapis do bazy:


//metoda do obslugi zapisu danych
void b_Click(object sender, EventArgs e)
{
//slownik w ktorym bedziemy zbierac dane do zapisania
Dictionary slownik_danych = new Dictionary();
// przeiterowanie sie po textboxach w celu stworzenia slownika z danymi
foreach (Control c in this.Controls)
{
//prawie 'refleksja' sprawdzenie typow do rzutowania
if (c.GetType() == typeof(TextBox))
{
//rzutowanie kontrolki c na textbox
TextBox t = (TextBox)c;

//stworzenie danych w slowniku nazwa kontrolki (nazwa kolumny) oraz wpisana wartosc
slownik_danych.Add(t.Name, t.Text);
}
}

//zapisanie wprowadzonych danych do bazy
int i = jdb.zapisz_dane(slownik_danych, "klienci");
// sorawdzenie czy się udało zapisać dane:
if (i != 1)
{
// jesli nie to pokazujemy ładny komunikat.
MessageBox.Show("Nie udało się zapisać danych, popraw je", "błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}

Oczywiście do tej pory w jdb nie mieliśmy metody która by potrafiła wygenerować odpowiedni SQL do zapisu danych, poniżej metody które pozwolą to osiągnąć:


///

/// wykonuje sql command z zapytaniami insert/update
///

/// sql command /// ilość wierszy
public int zapisz_dane(SqlCommand sqlc)
{
sqlc.Connection = this.polaczenie; // wskazanie polaczenia do bazy danych
return sqlc.ExecuteNonQuery();
}

///

/// pobiera opis wybranej tabeli
///

/// nazwa tabeli /// informacje o kolumnach w kolejnosci w jakiej są wyświetlane w sql server
public DataTable pobierz_opis_tabeli(string nazwaTabeli)
{
//zapytanie do information_schema pobierajace opis tabeli
string q = "SELECT column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name=@nazwaTabeli"
+ " ORDER BY ordinal_position";

//zwraca obiekt zwrocony przez metode pobierz dane z parametrem
return this.pobierz_dane(q, "@nazwaTabeli", nazwaTabeli);
}

///

/// metoda do wyznaczenia prefixu w nazwach kolumn
///

/// nazwa tabeli /// pefix uzywany w kolumnach
public string getColPrefix(string nazwaTabeli)
{
//probuje wyznaczyc prefix
try
{
// zapytanie ktore zwroci 1 wiersz z info o kolumnach w tabeli
string q = "SELECT top 1 column_name,data_type, character_maximum_length FROM"
+ " information_schema.COLUMNS WHERE table_name= @nazwaTabeli"
+ " ORDER BY ordinal_position";
//pobieranie danych do datatable
DataTable dt = this.pobierz_dane(q, "@nazwaTabeli", nazwaTabeli);

// stworzenie zmiennej prefix ktora zawiera cala nazwe kolumny z 1 wiersza
string prefix = dt.Rows[0]["column_name"].ToString();
int position = prefix.IndexOf('_'); // wyznaczenie pozycji znaku _ ktory oddziela prefix
prefix = prefix.Substring(0, position); // wyznaczenie napisu przed znakiem _

return prefix; // zwrocenie prefixu
}
// jesli sie nie udalo to zwroci null,
// mozna ustawic breakpoint na e.toString by sprawdzic jaki dokladnie blad wystapil
catch (Exception e)
{
e.ToString();
return null;
}
}

///

/// metoda ktora zwraca liste kolumn w formacie listy stringow
///

/// data table z wynikiem zapytania o opis tabeli /// liste stringow z nazwami kolumn
public List gen_lista_kolumn(DataTable dt)
{
//stworzenie nowej listy
List lista = new List();
//iterowanie po liscie kolumn dostarczonej jako parametr
foreach (DataRow dr in dt.AsEnumerable() ){
//dodaj do listy kolumne column_name z wiersza
lista.Add(dr["column_name"].ToString());
}
//zwroc liste stringow
return lista;
}

///

/// metoda do budowania zapytan typu insert
///

/// slownik z danymi do zapisania w bazie /// nazwa tabeli gdzie dane maja byc zapisane /// obiekt sql command do zapisania
public SqlCommand zbuduj_zapytanie_zapisujace(Dictionary daneWej, string nazwaTabeli)
{
//stworzenie obiektu ktory zostanie zwrocony
SqlCommand komenda_sql = new SqlCommand();
//zmienna kontrolna (wykorzystana pozniej)
int inu = 0;
//wygenerowanie listy kolumn w tabeli
List lista_kolumn = this.gen_lista_kolumn(this.pobierz_opis_tabeli(nazwaTabeli));
//zapytanie sql
string sql = " INSERT INTO " + nazwaTabeli + " (";

//iterowanie po kazdym wystapieniu ze slownika w celu zbudowania listy kolumn
foreach (var c in daneWej)
{
// linq (tutaj troche zbedne poniewaz metoda zostala uproszczona)
var kolatr = from p in lista_kolumn
where p.ToLower() == c.Key.ToLower()
select p;
// powyzsze linq sprawdza czy kolumna (c.key) znajduje sie na liscie kolumn w tabeli
kolatr.ToArray().Length.ToString();

//sprawdzenie czy linq zwrocilo jeden element (po drodze cast na tablice).
if (kolatr.ToArray().Length ==1)
{
//sprawdzenie czy dane do zapisania sa 'sensowne'
if (c.Value != null && c.Value.ToString() != "")
{
//doklejenie odpowiedniego kawalka sql
sql += c.Key + ", ";
//inkrementacja zmiennej kontrolnej
inu++;
}
}
}
// po petli usuwam zbedny przecinek
sql = sql.Substring(0, sql.Length - 2);

//dodaje kolejny kawalek kodu sql
sql += ") VALUES (";

// kolejna iteracja po danych wejsciowych teraz by dodac wartosci do zapytania
foreach (var c in daneWej)
{
// to samo linq co wyzej
var kolatr = from p in lista_kolumn
where p.ToLower() == c.Key.ToLower()
select p;
//sprawdzenie czy linq jest ok | tak jak wyzej
if (kolatr.ToArray().Length == 1)
{
//proba stworzenia odpowiedniego parametru i dodanie go sqlcommand
try
{
//sprawdzenie czy dane do zapisu maja 'sens' (jak wyzej)
if (c.Value != null && c.Value.ToString() != "")
{
//stworzenie nazwy parametru
string paramName = "@" + c.Key;
//dodanie parametru do sql
sql += paramName + ", ";
//stworzenie obiektu parametru
SqlParameter param = new SqlParameter (paramName, c.Value);
//dodanie parametru do sql command
komenda_sql.Parameters.Add(param);
}
}
//jesli sie cos nie powiodlo to mozna ustawic sobie breakpoint by zbadac dlaczego nie dziala.
catch (Exception e)
{
e.ToString();
}
}
}

//usuwam zbedne przecinki
sql = sql.Substring(0, sql.Length - 2);
// zakonczenie zapytania
sql += ")";

//sprawdzenie czy udalo sie dodac chociaz jedna kolumne
//zmienna kontrolna :)
if (inu > 0)
{
// przypisanie sklejonego stringa do komendy
komenda_sql.CommandText = sql;
//przypisanie polaczenia do komendy (teraz komenda jest gotowa do wykonania)
komenda_sql.Connection = this.polaczenie;
return komenda_sql;
}
//jesli nie udalo sie dodac zadnej kolumny to zwracamy null
return null;
}

///

/// zapisuje w bazie korzystajac z dynamicznego tworzenia zapytania
///

/// dane do zapisania /// nazwa tabeli gdzie dane maja byc zapisane /// 1 w przypadku sukcesu, -1 gdy porażka
public int zapisz_dane(Dictionary daneWej, string nazwaTabeli)
{
//proba zapisu
try
{
return this.zapisz_dane(this.zbuduj_zapytanie_zapisujace(daneWej, nazwaTabeli));
}
// jesli sie nie udalo, mozna ustawic break point by zobaczyc co poszlo nie tak
catch (Exception e)
{
e.ToString();
return -1;
}
}

kompletny projekt można ściągnąć tu: ElasticApp1

ADO.NET zapytania parametryzowane – C#

W poprzednim wpisie pokazałem, jak napisać prostą klasę w C#, dzięki której można pobierać wyniki prostych zapytań typu select. W tym wpisie pokażę, jak parametryzować zapytania i jak tego nie robić. Zakładam, że czytając ten post, masz drogi czytelniku jakąś klasę na wzór z poprzedniego posta.

W poprzednim poście aplikacja potrafiła wykonać zapytanie ‘select * from klienci’, a gdybyśmy chcieli pokazać konkretnego klienta? Normalnie w SSMS wystarczy napisać zapytanie

select * from klienci where klient_id = 1

Jeśli powyższe zapytanie jest niejasne, zapraszam do lektury o filtrowaniu danych (kurs sql). Oczywiście można w zapytaniu podstawić jakąkolwiek inną tabelę i kolumnę, przykłady opieram na poprzednich postach (zestawy szkoleniowe).

Powyższe zapytanie zwróci nam jeden wiersz.


Zanim przejdziemy do tak zwanego “kodzenia”, jedna uwaga, zmieniłem komputer, na którym prezentuję kod, więc nazwy aplikacji i niektórych kontrolek i obiektów mogły się zmienić.

Gotową klasę można pobrać tutaj
 

By wykonać przykład, który chcę zaprezentować, musimy mieć projekt, posiadający jedną formę, na której znajduje się guzik oraz datagridview.

Gdy nasze zapytanie wkleimy do kodu, gdzie wykonuje się kod po kliknięciu guzika
private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");
dataGridView1.DataSource = db.pobierz_dane("select * from klienci where klient_id=1");
}

Po skompilowaniu, uruchomieniu i kliknięciu guzika pojawi się jeden wiersz w DataGridView:

Oczywiście bez sensu jest zmieniać kod, by zobaczyć inny rekord. Dołóżmy na formę textboxa.

Teraz załóżmy, że użytkownik aplikacji zna numery identyfikacyjne klientów i chce oglądać pojedynczych klientów. Do tego celu będzie w textboxa (pole tekstowe) wpisywał numer klienta.

Najprostszą drogą do osiągnięcia tego celu jest konkatenacja w zapytaniu:
if (textBox1.Text.Length > 0)
{

dataGridView1.DataSource = db.pobierz_dane(“select * from klienci where klient_id=” + textBox1.Text);
}

Powyższy oczywiście zadziała, jeśli użytkownik wpisze liczbę do pola tekstowego. Więc, generalnie nie jest to najlepsze rozwiązanie. Dodatkowo, przy takim rozwiązaniu narażamy się na SQL Injection, użytkownik może dopisać część zapytania, która w skrajnych przypadkach może doprowadzić do katastrofalnych zmian w bazie danych.

Przykład na to, że działa:

Przykład na to, że można popsuć coś w ten sposób:

W między czasie, treść zapytania przeniosłem do zmiennej q, tak by móc zdebuggować program i zobaczyć, jak wygląda zapytanie dostarczane do funkcji. Od razu widać, że taki sposób parametryzacji jest bardzo niebezpieczny.

Aby zrobić to poprowanie, powinniśmy skorzystać z obiektu SqlParameter. Do tego celu najlepiej zmodyfikować naszą klasę do obsługi bazy danych.

Skopiujmy naszą funkcję służącą do pobierania danych i dodajmy argumenty wejściowe:

public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
..

}

Teraz musimy dodać Parametr do obiektu SqlCommand, tak aby nasza funkcja wyglądała, np. tak:
public DataTable pobierz_dane(string q,string nazwa_p,object wartosc_p)
{
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
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Dodaliśmy te dwie linie:
SqlParameter par = new SqlParameter(nazwa_p, wartosc_p); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand

Musimy je dodać w miejscu, gdzie już mamy utworzoną instancję obiektu SqlCommand. Kolejność dodawania nie ma znaczenia. Parametry należy dodać do obiektu przed wywołaniem zapytania (metoda ExecuteReader).

Gdy już mamy gotową metodę, możemy zmienić nieco kod w naszej metodzie wywoływanej po kliknięciu guzika:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,”@id”,textBox1.Text);
}
}

Jak widać, w zapytaniu pojawił się @id. Jest to wskaźnik parametru i informacja o jego nazwie, w przypadku SQLServer parametry powinny się zaczynać się od @. Teraz pod @id, przy wykonywaniu zapytania zostanie podstawiona wartość z pola tekstowego, jednak środowisko .NET zadba za nas o niezbędne walidacje tak, by wszystko zadziałało jak należy:

Teraz próba sql injection skończy się wyjątkiem:

Nasz kod nie posiada bloków try i catch, więc aplikacja najzwyczajniej w świecie się wywali. Kwestii Obsługi błędów poświecę osobny wpis.

Oczywiście może zdarzyć się sytuacja, że będziemy potrzebowali większej ilości parametrów, do tego celu możemy skorzystać ze słowników zaimplementowanych w języku C#. Potrzebujemy znowu przeciążyć metodę od pobierania danych.

Słownik w C# jest listą par obiektów, Para jest kluczem i wartością (tak samo jak SQLParameter). Oczywiście możemy przekazać od razu listę SQLParameter do naszej funkcji lub gotowy obiekt SQLCommand. Ale pokazana ścieżka wymusza użycie obiektów Sql* w jednym miejscu. Tak więc nasza funkcja może wyglądać tak:

public DataTable pobierz_dane(string q, Dictionary < string,object > lista_parametrow )
{
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
SqlParameter par; // deklaracja zmiennej
foreach (KeyValuePair<string, object> parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

dr = sqlc.ExecuteReader(); //wykonanie zapytanie i utworzenie wskaznika dr
dt.Load(dr); //zaladowanie danych do obiektu DataTAble
return dt; // zwrocenie danych

}

Jak widać w pierwszej linii:
public DataTable pobierz_dane(string q, Dictionary< string,object > lista_parametrow )
mamy deklarację zmiennej typu słownikowego: Dictionary< string,object > lista_parametrow

Później dodaliśmy pętlę foreach (co oznacza dla każdego obiektu w liście/słowniku/tablicy etc. wykonaj…)

foreach (KeyValuePair< string, object > parametr in lista_parametrow)
//iteracja po wszystkich elementach slownika
{
par = new SqlParameter(parametr.Key, parametr.Value); // stworzenie nowej instancji parametru
sqlc.Parameters.Add(par); // dodanie utworzonego parametru do sqlCommand
}

Dla każdej pary słownika tworzymy obiekt KeyValuePair, jest to obiekt, potrafiący przechowywać dwie wartości jednocześnie. Jest to po prostu struktura/obiekt, posiadający dwie właściwości (key, value). W pętli powstają nowe instancje obiektu i dodawane są do listy parametrów. Mimo, iż obiekt par za każdym razem – mogłoby się wydawać – będzie nadpisany, o tyle referencje do pamięci będą poprawne i lista parametrów w obiekcie SQLCommand tak naprawdę będzie zawierać pełną listę dostarczonych parametrów. Przeróbmy nasz przykład, by działał ze słownikiem (na razie dodamy tylko jeden parametr).

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb("localhost\\sqlexpress", "zajecia");

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
string q = “select * from klienci where klient_id=@id”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}

Tworzymy obiekt (instancję) słownika i dodajemy do niej jedną parę: klucz i wartość (key,value).
Value jest typem object, co oznacza, że możemy podstawiać tam dowolny typ obiektu, nieważne, czy to będzie data, napis, czy liczba, ten kod zadziała i dane zostaną odpowiednio zwalidowane.

Żeby sprawdzić, czy zadziała to dla większej ilości parametrów, dodajmy jeszcze jedno pole tekstowe. Wyobraźmy sobie, że będziemy porównywać dwóch klientów:

private void button1_Click(object sender, EventArgs e)
{
jodb db = new jodb(“localhost\\sqlexpress”, “zajecia”);

Dictionary<string, object> lista_par = new Dictionary< string, object >();
lista_par.Add(“@id”, textBox1.Text);
lista_par.Add(“@id2”, textBox2.Text);
string q = “select * from klienci where klient_id=@id or klient_id=@id2”;
dataGridView1.DataSource = db.pobierz_dane(q,lista_par);
}
}

Dodajemy jeszcze jeden parametr do zapytania, oraz dodajemy go do słownika, efekt:

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:

Zestaw szkoleniowy 2 – Select i złączenia (sql joins)

Wykonując zadania z zestawu szkoleniowego 1, można było się nauczyć podstaw zapytań SQL,
w zestawie drugim – ćwiczenia związane są z łączeniem danych z różnych tabel. By zachować maksymalną przejrzystość w przykładach zostaną użyte tylko 2 tabele.
Wykonanie poniższego skryptu utworzy odpowiednie tabele i wprowadzi odpowiednie dane, przed wykonaniem skryptu proszę się upewnić że takie struktury nie istnieją w bazie (jeśli wcześniej na tej bazie były wykonywane ćwiczenia z postu o kluczu obcym takie obiekty mogą już tam istnieć).

use zajecia
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 klienci (klient_imie) values ('Ania');
insert into klienci (klient_imie) values ('Marysia');
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)
delete from klienci where klient_id = 5

Po wykonaniu tego skryptu proszę sprawdzić czy wyniki poniższych zapytań są zgodne:
select * from adresy;
select * from klienci

Jeśli wszystko się zgadza proszę wykonać poniższe ćwiczenia. W przypadku wątpliwości zapraszam do lektury:

Zadania

1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
7) Pokaż klientów którzy posiadają więcej niż 1 adres
8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.

Odpowiedzi:
1) Pokaż w jakich miejscowościach mieszkają klienci (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id


Pozostaje jeszcze kwestia wyjaśnienia dlaczego Krzyś pokazał się dwa razy:
Dodając kolumny do zapytania:
select klient_imie,adres_miejscowosc,klient_id,adres_klient_id,adres_id from klienci
inner join adresy on
adres_klient_id=klient_id


Widać wyraźnie iż Krzys ma przypisane 2 różne adresy.

2) Pokaż w jakich miejscowościach klienci posiadają adresy typu 1 (kolumny: klient_imie, adres_miejscowosc)
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id
where adres_typ = 1


Lub można też użyć innego zapisu:
select klient_imie,adres_miejscowosc from klienci
inner join adresy on
adres_klient_id=klient_id and adres_typ = 1

wynik zapytania będzie dokładnie taki sam.

3) Pokaż dla jakich adresów nie ma przypisanych klientów ( przygotuj min. 2 rozwiązania)
Poniżej rozwiązania korzystające ze złączeń:
select adres_miejscowosc,klient_id from adresy
left outer join klienci on
adres_klient_id = klient_id
where klient_id is null

Lub można na odwrót, korzystając ze złączenia prawostronnego:
select adres_miejscowosc,klient_id from klienci
right outer join adresy on
adres_klient_id = klient_id
where klient_id is null

Podane rozwiązania bazują na złączeniach, jednak proszę być świadomym że takie zadanie da się rozwiązać jeszcze inaczej.

4) Pokaż dla jakich klientów nie ma przypisanych adresów ( przygotuj min. 2 rozwiązania)
To zadanie jest analogiczne do poprzedniego.
select klient_imie,adres_miejscowosc from klienci
left outer join adresy on
adres_klient_id = klient_id
where adres_id is null

I tak samo jak poprzednie zadanie można je wykonać z pomocą złączenia prawostronnego:
select klient_imie,adres_miejscowosc from adresy
right outer join klienci on
adres_klient_id = klient_id
where adres_id is null

5) Pokaż w jednej kolumnie imiona i nazwy miejscowości dla wszystkich klientów
select adres_miejscowosc + ' ' + klient_imie from klienci
left outer join adresy on
adres_klient_id = klient_id

6) Dlaczego klient z id=6 w poprzednim zadaniu wyświetlił się jako null? Jak to obejść?
W poprzednich wynikach widać że ostatni wiersz wyświetlił się jako null, jest to spowodowane operacją konkatenacji łańcucha z wartością null (Imię + null) = null.

7) Pokaż klientów którzy posiadają więcej niż 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) > 1

8) pokaż klientów którzy posiadają tylko i wyłącznie 1 adres
select klient_imie from klienci
inner join adresy on
adres_klient_id=klient_id
group by klient_imie
having count(*) =1

9) Podziel klientów na grupy ze względu na pierwszą literę imienia i policz ile przypada adresów na każdą z grup.
select LEFT(klient_imie,1),COUNT(*) from klienci
inner join adresy on
adres_klient_id=klient_id
group by LEFT(klient_imie,1)

Zestaw szkoleniowy 1 – SQL – Podstawy SELECT

Postanowiłem udostępnić dość prosty zestaw szkoleniowy, który powinien pomóc w pierwszych chwilach z bazami danych. Zestaw jest przygotowany dla bazy SQL SERVER, a zadania są weryfikowane w środowisku SQL Server 2008R2.


create database zajecia;
use zajecia;
create table przyjaciele (
id int not null primary key identity(1,1), -- unikalny id - pole numeryczne
imie varchar(255), -- pole tekstowe
plec int, -- 0 dziewczyna 1 facet -- wartosc numeryczna
wiek int, -- wiek w latach -- wartosc liczbowa
zarobki int, -- zarobki w tysiacach -- wartosc liczbowa
kolor int) -- kolor skory 0 jasny 10 ciemny - skala -- wartosc liczbowa

--wstawienie danych do tabeli:
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Janek',1,19,2000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Ania',0,29,5000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krysia',0,25,3000,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marek',1,25,3500,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Krzysztof',1,32,1400,0);
insert into przyjaciele (imie,plec,wiek,zarobki,kolor)
values ('Marysia',0,21,1200,0);

Powyższy kod tworzy bazę zajecia, oraz dodaje do niej jedną tabelę o nazwie przyjaciele. Do utworzonej tabeli ładowane są przykładowe dane. Polecenia te można wkleić do okna zapytań w SSMS i wykonać (przycisk F5 lub execute).

po wykonaniu tych zapytań możemy otworzyć nowe query window (należy się upewnić czy korzystamy z bazy zajęcia) i można wykonać zapytanie:

select * from przyjaciele

Powyżej wynik zapytania.
Teraz lista zadań:
1) Pokaż tylko imiona.
2) Pokaż imiona zapisane Wielkimi literami
3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
4) Pokaż pierwszą i ostatnią literę imienia
5) Pokaż imiona osób które zarabiają mniej niż 2000
6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
7) Pokaż wiersze w których imiona kończą się na literę ‘a’
8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
9) Pokaż minimalne zarobki, maksymalne, średnią.
10) pokaż średnie zarobki w rozbiciu na płeć.

Poniżej odpowiedzi:

1) Pokaż tylko imiona.
select imie from przyjaciele

2) Pokaż imiona zapisane Wielkimi literami

select UPPER(imie) from przyjaciele


3) Pokaż 1 literę imienia i nadaj alias kolumnie pt PierwszaLitera
select LEFT(imie,1) PierwszaLitera from przyjaciele
lub
select LEFT(imie,1) as PierwszaLitera from przyjaciele
lub
select SUBSTRING(imie,1,1) as PierwszaLitera from przyjaciele

4) Pokaż pierwszą i ostatnią literę imienia

select LEFT(imie,1) + RIGHT(imie,1) from przyjaciele

5) Pokaż imiona osób które zarabiają mniej niż 2000
select imie from przyjaciele where zarobki < 2000

6) Pokaż wszystkie kolumny w tabeli posortowane po zarobkach
select * from przyjaciele order by zarobki

7) Pokaż wiersze w których imiona kończą się na literę ‘a’
select * from przyjaciele where imie like '%a'
lub
select * from przyjaciele where right(imie,1) = 'a'

8) Pokaż wiersze w których imiona nie kończą się na literę ‘a’ oraz zarobki są mniejsze niż 2000
select * from przyjaciele where imie not like '%a' and zarobki < 2000
lub
select * from przyjaciele where RIGHT(imie,1)!='a' and zarobki < 2000

9) Pokaż minimalne zarobki, maksymalne, średnią.
select MIN(zarobki),MAX(zarobki), AVG(zarobki) from przyjaciele

10) pokaż średnie zarobki w rozbiciu na płeć.
select AVG(zarobki),plec from przyjaciele
group by plec

weryfikacja numeru identyfikacji podatkowej – NIP

W wielu bazach danych stajemy przed problemem weryfikacji różnych numerów używanych w administracji, opisałem już jak weryfikować PESEL.
Poniżej opis algorytmu weryfikacji NIP (numer identyfikacji podatkowej). Podstawowe informacje o weryfikacji numeru można znaleźć w wikipedii.
Dodam jednak kilka słów od siebie.
Swego czasu NIP’y podawało się z kreskami(myślnikami), nie wnoszą one kompletnie nic do procesu walidacji, więc funkcja je usuwa ze sprawdzanego ciągu znaków. Oczywiście NIP musi być ciągiem numerycznym i zawierać odpowiednią ilość znaków. W dodatku warto uczulić się na wartości skompromitowane które mogą mimo wszystko przechodzić przez walidację, ale mamy 100% pewność że nie istnieją. Są to numery które zaczynają się na 0 bądź posiadają 2 cyfrę w numerze = 0 i pierwszą różną od 1. Czyli NIP zaczynający się 20 jest na pewno niepoprawny mimo iż suma kontrolna może być poprawna.
Suma kontrolna w numerze NIP to suma mnożników dzielona przez 11.

Funkcja zwraca:
1 gdy NIP jest poprawny,
0 w przypadku złej sumy kontrolnej
-2 gdy wartość jest skompromitowana
-3 gdy wartość nie jest numeryczna
-4 gdy długość ciągu jest różna od 10
-5 gdy ciąg jest nieokreślony (null)

CREATE FUNCTION [dbo].[is_nip]
(
— Add the parameters for the function here
@nip varchar(255)
)
RETURNS int
AS
BEGIN

if @nip is null begin return -5 end

set @nip = replace(@nip,’-‘,”)
— zla dlugosc numeru nip
if len(@nip) != 10 begin return -4 end

if ISNUMERIC(@nip) = 0 begin return -3 end

— zwroc wartosci skompromitowane
if @nip=’0000000000′ begin return -2 end
if @nip=’1234567891′ begin return -2 end
if @nip=’1111111111′ begin return -2 end
if @nip=’1111111112′ begin return -2 end
if @nip=’9999999999’ begin return -2 end
if @nip like ‘0%’ begin return -2 end
if @nip like ‘_0%’ and @nip not like ‘1%’ begin return -2 end

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

return 0;

END

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.