9. Dostęp do baz danych

9.1. Konnektor ADO.NET

Wróćmy do architektury warstwowej, z której korzystaliśmy już wielokrotnie

W analizowanych przykładach warstwa [dao] wykorzystywała dotychczas dwa rodzaje źródeł danych:

dane zapisane na stałe w kodzie
dane pochodzące z plików tekstowych

W niniejszym rozdziale analizujemy przypadek, w którym dane pochodzą z bazy danych. Architektura trójwarstwowa ewoluuje wówczas w kierunku architektury wielowarstwowej. Istnieje wiele jej odmian. Omówimy podstawowe pojęcia na przykładzie następującej architektury:

Na powyższym schemacie warstwa [dao] [1] komunikuje się z warstwą SGBD [3] za pośrednictwembiblioteki klas właściwej dla używanego modułu SGBD i dostarczanej wraz z nim. Warstwa ta implementuje standardowe funkcje zebrane pod nazwą ADO (Active X Data Objects). Taką warstwę nazywamy dostawcą (w tym przypadku dostawcą dostępu do bazy danych) lub łącznikiem. Większość plików SGBD posiada obecnie łącznik ADO.NET, co nie miało miejsca na początku istnienia platformy .NET. Konnektory .NET nie oferują standardowego interfejsu dla warstwy [dao], dlatego w jej kodzie znajdują się nazwy klas konnektora. Jeśli zmienimy SGBD, zmieniamy łącznik i klasy, a zatem konieczna jest zmiana warstwy [dao]. Jest to architektura zarówno wydajna, ponieważ łącznik .NET, napisany dla konkretnego SGBD, potrafi go optymalnie wykorzystać, jak i sztywna, ponieważ zmiana SGBD wiąże się ze zmianą warstwy [dao]. Ten drugi argument należy jednak relativizować: przedsiębiorstwa nie zmieniają SGBD zbyt często. Ponadto, jak zobaczymy później, od wersji 2.0 .NET istnieje łącznik generyczny, który zapewnia elastyczność bez utraty wydajności.

9.2. Dwa tryby korzystania ze źródła danych

Platforma .NET umożliwia korzystanie ze źródła danych na dwa różne sposoby:

tryb połączony
tryb offline

W trybie połączonym aplikacja

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

W trybie offline aplikacja

nawiązuje połączenie ze źródłem danych
pobiera kopię pamięciową wszystkich lub części danych ze źródła
zamyka połączenie
pracuje z kopią danych w pamięci, umożliwiającą odczyt i zapis
gdy praca zostanie zakończona, otwiera połączenie, wysyła zmodyfikowane dane do źródła danych w celu ich uwzględnienia, a następnie zamyka połączenie

W niniejszym rozdziale omawiamy wyłącznie tryb połączony.

9.3. Podstawowe pojęcia związane z obsługą bazy danych

Przedstawimy główne zasady korzystania z bazy danych na przykładzie serwera SQL Server Compact 3.5. Ten serwer SGBD jest dostarczany wraz z programem Visual Studio Express. Jest to lekka wersja serwera, która może obsługiwać tylko jednego użytkownika naraz. Wystarcza ona jednak do zapoznania się z programowaniem z wykorzystaniem baz danych. W dalszej części omówimy inne serwery.

Zastosowana architektura będzie wyglądać następująco:

Aplikacja konsolowa [1] będzie korzystać z bazy danych typu SqlServer Compact [3,4] za pośrednictwem łącznika Ado.Net tego SGBD [2].

9.3.1. — przykładowa baza danych

Będziemy tworzyć bazę danych bezpośrednio w programie Visual Studio Express. W tym celu utworzymy nowy projekt typu konsolowego.

[1]: projekt
[2]: otwieramy widok „Eksplorator baz danych”
[3]: tworzymy nowe połączenie

[4]: wybieramy typ z SGBD
[5,6]: wybieramy SGBD SQL Server Compact
[7]: tworzy się bazę danych
[8]: baza danych SQL Server Compact jest zawarta w jednym pliku o rozszerzeniu .sdf. Należy wskazać miejsce jej utworzenia, w tym przypadku w folderze projektu C#.
[9]: nowej bazie nadano nazwę [dbarticles.sdf]
[10]: wybieramy język francuski. Ma to wpływ na operacje sortowania.
[11,12]: bazę danych można zabezpieczyć hasłem. Tutaj „dbarticles”.
[13]: zatwierdzamy stronę z informacjami. Baza danych zostanie fizycznie utworzona:

[14]: nazwa właśnie utworzonej bazy danych
[15]: zaznaczamy opcję „Zapisz moje hasło”, aby nie trzeba było go za każdym razem ponownie wpisywać
[16]: sprawdzamy połączenie
[17]: wszystko w porządku
[18]: zatwierdzamy stronę z informacjami
[19]: połączenie pojawia się w przeglądarce baz danych
[20]: na razie baza nie zawiera tabel. Tworzymy jedną. Artykuł będzie zawierał następujące pola:
- id: unikalny identyfikator – klucz główny
- nom: nazwa artykułu – unikalna
- prix: cena artykułu
- stockactuel: aktualny stan magazynowy
- stockminimum: minimalny stan magazynowy, poniżej którego należy uzupełnić zapasy danego artykułu

[21]: pole [id] jest typu całkowitego i stanowi klucz główny [22] tabeli.
[23]: ten klucz główny jest typu Identity. Ta specyficzna dla serwera SGBD SQL koncepcja oznacza, że klucz główny zostanie wygenerowany przez sam serwer SGBD. W tym przypadku kluczem głównym będzie liczba całkowita zaczynająca się od 1 i zwiększana o 1 przy każdym nowym kluczu.

[24]: tworzone są pozostałe pola. Należy zauważyć, że pole [nom] ma , która jest ograniczona warunkiem unikalności [25].
[26]: nadajemy nazwę tabeli
[27]: po zatwierdzeniu struktury tabeli pojawia się ona w bazie danych.

[28]: wyświetlanie zawartości tabeli
[29]: na razie jest pusta
[30]: wypełniamy ją kilkoma danymi. Wiersz jest zatwierdzany natychmiast po przejściu do wprowadzania kolejnego wiersza. Pole [id] nie jest wypełniane: jest generowane automatycznie po zatwierdzeniu wiersza.

Pozostaje nam skonfigurować projekt tak, aby ta baza danych, która obecnie znajduje się w katalogu głównym projektu, była automatycznie kopiowana do folderu wykonawczego projektu:

[1]: wyświetlamy wszystkie pliki
[2]: pojawia się baza [dbarticles.sdf]
[3]: dodajemy ją do projektu

[4]: operacja dodawania źródła danych do projektu uruchamia kreatora, którego tutaj nie potrzebujemy [5].
[6]: baza danych jest teraz częścią projektu. Wracamy do trybu normalnego [7].
[8]: projekt wraz z bazą danych
[9]: w właściwościach bazy danych, oznaczonej jako [10], widać, że zostanie ona automatycznie skopiowana do folderu uruchomieniowego projektu. To właśnie stamtąd program, który zamierzamy napisać, będzie ją pobierał.

Teraz, gdy mamy już dostępną bazę danych, będziemy mogli z niej korzystać. Najpierw jednak przypomnijmy sobie kilka kwestii SQL.

9.3.2. Cztery podstawowe polecenia języka SQL

SQL (Structured Language Query) to częściowo znormalizowany język służący do wysyłania zapytań i aktualizacji baz danych. Wszystkie SGBD są zgodne ze znormalizowaną częścią SQL, ale wzbogacają język o własne rozszerzenia, które wykorzystują pewne specyficzne cechy SGBD. Spotkaliśmy się już z dwoma przykładami: automatyczne generowanie kluczy głównych oraz dozwolone typy dla kolumn tabeli często zależą od SGBD.

Cztery podstawowe polecenia języka SQL, które przedstawiamy, są znormalizowane i akceptowane przez wszystkie wersje SGBD:

`select col1, col2,... from table1, table2,... where condition order by expression ...`	Zapytanie służące do pobrania danych zawartych w bazie. Obowiązkowe są tylko słowa kluczowe w pierwszym wierszu, pozostałe są opcjonalne. Istnieją również inne słowa kluczowe, które nie zostały tutaj przedstawione. Dokonuje się połączenia ze wszystkimi tabelami, które znajdują się za słowem kluczowym „from” Zachowane są wyłącznie kolumny występujące po słowie kluczowym „select” Zachowane są wyłącznie wiersze spełniające warunek określony przez słowo kluczowe „where” Wynikowe wiersze, uporządkowane zgodnie z wyrażeniem zawartym w słowie kluczowym „order by”, stanowią wynik zapytania. Wynik ten jest tabelą.
`insert into table(col1,col2, ...) values (val1,val2, ...)`	Wstawia wiersz do tabeli. (col1, col2, ...) określa kolumny wiersza, które mają zostać zapełnione wartościami (val1, val2, ...).
`update table set col1=val1, col2=val2 where condition`	Aktualizuje wiersze tabeli spełniające warunek (wszystkie wiersze, jeśli nie ma warunku WHERE). Dla tych wierszy kolumna coli otrzymuje wartość vali
`delete from table where condition`	Usuwa wszystkie wiersze tabeli spełniające warunek

Napiszemy aplikację konsolową umożliwiającą wysyłanie poleceń SQL do bazy danych [dbarticles], którą utworzyliśmy wcześniej. Oto przykładowe wykonanie. Zachęcamy czytelnika do zapoznania się z wysłanymi poleceniami SQL i ich wynikami.

Chaîne de connexion à la base : [Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;Persist Security Info=True]

Requête SQL (rien pour arrêter) : select id,nom,prix,stockactuel,stockminimum from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 vélo 500 10 5
2 pompe 10 10 2
3 arc 600 4 1
4 flèches - lot de 6 100 12 20
5 combinaison de plongée 300 8 2
6 bouteilles d'oxygène 120 10 5

Requête SQL (rien pour arrêter) : insert into articles(nom,prix,stockactuel,stockminimum) values('x',100,10,1)
Il y a eu 1 ligne(s) modifiée(s)

Requête SQL (rien pour arrêter) : select id,nom,prix,stockactuel,stockminimum from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 vélo 500 10 5
...
6 bouteilles d'oxygène 120 10 5
9 x 100 10 1

Requête SQL (rien pour arrêter) : update articles set prix=prix*1.1 where id=9
Il y a eu 1 ligne(s) modifiée(s)

Requête SQL (rien pour arrêter) : select id,nom,prix,stockactuel,stockminimum from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 vélo 500 10 5
...
6 bouteilles d'oxygène 120 10 5
9 x 110 10 1

Requête SQL (rien pour arrêter) : delete from articles where id=9
Il y a eu 1 ligne(s) modifiée(s)

Requête SQL (rien pour arrêter) : select id,nom,prix,stockactuel,stockminimum from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 vélo 500 10 5
...
6 bouteilles d'oxygène 120 10 5

wiersz 1: tzw. ciąg połączenia: zawiera wszystkie parametry umożliwiające połączenie z bazą danych.
wiersz 3: żądamy zawartości tabeli [articles]
wiersz 16: wstawiamy nowy wiersz. Należy zauważyć, że pole id nie jest inicjowane w tej operacji, ponieważ to pole SGBD wygeneruje wartość tego pola.
wiersz 19: weryfikacja. Wiersz 28 – wiersz został pomyślnie dodany.
wiersz 30: zwiększamy o 10% cenę właśnie dodanego artykułu.
wiersz 33: sprawdzamy
wiersz 42: podwyżka ceny rzeczywiście miała miejsce
wiersz 44: usuwamy wcześniej dodany artykuł
wiersz 47: sprawdzamy
wiersze 53–55: pozycja już nie istnieje.

9.3.3. Podstawowe interfejsy ADO.NET dla trybu połączonego

Wróćmy do schematu aplikacji korzystającej z bazy danych za pośrednictwem łącznika ADO.NET:

W trybie połączonym aplikacja:

otwiera połączenie ze źródłem danych
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. Metody M i właściwości P tego interfejsu, z których będziemy korzystać, to:

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. Metody M i właściwości P tego interfejsu, z których będziemy korzystać, to:

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 należy wykonać polecenie 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. Uzyskuje się liczbę wierszy, których dotyczy operacja (zaktualizowanych, wstawionych, usuniętych).
`ExecuteScalar`	M	w celu wykonania polecenia 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 zlecenia
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 zlecenia SQL Select. Obiekt IDataReader reprezentuje tabelę z wierszami i kolumnami, którą przetwarzamy sekwencyjnie: najpierw pierwszy wiersz, potem drugi itd. Metody M i właściwości P tego interfejsu, z których będziemy korzystać, to:

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()){
     // przetwarzanie bieżącej linii
        ...
}
// zamknięcie czytnika
reader.Close();
// zamknięcie połączenia
connexion.Close();

9.3.4. Obsługa błędów

Wróćmy do architektury aplikacji z bazą danych:

Warstwa [dao] może napotkać wiele błędów podczas obsługi bazy danych. Błędy te będą zgłaszane jako wyjątki generowane przez łącznik ADO.NET. Kod warstwy [dao] musi je obsługiwać. Każda operacja na bazie danych musi być wykonywana w bloku try / catch / finally, aby przechwycić i obsłużyć ewentualny wyjątek oraz zwolnić niezbędne zasoby. W ten sposób kod przedstawiony powyżej, służący do przetwarzania wyniku polecenia Select, przyjmuje następującą postać:

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

Niezależnie od sytuacji obiekty IDataReader i IDbConnection muszą zostać zamknięte. Dlatego też zamknięcie to odbywa się w blokach finally.

Zamknięcie połączenia oraz obiektu IDataReader można zautomatyzować za pomocą klauzuli using:

