3. Studium przypadku

Chcemy stworzyć aplikację .NET, która pozwoli użytkownikowi przeprowadzać symulacje obliczeń wynagrodzeń opiekunek dziecięcych ze stowarzyszenia „Maison de la petite enfance” w danej gminie. Zajmiemy się zarówno organizacją kodu aplikacji DotNet, jak i samym kodem.

3.1. a bazy danych

Dane statyczne potrzebne do sporządzenia karty płacowej są przechowywane w bazie danych SQL Server Express o nazwie dbpam (pam = Paie Assistante Maternelle). Administratorem tej bazy jest użytkownik o nazwie sa, którego hasło brzmi msde.

Baza zawiera trzy tabele: EMPLOYES, COTISATIONS oraz INDEMNITES, których struktura jest następująca:


Table EMPLOYES : rassemble des informations sur les différentes assistantes maternelles

Struktura:

`SS`	numer ubezpieczenia społecznego pracownika – klucz główny
`NOM`	nazwisko pracownika
`prenom`	jego imię
`ADRESSE`	jego adres
`VILLE`	jego miasto
`CODEPOSTAL`	jego kod pocztowy
`INDICE`	jego wskaźnik wynagrodzenia – klucz obcy w polu [INDICE] tabeli [INDEMNITES]

Jego zawartość mogłaby wyglądać następująco:


Table COTISATIONS : rassemble les taux des cotisations sociales prélevées sur le salaire

Struktura:

Jego zawartość mogłaby wyglądać następująco:

Stawki składek na ubezpieczenie społeczne są niezależne od pracownika. Powyższa tabela zawiera tylko jeden wiersz.


Table INDEMNITES : rassemble les différentes indemnités dépendant de l'indice de l'employé

`INDICE`	wskaźnik wynagrodzenia – klucz główny
`BASEHEURE`	cena netto w euro za godzinę opieki
`ENTRETIENJOUR`	dodatek na utrzymanie w euro za dzień opieki
`REPASJOUR`	dodatek na posiłki w euro za dzień opieki
`INDEMNITESCP`	dodatek urlopowy. Jest to procent, który należy zastosować do wynagrodzenia podstawowego.

Jego treść mogłaby wyglądać następująco:

Należy zauważyć, że dodatki mogą się różnić w zależności od opiekunki. Są one bowiem powiązane z konkretną opiekunką poprzez jej wskaźnik wynagrodzenia. Tak więc pani Marie Jouveinal, której wskaźnik wynagrodzenia wynosi 2 (tabela EMPLOYES), ma stawkę godzinową w wysokości 2,1 euro (tabela INDEMNITES).

Związki między tymi trzema tabelami są następujące:

Między kolumną EMPLOYES (INDICE) a kolumną INDEMNITES (INDICE) istnieje relacja klucza obcego.

W ten sposób utworzona baza danych [dbpam] generuje dwa pliki w folderze serwera SQL Server Express:

Pliki o nazwie [dbpam.mdf, dbpam_log.ldf] można przenieść na inny komputer i ponownie podłączyć do serwera SGBD SQL Server Express na tym komputerze. Oto jak to zrobić:

Pliki BD i [dbpam] należy skopiować do folderu

uruchamia się program SQL Server Express
za pomocą klienta SQL Server Management Studio Express, a następnie dołączyć plik [dbpam.mdf] do pliku SGBD:

kliknij prawym przyciskiem myszy na pliku [Databases] / Dołącz
wybierz plik [dbpam.mdf] za pomocą przycisku [Add] (nie pokazany na obrazku)
załączony plik spowoduje utworzenie pliku BD, który nie może już istnieć. Tutaj, w polu [Attach As] nadano nazwę [dbpam2] nowemu plikowi BD.
Widoczny jest nowy plik BD wraz z jego tabelami

Ta technika dołączania pliku BD jest przydatna do przenoszenia pliku BD z jednego stanowiska na inne i będziemy z niej czasami korzystać w tym miejscu.

3.2. Sposób obliczania wynagrodzenia opiekunki do dzieci

Przedstawiamy teraz sposób obliczania miesięcznego wynagrodzenia opiekunki do dzieci. Jako przykład posłużymy się wynagrodzeniem pani Marie Jouveinal, która przepracowała 150 godzin w ciągu 20 dni w miesiącu rozliczeniowym.