// przetwarzanie połączenia
try{
    using(IDbConnection connexion=...){
         // otwarcie
        connexion.Open();
         // przygotowanie zamówienia
        IDbCommand commande=new Command();
        commande.Connection=connexion;
         // wykonanie polecenia select
        commande.CommandText="select ...";
        using(IDbDataReader reader=commande.ExecuteReader()){
             // przetwarzanie wyników
            while(reader.Read()){
             // przetwarzanie bieżącej linii
            ...
        }// przy użyciu IData
    }//przy użyciu IDbConnection
}catch(Exception ex){
     // obsługa wyjątków
    ...
}
..

W wierszu 3 klauzula using gwarantuje, że połączenie otwarte w bloku using(...){...} zostanie zamknięte poza tym blokiem, niezależnie od sposobu wyjścia z bloku: w sposób normalny lub w wyniku wystąpienia wyjątku. Oszczędzamy jeden finally, ale korzyść nie polega na tej niewielkiej oszczędności. Zastosowanie using pozwala programiście uniknąć konieczności samodzielnego zamykania połączenia. Zapomnienie o zamknięciu połączenia może jednak pozostać niezauważone i spowodować „zawieszenie” aplikacji w sposób, który będzie wydawał się przypadkowy, za każdym razem, gdy obiekt SGBD osiągnie maksymalną liczbę otwartych połączeń, jaką jest w stanie obsłużyć.
Wiersz 11: w podobny sposób zamykamy obiekt IDataReader.

9.3.5. Konfiguracja przykładowego projektu

Ostateczny projekt będzie wyglądał następująco:

[1]: projekt będzie zawierał plik konfiguracyjny [App.config]
[2]: wykorzystuje on klasy z dwóch plików DLL, które domyślnie nie są uwzględnione w odwołaniach i które należy zatem dodać do odwołań projektu:
- [System.Configuration] w celu wykorzystania pliku konfiguracyjnego [App.config]
- [System.Data.SqlServerCe] w celu korzystania z bazy danych SQL Server Compact
[3, 4]: przypomnienie, jak dodać odwołania do projektu.
[5, 6]: przypomnienie, jak dodać plik [App.config] do projektu.

Plik konfiguracyjny [App.config] będzie wyglądał następująco:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
  <add name="dbSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
 </connectionStrings>
</configuration>

wiersze 3–5: tag <connectionStrings> w liczbie mnogiej definiuje ciągi połączeń z bazami danych. Ciąg połączenia ma postać „parametr1=wartość1;parametr2=wartość2;...”. Określa ona wszystkie parametry niezbędne do nawiązania połączenia z konkretną bazą danych. Ciągi połączeń zmieniają się w zależności od każdego pliku SGBD. Strona [http://www.connectionstrings.com/] podaje ich format dla głównych plików SGBD.
wiersz 4: definiuje konkretny ciąg połączenia, w tym przypadku dla bazy SQL Server Compact dbarticles.sdf, którą utworzyliśmy wcześniej:
- name = nazwa ciągu połączenia. To właśnie na podstawie tej nazwy program w języku C# pobiera ciąg połączenia
- connectionString: ciąg połączenia dla bazy danych SQL Server Compact
- DataSource: określa ścieżkę do bazy danych. Składnia |DataDirectory| wskazuje folder, w którym uruchamiany jest projekt.
- Hasło: hasło do bazy danych. Parametr ten jest pomijany, jeśli nie ma hasła.

Kod w języku C# służący do pobrania poprzedniego ciągu połączenia wygląda następująco:


string connectionString = ConfigurationManager.ConnectionStrings["dbSqlServerCe"].ConnectionString;

ConfigurationManager to klasa DLL [System.Configuration], która umożliwia obsługę pliku [App.config].
ConnectionsStrings["nom"].ConnectionString: oznacza atrybut connectionString tagu < add name="nazwa" connectionString="..."> w sekcji <connectionStrings> z [App.config]

Projekt został skonfigurowany. Teraz przyjrzymy się klasie [Program.cs], której przykładowe działanie widzieliśmy wcześniej.

9.3.6. Przykładowy program

Program [program.cs] wygląda następująco:


using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Text;
using System.Text.RegularExpressions;
using System.Configuration;

namespace Chap7 {
    class SqlCommands {
        static void Main(string[] args) {

            // konsola aplikacji – wykonuje zapytania SQL wpisane z klawiatury
            // w bazie danych, której ciąg połączenia jest pobierany z pliku konfiguracyjnego

            // obsługa pliku konfiguracyjnego [App.config]
            string connectionString = null;
            try {
                connectionString = ConfigurationManager.ConnectionStrings["dbSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // wyświetlenie ciągu połączenia
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // tworzy się słownik akceptowanych poleceń SQL
            string[] commandesSQL = new string[] { "select", "insert", "update", "delete" };
            Dictionary<string, bool> dicoCommandes = new Dictionary<string, bool>();
            for (int i = 0; i < commandesSQL.Length; i++) {
                dicoCommandes.Add(commandesSQL[i], true);
            }

            // odczyt i wykonanie poleceń SQL wpisanych z klawiatury
            string requête = null;    // tekst zapytania SQL
            string[] champs;            // pola zapytania    
            Regex modèle = new Regex(@"\s+");    // sekwencja spacji

            // pętla wprowadzania i wykonywania poleceń SQL wpisanych z klawiatury
            while (true) {
                // żądanie zapytania
                Console.Write("\nRequête SQL (rien pour arrêter) : ");
                requête = Console.ReadLine().Trim().ToLower();
                // koniec?
                if (requête == "")
                    break;
                // rozbijamy zapytanie na pola
                champs = modèle.Split(requête);
                // czy zapytanie jest prawidłowe?
                if (champs.Length == 0 || ! dicoCommandes.ContainsKey(champs[0])) {
                    // komunikat o błędzie
                    Console.WriteLine("Requête invalide. Utilisez select, insert, update, delete ou rien pour arrêter");
                    // kolejne zapytanie
                    continue;
                }
                // wykonanie zapytania
                if (champs[0] == "select") {
                    ExecuteSelect(connectionString, requête);
                } else
                    ExecuteUpdate(connectionString, requête);
            }
        }

        // wykonanie zapytania aktualizacyjnego
        static void ExecuteUpdate(string connectionString, string requête) {
    ...
        }

        // wykonywanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
....
        }
    }
}

wiersze 1–6: przestrzenie nazw używane w aplikacji. Zarządzanie bazą danych SQL Server Compact wymaga przestrzeni nazw [System.Data.SqlServerCe] z wiersza 3. Mamy tu do czynienia z zależnością od przestrzeni nazw zastrzeżonej dla SGBD. Można z tego wywnioskować, że w przypadku zmiany SGBD konieczna będzie modyfikacja programu.
wiersz 18: ciąg połączenia z bazą danych jest odczytywany z pliku [App.config] i wyświetlany w wierszu 25. Posłuży on do nawiązania połączenia z bazą danych.
wiersze 28–32: słownik przechowujący nazwy czterech dozwolonych poleceń SQL: select, insert, update, delete.
wiersze 40–62: pętla służąca do wprowadzania poleceń SQL wpisywanych z klawiatury oraz ich wykonywania w bazie danych
wiersz 48: wiersz wpisany na klawiaturze jest rozkładany na pola w celu ustalenia pierwszego elementu, który musi brzmieć: select, insert, update, delete
wiersze 50–55: jeśli zapytanie jest nieprawidłowe, wyświetlany jest komunikat o błędzie i przechodzi się do następnego zapytania.
wiersze 57–61: wykonywane jest wprowadzone polecenie SQL. Sposób wykonania różni się w zależności od tego, czy mamy do czynienia z poleceniem select, czy też z poleceniem insert, update, delete. W pierwszym przypadku polecenie pobiera dane z bazy bez wprowadzania w niej zmian, w drugim aktualizuje bazę bez pobierania danych. W obu przypadkach wykonanie zleca się metodzie, która wymaga dwóch parametrów:
- ciąg połączenia, który umożliwi jej połączenie z bazą danych
- polecenie SQL, które ma zostać wykonane przy użyciu tego połączenia

9.3.7. Wykonanie zapytania SELECT

Wykonanie zlecenia SQL wymaga wykonania następujących kroków:

Połączenie z bazą danych
Wysyłanie poleceń SQL do bazy danych
Przetwarzanie wyników polecenia SQL
Zamknięcie połączenia

Kroki 2 i 3 są wykonywane wielokrotnie, a zamknięcie połączenia następuje dopiero po zakończeniu operacji na bazie danych. Otwarte połączenia są ograniczonymi zasobami w ramach SGBD. Należy je oszczędzać. Dlatego zawsze dąży się do ograniczenia czasu trwania otwartego połączenia. W analizowanym przykładzie połączenie jest zamykane po każdym poleceniu SQL. Dla kolejnego polecenia SQL otwierane jest nowe połączenie. Otwieranie i zamykanie połączenia wiąże się z kosztami. Aby zmniejszyć ten koszt, niektóre systemy SGBD oferują koncepcję pul otwartych połączeń: podczas inicjalizacji aplikacji otwieranych jest N połączeń, które są przypisywane do puli. Pozostaną one otwarte do zakończenia działania aplikacji. Gdy aplikacja otwiera połączenie, otrzymuje jedno z N już otwartych połączeń z puli. Gdy zamyka połączenie, jest ono po prostu zwracane do puli. Zaletą tego systemu jest to, że jest on przezroczysty dla programisty: program nie wymaga modyfikacji, aby korzystać z puli połączeń. Konfiguracja puli połączeń zależy od SGBD.

W pierwszej kolejności zajmiemy się wykonaniem poleceń SQL i Select. Metoda ExecuteSelect w naszym przykładowym programie wygląda następująco:


// wykonanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                    // nawiązanie połączenia
                    connexion.Open();
                    // wykonanie sqlCommand z zapytaniem SELECT
                    SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                    SqlCeDataReader reader= sqlCommand.ExecuteReader();
                    // wyświetlanie wyników
                    AfficheReader(reader);
                }
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // wyświetlanie czytnika
        static void AfficheReader(IDataReader reader) {
...
        }

wiersz 2: metoda otrzymuje dwa parametry:
- ciąg połączenia [connectionString], który umożliwi jej połączenie z bazą danych
- polecenie SQL Select [requête], które ma zostać wykonane przy użyciu tego połączenia
wiersz 4: każda operacja na bazie danych może wygenerować wyjątek, który warto obsłużyć. Jest to tym ważniejsze w tym przypadku, że polecenia SQL podane przez użytkownika mogą zawierać błędy składniowe. Musimy mieć możliwość poinformowania go o tym. Cały kod znajduje się zatem w bloku try / catch.
wiersz 5: mamy tu kilka kwestii:
- połączenie z bazą danych jest inicjowane za pomocą ciągu połączenia [connectionString]. Nie jest ono jeszcze otwarte. Zostanie otwarte w wierszu 7.
- Klauzula using (Ressource) {...} to ułatwienie składniowe gwarantujące zwolnienie zasobu Ressource – w tym przypadku połączenia – po opuszczeniu bloku kontrolowanego przez using.
- Połączenie jest typu zastrzeżonego: SqlCeConnection, właściwego dla serwera SGBD SQL Server Compact.
wiersz 7: połączenie jest otwarte. W tym momencie wykorzystywane są parametry łańcucha połączenia.
wiersz 9: polecenie SQL jest wysyłane za pośrednictwem obiektu zastrzeżonego SqlCeCommand. Wiersz 9 inicjuje ten obiekt, podając dwie informacje: połączenie, z którego należy skorzystać, oraz polecenie SQL, które ma zostać wysłane za jego pośrednictwem. Obiekt SqlCeCommand służy zarówno do wykonania polecenia Select, jak i polecenia Update, Insert oraz Delete. Jego właściwości i metody zostały przedstawione w paragrafie 9.3.3.
wiersz 10: polecenie SQL Select jest wykonywane za pomocą metody ExecuteReader obiektuobiektu SqlCeCommand, który zwraca obiekt IDataReader, którego metody i właściwości zostały przedstawione w paragrafie 9.3.3.
wiersz 12: wyświetlanie wyników jest powierzone następującej metodzie AfficheReader:


        // wyświetlanie czytnika
        static void AfficheReader(IDataReader reader) {
            using (reader) {
                // analiza wyników
                // -- kolumny
                StringBuilder ligne = new StringBuilder();
                int i;
                for (i = 0; i < reader.FieldCount - 1; i++) {
                    ligne.Append(reader.GetName(i)).Append(",");
                }
                ligne.Append(reader.GetName(i));
                Console.WriteLine("\n{0}\n{1}\n{2}\n", "".PadLeft(ligne.Length, '-'), ligne, "".PadLeft(ligne.Length, '-'));
                // -- dane
                while (reader.Read()) {
                    // przetwarzanie bieżącego wiersza
                    ligne = new StringBuilder();
                    for (i = 0; i < reader.FieldCount; i++) {
                        ligne.Append(reader[i].ToString()).Append(" ");
                    }
                    Console.WriteLine(ligne);
                }
            }
}

wiersz 2: metoda otrzymuje obiekt IDataReader. Należy zauważyć, że w tym przypadku użyto interfejsu, a nie konkretnej klasy.
wiersz 3: klauzula using służy do automatycznego zamknięcia obiektu IDataReader.
wiersze 8–10: wyświetlane są nazwy kolumn tabeli wynikowej zapytania Select. Są to kolumny coli z zapytania `select col1, col2, ... from table ...`
wiersze 14–21: przeglądamy tabelę wyników i wyświetlamy wartości każdego wiersza tabeli.
wiersz 18: nie znamy typu kolumny nr i w tabeli wyników, ponieważ nie znamy tabeli, z której pochodzą dane. Nie możemy zatem użyć składni reader.GetXXX(i), gdzie XXX oznacza typ kolumny nr i, ponieważ nie znamy tego typu. W takim przypadku stosuje się składnię reader.Item[i].ToString(), aby uzyskać reprezentację kolumny nr i w postaci ciągu znaków. Składnię reader.Item[i].ToString() można skrócić do reader[i].ToString().

9.3.8. Wykonanie polecenia aktualizacji: INSERT, UPDATE, DELETE

Kod metody ExecuteUpdate jest następujący:


        // wykonanie zapytania o aktualizację
        static void ExecuteUpdate(string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                    // nawiązanie połączenia
                    connexion.Open();
                    // wykonuje sqlCommand z zapytaniem o aktualizację
                    SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // wyświetlenie wyniku
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
}

Stwierdziliśmy, że wykonanie polecenia zapytania Select nie różni się od wykonania polecenia aktualizacji Update, Insert, Delete jedynie metodą obiektu SqlCeCommand: ExecuteReader dla Select, ExecuteNonQuery dla Update, Insert, Delete. W powyższym kodzie komentujemy jedynie tę ostatnią metodę:

wiersz 10: poleczenie Update, Insert, Delete jest wykonywane przez metodę ExecuteNonQuery obiektu SqlCeCommand. Jeśli operacja zakończy się powodzeniem, metoda ta zwraca liczbę wierszy zaktualizowanych (update), wstawionych (insert) lub usuniętych (delete).
wiersz 12: liczba ta jest wyświetlana na ekranie

Zachęcamy czytelnika do zapoznania się z przykładem wykonania tego kodu w paragrafie 9.3.2.

9.4. Inne łączniki ADO.NET

Kod, który analizowaliśmy, jest zastrzeżony: opiera się na przestrzeni nazw [System.Data.SqlServerCe] przeznaczonej dla serwera SGBD SQL Server Compact. Teraz stworzymy ten sam program z różnymi łącznikami .NET i zobaczymy, co się zmieni.

9.4.1. Łącznik SQL Server 2005

Zastosowana architektura będzie wyglądała następująco:

Instalacja SQL Server 2005 została opisana w załącznikach w punkcie 1.1.

Tworzymy drugi projekt w tym samym rozwiązaniu, co poprzednio, a następnie tworzymy bazę danych SQL Server 2005. SGBD SQL Server 2005 należy uruchomić przed wykonaniem poniższych czynności:

[1]: utworzyć nowy projekt w bieżącym rozwiązaniu i ustawić go jako projekt bieżący.
[2]: utworzenie nowego połączenia
[3]: wybrać typ połączenia

[4]: wybierz serwer SGBD SQL
[5]: wynik poprzedniego wyboru
[6]: użyj przycisku [Browse], aby wskazać miejsce utworzenia bazy danych SQL Server 2005. Baza danych jest zawarta w pliku .mdf.
[7]: wybierz katalog główny nowego projektu i nazwij bazę danych [dbarticles.mdf].
[8]: należy użyć uwierzytelniania systemu Windows.
[9]: zatwierdź stronę z informacjami

[11]: baza SQL Server
[12]: utwórz tabelę. Będzie ona identyczna z bazą SQL Server Compact utworzoną wcześniej.
[13]: pole [id]
[14]: pole [id] jest typu Identity.
[15,16]: pole [id] jest kluczem głównym

[17]: pozostałe pola tabeli
[18]: nadaj tabeli nazwę [articles] podczas jej zapisywania (Ctrl+S).

Pozostaje nam jeszcze wprowadzić dane do tabeli:

Dodajemy bazę danych do projektu:

Odwołania do projektu są następujące:

Plik konfiguracyjny [App.config] ma następującą treść:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
  <add name="connectString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;" />
     <add name="connectString2" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;Connect Timeout=30;" />
 </connectionStrings>
</configuration>

wiersz 4: ciąg połączenia z bazą danych [dbarticles.mdf] z uwierzytelnianiem systemu Windows
wiersz 5: ciąg połączenia z bazą danych [dbarticles.mdf] z uwierzytelnianiem serwera SQL. [sa,msde] to para (login, hasło) administratora serwera SQL Server, zgodnie z definicją w paragrafie 1.1.

Program [Program.cs] przebiega w następujący sposób:


using System.Data.SqlClient;
...

namespace Chap7 {
    class SqlCommands {
        static void Main(string[] args) {
...
            // przetwarzanie pliku konfiguracyjnego [App.config]
            string connectionString = null;
            try {
                connectionString = ConfigurationManager.ConnectionStrings["connectString2"].ConnectionString;
            } catch (Exception e) {
...
            }
...
            // odczyt i wykonanie poleceń SQL wpisanych z klawiatury
...
        }

        // wykonanie żądania aktualizacji
        static void ExecuteUpdate(string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (SqlConnection connexion = new SqlConnection(connectionString)) {
                    // nawiązanie połączenia
                    connexion.Open();
                    // wykonanie sqlCommand wraz z zapytaniem o aktualizację
                    SqlCommand sqlCommand = new SqlCommand(requête, connexion);
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // wyświetlenie wyniku
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
....
            }
        }

        // wykonanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (SqlConnection connexion = new SqlConnection(connectionString)) {
                    // nawiązanie połączenia
                    connexion.Open();
                    // wykonuje sqlCommand z zapytaniem SELECT
                    SqlCommand sqlCommand = new SqlCommand(requête, connexion);
                    SqlDataReader reader = sqlCommand.ExecuteReader();
                    // przetwarzanie wyników
...
                }
            } catch (Exception ex) {
...
            }
        }
    }
}

wiersz 1: przestrzeń nazw [System.Data.SqlClient] zawiera klasy umożliwiające zarządzanie bazą danych SQL Server 2005
wiersz 24: połączenie jest typu SQLConnection
wiersz 28: obiekt zawierający polecenia SQL jest typu SQLCommand
wiersz 47: obiekt zawierający wynik polecenia SQL Select jest typu SQLDataReader

Kod jest identyczny z tym używanym w przypadku serwera SGBD SQL Server Compact, z wyjątkiem nazw klas. Aby go uruchomić, można użyć (wiersz 11) jednego z dwóch ciągów połączeń zdefiniowanych w [App.config].

9.4.2. Konnektor MySQL5

Zastosowana architektura będzie wyglądać następująco:

Instalacja modułu MySQL5 została opisana w załącznikach w punkcie 1.2, a instalacja łącznika Ado.Net w punkcie 1.2.5.

Tworzymy trzeci projekt w tym samym rozwiązaniu, co poprzednio, i dodajemy do niego niezbędne odniesienia:

[1]: nowy projekt
[2]: do którego dodajemy odniesienia
[3]: DLL, [MySQL.Data], łącznika Ado.Net, MySql5, a także [System.Configuration], [4].

Teraz tworzymy bazę danych [dbarticles] oraz jej tabelę [articles]. Należy uruchomić SGBD oraz MySQL5. Ponadto uruchamia się klienta [Query Browser] (patrz punkt 1.2.3).

[1]: w [Query Browser] należy kliknąć prawym przyciskiem myszy w obszarze [Schemata] [2], aby utworzyć [3] – nowy schemat, czyli bazę danych.
[4]: baza danych będzie nosić nazwę [dbarticles]. W [5] jest ona widoczna. Na razie nie zawiera żadnych tabel. Uruchomimy następujący skrypt SQL:

/* wybór bieżącej bazy danych */
USE dbarticles;
/* tworzenie tabeli artykułów */
CREATE TABLE ARTICLES (
    ID            INTEGER PRIMARY KEY AUTO_INCREMENT,
    NOM           VARCHAR(20) NOT NULL,
    PRIX          DOUBLE PRECISION NOT NULL,
    STOCKACTUEL   INTEGER NOT NULL,
    STOCKMINIMUM  INTEGER NOT NULL
);
/* wstawianie danych do tabeli */
INSERT INTO ARTICLES (NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES ('article1', 100, 10, 1);
INSERT INTO ARTICLES (NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES ('article2', 200, 20, 2);
INSERT INTO ARTICLES (NOM, PRIX, STOCKACTUEL, STOCKMINIMUM) VALUES ('article3', 300, 30, 3);
/* dodawanie ograniczeń */
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRIX check (PRIX>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKACTUEL check (STOCKACTUEL>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NOM check (NOM<>'');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NOM UNIQUE (NOM);

wiersz 1: baza [dbarticles] staje się bazą bieżącą. Kolejne polecenia SQL będą wykonywane właśnie na tej bazie.
wiersze 4–10: definicja tabeli [ARTICLES]. Należy zwrócić uwagę, że SQL jest właścicielem MySQL. Typy kolumn oraz automatyczne generowanie klucza głównego (atrybut AUTO_INCREMENT) różnią się od tych spotykanych w przypadku tabel SGBD i SQL w wersjach Server Compact i Express.
wiersze 12–14: dodano trzy wiersze
wiersze 16–21: dodanie ograniczeń integralności dla kolumn.

Skrypt ten jest wykonywany w [MySQL Query Browser]:

w [MySQL Query Browser] [6] ładowany jest skrypt [7]. Widać to w [8]. W [9] jest on wykonywany.

w pliku [10] utworzono tabelę [articles]. Klikamy na nią dwukrotnie. Pojawia się okno [11] z zapytaniem [12] gotowym do wykonania przez [13]. W [14] widoczny jest wynik wykonania. Mamy rzeczywiście trzy oczekiwane wiersze. Należy zauważyć, że wartości pola [ID] zostały wygenerowane automatycznie (atrybut AUTO_INCREMENT tego pola).

Teraz, gdy baza danych jest gotowa, możemy powrócić do tworzenia aplikacji w Visual Studio.

W [1] znajduje się program [Program.cs] oraz plik konfiguracyjny [App.config]. Oto jego treść:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
     <add name="dbArticlesMySql5" connectionString="Server=localhost;Database=dbarticles;Uid=root;Pwd=root;" />
 </connectionStrings>
</configuration>

W wierszu 4 elementy ciągu połączenia są następujące:

Server: nazwa komputera, na którym znajduje się SGBD, MySQL, w tym przypadku localhost, c.a.d – komputer, na którym program będzie uruchamiany.
Baza danych: nazwa zarządzanej bazy danych, w tym przypadku dbarticles
Uid: nazwa użytkownika, tutaj root
Pwd: hasło użytkownika, w tym przypadku root. Te dwie informacje odnoszą się do administratora utworzonego w punkcie 1.2.

Program [Program.cs] jest identyczny z programem z poprzednich wersji, z wyjątkiem następujących szczegółów:

`espace de noms`	MySql.Data.MySqlClient
`classe Connection`	MySqlConnection
`classe Command`	MySqlCommand
`classe DataReader`	MySqlDataReader

Program wykorzystuje ciąg połączenia o nazwie dbArticlesMySql5 zawarty w pliku [App.config]. Wykonanie programu daje następujące wyniki:

Chaîne de connexion à la base : [Server=localhost;Database=dbarticles;Uid=root;Pwd=root;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 article1 100 10 1
2 article2 200 20 2
3 article3 300 30 3

9.4.3. Łącznik ODBC

Zastosowana architektura będzie wyglądać następująco:

Zaletą łączników ODBC jest to, że zapewniają one standardowy interfejs dla aplikacji, które z nich korzystają. Dzięki temu nowa aplikacja będzie mogła, przy użyciu jednego kodu, komunikować się z każdym urządzeniem SGBD wyposażonym w złącze ODBC, c.a.d oraz z większością urządzeń SGBD. Wydajność złączy ODBC jest niższa niż w przypadku złączy „własnościowych”, które potrafią wykorzystać wszystkie cechy konkretnego urządzenia SGBD. Z drugiej strony zyskujemy dużą elastyczność aplikacji: można zmienić serwer SGBD bez konieczności modyfikacji kodu.

Rozważamy przykład, w którym aplikacja korzysta z bazy danych MySQL5 lub bazy danych SQL Server Express w zależności od podanego łańcucha połączenia. W dalszej części zakładamy, że:

serwery SGBD, SQL (Express) oraz MySQL5 zostały uruchomione
że sterownik ODBC dla serwera MySQL5 jest zainstalowany na komputerze (patrz punkt 1.2.6). Sterownik dla serwera SQL Server 2005 jest dostępny domyślnie.
Wykorzystywane bazy danych to te z punktu 9.4.2 dla bazy MySQL5 oraz te z punktu 9.4.1 dla bazy SQL Server Express.

Nowy projekt w programie Visual Studio wygląda następująco:

Powyżej baza SQL Server [dbarticles.mdf] utworzona w punkcie 9.4.1 została skopiowana do folderu projektu.

Plik konfiguracyjny [App.config] ma następującą treść:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
     <add name="dbArticlesOdbcMySql5" connectionString="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=dbarticles; User=root;Password=root;" />
     <add name="dbArticlesOdbcSqlServer2005" connectionString="Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
 </connectionStrings>
</configuration>

wiersz 4: ciąg połączenia źródła ODBC MySQL5. Jest to ciąg, który został już omówiony, zawierający nowy parametr „Driver”, który określa sterownik ODBC, który ma być używany.
wiersz 5: ciąg połączenia źródła ODBC SQL Server Express. Jest to ciąg już wykorzystany w poprzednim przykładzie, do którego dodano parametr „Driver”.

Program [Program.cs] jest identyczny z programem z poprzednich wersji, z wyjątkiem następujących szczegółów:

`espace de noms`	System.Data.Odbc
`classe Connection`	OdbcConnection
`classe Command`	OdbcCommand
`classe DataReader`	OdbcDataReader

Program wykorzystuje jeden z dwóch ciągów połączeń zdefiniowanych w pliku [App.config]. Wykonanie programu daje następujące wyniki:

Z ciągiem połączenia [dbArticlesOdbcSqlServer2005]:

Chaîne de connexion à la base : [Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
id,nom,prix,stockactuel,stockminimum
------------------------------------

1 vélo 500,0000 10 5
2 pompe 10,0000 10 2
3 arc 610,0000 4 1
4 flèches - lot de 6 100,0000 12 20
5 combinaison de plongée 300,0000 8 2
6 Bouteilles d'oxygène 120,0000 10 5

Z ciągiem połączenia [dbArticlesOdbcMySql5]:

Chaîne de connexion à la base : [Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=dbarticles; User=root;Password=root;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 article1 100 10 1
2 article2 200 20 2
3 article3 300 30 3

9.4.4. Łącznik OLE DB

Zastosowana architektura będzie wyglądać następująco:

Podobnie jak złącza ODBC, złącza OLE i DB (Object Linking and Embedding DataBase) oferują standardowy interfejs dla aplikacji, które z nich korzystają. Sterowniki ODBC umożliwiają dostęp do baz danych. Źródła danych dla sterowników OLE i DB są bardziej zróżnicowane: bazy danych, systemy pocztowe, katalogi itp. Każde źródło danych może stać się przedmiotem sterownika Ole DB, jeśli zdecyduje się na to producent. W ten sposób uzyskujemy standardowy dostęp do szerokiej gamy danych.

Rozważamy przykład, w którym aplikacja korzysta z bazy ACCESS lub bazy SQL Server Express w zależności od podanego łańcucha połączenia. W dalszej części zakładamy, że serwer SGBD SQL Server Express został uruchomiony, a wykorzystywana baza danych jest taka sama jak w poprzednim przykładzie.

Nowy projekt w Visual Studio wygląda następująco:

w [1]: przestrzeń nazw wymagana dla łączników OLE i DB to [System.Data.OleDb], obecna w powyższym odwołaniu [System.Data]. Baza danych SQL Server [dbarticles.mdf] została skopiowana z poprzedniego projektu. Baza danych [dbarticles.mdb] została utworzona w programie Access.
W przypadku [2]: podobnie jak baza SQL Server, baza ACCESS posiada właściwość [Copy to Output Directory=Copy Always], dzięki czemu jest automatycznie kopiowana do folderu uruchomieniowego projektu.

Baza danych ACCESS [dbarticles.mdb] wygląda następująco:

W pliku [1] znajduje się struktura tabeli [articles], a w pliku [2] – jej zawartość.

Plik konfiguracyjny [App.config] ma następującą postać:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
     <add name="dbArticlesOleDbAccess"  connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\dbarticles.mdb;"/>
     <add name="dbArticlesOleDbSqlServer2005" connectionString="Provider=SQLNCLI;Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
 </connectionStrings>
</configuration>

wiersz 4: ciąg połączenia źródła OLE DB ACCESS. Znajduje się tam parametr Provider, który określa sterownik OLE DB, który ma być używany, a także ścieżkę do bazy danych
wiersz 5: ciąg połączenia źródła OLE DB Server Express.

Program [Program.cs] jest identyczny z programem z poprzednich wersji, z wyjątkiem następujących szczegółów:

`espace de noms`	System.Data.OleDb
`classe Connection`	OleDbConnection
`classe Command`	OleDbCommand
`classe DataReader`	OleDbDataReader

Program wykorzystuje jeden z dwóch ciągów połączeń zdefiniowanych w pliku [App.config]. Uruchomienie programu z ciągiem połączeń [dbArticlesOleDbAccess] daje następujące wyniki:

Chaîne de connexion à la base : [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\dbarticles.mdb;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
id,nom,prix,stockactuel,stockminimum
------------------------------------

1 vélo 1202 5 2
2 arc 5000 10 2
3 canoé 1502 12 6
4 fusil 3000 10 2
5 skis nautiques 1800 5 2
6 essai3 3 3 3
7 cachalot 200000 1 0
8 léopard 500000 1 1
9 panthère 800000 1 1

9.4.5. Łącznik ogólny

Zastosowana zostanie następująca architektura:

Podobnie jak łączniki ODBC, OLE i DB, łącznik generyczny zapewnia standardowy interfejs dla aplikacji, które z niego korzystają, jednocześnie poprawiając wydajność bez utraty elastyczności. W rzeczywistości złącze generyczne opiera się na zastrzeżonych złączach typu SGBD. Aplikacja korzysta z klas złącza generycznego. Klasy te pełnią rolę pośredników między aplikacją a złączem zastrzeżonym.

W powyższym przykładzie, gdy aplikacja żąda na przykład połączenia z łącznikiem generycznym, ten zwraca jej instancję IDbConnection, interfejs połączeń opisany w paragrafie 9.3.3, zaimplementowany przez klasę MySQLConnection lub SQLConnection w zależności od charakteru zgłoszonego żądania. Mówi się, że łącznik generyczny posiada klasy typu fabrycznego: wykorzystuje się klasę fabryczną, aby poprosić go o utworzenie obiektów i przekazanie do nich odwołań (wskaźników). Stąd jego nazwa (factory = fabryka, fabryka produkująca obiekty).

Nie istnieje jeden łącznik generyczny obsługujący wszystkie obiekty typu SGBD (kwiecień 2008 r.). Aby sprawdzić, które z nich są zainstalowane na danym komputerze, można skorzystać z następującego programu:


using System;
using System.Data;
using System.Data.Common;

namespace Chap7 {
    class Providers {
        public static void Main() {
            DataTable dt = DbProviderFactories.GetFactoryClasses();
            foreach (DataColumn col in dt.Columns) {
                Console.Write("{0}|", col.ColumnName);
            }
            Console.WriteLine("\n".PadRight(40, '-'));
            foreach (DataRow row in dt.Rows) {
                foreach (object item in row.ItemArray) {
                    Console.Write("{0}|", item);
                }
                Console.WriteLine("\n".PadRight(40, '-'));
            }
        }
    }
}

wiersz 8: metoda statyczna [DbProviderFactories.GetFactoryClasses()] zwraca listę zainstalowanych łączników generycznych w postaci tabeli bazy danych umieszczonej w pamięci (DataTable).
wiersze 9–11: wyświetlają nazwy kolumn tabeli dt:
- dt.Columns to lista kolumn tabeli. Kolumna C ma typ DataColumn
- [DataColumn]. ColumnName to nazwa kolumny
wiersze 13–18: przedstawiają wiersze tabeli dt:
- dt.Rows to lista wierszy tabeli. Wiersz L ma typ DataRow
- [DataRow]. ItemArray to tablica obiektów, gdzie każdy obiekt reprezentuje kolumnę wiersza

Wynik wykonania na moim komputerze jest następujący:

Name|Description|InvariantName|AssemblyQualifiedName|
---------------------------------------
Odbc Data Provider|.Net Framework Data Provider for Odbc|System.Data.Odbc|System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089|
---------------------------------------
OleDb Data Provider|.Net Framework Data Provider for OleDb|System.Data.OleDb|System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089|
---------------------------------------
OracleClient Data Provider|.Net Framework Data Provider for Oracle|System.Data.OracleClient|System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089|
---------------------------------------
SqlClient Data Provider|.Net Framework Data Provider for SqlServer|System.Data.SqlClient|System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089|
---------------------------------------
Microsoft SQL Server Compact Data Provider|.NET Framework Data Provider for Microsoft SQL Server Compact|System.Data.SqlServerCe.3.5|System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91|
---------------------------------------
MySQL Data Provider|.Net Framework Data Provider for MySQL|MySql.Data.MySqlClient|MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d|

wiersz 1: tabela ma cztery kolumny. Trzy pierwsze są dla nas tutaj najbardziej przydatne.

Poniższy wyświetlacz pokazuje, że dostępne są następujące łączniki generyczne:

Nazwa	Identyfikator
`Odbc Data Provider`	System.Data.Odbc
`OleDb Data Provider`	System.Data.OleDb
`OracleClient Data Provider`	System.Data.OracleClient
`SqlClient Data Provider`	System.Data.SqlClient
`Microsoft SQL Server Compact Data Provider`	System.Data.SqlServerCe.3.5
`MySQL Data Provider`	MySql.Data.MySqlClient

W programie napisanym w języku C# można uzyskać dostęp do generycznego łącznika za pomocą jego identyfikatora.

Rozważamy przykład, w którym aplikacja korzysta z różnych baz danych, które stworzyliśmy do tej pory. Aplikacja otrzyma dwa parametry:

pierwszy parametr określa typ SGBD, aby zapewnić użycie właściwej biblioteki klas
drugi parametr określa obsługiwany system baz danych za pomocą ciągu połączenia.

Nowy projekt w Visual Studio wygląda następująco:

w [1]: przestrzeń nazw niezbędna dla łączników generycznych to [System.Data.common], zawarta w bibliotece referencyjnej [System.Data].

Plik konfiguracyjny [App.config] ma następującą postać:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="dbArticlesSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
        <add name="dbArticlesSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
        <add name="dbArticlesMySql5" connectionString="Server=localhost;Database=dbarticles;Uid=root;Pwd=root;" />
        <add name="dbArticlesOdbcMySql5" connectionString="Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=dbarticles; User=root;Password=root;Option=3;" />
        <add name="dbArticlesOleDbSqlServer2005" connectionString="Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
        <add name="dbArticlesOdbcSqlServer2005" connectionString="Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\dbarticles.mdf;Uid=sa;Pwd=msde;" />
        <add name="dbArticlesOleDbAccess"  connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\dbarticles.mdb;Persist Security Info=True"/>
    </connectionStrings>
    <appSettings>
        <add key="factorySqlServerCe" value="System.Data.SqlServerCe.3.5"/>
        <add key="factoryMySql" value="MySql.Data.MySqlClient"/>
        <add key="factorySqlServer" value="System.Data.SqlClient"/>
        <add key="factoryOdbc" value="System.Data.Odbc"/>
        <add key="factoryOleDb" value="System.Data.OleDb"/>
    </appSettings>
</configuration>

wiersze 3–11: ciągi połączeń dla różnych wykorzystywanych baz danych.
wiersze 13–17: nazwy ogólnych łączników, które należy wykorzystać

Program [Program.cs] ma następującą postać:


...
using System.Data.Common;

namespace Chap7 {
    class SqlCommands {
        static void Main(string[] args) {

            // aplikacja konsolowa – wykonuje zapytania SQL wpisane z klawiatury
            // w bazie danych, której ciąg połączenia oraz nazwa powiązanego łącznika SGBD są pobierane z pliku konfiguracyjnego

            // weryfikacja parametrów
            if (args.Length != 2) {
                Console.WriteLine("Syntaxe : pg factory connectionString");
                return;
            }

            // przetwarzanie pliku konfiguracyjnego
            string factory = null;
            string connectionString = null;
            DbProviderFactory connecteur = null;
            try {
                // factory
                factory = ConfigurationManager.AppSettings[args[0]];
                // łańcuch połączeń
                connectionString = ConfigurationManager.ConnectionStrings[args[1]].ConnectionString;
                // uzyskuje się ogólne złącze dla SGBD
                connecteur = DbProviderFactories.GetFactory(factory);
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // wyświetlenia
            Console.WriteLine("Provider factory : [{0}]\n", factory);
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

...
                // wykonanie zapytania
                if (champs[0] == "select") {
                    ExecuteSelect(connecteur,connectionString, requête);
                } else
                    ExecuteUpdate(connecteur, connectionString, requête);
            }
        }

        // wykonanie zapytania o aktualizację
        static void ExecuteUpdate(DbProviderFactory connecteur, string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // konfiguracja połączenia
                    connexion.ConnectionString = connectionString;
                    // nawiązanie połączenia
                    connexion.Open();
                    // konfiguracja polecenia 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // wykonanie zapytania
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // wyświetlanie wyniku
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // wykonanie zapytania SELECT
        static void ExecuteSelect(DbProviderFactory connecteur, string connectionString, string requête) {
            // obsługa ewentualnych wyjątków
            try {
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // konfiguracja połączenia
                    connexion.ConnectionString = connectionString;
                    // nawiązanie połączenia
                    connexion.Open();
                    // konfiguracja polecenia 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // wykonanie zapytania
                    DbDataReader reader = sqlCommand.ExecuteReader();
                    // wyświetlanie wyników
...
                }
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }
    }
}

wiersze 12–14: aplikacja otrzymuje dwa parametry: nazwę łącznika generycznego oraz ciąg połączenia z bazą danych w postaci kluczy z pliku [App.config].
wiersze 23, 25: z pliku [App.config] pobierana jest nazwa łącznika generycznego oraz ciąg połączenia
wiersz 27: tworzona jest instancja łącznika generycznego. Od tego momentu jest on powiązany z konkretnym obiektem SGBD.
wiersze 39–43: wykonanie polecenia SQL wprowadzonego z klawiatury jest przekazywane do dwóch metod, do których przekazywane są:
- zapytanie do wykonania
- ciąg połączenia identyfikujący bazę danych, w której zapytanie zostanie wykonane
- ogólny łącznik, który określa klasy, których należy użyć do komunikacji z SGBD zarządzającym bazą.
wiersze 50–54: nawiązywane jest połączenie za pomocą metody CreateConnection (wiersz 50) łącznika generycznego, a następnie konfigurowane przy użyciu ciągu połączenia bazy danych, którą ma obsługiwać (wiersz 52). Następnie połączenie jest otwierane (wiersz 54).
wiersze 56–58: obiekt Command, niezbędny do wykonania polecenia SQL, jest tworzony za pomocą metody CreateCommand łącznika generycznego. Następnie jest on konfigurowany z tekstem zlecenia SQL, które ma zostać wykonane (wiersz 57), oraz połączeniem, na którym ma ono zostać wykonane (wiersz 58).
wiersz 60: wykonywane jest polecenie aktualizacji SQL
wiersze 74–87: znajduje się tam podobny kod. Nowość pojawia się w wierszu 84. Obiekt Reader uzyskany w wyniku wykonania polecenia Select jest typu DbDataReader, który wykorzystuje się tak samo jak obiekty OleDbDataReader, OdbcDataReader, ... z którymi już mieliśmy do czynienia.

Oto kilka przykładów wykonania.

W przypadku bazy MySQL5:

Otwieramy stronę właściwości projektu [1] i wybieramy zakładkę [Debug] [2]. W [3] – klucz łącznika z wiersza 14 w [App.config]. W pliku [4] klucz łańcucha połączeń z wiersza 6 pliku [App.config]. Wyniki wykonania są następujące:

Provider factory : [MySql.Data.MySqlClient]
Chaîne de connexion à la base : [Server=localhost;Database=dbarticles;Uid=root;Pwd=root;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 article1 100 10 1
2 article2 200 20 2
3 article3 300 30 3

Z bazą SQL Server Compact:

W [1] klucz łącznika z wiersza 13 w [App.config]. W pliku [2] klucz łańcucha połączenia z wiersza 4 pliku [App.config]. Wyniki wykonania są następujące:

Provider factory : [System.Data.SqlServerCe.3.5]
Chaîne de connexion à la base : [Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;]

Requête SQL (rien pour arrêter) : select * from articles

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

1 vélo 500 10 5
2 pompe 10 10 2
3 arc 600 4 1
4 flèches - lot de 6 100 12 20
5 combinaison de plongée 300 8 2
6 bouteilles d'oxygène 120 10 5

Zachęcamy czytelnika do przetestowania innych baz danych.

9.4.6. Który łącznik wybrać?

Wróćmy do architektury aplikacji z bazami danych:

Zapoznaliśmy się z różnymi typami łączników ADO.NET:

konektory zastrzeżone są najbardziej wydajne, ale powodują, że warstwa [dao] staje się zależna od klas zastrzeżonych. Zmiana SGBD wiąże się ze zmianą warstwy [dao].
Łączniki typu ODBC, OLE lub DB umożliwiają pracę z wieloma bazami danych bez konieczności zmiany warstwy [dao]. Są one mniej wydajne niż łączniki zastrzeżone.
Konektor generyczny opiera się na konektorach własnościowych, oferując jednocześnie standardowy interfejs dla warstwy [dao].

Wydaje się zatem, że łącznik generyczny jest łącznikiem idealnym. W praktyce jednak łącznik generyczny nie jest w stanie ukryć wszystkich specyficznych cech SGBD za standardowym interfejsem. W następnym akapicie omówimy pojęcie zapytania parametrycznego. W przypadku serwera SQL zapytanie parametryczne ma następującą postać:

insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)

W przypadku serwera MySQL5 to samo zapytanie wyglądałoby następująco:

insert into articles(nom,prix,stockactuel,stockminimum) values(?,?,?,?)

Istnieje zatem różnica w składni. Właściwość interfejsu IDbCommand opisana w paragrafie 9.3.3, związana z parametrami, jest następująca:

Parameters

lista parametrów zlecenia SQL z ustawionymi parametrami. Zlecenie update articles set price=price*1.1 where id=@id posiada parametr @id.

Właściwość Parameters jest typu IDataParameterCollection, czyli interfejsem. Reprezentuje ona zbiór parametrów polecenia SQL CommandText. Właściwość Parameters posiada metodę Add służącą do dodawania parametrów typu IDataParameter, który jest z kolei interfejsem. Posiada on następujące właściwości:

ParameterName: nazwa parametru
DbType: typ SQL parametru
Value: wartość przypisana do parametru
...

Typ IDataParameter dobrze pasuje do parametrów zlecenia SQL

insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)

, ponieważ zawierają one parametry nazwane. Można wykorzystać właściwość ParameterName.

Typ IDataParameter nie pasuje do zlecenia SQL

insert into articles(nom,prix,stockactuel,stockminimum) values(?,?,?,?)

ponieważ parametry nie mają nazw. W takim przypadku brana jest pod uwagę kolejność dodania parametrów do kolekcji [IDbCommand.Parameters]. W tym przykładzie należy wprowadzić 4 parametry w kolejności: nom, prix, stockactuel, stockminimum. W zapytaniu z parametrami nazwanymi kolejność dodawania parametrów nie ma znaczenia. Ostatecznie programista nie może całkowicie pominąć kodu SGBD, którego używa podczas inicjalizacji parametrów zapytania parametrycznego. Jest to jedno z obecnych ograniczeń łącznika generycznego.

Istnieją frameworks, które pozwalają ominąć te ograniczenia, a ponadto wprowadzają nowe funkcje do warstwy [dao]:

Framework to zbiór bibliotek klas mających na celu ułatwienie określonego sposobu projektowania architektury aplikacji. Istnieje kilka takich frameworków, które umożliwiają pisanie warstw [dao], które są zarówno wydajne, jak i odporne na zmiany w SGBD:

Spring.Net [http://www.springframework.net/], przedstawiony już w niniejszym dokumencie, oferuje odpowiednik omawianego łącznika generycznego, bez jego ograniczeń, a także różne udogodnienia upraszczające dostęp do danych. Dostępna jest wersja w języku Java.
iBatis.Net [http://ibatis.apache.org] jest starszy i bogatszy w funkcje niż Spring.Net. Dostępna jest wersja w języku Java.
NHibernate [http://www.hibernate.org/] to port bardzo znanej w środowisku Java biblioteki Hibernate. NHibernate umożliwia warstwie [dao] komunikację z SGBD bez wysyłania poleceń SQL. Warstwa [dao] współpracuje z obiektami Hibernate. Język zapytań HBL (Hibernate Query Language) umożliwia wysyłanie zapytań do obiektów zarządzanych przez Hibernate. To właśnie one wysyłają polecenia SQL. Hibernate potrafi dostosować się do obiektów SQL, które są właścicielami obiektów SGBD.
LINQ (język zapytań INtegrated), zintegrowany z wersją 3.5 .NET i dostępny w C# 2008. LINQ idzie w ślady NHibernate, ale na razie (maj 2008 r.) obsługiwany jest tylko serwer SGBD SQL. Sytuacja ta powinna ulec zmianie z czasem. LINQ wykracza poza możliwości NHibernate: jego język zapytań umożliwia standardowe przeszukiwanie trzech różnych typów źródeł danych:
- zbiorów obiektów (LINQ to Objects)
- plik XML (LINQ to Xml)
- baza danych (LINQ to SQL)

W niniejszym dokumencie nie omówiono tych frameworków. Zaleca się jednak ich stosowanie w aplikacjach profesjonalnych.

9.5. Zapytania parametryczne

W poprzednim akapicie wspomnieliśmy o zapytaniach z parametrami. Przedstawiamy je tutaj na przykładzie serwera SGBD SQL Server Compact. Projekt wygląda następująco

w [1], projekt. Wykorzystywane są wyłącznie [App.config], [Article.cs] i [Parametres.cs]. Należy również zwrócić uwagę na bazę SQL Server oraz [dbarticles.sdf].
W przypadku [2] projekt jest skonfigurowany do uruchamiania [Parametres.cs]
w [3], odniesienia projektu

Plik konfiguracyjny [App.config] definiuje ciąg połączenia z bazą danych:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="dbArticlesSqlServerCe" connectionString="Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;" />
    </connectionStrings>
</configuration>

Plik [Article.cs] definiuje klasę [Article]. Obiekt Article zostanie wykorzystany do zapakowania informacji z wiersza tabeli ARTICLES w bazie danych [dbarticles.sdf]:


namespace Chap7 {
    class Article {
        // właściwości
        public int Id { get; set; }
        public string Nom { get; set; }
        public decimal Prix { get; set; }
        public int StockActuel { get; set; }
        public int StockMinimum { get; set; }

        // konstruktorzy
        public Article() { 
        }

        public Article(int id, string nom, decimal prix, int stockActuel, int stockMinimum) {
            Id = id;
            Nom = nom;
            Prix = prix;
            StockActuel = stockActuel;
            StockMinimum = stockMinimum;
        }

    }
}

Aplikacja [Parametres.cs] realizuje zapytania parametryczne:


using System;
using System.Data.SqlServerCe;
using System.Text;
using System.Data;
using System.Configuration;

namespace Chap7 {
    class Parametres {
        static void Main(string[] args) {

            // przetwarzanie pliku konfiguracyjnego
            string connectionString = null;
            try {
                // łańcuch połączenia
                connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // wyświetlanie
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // tworzenie tabeli artykułów
            Article[] articles = new Article[5];
            for (int i = 1; i <= articles.Length; i++) {
                articles[i-1] = new Article(0, "article" + i, i * 100, i * 10, i);
            }

            // obsługa ewentualnych wyjątków
            try {

                // usuwanie istniejących artykułów z bazy
                ExecuteUpdate(connectionString, "delete from articles");

                // wyświetlanie pozycji z tabeli
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");

                // wstawianie tabeli artykułów do bazy danych
                InsertArticles(connectionString, articles);

                // wyświetla się zawartość tabeli
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // wstawianie tabeli artykułów
        static void InsertArticles(string connectionString, Article[] articles) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // nawiązanie połączenia
                connexion.Open();
                // konfiguracja zamówienia
                string requête = "insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)";
                SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                sqlCommand.Parameters.Add("@nom",SqlDbType.NVarChar,30);
                sqlCommand.Parameters.Add("@prix", SqlDbType.Money);
                sqlCommand.Parameters.Add("@sa", SqlDbType.Int);
                sqlCommand.Parameters.Add("@sm", SqlDbType.Int);
                // kompilacja zamówienia
                sqlCommand.Prepare();
                // wstawianie wierszy
                for (int i = 0; i < articles.Length; i++) {
                    // inicjalizacja parametrów
                    sqlCommand.Parameters["@nom"].Value = articles[i].Nom;
                    sqlCommand.Parameters["@prix"].Value = articles[i].Prix;
                    sqlCommand.Parameters["@sa"].Value = articles[i].StockActuel;
                    sqlCommand.Parameters["@sm"].Value = articles[i].StockMinimum;
                    // wykonanie zapytania
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

        // wykonanie zapytania o aktualizację
        static void ExecuteUpdate(string connectionString, string requête) {
...
        }

        // wykonanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // wyświetlanie czytnika
        static void AfficheReader(IDataReader reader) {
...
    }
}

Nowością w stosunku do tego, co widzieliśmy wcześniej, jest procedura [InsertArticles] w wierszach 51–75:

wiersz 51: procedura otrzymuje dwa parametry:
- ciąg połączenia connectionString, który umożliwi procedurze połączenie się z bazą danych
- tablicę obiektów Article, którą należy dodać do tabeli Articles w bazie danych
wiersz 56: zapytanie o wstawienie obiektu [Article]. Posiada cztery parametry:
- @nom: nazwa artykułu
- @prix: jego cena
- @sa: aktualny stan magazynowy
- @sm: minimalny stan magazynowy

Składnia tego zapytania z parametrami jest zastrzeżona dla serwera SQL Server Compact. W poprzednim akapicie widzieliśmy, że w przypadku serwera MySQL5 składnia wyglądałaby następująco:

insert into articles(nom,prix,stockactuel,stockminimum) values(?,?,?,?)

W przypadku serwera SQL Server Compact każdy parametr musi być poprzedzony znakiem @. Nazwy parametrów są dowolne.

wiersze 58–61: definiuje się właściwości każdego z 4 parametrów i dodaje je po kolei do listy parametrów obiektu SqlCeCommand, który zawiera zlecenie SQL, które ma zostać wykonane.

Wykorzystujemy tutaj metodę [SqlCeCommand].Parameters.Add, która posiada sześć sygnatur. Korzystamy z dwóch następujących:

Add(string parameterName, SQLDbType type)

dodaje i konfiguruje parametr o nazwie parameterName. Nazwa ta musi być jedną z nazw skonfigurowanego zapytania parametrycznego: (@nazwa, ...). type określa typ SQL kolumny, której dotyczy parametr. Dostępnych jest wiele typów, w tym następujące:

typ SQL	typ C#	komentarz
`BigInt`	Int64
`DateTime`	DateTime
`Decimal`	Decimal
`Float`	Podwójna
`Int`	Int32
`Money`	Decimal
`NChar`	Ciąg znaków	ciąg o stałej długości
`NVarChar`	Ciąg	ciąg o zmiennej długości
`Real`	Single

Add(string parameterName, SQLDbType type, int size)

trzeci parametr size określa rozmiar kolumny. Informacja ta jest przydatna tylko w przypadku niektórych typów, np. typu SQL lub typu NVarChar.

wiersz 63: kompilujemy zapytanie parametryczne. Mówi się również, że je przygotowujemy, stąd nazwa metody. Operacja ta nie jest niezbędna. Służy ona poprawie wydajności. Gdy SGBD wykonuje polecenie SQL, przed jego wykonaniem przeprowadza pewne działania optymalizacyjne. Zapytanie parametryczne jest przeznaczone do wielokrotnego wykonywania z różnymi parametrami. Sam tekst zapytania pozostaje niezmieniony. Czynności optymalizacyjne można zatem wykonać tylko raz. Niektóre modele SGBD mają możliwość „przygotowania” lub „kompilacji” zapytań parametrycznych. Wówczas dla tego zapytania definiowany jest plan wykonania. Jest to wspomniana faza optymalizacji. Po skompilowaniu zapytanie jest wielokrotnie wykonywane, za każdym razem z nowymi parametrami, ale z tym samym planem wykonania.

Kompilacja nie jest jedyną zaletą zapytań parametrycznych. Wróćmy do analizowanego zapytania:

insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)

Można by chcieć skonstruować tekst zapytania programowo:

string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('"+nom+"',"+prix+","+sa+","+sm+")";

W powyższym przykładzie, jeśli (nazwa, cena, sa, sm) ma wartość („artykuł1”, 100, 10, 1), poprzednie zapytanie zmienia się na:

string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('article1',100,10,1)";

Teraz, jeśli (nazwa, cena, sa, sm) ma wartość („artykuł1”, 100, 10, 1), poprzednie zapytanie przyjmuje postać:

string requête="insert into articles(nom,prix,stockactuel,stockminimum) values('l'article1',100,10,1)";

i staje się niepoprawna pod względem składniowym z powodu apostrofu w nazwie l'article1. Jeśli nom pochodzi z danych wprowadzonych przez użytkownika, oznacza to, że musimy sprawdzić, czy wprowadzone dane nie zawierają apostrofów, a jeśli tak, to je usunąć. Ta neutralizacja zależy od SGBD. Zaletą zapytania przygotowanego jest to, że samo wykonuje tę pracę. Już sama ta wygoda uzasadnia użycie zapytania przygotowanego.

wiersze 65–73: pozycje z tabeli są wstawiane pojedynczo
wiersze 67–70: każdy z czterech parametrów zapytania otrzymuje swoją wartość poprzez właściwość Value.
wiersz 72: gotowe zapytanie wstawiające jest teraz wykonywane w zwykły sposób.

Oto przykład wykonania:

Chaîne de connexion à la base : [Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;]

Il y a eu 5 ligne(s) modifiée(s)

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------


------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

117 article1 100 10 1
118 article2 200 20 2
119 article3 300 30 3
120 article4 400 40 4
121 article5 500 50 5

wiersz 3: komunikat po usunięciu wszystkich wierszy z tabeli
wiersze 5–7: pokazują, że tabela jest pusta
wiersze 10–18: pokazują tabelę po wstawieniu 5 pozycji

9.6. Transactions

9.6.1. Informacje ogólne

Transakcja to sekwencja poleceń SQL wykonywana w sposób „atomowy”:

albo wszystkie operacje zakończą się powodzeniem
albo jedna z nich kończy się niepowodzeniem, a wtedy wszystkie poprzednie są anulowane

Ostatecznie operacje w ramach transakcji zostały albo pomyślnie zrealizowane, albo żadna z nich nie została zrealizowana. Gdy użytkownik sam kontroluje transakcję, zatwierdza ją za pomocą polecenia COMMIT lub anuluje za pomocą polecenia ROLLBACK.

W naszych poprzednich przykładach nie korzystaliśmy z transakcji. A jednak transakcje miały miejsce, ponieważ w przypadku polecenia SGBD polecenie SQL jest zawsze wykonywane w ramach transakcji. Jeśli klient .NET sam nie zainicjuje jawnej transakcji, to SGBD wykorzystuje transakcję domyślną. Wówczas występują dwa typowe przypadki:

każde pojedyncze polecenie SQL jest przedmiotem transakcji, zainicjowanej przez SGBD przed wydaniem polecenia, a następnie zamkniętej. Mówi się wówczas o trybie autocommit. Wszystko przebiega więc tak, jakby klient .NET przeprowadzał transakcje dla każdego zlecenia SQL.
SGBD nie działa w trybie autocommit i rozpoczyna transakcję domyślną przy pierwszym poleceniu SQL, które klient .NET wysyła poza transakcją, a następnie pozwala klientowi ją zamknąć. Wszystkie polecenia SQL wysyłane przez klienta .NET stają się wówczas częścią tej domyślnej transakcji. Transakcja ta może zakończyć się w wyniku różnych zdarzeń: klient zamyka połączenie, rozpoczyna nową transakcję itp., ale wówczas mamy do czynienia z sytuacją zależną od SGBD. Jest to tryb, którego należy unikać.

Tryb domyślny jest zazwyczaj ustalany poprzez konfigurację SGBD. Niektóre SGBD domyślnie działają w trybie autocommit, inne nie. SQLServer Compact domyślnie działa w trybie autocommit.

Polecenia SQL różnych użytkowników są wykonywane jednocześnie w transakcjach działających równolegle. Operacje wykonywane przez jedną transakcję mogą wpływać na operacje wykonywane przez inną transakcję. Wyróżnia się cztery poziomy izolacji między transakcjami różnych użytkowników:

Uncommitted Read
Committed Read
Odczyt powtarzalny
Serializable

Odczyt niezapisany

Ten tryb izolacji nazywany jest również „Dirty Read”. Oto przykład tego, co może się wydarzyć w tym trybie:

użytkownik U1 rozpoczyna transakcję na tabeli T
użytkownik U2 rozpoczyna transakcję na tej samej tabeli T
użytkownik U1 modyfikuje wiersze w tabeli T, ale jeszcze ich nie zatwierdza
użytkownik U2 „widzi” te zmiany i podejmuje decyzje na podstawie tego, co widzi
użytkownik anuluje swoją transakcję za pomocą ROLLBACK

Widać, że w punkcie 4 użytkownik U2 podjął decyzję na podstawie danych, które później okażą się nieprawdziwe.

Committed Read

Ten tryb izolacji pozwala uniknąć powyższej pułapki. W tym trybie użytkownik U2 w kroku 4 nie „zobaczy” zmian wprowadzonych przez użytkownika U1 w tabeli T. Zobaczy je dopiero po tym, jak użytkownik U1 zakończy swoją transakcję.

W tym trybie, zwanym również „Unrepeatable Read”, mogą jednak wystąpić następujące sytuacje:

użytkownik U1 rozpoczyna transakcję na tabeli T
użytkownik U2 rozpoczyna transakcję na tej samej tabeli T
użytkownik U2 wykonuje operację SELECT w celu uzyskania średniej z kolumny C wierszy tabeli T spełniających określony warunek
użytkownik U1 modyfikuje (UPDATE) niektóre wartości w kolumnie C tabeli T i zatwierdza je (COMMIT)
użytkownik U2 ponownie wykonuje tę samą operację SELECT, co w punkcie 3. Zauważy, że średnia w kolumnie C uległa zmianie w wyniku modyfikacji dokonanych przez U1.

Teraz użytkownik U2 widzi tylko zmiany „zatwierdzone” przez U1. Jednak pozostając w tej samej transakcji, dwie identyczne operacje (3 i 5) dają różne wyniki. Termin „Unrepeatable Read” (odczyt niepowtarzalny) określa tę sytuację. Jest to kłopotliwa sytuacja dla kogoś, kto chce uzyskać stabilny obraz tabeli T.

Repeatable Read

W tym trybie izolacji użytkownik ma gwarancję uzyskania tych samych wyników odczytów z bazy danych, dopóki pozostaje w tej samej transakcji. Pracuje on na „migawce”, na której nigdy nie są odzwierciedlane zmiany wprowadzone przez inne transakcje, nawet te zatwierdzone. Zmiany te zobaczy dopiero wtedy, gdy sam zakończy swoją transakcję za pomocą polecenia COMMIT lub ROLLBACK.

Ten tryb izolacji nie jest jednak jeszcze idealny. Po wykonaniu operacji 3 opisanej powyżej wiersze przeglądane przez użytkownika U2 są zablokowane. Podczas operacji 4 użytkownik U1 nie będzie mógł modyfikować (UPDATE) wartości w kolumnie C tych wierszy. Może jednak dodawać wiersze (INSERT). Jeśli niektóre z dodanych wierszy spełniają warunek sprawdzany w kroku 3, operacja 5 da średnią różną od tej obliczonej w kroku 3 z powodu dodanych wierszy. Wiersze te nazywane są czasem wierszami-widmami.

Aby rozwiązać ten nowy problem, należy przejść do izolacji „Serializable”.

Serializable

W tym trybie izolacji transakcje są całkowicie odizolowane od siebie. Gwarantuje to, że wynik dwóch transakcji przeprowadzanych jednocześnie będzie taki sam, jak gdyby zostały one wykonane jedna po drugiej. Aby to osiągnąć, podczas operacji 4, w której użytkownik U1 chce dodać wiersze, które zmieniłyby wynik operacji SELECT użytkownika U1, zostanie on zablokowany. Pojawi się komunikat o błędzie informujący, że wstawienie nie jest możliwe. Stanie się to możliwe dopiero po zatwierdzeniu transakcji przez użytkownika U2.

Cztery poziomy izolacji transakcji SQL nie są dostępne we wszystkich SGBD. Domyślnym poziomem izolacji jest zazwyczaj poziom Committed Read. Pożądany poziom izolacji dla transakcji można jawnie określić podczas tworzenia transakcji jawnej przez klienta .NET.

9.6.2. Zarządzanie transakcjami API

Połączenie implementuje interfejs IDbConnection przedstawiony w paragrafie 9.3.3. Interfejs ten posiada następującą metodę:

BeginTransaction

M

rozpoczyna transakcję.

Metoda ta ma dwie sygnatury:

IDbTransaction BeginTransaction(): rozpoczyna transakcję i zwraca obiekt IDbTransaction umożliwiający jej kontrolę
IDbTransaction BeginTransaction(IsolationLevel poziom): dodatkowo określa pożądany poziom izolacji dla transakcji. level przyjmuje wartości z następującej wyliczanki:

`ReadUncommitted`	transakcja może odczytywać dane zapisane przez inną transakcję, których ta ostatnia jeszcze nie zatwierdziła – należy tego unikać
`ReadCommitted`	transakcja nie może odczytywać danych zapisanych przez inną transakcję, których jeszcze nie zatwierdziła. Dane odczytane dwukrotnie z rzędu w ramach tej samej transakcji mogą się jednak różnić (odczyty niepowtarzalne), ponieważ w międzyczasie mogła je zmodyfikować inna transakcja (odczytywane wiersze nie są zablokowane – zablokowane są jedynie wiersze aktualizowane). Ponadto inna transakcja mogła dodać wiersze (wiersze-widma), które zostaną uwzględnione podczas drugiego odczytu.
`RepeatableRead`	Wiersze odczytywane przez transakcję są blokowane, podobnie jak wiersze aktualizowane. Zapobiega to modyfikacji tych wierszy przez inną transakcję. Nie uniemożliwia to jednak dodawania nowych wierszy.
`Serializable`	Tabele wykorzystywane przez transakcję są zablokowane, co uniemożliwia dodawanie nowych wierszy przez inną transakcję. Wszystko przebiega tak, jakby transakcja była jedyną działającą w danym momencie. Obniża to wydajność, ponieważ transakcje nie działają już równolegle.
`Snapshot`	transakcja działa na kopii danych utworzonej w momencie T. Stosowana, gdy transakcja jest tylko do odczytu. Daje ten sam wynik co serializable, unikając jednak związanych z nią kosztów.

Po uruchomieniu transakcji jest ona kontrolowana przez obiekt typu IDbTransaction, interfejs, z którego będziemy korzystać z następujących właściwości P i metod M:

Nazwa	Typ	Rola
`Connection`	P	połączenie IDbConnection, które obsługuje transakcję
`Commit`	M	zatwierdza transakcję – wyniki poleceń SQL wydanych w ramach transakcji są kopiowane do bazy danych.
`Rollback`	M	unieważnia transakcję – wyniki poleceń SQL wydanych w ramach transakcji nie są kopiowane do bazy danych.

9.6.3. Program przykładowy

Wracamy do poprzedniego projektu, aby teraz przyjrzeć się programowi [Transactions.cs]:

w [1], projekt.
w [2] projekt jest skonfigurowany do uruchamiania [Transactions.cs]

Kod [Transactions.cs] jest następujący:


using System;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;

namespace Chap7 {
    class Transactions {
        static void Main(string[] args) {

            // przetwarzanie pliku konfiguracyjnego
            string connectionString = null;
            try {
                // łańcuch połączenia
                connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // wyświetlenia
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // utworzenie tabeli zawierającej 2 pozycje o tej samej nazwie
            Article[] articles = new Article[2];
            for (int i = 1; i <= articles.Length; i++) {
                articles[i - 1] = new Article(0, "article", i * 100, i * 10, i);
            }
            // obsługa ewentualnych wyjątków
            try {
                Console.WriteLine("Insertion sans transaction...");
                // wstawiamy tabelę pozycji do bazy danych, najpierw bez transakcji
                ExecuteUpdate(connectionString, "delete from articles");
                try {
                    InsertArticlesOutOfTransaction(connectionString, articles);
                } catch (Exception ex) {
                    // komunikat o błędzie
                    Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
                }
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");

                // powtarzamy tę samą czynność, ale tym razem w transakcji
                Console.WriteLine("\n\nInsertion dans une transaction...");
                ExecuteUpdate(connectionString, "delete from articles");
                InsertArticlesInTransaction(connectionString, articles);
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // wstawianie tabeli artykułów bez transakcji
        static void InsertArticlesOutOfTransaction(string connectionString, Article[] articles) {
....
        }

        // wstawianie tabeli artykułów w transakcji
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
....
        }

        // wykonanie zapytania aktualizacyjnego
        static void ExecuteUpdate(string connectionString, string requête) {
....
        }

        // wykonanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // wyświetlenie czytnika
        static void AfficheReader(IDataReader reader) {
...
            }
        }
    }
}

wiersze 12–19: ciąg połączenia z bazą danych SQLServer jest odczytywany z pliku [App.config]
wiersze 25–28: tworzona jest tablica zawierająca dwa obiekty o nazwie Article. Oba te obiekty mają tę samą nazwę „article”. Jednak baza [dbarticles.sdf] ma ograniczenie unikalności w kolumnie [nom] (patrz paragraf 9.3.1). Dlatego te dwa artykuły nie mogą występować jednocześnie w bazie. Oba artykuły o nazwie „article” zostają dodane do tabeli articles. Powstanie zatem problem: c.a.d – wyjątek wygenerowany przez SGBD i przekazany przez jego łącznik ADO.NET. Aby zilustrować skutek transakcji, oba artykuły zostaną wstawione w dwóch różnych środowiskach:
- najpierw poza jakąkolwiek transakcją. Należy tutaj pamiętać, że w tym przypadku SQLServer Compact działa w trybie autocommit, a c.a.d umieszcza każde polecenie SQL w transakcji domyślnej. Pierwszy artykuł zostanie wstawiony. Drugi nie zostanie wstawiony.
- Następnie w jawnej transakcji obejmującej oba wstawienia. Ponieważ drugie wstawienie zakończy się niepowodzeniem, pierwsze zostanie cofnięte. Ostatecznie żadne wstawienie nie zostanie wykonane.
wiersz 33: tabela articles zostaje opróżniona
wiersz 35: wstawienie dwóch pozycji bez jawnej transakcji. Ponieważ wiadomo, że drugie wstawienie spowoduje wyjątek, jest on obsługiwany przez blok try/catch
wiersz 46: wyświetlenie tabeli articles
wiersze 44–46: powtarzamy tę samą sekwencję, ale tym razem do wstawiania danych używana jest jawna transakcja. Wystąpiony wyjątek jest tutaj obsługiwany przez metodę InsertArticlesInTransaction.
wiersze 54–56: metoda InsertArticlesOutOfTransaction jest metodą InsertArticles z programu [Parametres.cs] omówionego wcześniej.
wiersze 64–66: metoda ExecuteUpdate jest taka sama jak poprzednio. Zlecenie SQL jest wykonywane w transakcji domyślnej. Jest to możliwe w tym przypadku, ponieważ wiadomo, że w tej sytuacji SQLServer Compact działa w trybie autocommit.
wiersze 69–71: to samo dotyczy metody ExecuteSelect.

Metoda InsertArticlesInTransaction wygląda następująco:


// wstawienie tabeli artykułów do transakcji
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // nawiązanie połączenia
                connexion.Open();
                // konfiguracja polecenia
                string requête = "insert into articles(nom,prix,stockactuel,stockminimum) values(@nom,@prix,@sa,@sm)";
                SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                sqlCommand.Parameters.Add("@nom", SqlDbType.NVarChar, 30);
                sqlCommand.Parameters.Add("@prix", SqlDbType.Money);
                sqlCommand.Parameters.Add("@sa", SqlDbType.Int);
                sqlCommand.Parameters.Add("@sm", SqlDbType.Int);
                // kompilacja zlecenia
                sqlCommand.Prepare();
                // transakcja
                SqlCeTransaction transaction = null;
                try {
                    // rozpoczęcie transakcji
                    transaction = connexion.BeginTransaction(IsolationLevel.ReadCommitted);
                    // polecenie SQL musi zostać wykonane w tej transakcji
                    sqlCommand.Transaction = transaction;
                    // wstawianie wierszy
                    for (int i = 0; i < articles.Length; i++) {
                        // inicjalizacja parametrów
                        sqlCommand.Parameters["@nom"].Value = articles[i].Nom;
                        sqlCommand.Parameters["@prix"].Value = articles[i].Prix;
                        sqlCommand.Parameters["@sa"].Value = articles[i].StockActuel;
                        sqlCommand.Parameters["@sm"].Value = articles[i].StockMinimum;
                        // wykonanie zapytania
                        sqlCommand.ExecuteNonQuery();
                    }
                    // zatwierdzamy transakcję
                    transaction.Commit();
                    Console.WriteLine("transaction validée...");
                } catch {
                    // cofnięcie transakcji
                    if (transaction != null)transaction.Rollback();
                    Console.WriteLine("transaction invalidée...");
                }
            }
        }

Omawiamy tylko to, co odróżnia ją od metody InsertArticles z programu [Parametres.cs], który analizowaliśmy wcześniej:

wiersz 16: zadeklarowano transakcję SqlCeTransaction.
wiersze 17, 35: blok try/catch służący do obsługi wyjątku, który wystąpi po zakończeniu drugiego wstawiania
wiersz 19: tworzona jest transakcja. Należy ona do bieżącego połączenia.
wiersz 21: skonfigurowane polecenie SQL jest umieszczane w transakcji
wiersze 23–31: operacje wstawiania zostały wykonane
wiersz 33: wszystko przebiegło pomyślnie – transakcja została zatwierdzona – wstawienia zostaną ostatecznie zapisane w bazie danych.
wiersz 37: wystąpił problem. Transakcja zostaje cofnięta, jeśli istniała.

Wykonanie daje następujące wyniki:

Chaîne de connexion à la base : [Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;]

Insertion sans transaction...
Il y a eu 0 ligne(s) modifiée(s)
Erreur d'accès à la base de données (A duplicate value cannot be inserted into a unique index. [ Table name = ARTICLES,Constraint name = UQ__ARTICLES__0000000000000010 ])

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

126 article 100 10 1


Insertion dans une transaction...
Il y a eu 1 ligne(s) modifiée(s)
transaction invalidée...

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

wiersz 4: wyświetlony przez ExecuteUpdate („delete from articles”) – w tabeli nie było żadnych wierszy
wiersz 5: wyjątek spowodowany drugim wstawieniem. Komunikat wskazuje, że nie sprawdzono ograniczenia UQ__ARTICLES__0000000000000010. Więcej informacji można uzyskać, sprawdzając właściwości bazy danych:

w [1] w widoku [Database Explorer] programu Visual Studio utworzono połączenie [2] z bazą danych [dbarticles.sdf]. Połączenie to ma indeks UQ__ARTICLES__0000000000000010. Klikając prawym przyciskiem myszy na ten indeks, uzyskuje się dostęp do jego właściwości (Index properties)
w [3,4] widać, że indeks UQ__ARTICLES__0000000000000010 odpowiada ograniczeniu unikalności w kolumnie [NOM]

wiersze 7–11: wyświetlenie tabeli articles po dwóch wstawieniach. Nie jest pusta: wstawiono pierwszy artykuł.
wiersz 15: wyświetlony przez ExecuteUpdate („delete from articles”) – w tabeli znajdował się jeden wiersz
wiersz 16: komunikat wyświetlony przez InsertArticlesInTransaction w przypadku niepowodzenia transakcji.
wiersze 18–20: pokazują, że nie dokonano żadnego wstawienia. Rollback tej transakcji cofnął pierwsze wstawienie.

9.7. Metoda ExecuteScalar

9.7.1. Wśród metod interfejsu IDbCommand opisanego w paragrafie 9.3.3 znajdowała się następująca metoda:

ExecuteScalar

M

w celu wykonania polecenia SQL Select, zwracającego tylko jeden wynik, jak w przypadku: select count(*) from articles.

Poniżej przedstawiamy przykład zastosowania tej metody. Wróćmy do projektu:

w [1], projekt.
w [2] projekt jest skonfigurowany do uruchamiania [ExecuteScalar.cs]

Program [ExecuteScalar.cs] wygląda następująco:


...
namespace Chap7 {
    class Scalar {
        static void Main(string[] args) {

            // przetwarzanie pliku konfiguracyjnego
            string connectionString = null;
...

            // wyświetlanie
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // tworzenie tabeli zawierającej 5 pozycji
            Article[] articles = new Article[5];
            for (int i = 1; i <= articles.Length; i++) {
                articles[i - 1] = new Article(0, "article" + i, i * 100, i * 10, i);
            }

            // obsługa ewentualnych wyjątków
            try {
                // wstawianie tabeli artykułów do transakcji
                ExecuteUpdate(connectionString, "delete from articles");
                InsertArticlesInTransaction(connectionString, articles);
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
                // obliczamy średnią cen artykułów
                decimal prixMoyen = (decimal)ExecuteScalar(connectionString, "select avg(prix) from articles");
                Console.WriteLine("Prix moyen des articles={0}", prixMoyen);
                // lub liczbę artykułów
                int nbArticles = (int)ExecuteScalar(connectionString, "select count(id) from articles");
                Console.WriteLine("Nombre d'articles={0}", nbArticles);
            } catch (Exception ex) {
                // komunikat o błędzie
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // wstawianie tabeli artykułów do transakcji
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
...
        }


        // wykonanie zapytania aktualizacyjnego
        static object ExecuteScalar(string connectionString, string requête) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // nawiązanie połączenia
                connexion.Open();
                // wykonanie zapytania
                return new SqlCeCommand(requête, connexion).ExecuteScalar();
            }
        }

        // wykonanie zapytania o aktualizację
        static void ExecuteUpdate(string connectionString, string requête) {
...
        }

        // wykonanie zapytania SELECT
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // wyświetlenie w czytniku
        static void AfficheReader(IDataReader reader) {
...
        }
    }
}

wiersze 14–17: utworzenie tabeli zawierającej 5 pozycji
wiersz 22: tabela articles zostaje wyczyszczona
wiersz 23: tabela jest wypełniana 5 pozycjami
wiersz 24: tabela jest wyświetlana
wiersz 26: zapytanie o średnią cenę artykułów
wiersz 29: pobiera liczbę artykułów
wiersz 49: wykorzystanie metody [IDbCommand].ExecuteScalar() do obliczenia każdej z tych wartości.

Wyniki wykonania są następujące:

Chaîne de connexion à la base : [Data Source=|DataDirectory|\dbarticles.sdf;Password=dbarticles;]

Il y a eu 5 ligne(s) modifiée(s)
transaction validée...

------------------------------------
ID,NOM,PRIX,STOCKACTUEL,STOCKMINIMUM
------------------------------------

145 article1 100 10 1
146 article2 200 20 2
147 article3 300 30 3
148 article4 400 40 4
149 article5 500 50 5
Prix moyen des articles=300
Nombre d'articles=5

Wiersze 15 i 16 przedstawiają dwie wartości zwracane przez metodę ExecuteScalar.

9.8. Przykładowa aplikacja – wersja 7

Wracamy do przykładowej aplikacji IMPOTS. Ostatnia wersja została omówiona w paragrafie 7.6. Była to następująca aplikacja trójwarstwowa:

warstwa [ui] stanowiła interfejs graficzny [A], a warstwa [dao] pobierała dane z pliku tekstowego [B].
Instancjonowanie warstw i ich integracja z aplikacją były realizowane przez Spring.

Modyfikujemy warstwę [dao] tak, aby pobierała dane z bazy danych.

9.8.1. a bazy danych

Zawartość poprzedniego pliku tekstowego [B] jest umieszczana w bazie danych MySQL5. Pokazujemy, jak to zrobić:

[1]: uruchomiono program MySQL Administrator
[2,3]: w obszarze [Schemata] kliknij prawym przyciskiem myszy i wybierz opcję [Create Schema], aby utworzyć nową bazę danych
[4]: baza będzie nosiła nazwę [bdimpots]
[5]: została dodana do baz w obszarze [Schemata].

[6,7]: kliknij prawym przyciskiem myszy na tabelę i wybierz opcję [Create New Table], aby utworzyć tabelę
[8]: tabela będzie nosiła nazwę [tranches]. Będzie zawierała kolumny z tabeli [id, limite, coeffR, coeffN].
[9,10]: [id] jest kluczem głównym typu INTEGER i posiada atrybut AUTO_INCREMENT [10]: to właśnie SGBD będzie odpowiedzialny za wypełnienie tej kolumny podczas dodawania wierszy.
Kolumny o nazwie [limite, coeffR, coeffN] są typu DOUBLE.
[11,12]: nowa tabela pojawia się w zakładce [Schema Tables] bazy danych.

[13,14]: w celu wprowadzenia danych do tabeli
[15]: uruchomiono [Query Browser]
[16]: dane zostały wprowadzone i zweryfikowane dla kolumn [limite, coeffR, coeffN]. Kolumna [id] została wypełniona przez SGBD. Walidacja odbyła się przy użyciu [17].

nadal w [Query Browser] [18], uruchamia się [20] zapytanie [19]. Zapytanie to tworzy użytkownika „admimpots” z hasłem „mdpimpots” i przyznaje mu wszystkie uprawnienia (grant all privileges) do wszystkich obiektów bazy danych bdimpots (on bdimpots.*). Dzięki temu będziemy mogli pracować z bazą danych [bdimpots] przy użyciu użytkownika [admimpots] zamiast administratora [root].

9.8.2. Rozwiązanie w Visual Studio

Będziemy postępować zgodnie z procedurą omówioną dla wersji 5 przykładowej aplikacji (patrz punkt 6.4). Stopniowo zbudujemy następujące rozwiązanie w Visual Studio:

w [1]: rozwiązanie ImpotsV7 składa się z trzech projektów, po jednym dla każdej z trzech warstw aplikacji
w [2]: projekt [dao] warstwy [dao], który odtąd będzie korzystał z bazy danych
w [3]: projekt [metier] warstwy [metier]. W tym miejscu powracamy do warstwy [metier] z wersji 5, opisanej w paragrafie 6.4.4.
w [4]: projekt [ui] warstwy [ui]. W tym miejscu wykorzystujemy warstwę [ui] z wersji 6, opisaną w paragrafie 7.6.

Opieramy się na dotychczasowych osiągnięciach, aby odzyskać dwie już napisane warstwy: [ui] i [metier]. Jest to możliwe dzięki wybranej architekturze warstwowej. Będziemy jednak potrzebować kodów źródłowych warstw [ui] i [metier]. Nie można bowiem zadowolić się jedynie warstwami DLL. Kiedy w wersji 5 utworzono warstwę DLL warstwy [metier], była ona zależna od warstwy DLL warstwy [dao]. Ta zależność została na stałe zapisana w warstwie DLL warstwy [metier] (nazwa warstwy DLL warstwy [dao], wersja, token tożsamości itp.). W związku z tym plik DLL z wersji 5 [ImpotsV5-metier.dll] akceptuje współpracę wyłącznie z plikiem DLL i [ImpotsV5-dao.dll], z którymi został skompilowany. Jeśli zmienimy DLL w warstwie [dao], należy ponownie skompilować warstwę [metier], aby utworzyć dla niej nowy token DLL. To samo dotyczy warstwy [ui]. Warstwy [ui] i [metier] nie zostaną zatem zmodyfikowane, ale zostaną ponownie skompilowane, aby współpracowały z warstwą DLL nowej warstwy [dao].

9.8.3. Warstwa [dao]

Odniesienia do projektu (patrz [1] w projekcie)

nunit.framework: do testów NUnit
System.Configuration: w celu wykorzystania pliku konfiguracyjnego [App.config]
System.Data: ponieważ korzystamy z bazy danych.

Entities (patrz [2] w projekcie)

Klasy [TrancheImpot] i [ImpotException] pochodzą z poprzednich wersji.

Warstwa [dao] (patrz [3] w projekcie)

Interfejs [IImpotDao] nie uległ zmianie:


using Entites;

namespace Dao {
    public interface IImpotDao {
        // przedziały podatkowe
        TrancheImpot[] TranchesImpot{get;}
    }
}

Klasa implementacyjna [DataBaseImpot] tego interfejsu jest następująca:


using System;
using System.Collections.Generic;
using System.Data.Common;
using Entites;

namespace Dao {
    public class DataBaseImpot : IImpotDao {
        // przedziały podatkowe
        private TrancheImpot[] tranchesImpot;
        public TrancheImpot[] TranchesImpot { get { return tranchesImpot; } }

        // producent
        public DataBaseImpot(string factory, string connectionString, string requête) {
            // fabryka: fabryka docelowa dla SGBD
            // connectionString: ciąg łączący z bazą przedziałów podatkowych
            // obsługuje ewentualne wyjątki
            try {
                // pobieramy ogólny łącznik dla SGBD
                DbProviderFactory connecteur = DbProviderFactories.GetFactory(factory);
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // konfiguracja połączenia
                    connexion.ConnectionString = connectionString;
                    // nawiązanie połączenia
                    connexion.Open();
                    // konfiguracja polecenia 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // wykonanie zapytania
                    List<TrancheImpot> listTrancheImpot = new List<TrancheImpot>();
                    using (DbDataReader reader = sqlCommand.ExecuteReader()) {
                        while (reader.Read()) {
                            // tworzy się nową klasę podatkową
                            listTrancheImpot.Add(new TrancheImpot() { Limite = reader.GetDecimal(0), CoeffR = reader.GetDecimal(1), CoeffN = reader.GetDecimal(2) });
                        }
                    }
                    // umieszczanie przedziałów podatkowych w instancji
                    tranchesImpot = listTrancheImpot.ToArray();
                }
            } catch (Exception ex) {
                // wyjątek jest enkapsulowany w typie ImpotException
                throw new ImpotException("Erreur de lecture des tranches d'impôt", ex) { Code = 101 };
            }

        }
    }
}

wiersz 7: klasa [DataBaseImpot] implementuje interfejs [IImpotDao].
wiersz 10: implementacja metody [TranchesImpot] interfejsu. Ogranicza się ona do zwracania odwołania do tablicy przedziałów podatkowych z wiersza 9. Tablica ta zostanie utworzona przez konstruktor klasy.
wiersz 13: konstruktor. Wykorzystuje on łącznik generyczny (patrz paragraf 9.4.5) w celu korzystania z bazy danych przedziałów podatkowych. Konstruktor otrzymuje trzy parametry:
- nazwę „fabryki”, do której zwróci się o klasy umożliwiające połączenie z bazą, wysłanie poleceń SQL oraz wykorzystanie wyniku zapytania SELECT.
- ciąg połączenia, którego musi użyć, aby połączyć się z bazą danych
- polecenie SQL Select, które musi wykonać, aby uzyskać przedziały podatkowe.
wiersz 19: żądanie łącznika od „factory”
wiersz 20: tworzy połączenie z tym łącznikiem. Połączenie zostało utworzone, ale nie jest jeszcze gotowe do działania
wiersz 22: inicjowany jest ciąg połączenia. Teraz można nawiązać połączenie.
wiersz 24: nawiązywane jest połączenie
wiersz 26: żądanie od łącznika obiektu [DbCommand] w celu wykonania zlecenia SQL
wiersz 27: określa zlecenie SQL do wykonania
wiersz 28: określa połączenie, na którym ma zostać wykonane
wiersz 30: tworzona jest pusta lista [listTrancheImpot] zawierająca obiekty typu [TrancheImpot].
wiersz 31: wykonywane jest polecenie SQL Select
wiersze 32–35: obiekt [DbDataReader] będący wynikiem polecenia Select jest przetwarzany. Każdy wiersz tabeli wynikowej polecenia Select służy do utworzenia instancji obiektu typu [TrancheImpot], który jest dodawany do listy [listTrancheImpot].
wiersz 38: lista obiektów typu [TrancheImpot] jest przenoszona do tablicy w wierszu 9.
wiersze 40–43: ewentualny wyjątek jest enkapsulowany w typie [ImpotException] i otrzymuje kod błędu 101 (dowolny).

Test [Test1] (por. [4] w projekcie)

Klasa [Test1] służy wyłącznie do wyświetlania przedziałów podatkowych na ekranie. Jest to ta sama klasa, która została już wykorzystana w wersji 5 (punkt 6.4.3), z wyjątkiem instrukcji instancjonującej warstwę [dao] (wiersz 14).


using System;
using Dao;
using Entites;
using System.Configuration;

namespace Tests {
    class Test1 {
        static void Main() {

            // tworzy się warstwę [dao]
            IImpotDao dao = null;
            try {
                // utworzenie warstwy [dao]
                dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
            } catch (ImpotException e) {
                // wyświetlenie błędu
                string msg = e.InnerException == null ? null : String.Format(", Exception d'origine : {0}", e.InnerException.Message);
                Console.WriteLine("L'erreur suivante s'est produite : [Code={0},Message={1}{2}]", e.Code, e.Message, msg == null ? "" : msg);
                // zatrzymanie programu
                Environment.Exit(1);
            }
            // wyświetlanie przedziałów podatkowych
            TrancheImpot[] tranchesImpot = dao.TranchesImpot;
            foreach (TrancheImpot t in tranchesImpot) {
                Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
            }
        }
    }
}

W wierszu 14 wykorzystywany jest następujący plik konfiguracyjny [App.config]:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="dbImpotsMySql5" connectionString="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;" />
    </connectionStrings>
    <appSettings>
        <add key="requete" value="select limite, coeffr, coeffn from tranches"/>
        <add key="factoryMySql5" value="MySql.Data.MySqlClient"/>
    </appSettings>
</configuration>

wiersz 4: ciąg połączenia z bazą danych MySQL5. Należy zauważyć, że to użytkownik [admimpots] nawiąże połączenie.
wiersz 8: „factory” do pracy z SGBD MySQL5
wiersz 7: zapytanie SQL typu SELECT służące do uzyskania przedziałów podatkowych.

Projekt jest skonfigurowany do uruchamiania [Test1.cs]:

Wykonanie testu daje następujące wyniki:

4962:0:0
8382:0,068:291,09
14753:0,191:1322,92
23888:0,283:2668,39
38868:0,374:4846,98
47932:0,426:6883,66
0:0,481:9505,54

Test NUnit [NUnit1] (por. [4] w projekcie)

Test jednostkowy [NUnit1] jest tym samym testem, który był już używany w wersji 5 (punkt 6.4.3), z wyjątkiem instrukcji instancjonującej warstwę [dao] (wiersz 16).


using System;
using System.Configuration;
using Dao;
using Entites;
using NUnit.Framework;

namespace Tests {
    [TestFixture]
    public class NUnit1 : AssertionHelper{
        // warstwa [dao] do przetestowania
        private IImpotDao dao;

        // konstruktor
        public NUnit1() {
            // inicjalizacja warstwy [dao]
            dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
        }

        // test
        [Test]
        public void ShowTranchesImpot(){
            // wyświetlanie przedziałów podatkowych
            TrancheImpot[] tranchesImpot = dao.TranchesImpot;
            foreach (TrancheImpot t in tranchesImpot) {
                Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
            }
            // kilka testów
            Expect(tranchesImpot.Length,EqualTo(7));
            Expect(tranchesImpot[2].Limite,EqualTo(14753).Within(1e-6));
            Expect(tranchesImpot[2].CoeffR, EqualTo(0.191).Within(1e-6));
            Expect(tranchesImpot[2].CoeffN, EqualTo(1322.92).Within(1e-6));
        }
    }
}

Aby wykonać ten test jednostkowy, projekt musi być typu [Class Library]:

w [1]: typ projektu został zmieniony
na [2]: wygenerowany plik DLL będzie nosił nazwę [ImpotsV7-dao.dll]
w [3]: po wygenerowaniu (F6) projektu folder [dao/bin/Release] zawiera pliki DLL i [ImpotsV7-dao.dll]. Zawiera on również plik konfiguracyjny [App.config], którego nazwa została zmieniona na [nom DLL].config. Jest to standardowa procedura w programie Visual Studio.

Następnie pliki DLL i [ImpotsV7-dao.dll] są ładowane do frameworka NUnit i uruchamiane:

w [1]: testy zakończyły się powodzeniem. Uznajemy zatem warstwę [dao] za gotową do działania. Jej plik DLL zawiera wszystkie klasy projektu, w tym klasy testowe. Te ostatnie są zbędne. Przebudowujemy warstwę DLL w celu wykluczenia z niej klas testowych.
w [2]: folder [tests] został wykluczony z projektu
w [3]: nowy projekt. Jest on ponownie generowany przez F6 w celu utworzenia nowego pliku DLL. To właśnie ten plik DLL będzie wykorzystywany przez warstwy [metier] i [ui] aplikacji.

9.8.4. Warstwa [metier]

w [1] projekt [metier] stał się aktywnym projektem rozwiązania
w [2]: odniesienia do projektu. Należy zwrócić uwagę na odniesienie w DLL do warstwy [dao] utworzonej wcześniej. Procedura dodawania odniesień została opisana w wersji 5, w paragrafie 6.4.4.
w [3]: warstwa [metier]. Jest to warstwa z wersji 5, opisana w paragrafie 6.4.4.

Projekt [metier] jest skonfigurowany do generowania pliku DLL:

[1]: projekt jest typu „biblioteka klas”
[2]: wygenerowanie projektu spowoduje utworzenie plików DLL, [ImpotsV7-metier.dll] oraz [3].

Projekt został wygenerowany (F6).

9.8.5. Warstwa [ui]

w [1] projekt [ui] stał się aktywnym projektem rozwiązania
w [2]: numery referencyjne projektu. Należy zwrócić uwagę na numery referencyjne w DLL warstw [dao] i [metier].
w [3]: warstwa [ui]. Jest to warstwa z wersji 6 opisana w paragrafie 7.6.
w [4] plik konfiguracyjny [App.config] jest analogiczny do tego z wersji 6. Różni się od niego jedynie sposobem, w jaki warstwa [dao] jest instancjonowana przez Spring:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>

    <spring>
        <context>
            <resource uri="config://spring/objects" />
        </context>
        <objects xmlns="http://www.springframework.net">
            <object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
                <constructor-arg index="0" value="MySql.Data.MySqlClient"/>
                <constructor-arg index="1" value="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;"/>
                <constructor-arg index="2" value="select limite, coeffr, coeffn from tranches"/>
            </object>
            <object name="metier" type="Metier.ImpotMetier, ImpotsV7-metier">
                <constructor-arg index="0" ref="dao"/>
            </object>
        </objects>
    </spring>
</configuration>

wiersze 11–25: konfiguracja Springa
wiersze 15–24: obiekty instancjonowane przez Spring
wiersze 16–20: instancjonowanie warstwy [dao]
wiersz 16: warstwa [dao] jest instancjonowana przez klasę [Dao.DataBaseImpot], która znajduje się w DLL [ImpotsV7-Dao]
wiersze 17–19: trzy parametry (fabryka używanego obiektu SGBD, ciąg połączenia, żądanie SQL), które należy przekazać konstruktorowi klasy [Dao.DataBaseImpot]
wiersze 21–23: instancja warstwy [metier]. Jest to ta sama konfiguracja, co w wersji 6.

Testy

Projekt [ui] jest skonfigurowany w następujący sposób:

[1]: projekt jest typu „Aplikacja Windows”
[2]: kompilacja projektu spowoduje wygenerowanie pliku wykonywalnego [ImpotsV7-ui.exe]

Przykład uruchomienia znajduje się w pliku [3].

9.8.6. Zmiana bazy danych

Powyższa warstwa [dao] została napisana z wykorzystaniem łącznika generycznego i bazy MySQL5. Zamierzamy teraz przejść na bazę SQL Server Compact, aby pokazać, że zmieni się jedynie konfiguracja.

Baza SQL Server Compact będzie wyglądać następująco:

[1]: baza danych [dbimpots.sdf] w widoku [DataBase Explorer] programu Visual Studio [2]. Została utworzona bez hasła.
[3]: tabela [data] zawierająca dane. Celowo wybrano inne nazwy dla tabeli i kolumn niż te używane w bazie MySQL5, aby ponownie podkreślić korzyści płynące z umieszczania tego rodzaju szczegółów w pliku konfiguracyjnym, a nie w kodzie.
[4]: kolumna [id] jest kluczem głównym i posiada atrybut Identity: to właśnie SGBD będzie jej przypisywać wartości.
[5]: zawartość tabeli [data].

[6]: baza danych [dbimpots.sdf] została umieszczona w folderze projektu [ui] i włączona do tego projektu.
[7]: baza danych [dbimpots.sdf] zostanie skopiowana do folderu uruchomieniowego projektu.

Plik konfiguracyjny [App.config] dla nowej bazy danych ma następującą treść:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>

    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>

    <spring>
        <context>
            <resource uri="config://spring/objects" />
        </context>
        <objects xmlns="http://www.springframework.net">
            <!--
            <object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
                <constructor-arg index="0" value="MySql.Data.MySqlClient"/>
                <constructor-arg index="1" value="Server=localhost;Database=bdimpots;Uid=admimpots;Pwd=mdpimpots;"/>
                <constructor-arg index="2" value="select limite, coeffr, coeffn from tranches"/>
            </object>
            -->
            <object name="dao" type="Dao.DataBaseImpot, ImpotsV7-dao">
                <constructor-arg index="0" value="System.Data.SqlServerCe.3.5"/>
                <constructor-arg index="1" value="Data Source=|DataDirectory|\dbimpots.sdf;" />
                <constructor-arg index="2" value="select data1, data2, data3 from data"/>
            </object>
            <object name="metier" type="Metier.ImpotMetier, ImpotsV7-metier">
                <constructor-arg index="0" ref="dao"/>
            </object>
        </objects>
    </spring>
</configuration>

wiersze 23–27: konfiguracja warstwy [dao] w celu wykorzystania bazy danych [dbimpots.sdf].

Wyniki wykonania są identyczne jak poprzednio. Warto zwrócić uwagę na korzyści wynikające z zastosowania łącznika generycznego, dzięki czemu warstwa [dao] jest odporna na zmiany w SGBD. Zauważyliśmy jednak, że łącznik ten nie sprawdza się we wszystkich sytuacjach, zwłaszcza tam, gdzie stosowane są zapytania parametryczne. Istnieją zatem inne rozwiązania, takie jak wspomniane wcześniej frameworki innych firm służące do dostępu do danych (Spring, iBatis, NHibernate, LINQ, ...).

9.9. Aby dowiedzieć się więcej...

LINQ jest omawiane w wielu publikacjach, w szczególności w książce: „C# 3.0 in a Nutshell”, autorstwa Josepha i Bena Albahari, wydawnictwa O’Reilly, o której wspomniano już we wstępie do niniejszego dokumentu.
iBatis jest omówiony w książce: „iBatis in Action”, autorstwa Clintona Begina, wydawnictwo Manning
Książka „Nhibernate in Action”, wydana przez wydawnictwo Manning, ma ukazać się w lipcu 2008 r.

Spring, iBatis oraz NHibernate mają podręczniki referencyjne dostępne na stronach internetowych tych różnych frameworków.