Uwzględniono następujące elementy:	`[TOTALHEURES]: total des heures travaillées dans le mois` `[TOTALJOURS]: total des jours travaillés dans le mois`	`[TOTALHEURES]=150 [TOTALJOURS]= 20`
Wynagrodzenie podstawowe opiekunki do dzieci oblicza się według następującego wzoru:	`[SALAIREBASE]=([TOTALHEURES]* [BASEHEURE])*(1+ [INDEMNITESCP]/100)`	`[SALAIREBASE]=(150[2.1])(1+0.15)= 362,25`
Od tego wynagrodzenia podstawowego należy potrącić określone składki na ubezpieczenie społeczne:	`Contribution sociale généralisée et contribution au remboursement de la dette sociale : [SALAIREBASE][CSGRDS/100]` `Contribution sociale généralisée déductible : [SALAIREBASE][CSGD/100]` `Sécurité sociale, veuvage, vieillesse : [SALAIREBASE][SECU/100]` `Retraite Complémentaire + AGPF + Assurance Chômage : [SALAIREBASE][RETRAITE/100]`	`CSGRDS : 12,64` `CSGD : 22,28` `Sécurité sociale : 34,02` `Retraite : 28,55`
Łączna kwota składek na ubezpieczenie społeczne:	`[COTISATIONSSOCIALES]=[SALAIR EBASE]*(CSGRDS+CSGD+SECU+RETR`	`[COTISATIONSSOCIALES]=97,48`
Ponadto opiekunka ma prawo, za każdy przepracowany dzień, do dodatku na utrzymanie oraz dodatku na posiłki. W związku z tym otrzymuje następujące dodatki:	`[Indemnités]=[TOTALJOURS]*(EN TRETIENJOUR+REPASJOUR)`	`[INDEMNITES]=104`
Ostatecznie wynagrodzenie netto przysługujące opiekunce nad dziećmi wynosi:	`[SALAIREBASE]- [COTISATIONSSOCIALES]+ [INDEMNITÉS]`	`[salaire NET]=368,77`

3.3. Przypomnienia ADO.NET

Aplikacja do obliczania wynagrodzeń wymaga informacji z bazy danych [dbpam]. Jej architektura będzie wyglądać następująco:

w [1] użytkownik wysyła zapytanie
w [2] aplikacja do obliczania wynagrodzeń przetwarza to żądanie.
może wówczas potrzebować danych z bazy danych. Wysyła więc zapytanie do dostawcy (provider) ADO.NET dotyczące bazy SGBD wykorzystywanej przez [4].
Ten ostatni korzysta z bazy danych [5] i przekazuje wyniki dostawcy ADO.NET, który z kolei przekazuje je aplikacji
ta z kolei wykorzystuje te wyniki i generuje odpowiedź [5] dla użytkownika

Przypomnijmy teraz główne interfejsy udostępniane przez dostawcę ADO.NET swoim klientom [3].

W trybie połączonym aplikacja:

nawiązuje połączenie ze źródłem danych
współpracuje ze źródłem danych w trybie odczytu/zapisu
zamyka połączenie

Operacje te dotyczą głównie trzech interfejsów ADO.NET:

IDbConnection, który zawiera właściwości i metody połączenia.
IDbCommand, który zawiera właściwości i metody wykonanego polecenia SQL.
IDataReader, który zawiera właściwości i metody wyniku polecenia SQL Select.

Interfejs IDbConnection

Służy do zarządzania połączeniem z bazą danych. Wśród metod M i właściwości P tego interfejsu znajdują się następujące:

Nazwa	Typ	Rola
`ConnectionString`	P	ciąg znaków służący do połączenia z bazą danych. Określa on wszystkie parametry niezbędne do nawiązania połączenia z konkretną bazą danych.
`Open`	M	otwiera połączenie z bazą zdefiniowaną przez ConnectionString
`Close`	M	zamyka połączenie
`BeginTransaction`	M	rozpoczyna transakcję.
`State`	P	stan połączenia: ConnectionState.Closed, ConnectionState.Open, ConnectionState.Connecting, ConnectionState.Executing, ConnectionState.Fetching, ConnectionState.Broken

Jeśli Connection jest klasą implementującą interfejs IDbConnection, nawiązanie połączenia może przebiegać w następujący sposób:

1
2
3

IDbConnection connexion=new Connection();
connexion.ConnectionString=...;
connexion.Open();

Interfejs IDbCommand

Służy do wykonania polecenia SQL lub procedury przechowywanej. Wśród metod M i właściwości P tego interfejsu znajdują się następujące:

Nazwa	Typ	Rola
`CommandType`	P	wskazuje, co należy wykonać – przyjmuje wartości z listy wyliczeniowej: - CommandType.Text: wykonuje polecenie SQL zdefiniowane we właściwości CommandText. Jest to wartość domyślna. - CommandType.StoredProcedure: wykonuje procedurę przechowywaną w bazie
`CommandText`	P	- tekst polecenia SQL, które ma zostać wykonane, jeśli CommandType = CommandType.Text - nazwa procedury przechowywanej, która ma zostać wykonana, jeśli CommandType = CommandType.StoredProcedure
`Connection`	P	połączenie IDbConnection, które ma zostać użyte do wykonania zlecenia SQL
`Transaction`	P	transakcja IDbTransaction, w ramach której ma zostać wykonane zlecenie SQL
`Parameters`	P	lista parametrów zdefiniowanego polecenia SQL. Polecenie update articles set price=price1.1 where id=@id* posiada parametr @id.
`ExecuteReader`	M	w celu wykonania polecenia SQL Select. Otrzymujemy obiekt IDataReader reprezentujący wynik polecenia Select.
`ExecuteNonQuery`	M	w celu wykonania polecenia SQL: aktualizacja, wstawienie, usunięcie. Otrzymujemy liczbę wierszy, których dotyczy operacja (zaktualizowanych, wstawionych, usuniętych).
`ExecuteScalar`	M	aby wykonać polecenie SQL Select, które zwraca tylko jeden wynik, tak jak w przypadku: select count() from articles*.
`CreateParameter`	M	w celu utworzenia parametrów IDbParameter dla zdefiniowanego zlecenia SQL.
`Prepare`	M	pozwala zoptymalizować wykonanie zapytania z parametrami, gdy jest ono wykonywane wielokrotnie z różnymi parametrami.

Jeśli Command jest klasą implementującą interfejs IDbCommand, wykonanie polecenia SQL bez transakcji będzie miało następującą postać:

// nawiązanie połączenia 
IDbConnection connexion=...
connexion.Open();
// przygotowanie polecenia
IDbCommand commande=new Command();
commande.Connection=connexion;
// wykonanie polecenia select
commande.CommandText="select ...";
IDbDataReader reader=commande.ExecuteReader();
...
// wykonanie polecenia update, insert, delete
commande.CommandText="insert ...";
int nbLignesInsérées=commande.ExecuteNonQuery();
...
// zamknięcie połączenia
connexion.Close();

Interfejs IDataReader

Służy do enkapsulacji wyników polecenia SQL Select. Obiekt IDataReader reprezentuje tabelę z wierszami i kolumnami, z której korzysta się sekwencyjnie: najpierw pierwszy wiersz, potem drugi itd. Wśród metod M i właściwości P tego interfejsu znajdują się następujące:

Nazwa	Typ	Rola
`FieldCount`	P	liczba kolumn w tabeli IDataReader
`GetName`	M	GetName(i) zwraca nazwę kolumny nr i z tabeli IDataReader.
`Item`	P	Element [i] reprezentuje kolumnę nr i w bieżącym wierszu tabeli IDataReader.
`Read`	M	przechodzi do następnego wiersza tabeli IDataReader. Zwraca wartość logiczną True, jeśli odczyt się powiódł, a w przeciwnym razie – False.
`Close`	M	zamyka tabelę IDataReader.
`GetBoolean`	M	GetBoolean(i): zwraca wartość logiczną z kolumny nr i bieżącego wiersza tabeli IDataReader. Inne analogiczne metody to: GetDateTime, GetDecimal, GetDouble, GetFloat, GetInt16, GetInt32, GetInt64, GetString.
`Getvalue`	M	Getvalue(i): zwraca wartość z kolumny nr i bieżącego wiersza tabeli IDataReader jako typ object.
`IsDBNull`	M	IsDBNull(i) zwraca True, jeśli kolumna nr i w bieżącym wierszu tabeli IDataReader nie ma wartości, co jest oznaczone wartością SQL NULL.

Wykorzystanie obiektu IDataReader często wygląda następująco:

// otwarcie połączenia 
IDbConnection connexion=...
connexion.Open();
// przygotowanie polecenia
IDbCommand commande=new Command();
commande.Connection=connexion;
// wykonanie polecenia select
commande.CommandText="select ...";
IDataReader reader=commande.ExecuteReader();
// przetwarzanie wyników
while(reader.Read()){
     // obsługa bieżącej linii
        ...
}
// zamknięcie czytnika
reader.Close();
// zamknięcie połączenia
connexion.Close();