9. Доступ до баз даних

9.1. Коннектор ADO.NET

Повернемося до багаторівневої архітектури, яка використовувалася неодноразово

У розглянутих прикладах рівень [dao] наразі використовував два типи джерел даних:

• дані, вбудовані безпосередньо в код
• дані з текстових файлів

У цьому розділі ми розглянемо випадок, коли дані надходять із бази даних. У цьому разі тришарова архітектура перетворюється на багатошарову. Існує кілька її різновидів. Ми розглянемо основні концепції на прикладі такої:

На наведеному вище схематичному зображенні рівень [dao] [1] взаємодіє з рівнем SGBD [3] за допомогоюбібліотеку класів, що належить до використовуваного SGBD і постачається разом з ним. Цей рівень реалізує стандартні функції, об’єднані під назвою ADO (Active X Data Objects). Такий рівень називають провайдером (у даному випадку — постачальником доступу до бази даних) або коннектором. Більшість SGBD тепер мають коннектор ADO.NET, чого не було на початку існування платформи .NET. Коннектори .NET не надають стандартного інтерфейсу для рівня [dao], тому в його коді містяться імена класів коннектора. Якщо змінити SGBD, змінюється і коннектор, і класи, а отже, доводиться змінювати й рівень [dao]. Це водночас ефективна архітектура, оскільки коннектор .NET, написаний для конкретного SGBD, вміє максимально ефективно використовувати його, і водночас є жорсткою, оскільки зміна SGBD означає необхідність змінити шар [dao]. Цей другий аргумент слід розглядати з обережністю: підприємства не змінюють SGBD дуже часто. Крім того, пізніше ми побачимо, що починаючи з версії 2.0 .NET існує універсальний коннектор, який забезпечує гнучкість без втрати продуктивності.

9.2. Два режими роботи з джерелом даних

Платформа .NET дозволяє використовувати джерело даних двома різними способами:

1. режим з підключенням
2. автономний режим

У режимі з підключенням додаток

1. встановлює з’єднання з джерелом даних
2. працює з джерелом даних у режимі читання/запису
3. закриває з'єднання

У режимі без підключення додаток

1. відкриває з'єднання з джерелом даних
2. отримує копію в пам'яті всіх або частини даних із джерела
3. закриває з'єднання
4. працює з копією даних у пам’яті в режимі читання/запису
5. коли робота завершена, відкриває з’єднання, надсилає змінені дані до джерела даних для їхнього врахування, закриває з’єднання

Тут ми розглядаємо лише режим з підключенням.

9.3. Основні поняття роботи з базою даних

Ми розглянемо основні поняття використання бази даних на прикладі SQL Server Compact 3.5. Цей SGBD входить до складу Visual Studio Express. Це полегшена версія, яка може обслуговувати лише одного користувача одночасно. Однак її цілком достатньо для ознайомлення з програмуванням із базами даних. Пізніше ми розглянемо інші сервери.

Архітектура буде такою:

Консольний додаток [1] використовуватиме базу даних типу SqlServer Compact [3,4] через коннектор Ado.Net цього SGBD [2].

9.3.1. — приклад бази даних

Ми створимо базу даних безпосередньо у Visual Studio Express. Для цього створимо новий проект типу «консоль».

• [1]: проект
• [2]: відкриваємо вікно «Провідник баз даних»
• [3]: створюємо нове з’єднання

• [4]: вибираємо тип SGBD
• [5,6]: вибирається SGBD SQL Server Compact
• [7]: створюється база даних
• [8]: база даних SQL Server Compact інкапсульована в єдиний файл із розширенням .sdf. Вказуємо, де її створити, у даному випадку — у папці проекту C#.
• [9]: новій базі даних присвоєно ім’я [dbarticles.sdf]
• [10]: вибираємо французьку мову. Це впливає на операції сортування.
• [11,12]: базу даних можна захистити паролем. У цьому випадку — «dbarticles».
• [13]: підтверджуємо сторінку з інформацією. База даних буде фізично створена:

• [14]: назва щойно створеної бази даних
• [15]: встановлюємо галочку біля опції «Save my password», щоб не вводити пароль щоразу заново
• [16]: перевіряємо з’єднання
• [17]: усе гаразд
• [18]: підтверджуємо сторінку з інформацією
• [19]: з’єднання відображається в оглядачі баз даних
• [20]: наразі база даних не містить таблиць. Створюємо одну. Стаття матиме такі поля:
  • id: унікальний ідентифікатор — первинний ключ
  • nom: назва товару — унікальна
  • prix: ціна товару
  • stockactuel: поточний запас товару
  • stockminimum: мінімальний запас, при недосягненні якого товар потрібно поповнити

• [21]: поле [id] є цілочисельним і є первинним ключем [22] таблиці.
• [23]: цей первинний ключ має тип Identity. Ця особливість, властива серверам SGBD та SQL, означає, що первинний ключ буде генеруватися самим сервером SGBD. У цьому випадку первинний ключ буде цілим числом, що починається з 1 і збільшується на 1 з кожним новим ключем.

• [24]: створюються інші поля. Слід зауважити, що поле [nom] має обмеження унікальності [25].
• [26]: таблиці присвоюється ім’я
• [27]: після перевірки структури таблиці вона з’являється в базі даних.

• [28]: запит на перегляд вмісту таблиці
• [29]: наразі вона порожня
• [30]: заповнюємо її деякими даними. Рядок підтверджується, щойно переходимо до введення наступного рядка. Поле [id] не заповнюється: воно генерується автоматично під час підтвердження рядка.

Залишається налаштувати проєкт так, щоб ця база даних, яка наразі знаходиться в кореневій папці проєкту, автоматично копіювалася до папки виконання проєкту:

• [1]: перегляд усіх файлів
• [2]: з’являється база даних [dbarticles.sdf]
• [3]: її додано до проекту

• [4]: операція додавання джерела даних до проекту запускає майстер, який нам тут не потрібен [5].
• [6]: база даних тепер є частиною проєкту. Повертаємося до звичайного режиму [7].
• [8]: проект разом із базою даних
• [9]: у властивостях бази даних можна побачити, що [10] буде автоматично скопійована в папку виконання проекту. Саме звідти програма, яку ми будемо писати, буде її завантажувати.

Тепер, коли у нас є база даних, ми зможемо нею скористатися. Але спершу нагадаємо деякі основні моменти SQL.

9.3.2. Чотири основні команди мови SQL

SQL (Structured Language Query) — це частково стандартизована мова запитів та оновлення баз даних. Усі SGBD відповідають стандартизованій частині SQL, але додають до мови власні розширення, які використовують певні особливості SGBD. Ми вже зустрічали два приклади цього: автоматичне генерування первинних ключів та дозволені типи для стовпців таблиці часто залежать від SGBD.

Чотири основні команди мови SQL, які ми представляємо, є стандартизованими та підтримуються всіма SGBD:

select col1, col2,... from table1, table2,... where condition order by expression ...	Запит, що дозволяє отримати дані, які містяться в базі. Обов’язковими є лише ключові слова першого рядка, решта — необов’язкові. Існують також інші ключові слова, які тут не наведено. Здійснюється з'єднання з усіма таблицями, що стоять після ключового слова from Зберігаються лише стовпці, що знаходяться після ключового слова «select» Зберігаються лише рядки, що відповідають умові ключового слова «where» Отримані рядки, впорядковані за виразом ключового слова «order by», утворюють результат запиту. Цей результат є таблицею.
insert into table(col1,col2, ...) values (val1,val2, ...)	Вставляє рядок у таблицю. (col1, col2, ...) вказує стовпці рядка, які потрібно заповнити значеннями (val1, val2, ...).
update table set col1=val1, col2=val2 where condition	Оновлює рядки таблиці, що відповідають умові (усі рядки, якщо немає where). Для цих рядків стовпець coli отримує значення vali
delete from table where condition	Видаляє всі рядки таблиці, що відповідають умові

Ми напишемо консольну програму, яка дозволить надсилати команди SQL до бази даних [dbarticles], яку ми створили раніше. Ось при ний приклад виконання. Читачеві пропонується розібратися у надісланих командах SQL та їхніх результатах.

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

• рядок 1: так званий рядок підключення: він містить усі параметри, необхідні для підключення до бази даних.
• рядок 3: запитується вміст таблиці [articles]
• рядок 16: вставляється новий рядок. Слід зауважити, що поле id у цій операції не ініціалізується, оскільки саме SGBD генеруватиме значення цього поля.
• рядок 19: перевірка. Рядок 28 — рядок було успішно додано.
• рядок 30: ціну щойно доданого товару збільшують на 10%.
• рядок 33: перевіряємо
• рядок 42: підвищення ціни відбулося
• рядок 44: видаляємо товар, який додали раніше
• рядок 47: перевіряємо
• рядки 53–55: товару більше немає.

9.3.3. Основні інтерфейси ADO.NET для режиму з підключенням

Повернемося до схеми додатка, що використовує базу даних через коннектор ADO.NET:

У режимі підключення додаток:

1. відкриває з’єднання з джерелом даних
2. працює з джерелом даних у режимі читання/запису
3. закриває з’єднання

Ці операції переважно стосуються трьох інтерфейсів ADO.NET:

• IDbConnection, який інкапсулює властивості та методи з’єднання.
• IDbCommand, що інкапсулює властивості та методи виконаного запиту SQL.
• IDataReader, що інкапсулює властивості та методи результату команди SQL Select.

Інтерфейс IDbConnection

Служить для управління з'єднанням із базою даних. Методи M та властивості P цього інтерфейсу, які ми будемо використовувати, такі:

Назва	Тип	Роль
ConnectionString	P	рядок підключення до бази даних. Він визначає всі параметри, необхідні для встановлення з'єднання з конкретною базою даних.
Open	M	відкриває з’єднання з базою даних, визначеною параметром ConnectionString
Close	M	закриває з’єднання
BeginTransaction	M	починає транзакцію.
State	P	стан з'єднання: ConnectionState.Closed, ConnectionState.Open, ConnectionState.Connecting, ConnectionState.Executing, ConnectionState.Fetching, ConnectionState.Broken

Якщо Connection є класом, що реалізує інтерфейс IDbConnection, відкриття з'єднання можна здійснити наступним чином:

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

Інтерфейс IDbCommand

використовується для виконання команди SQL або збереженої процедури. Методи M та властивості P цього інтерфейсу, які ми будемо використовувати, такі:

Назва	Тип	Роль
CommandType	P	вказує, що потрібно виконати — значення береться з переліку: - CommandType.Text: виконує команду SQL, визначену у властивості CommandText. Це значення за замовчуванням. - CommandType.StoredProcedure: виконує процедуру, збережену в базі
CommandText	P	- текст команди SQL, яку слід виконати, якщо CommandType = CommandType.Text - ім’я збереженої процедури, яку слід виконати, якщо CommandType = CommandType.StoredProcedure
Connection	P	з'єднання IDbConnection, яке слід використовувати для виконання команди SQL
Transaction	P	транзакція IDbTransaction, в якій слід виконати наказ SQL
Parameters	P	список параметрів налаштованого запиту SQL. Запит update articles set price=price*1.1 where id=@id має параметр @id.
ExecuteReader	M	для виконання запиту SQL Select. Отримуємо об’єкт IDataReader, що представляє результат запиту Select.
ExecuteNonQuery	M	для виконання команди SQL «Оновити, Вставити, Видалити». Отримується кількість рядків, на які вплинула операція (оновлені, вставлені, видалені).
ExecuteScalar	M	для виконання команди SQL Select, яка повертає лише один результат, як у запиті: select count(*) from articles.
CreateParameter	M	для створення параметрів IDbParameter для налаштованого запиту SQL.
Prepare	M	дозволяє оптимізувати виконання параметризованого запиту, коли він виконується багато разів з різними параметрами.

Якщо Command — це клас, що реалізує інтерфейс IDbCommand, виконання команди SQL без транзакції матиме такий вигляд:

// відкриття з'єднання 
IDbConnection connexion=...
connexion.Open();
// підготовка замовлення
IDbCommand commande=new Command();
commande.Connection=connexion;
// виконання команди select
commande.CommandText="select ...";
IDbDataReader reader=commande.ExecuteReader();
...
// виконання команди update, insert, delete
commande.CommandText="insert ...";
int nbLignesInsérées=commande.ExecuteNonQuery();
...
// закриття з'єднання
connexion.Close();

Інтерфейс IDataReader

використовується для інкапсуляції результатів команди SQL Select. Об’єкт IDataReader представляє таблицю зі рядками та стовпцями, які обробляються послідовно: спочатку перший рядок, потім другий, ... Методи M та властивості P цього інтерфейсу, які ми будемо використовувати, будуть такими:

Назва	Тип	Роль
FieldCount	P	кількість стовпців у таблиці IDataReader
GetName	M	GetName(i) повертає назву стовпця № i таблиці IDataReader.
Item	P	Item[i] представляє стовпець № i поточного рядка таблиці IDataReader.
Read	M	переходить до наступного рядка таблиці IDataReader. Повертає логічне значення True, якщо зчитування відбулося успішно, і False — у протилежному випадку.
Close	M	закриває таблицю IDataReader.
GetBoolean	M	GetBoolean(i): повертає булеве значення стовпця № i поточного рядка таблиці IDataReader. Інші аналогічні методи такі: GetDateTime, GetDecimal, GetDouble, GetFloat, GetInt16, GetInt32, GetInt64, GetString.
Getvalue	M	Getvalue(i): повертає значення стовпця № i поточного рядка таблиці IDataReader у вигляді типу object.
IsDBNull	M	IsDBNull(i) повертає True, якщо стовпець № i поточного рядка таблиці IDataReader не має значення, що позначається значенням SQL NULL.

Використання об’єкта IDataReader часто виглядає наступним чином:

// відкриття з'єднання 
IDbConnection connexion=...
connexion.Open();
// підготовка команди
IDbCommand commande=new Command();
commande.Connection=connexion;
// виконання команди SELECT
commande.CommandText="select ...";
IDataReader reader=commande.ExecuteReader();
// обробка результатів
while(reader.Read()){
     // обробка поточного рядка
        ...
}
// закриття зчитувача
reader.Close();
// закриття з'єднання
connexion.Close();

9.3.4. Обробка помилок

Повернемося до архітектури додатка з базою даних:

На рівні [dao] під час роботи з базою даних можуть виникати численні помилки. Вони будуть передані у вигляді винятків, що генеруються коннектором ADO.NET. Код рівня [dao] повинен обробляти ці винятки. Будь-яка операція з базою даних повинна виконуватися в блоці try / catch / finally, щоб перехопити та обробити можливий виняток і звільнити необхідні ресурси. Таким чином, наведений вище код для обробки результату команди Select виглядає наступним чином:

// ініціалізація з'єднання
IDbConnection connexion=...
// обробка з'єднання
try{
     // відкриття
    connexion.Open();
     // підготовка команди
    IDbCommand commande=new Command();
    commande.Connection=connexion;
     // виконання команди select
    commande.CommandText="select ...";
    IDbDataReader reader=commande.ExecuteReader();
     // обробка результатів
    try{
        while(reader.Read()){
         // обробка поточного рядка
        ...
    }finally{
         // закриття зчитувача
        reader.Close();
    }
}catch(Exception ex){
     // обробка виняткових ситуацій
    ...
}finally{
     // закриття з'єднання
    connexion.Close();
}
...

Що б не сталося, об’єкти IDataReader та IDbConnection мають бути закриті. Саме тому це закриття здійснюється у блоках finally.

Закриття з’єднання та об’єкта IDataReader можна автоматизувати за допомогою оператора using:

// обробка з'єднання
try{
    using(IDbConnection connexion=...){
         // відкриття
        connexion.Open();
         // підготовка замовлення
        IDbCommand commande=new Command();
        commande.Connection=connexion;
         // виконання команди select
        commande.CommandText="select ...";
        using(IDbDataReader reader=commande.ExecuteReader()){
             // обробка результатів
            while(reader.Read()){
             // обробка поточного рядка
            ...
        }// з використанням IData
    }//з використанням IDbConnection
}catch(Exception ex){
     // обробка винятків
    ...
}
..

• У рядку 3 оператор using гарантує, що з'єднання, відкрите в блоці using(...){...}, буде закрите поза межами цього блоку, незалежно від того, як відбувається вихід із блоку: у звичайному порядку чи внаслідок виникнення винятку. Ми економимо один finally, але суть не в цій незначній економії. Використання using позбавляє розробника необхідності самостійно закривати з’єднання. Однак забуття закрити з’єднання може залишитися непоміченим і «зависати» додаток у спосіб, що здаватиметься випадковим, щоразу, коли SGBD досягатиме максимальної кількості відкритих з’єднань, яку він може підтримувати.
• Рядок 11: аналогічним чином закриваємо об’єкт IDataReader.

9.3.5. Налаштування прикладу проєкту

Остаточний проект матиме такий вигляд:

• [1]: проект матиме файл конфігурації [App.config]
• [2]: він використовує класи з двох DLL, які за замовчуванням не вказані у списку посилань, тому їх потрібно додати до посилань проекту:
  • [System.Configuration] для роботи з файлом конфігурації [App.config]
  • [System.Data.SqlServerCe] — для роботи з базою даних Sql Server Compact
• [3, 4]: нагадує, як додавати посилання до проекту.
• [5, 6]: нагадує, як додати файл [App.config] до проекту.

Файл конфігурації [App.config] матиме такий вигляд:

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

• рядки 3–5: тег <connectionStrings> у множині визначає рядки підключення до баз даних. Рядок підключення має вигляд «параметр1=значення1;параметр2=значення2;...». Вона визначає всі параметри, необхідні для встановлення з’єднання з конкретною базою даних. Ці рядки з’єднання змінюються для кожного SGBD. На сайті [http://www.connectionstrings.com/] наведено їхній формат для основних SGBD.
• рядок 4: визначає конкретний рядок підключення, в даному випадку для бази даних SQL Server Compact dbarticles.sdf, яку ми створили раніше:
  • name = ім’я рядка підключення. Саме за цим ім’ям програма на C# отримує рядок підключення
  • connectionString: рядок підключення до бази даних SQL Server Compact
  • DataSource: вказує шлях до бази даних. Синтаксис |DataDirectory| вказує на папку виконання проєкту.
  • Password: пароль до бази даних. Цей параметр відсутній, якщо пароль не встановлено.

Код на C# для отримання попереднього рядка підключення виглядає так:

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

• ConfigurationManager — це клас DLL [System.Configuration], який дозволяє використовувати файл [App.config].
• ConnectionsStrings["nom"].ConnectionString: позначає атрибут connectionString тегу < add name="nom" connectionString="..."> у розділі <connectionStrings> з [App.config]

Проєкт налаштовано. Тепер розглянемо клас [Program.cs], приклад виконання якого ми бачили раніше.

9.3.6. Приклад програми

Програма [program.cs] має такий вигляд:

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) {

            // консоль додатка — виконує запити SQL, введені з клавіатури
            // у базі даних, рядок підключення до якої отримується з конфігураційного файлу

            // обробка конфігураційного файлу [App.config]
            string connectionString = null;
            try {
                connectionString = ConfigurationManager.ConnectionStrings["dbSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // виведення рядка підключення
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // створюється словник прийнятних 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);
            }

            // читання та виконання команд SQL, введених з клавіатури
            string requête = null;    // текст запиту SQL
            string[] champs;            // поля запиту    
            Regex modèle = new Regex(@"\s+");    // послідовність пробілів

            // цикл введення-виконання команд SQL, введених з клавіатури
            while (true) {
                // запит
                Console.Write("\nRequête SQL (rien pour arrêter) : ");
                requête = Console.ReadLine().Trim().ToLower();
                // закінчено?
                if (requête == "")
                    break;
                // розбиття запиту на поля
                champs = modèle.Split(requête);
                // запит дійсний?
                if (champs.Length == 0 || ! dicoCommandes.ContainsKey(champs[0])) {
                    // повідомлення про помилку
                    Console.WriteLine("Requête invalide. Utilisez select, insert, update, delete ou rien pour arrêter");
                    // наступний запит
                    continue;
                }
                // виконання запиту
                if (champs[0] == "select") {
                    ExecuteSelect(connectionString, requête);
                } else
                    ExecuteUpdate(connectionString, requête);
            }
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
    ...
        }

        // виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
....
        }
    }
}

• рядки 1–6: простори імен, що використовуються в додатку. Для управління базою даних SQL Server Compact потрібен простір імен [System.Data.SqlServerCe] із рядка 3. Тут маємо залежність від власницького простору імен SGBD. З цього можна зробити висновок, що програму доведеться змінити, якщо змінити SGBD.
• рядок 18: рядок підключення до бази даних зчитується з файлу [App.config] і виводиться у рядку 25. Він буде використовуватися для встановлення з’єднання з базою даних.
• рядки 28–32: словник, що зберігає назви чотирьох дозволених команд SQL: select, insert, update, delete.
• рядки 40–62: цикл введення команд SQL, введених з клавіатури, та їх виконання в базі даних
• рядок 48: рядок, введений з клавіатури, розбивається на поля, щоб визначити перший елемент, який повинен бути одним із таких: select, insert, update, delete
• рядки 50–55: якщо запит недійсний, виводиться повідомлення про помилку, і програма переходить до наступного запиту.
• рядки 57–61: виконується введена команда SQL. Це виконання відбувається по-різному залежно від того, чи маємо справу з командою select, чи з командою insert, update, delete. У першому випадку команда отримує дані з бази, не змінюючи її, у другому — оновлює базу, не отримуючи даних. В обох випадках виконання делегується методу, якому потрібні два параметри:
  • рядок підключення, який дозволить йому підключитися до бази даних
  • запит SQL, який потрібно виконати через це з’єднання

9.3.7. Виконання запиту SELECT

Виконання команд SQL вимагає таких кроків:

1. Підключення до бази даних
2. Відправлення команд SQL до бази даних
3. Обробка результатів команди SQL
4. Закриття з'єднання

Етапи 2 і 3 виконуються повторно, а закриття з’єднання відбувається лише після завершення роботи з базою даних. Відкриті з’єднання є обмеженими ресурсами SGBD. Їх потрібно економити. Тому завжди слід прагнути обмежити час існування відкритого з’єднання. У розглянутому прикладі з’єднання закривається після кожного запиту SQL. Для наступного запиту SQL відкривається нове з’єднання. Відкриття/закриття з’єднання є ресурсоємним процесом. Щоб зменшити ці витрати, деякі SGBD пропонують концепцію пулів відкритих з’єднань: під час ініціалізації додатка відкривається N з’єднань, які закріплюються за пулом. Вони залишатимуться відкритими до завершення роботи додатка. Коли додаток відкриває з’єднання, він отримує одне з N вже відкритих з’єднань із пулу. Коли він закриває з’єднання, воно просто повертається до пулу. Перевага цієї системи полягає в тому, що вона є прозорою для розробника: програму не потрібно модифікувати для використання пулу з’єднань. Налаштування пулу з’єднань залежить від SGBD.

Спочатку розглянемо виконання команд SQL та Select. Метод ExecuteSelect у нашій прикладній програмі виглядає так:

// виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                    // відкриття з'єднання
                    connexion.Open();
                    // виконує sqlCommand із запитом SELECT
                    SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                    SqlCeDataReader reader= sqlCommand.ExecuteReader();
                    // виведення результатів
                    AfficheReader(reader);
                }
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // відображення читача
        static void AfficheReader(IDataReader reader) {
...
        }

• рядок 2: метод отримує два параметри:
  • рядок підключення [connectionString], який дозволить йому підключитися до бази даних
  • команду SQL Select [requête], яку потрібно виконати через це з’єднання
• рядок 4: будь-яка операція з базою даних може спричинити виняток, який, можливо, потрібно обробити. Це особливо важливо в даному випадку, оскільки команди SQL, введені користувачем, можуть містити синтаксичні помилки. Ми повинні мати можливість повідомити йому про це. Тому весь код розміщений у блоці try / catch.
• рядок 5: тут є кілька моментів:
  • з’єднання з базою даних ініціалізується за допомогою рядка з’єднання [connectionString]. Воно ще не відкрите. Воно буде відкрите у рядку 7.
  • клаузула using (Ressource) {...} — це синтаксичний прийом, що гарантує звільнення ресурсу Ressource (у даному випадку — з’єднання) після виходу з блоку, контрольованого using.
  • З'єднання має власний тип: SqlCeConnection, характерний для сервера SGBD SQL Server Compact.
• рядок 7: з’єднання відкрито. Саме в цей момент використовуються параметри ланцюга з’єднання.
• рядок 9: команда SQL надсилається через власний об’єкт SqlCeCommand. Рядок 9 ініціалізує цей об’єкт двома даними: з’єднанням, яке слід використовувати, та командою SQL, яку слід надіслати через нього. Об’єкт SqlCeCommand використовується як для виконання команди Select, так і для виконання команд Update, Insert, Delete. Його властивості та методи були описані в розділі 9.3.3.
• рядок 10: наказ SQL Select виконується за допомогою методу ExecuteReader об’єктаоб’єкта SqlCeCommand, який повертає об’єкт IDataReader, методи та властивості якого описано в розділі 9.3.3.
• рядок 12: відображення результатів доручено наступному методу AfficheReader:

        // відображення читача
        static void AfficheReader(IDataReader reader) {
            using (reader) {
                // обробка результатів
                // -- стовпці
                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, '-'));
                // -- дані
                while (reader.Read()) {
                    // обробка поточного рядка
                    ligne = new StringBuilder();
                    for (i = 0; i < reader.FieldCount; i++) {
                        ligne.Append(reader[i].ToString()).Append(" ");
                    }
                    Console.WriteLine(ligne);
                }
            }
}

• рядок 2: метод отримує об’єкт IDataReader. Зазначимо, що тут ми використали інтерфейс, а не конкретний клас.
• рядок 3: оператор using використовується для автоматичного закриття об’єкта IDataReader.
• рядки 8–10: виводяться назви стовпців таблиці результатів запиту Select. Це стовпці coli із запиту select col1, col2, ... from table ...
• рядки 14–21: виконується обхід таблиці результатів і виводяться значення кожного рядка таблиці.
• рядок 18: тип стовпця № i у результатах невідомий, оскільки невідома таблиця, до якої звертається запит. Тому не можна використовувати синтаксис reader.GetXXX(i), де XXX — це тип стовпця № i, оскільки цей тип невідомий. Тоді використовується синтаксис reader.Item[i].ToString(), щоб отримати представлення стовпця № i у вигляді символьного рядка. Синтаксис reader.Item[i].ToString() можна скоротити до reader[i].ToString().

9.3.8. Виконання команди оновлення: INSERT, UPDATE, DELETE

Код методу ExecuteUpdate такий:

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                    // відкриття з'єднання
                    connexion.Open();
                    // виконує sqlCommand із запитом на оновлення
                    SqlCeCommand sqlCommand = new SqlCeCommand(requête, connexion);
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // виведення результату
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
}

Ми вже зазначали, що виконання запиту Select нічим не відрізняється від виконання оновлення Update, Insert, Delete лише методом об’єкта SqlCeCommand, що використовується: ExecuteReader для Select, ExecuteNonQuery для Update, Insert, Delete. У наведеному вище коді ми коментуємо лише останній метод:

• рядок 10: запит Update, Insert, Delete виконується методом ExecuteNonQuery об’єкта SqlCeCommand. У разі успіху цей метод повертає кількість оновлених (update), вставлених (insert) або видалених (delete) рядків.
• рядок 12: ця кількість рядків виводиться на екран

Читачеві пропонується ознайомитися з прикладом виконання цього коду в розділі 9.3.2.

9.4. Інші коннектори ADO.NET

Код, який ми розглядали, є пропрієтарним: він залежить від простору імен [System.Data.SqlServerCe], призначеного для SGBD SQL Server Compact. Тепер ми створимо ту саму програму з різними з’єднувачами .NET і подивимося, що зміниться.

9.4.1. Коннектор SQL Server 2005

Архітектура буде такою:

Процес встановлення SQL Server 2005 описано в додатках до розділу 1.1.

Ми створюємо другий проєкт у тому самому рішенні, що й раніше, а потім створюємо базу даних SQL Server 2005. SGBD SQL Server 2005 необхідно запустити перед виконанням наступних операцій:

• [1]: створити новий проєкт у поточній збірці та зробити його поточним проєктом.
• [2]: створити нове з’єднання
• [3]: вибрати тип з’єднання

• [4]: вибрати SGBD SQL-сервер
• [5]: результат попереднього вибору
• [6]: скористайтеся кнопкою [Browse], щоб вказати, де створити базу даних SQL Server 2005. База даних інкапсульована у файл .mdf.
• [7]: виберіть кореневий каталог нового проєкту та назвіть базу даних [dbarticles.mdf].
• [8]: використовувати аутентифікацію Windows.
• [9]: підтвердіть сторінку з інформацією

• [11]: база даних SQL Server
• [12]: створити таблицю. Вона буде ідентичною базі даних SQL Server Compact, створеній раніше.
• [13]: поле [id]
• [14]: поле [id] має тип Identity.
• [15,16]: поле [id] є первинним ключем

• [17]: інші поля таблиці
• [18]: при збереженні таблиці (Ctrl+S) надати їй ім’я [articles].

Залишилося внести дані в таблицю:

Додаємо базу даних до проєкту:

Референції проекту такі:

Файл конфігурації [App.config] має такий вигляд:

<?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>

• рядок 4: рядок підключення до бази даних [dbarticles.mdf] з автентифікацією Windows
• рядок 5: рядок підключення до бази даних [dbarticles.mdf] з автентифікацією на сервері SQL. [sa,msde] — це пара (логін, пароль) адміністратора сервера SQL Server, як визначено в пункті 1.1.

Програма [Program.cs] працює наступним чином:

using System.Data.SqlClient;
...

namespace Chap7 {
    class SqlCommands {
        static void Main(string[] args) {
...
            // обробка файлу конфігурації [App.config]
            string connectionString = null;
            try {
                connectionString = ConfigurationManager.ConnectionStrings["connectString2"].ConnectionString;
            } catch (Exception e) {
...
            }
...
            // читання та виконання команд SQL, введених з клавіатури
...
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (SqlConnection connexion = new SqlConnection(connectionString)) {
                    // відкриття з'єднання
                    connexion.Open();
                    // виконання sqlCommand із запитом на оновлення
                    SqlCommand sqlCommand = new SqlCommand(requête, connexion);
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // виведення результату
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
....
            }
        }

        // виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (SqlConnection connexion = new SqlConnection(connectionString)) {
                    // відкриття з'єднання
                    connexion.Open();
                    // виконує sqlCommand із запитом SELECT
                    SqlCommand sqlCommand = new SqlCommand(requête, connexion);
                    SqlDataReader reader = sqlCommand.ExecuteReader();
                    // обробка результатів
...
                }
            } catch (Exception ex) {
...
            }
        }
    }
}

• рядок 1: простір імен [System.Data.SqlClient] містить класи, що дозволяють керувати базою даних SQL Server 2005
• рядок 24: з’єднання має тип SQLConnection
• рядок 28: об’єкт, що інкапсулює оператори SQL, має тип SQLCommand
• рядок 47: об’єкт, що інкапсулює результат команди SQL Select, має тип SQLDataReader

Код ідентичний тому, що використовується з SGBD SQL Server Compact, за винятком назв класів. Для його виконання можна використовувати (рядок 11) будь-який із двох рядків підключення, визначених у [App.config].

9.4.2. Коннектор MySQL5

Архітектура буде такою:

Встановлення MySQL5 описано в додатках у розділі 1.2, а встановлення коннектора Ado.Net — у розділі 1.2.5.

Ми створюємо третій проєкт у тому самому рішенні, що й раніше, та додаємо до нього необхідні посилання:

• [1]: новий проєкт
• [2]: до якого додаємо посилання
• [3]: DLL, [MySQL.Data], Ado.Net, MySql5, а також [System.Configuration], [4].

Тепер ми створюємо базу даних [dbarticles] та її таблицю [articles]. Необхідно запустити SGBD та MySQL5. Крім того, запускаємо клієнт [Query Browser] (див. розділ 1.2.3).

• [1]: у [Query Browser] клацніть правою кнопкою миші в області [Schemata] [2], щоб створити [3] — нову схему, тобто базу даних.
• [4]: база даних матиме назву [dbarticles]. У [5] її можна побачити. Наразі вона не містить таблиць. Ми виконаємо наступний скрипт SQL:

/* вибір поточної бази даних */
USE dbarticles;
/* створення таблиці товарів */
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
);
/* вставлення даних у таблицю */
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);
/* додавання обмежень */
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);

• рядок 1: база [dbarticles] стає поточною базою. Наступні команди SQL будуть виконуватися саме на ній.
• рядки 4–10: визначення таблиці [ARTICLES]. Зверніть увагу, що SQL є власником MySQL. Типи стовпців та автоматичне генерування первинного ключа (атрибут AUTO_INCREMENT) відрізняються від того, що зустрічалося у SGBD та SQL для Server Compact та Express.
• рядки 12–14: додано три рядки
• рядки 16–21: додавання обмежень цілісності для стовпців.

Цей скрипт виконується в [MySQL Query Browser]:

• у [MySQL Query Browser] [6] завантажується скрипт [7]. Це видно у [8]. У [9] він виконується.

• у [10] створено таблицю [articles]. Двічі клацніть на ній. З’явиться вікно [11] із запитом [12], готовим до виконання за допомогою [13]. У [14] — результат виконання. Ми бачимо три очікувані рядки. Зазначимо, що значення поля [ID] були згенеровані автоматично (атрибут AUTO_INCREMENT цього поля).

Тепер, коли база даних готова, ми можемо повернутися до розробки додатка у Visual Studio.

У [1] містяться програма [Program.cs] та файл конфігурації [App.config]. Останній має такий вигляд:

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

У рядку 4 елементи рядка підключення такі:

• Server: ім’я комп’ютера, на якому розташовані SGBD, MySQL, тут — localhost, c.a.d; це комп’ютер, на якому буде виконуватися програма.
• Database: ім’я бази даних, що обслуговується, у даному випадку dbarticles
• Uid: логін користувача, тут root
• Pwd: його пароль, у даному випадку root. Ці два параметри позначають адміністратора, створеного в розділі 1.2.

Програма [Program.cs] ідентична програмам попередніх версій, за винятком таких деталей:

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

Програма використовує рядок підключення з назвою dbArticlesMySql5 у файлі [App.config]. Виконання дає такі результати:

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. Коннектор ODBC

Використовуватиметься така архітектура:

Перевага з'єднувачів ODBC полягає в тому, що вони надають стандартний інтерфейс для додатків, які їх використовують. Таким чином, новий додаток зможе, використовуючи єдиний код, взаємодіяти з будь-яким SGBD, що має роз'єм ODBC, c.a.d та більшість SGBD. Продуктивність з’єднувачів ODBC нижча, ніж у «пропрієтарних» з’єднувачів, які вміють використовувати всі характеристики конкретного SGBD. Натомість це забезпечує велику гнучкість додатка: можна змінювати SGBD без зміни коду.

Ми розглянемо приклад, у якому додаток використовує базу даних MySQL5 або базу даних SQL Server Express залежно від ланцюга з’єднання, який йому задано. Далі ми припускаємо, що:

• сервери Express SGBD, SQL та MySQL5 запущено
• що драйвер ODBC для MySQL5 присутній на комп’ютері (див. розділ 1.2.6). Драйвер для SQL Server 2005 присутній за замовчуванням.
• використовуються бази даних, зазначені в розділі 9.4.2 для бази MySQL5, та в розділі 9.4.1 для бази SQL Server Express.

Новий проєкт Visual Studio має такий вигляд:

Вищезазначена база даних SQL Server [dbarticles.mdf], створена у розділі 9.4.1, була скопійована до папки проєкту.

Файл конфігурації [App.config] має такий вигляд:

<?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>

• рядок 4: рядок підключення джерела ODBC MySQL5. Це рядок, який ми вже розглядали, у якому міститься новий параметр Driver, що визначає драйвер ODBC, який слід використовувати.
• рядок 5: рядок підключення джерела ODBC SQL Server Express. Це рядок, який вже використовувався в попередньому прикладі, до якого було додано параметр Driver.

Програма [Program.cs] ідентична програмі попередніх версій, за винятком таких деталей:

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

Програма використовує один із двох рядків підключення, визначених у файлі [App.config]. Виконання дає такі результати:

З рядком підключення [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

З рядком підключення [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. Коннектор OLE DB

Архітектура буде такою:

Як і з'єднувачі ODBC, з'єднувачі OLE та DB (Object Linking and Embedding DataBase) мають стандартний інтерфейс для програм, що їх використовують. Драйвери ODBC забезпечують доступ до баз даних. Джерела даних для драйверів OLE та DB є більш різноманітними: бази даних, поштові системи, каталоги тощо. Будь-яке джерело даних може стати об’єктом драйвера Ole DB, якщо розробник вирішить це зробити. Таким чином, ми отримуємо стандартний доступ до широкого спектру даних.

Розглянемо приклад, у якому програма використовує базу даних ACCESS або базу даних SQL Server Express залежно від наданого ланцюга з’єднання. Далі ми припускаємо, що сервер Express SGBD SQL запущено, а використовувана база даних — та сама, що й у попередньому прикладі.

Новий проєкт Visual Studio має такий вигляд:

• у [1]: простір імен, необхідний для з’єднувачів OLE та DB, — це [System.Data.OleDb], який міститься у згадці [System.Data] вище. База даних SQL Server [dbarticles.mdf] була скопійована з попереднього проєкту. База даних [dbarticles.mdb] була створена за допомогою Access.
• У [2]: як і база даних SQL Server, база даних ACCESS має властивість [Copy to Output Directory=Copy Always], завдяки чому вона автоматично копіюється у папку виконання проекту.

База даних ACCESS [dbarticles.mdb] має такий вигляд:

У файлі [1] міститься структура таблиці [articles], а у файлі [2] — її вміст.

Конфігураційний файл [App.config] має такий вигляд:

<?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>

• рядок 4: рядок підключення до джерела OLE DB ACCESS. У ньому міститься параметр Provider, який визначає драйвер OLE DB, що має використовуватися, а також шлях до бази даних
• рядок 5: рядок підключення до джерела OLE DB Server Express.

Програма [Program.cs] ідентична програмі попередніх версій, за винятком таких деталей:

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

Програма використовує один із двох рядків підключення, визначених у файлі [App.config]. Виконання дає такі результати з рядком підключення [dbArticlesOleDbAccess]:

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. Універсальний коннектор

Використовуватиметься така архітектура:

Як і з'єднувачі ODBC, OLE та DB, загальний з'єднувач надає стандартний інтерфейс для додатків, що його використовують, але покращує продуктивність без втрати гнучкості. Дійсно, універсальний коннектор базується на власницьких коннекторах SGBD. Додаток використовує класи універсального коннектора. Ці класи слугують посередниками між додатком та власницьким коннектором.

Як показано вище, коли додаток, наприклад, запитує підключення до універсального з’єднувача, той повертає йому екземпляр IDbConnection, інтерфейс підключень, описаний у розділі 9.3.3, реалізований класом MySQLConnection або SQLConnection залежно від характеру запиту, що надійшов. Кажуть, що універсальний коннектор має класи типу «фабрика»: клас-фабрику використовують для того, щоб попросити його створити об’єкти та надати посилання (покажчики) на них. Звідси й походить його назва (factory = фабрика, фабрика з виробництва об’єктів).

Універсального коннектора для всіх SGBD не існує (квітень 2008 року). Щоб дізнатися, які з них встановлені на комп’ютері, можна скористатися такою програмою:

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, '-'));
            }
        }
    }
}

• рядок 8: статичний метод [DbProviderFactories.GetFactoryClasses()] повертає список встановлених універсальних коннекторів у вигляді таблиці бази даних, розміщеної в пам’яті (DataTable).
• рядки 9–11: відображають назви стовпців таблиці dt:
  • dt.Columns — це список стовпців таблиці. Стовпець C має тип DataColumn
  • [DataColumn]. ColumnName — це назва стовпця
• рядки 13–18: відображають рядки таблиці dt:
  • dt.Rows — це список рядків таблиці. Рядок L має тип DataRow
  • [DataRow].ItemArray — це масив об’єктів, де кожен об’єкт представляє стовпець рядка

Результат виконання на моїй машині такий:

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|

• рядок 1: таблиця має чотири стовпці. Три перші з них є для нас найкориснішими в даному випадку.

Наступне зображення показує, що ми маємо такі загальні з’єднувачі:

Назва	Ідентифікатор
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

До загального з'єднувача можна отримати доступ у програмі на C# за допомогою його ідентифікатора.

Розглянемо приклад, у якому програма використовує різні бази даних, які ми створили до цього моменту. Програма отримає два параметри:

• перший параметр визначає тип SGBD, щоб забезпечити використання відповідної бібліотеки класів
• другий параметр визначає базу даних, що обробляється, за допомогою рядка підключення.

Новий проєкт Visual Studio виглядає так:

• у [1]: простір імен, необхідний для загальних коннекторів, — це [System.Data.common], що міститься у просторі імен [System.Data].

Файл конфігурації [App.config] має такий вигляд:

<?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>

• рядки 3–11: рядки підключення до різних баз даних, що використовуються.
• рядки 13–17: імена загальних з’єднувачів, які слід використовувати

Програма [Program.cs] має такий вигляд:

...
using System.Data.Common;

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

            // консольний додаток — виконує запити SQL, введені з клавіатури
            // до бази даних, ланцюг підключення до якої, а також ім’я відповідного з’єднувача SGBD, отримуються з конфігураційного файлу

            // перевірка параметрів
            if (args.Length != 2) {
                Console.WriteLine("Syntaxe : pg factory connectionString");
                return;
            }

            // обробка конфігураційного файлу
            string factory = null;
            string connectionString = null;
            DbProviderFactory connecteur = null;
            try {
                // заводські налаштування
                factory = ConfigurationManager.AppSettings[args[0]];
                // ланцюг з'єднання
                connectionString = ConfigurationManager.ConnectionStrings[args[1]].ConnectionString;
                // отримуємо універсальний роз'єм для SGBD
                connecteur = DbProviderFactories.GetFactory(factory);
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // перегляди
            Console.WriteLine("Provider factory : [{0}]\n", factory);
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

...
                // виконання запиту
                if (champs[0] == "select") {
                    ExecuteSelect(connecteur,connectionString, requête);
                } else
                    ExecuteUpdate(connecteur, connectionString, requête);
            }
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(DbProviderFactory connecteur, string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // налаштування з'єднання
                    connexion.ConnectionString = connectionString;
                    // відкриття з'єднання
                    connexion.Open();
                    // налаштування команди 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // виконання запиту
                    int nbLignes = sqlCommand.ExecuteNonQuery();
                    // виведення результату
                    Console.WriteLine("Il y a eu {0} ligne(s) modifiée(s)", nbLignes);
                }
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // виконання запиту Select
        static void ExecuteSelect(DbProviderFactory connecteur, string connectionString, string requête) {
            // обробка можливих винятків
            try {
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // налаштування з'єднання
                    connexion.ConnectionString = connectionString;
                    // відкриття з'єднання
                    connexion.Open();
                    // налаштування команди 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // виконання запиту
                    DbDataReader reader = sqlCommand.ExecuteReader();
                    // виведення результатів
...
                }
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }
    }
}

• рядки 12–14: програма отримує два параметри: ім’я загального коннектора та рядок підключення до бази даних у вигляді ключів із файлу [App.config].
• рядки 23, 25: з файлу [App.config] отримуються ім’я загального коннектора та рядок підключення
• рядок 27: створюється екземпляр загального з’єднувача. Відтепер він пов’язаний із конкретним SGBD.
• рядки 39–43: виконання команди SQL, введеної з клавіатури, делегується двом методам, яким передаються:
  • запит, що має бути виконаний
  • рядок підключення, що ідентифікує базу даних, у якій буде виконано запит
  • універсальний коннектор, який ідентифікує класи, що використовуються для взаємодії з SGBD, який керує базою даних.
• рядки 50–54: з’єднання встановлюється за допомогою методу CreateConnection (рядок 50) загального з’єднувача, а потім налаштовується за допомогою рядка підключення до бази даних, що обслуговується (рядок 52). Після цього з’єднання відкривається (рядок 54).
• рядки 56–58: об’єкт Command, необхідний для виконання команди SQL, створюється за допомогою методу CreateCommand загального коннектора. Потім він налаштовується з текстом команди SQL, яку потрібно виконати (рядок 57), та підключенням, через яке її потрібно виконати (рядок 58).
• рядок 60: виконується команда оновлення SQL
• рядки 74–87: тут міститься аналогічний код. Новинка міститься у рядку 84. Об’єкт Reader, отриманий у результаті виконання команди Select, має тип DbDataReader, який використовується так само, як об’єкти OleDbDataReader, OdbcDataReader, ... з якими ми вже стикалися.

Ось кілька прикладів виконання.

З базою MySQL5:

Відкриваємо сторінку властивостей проєкту [1] і вибираємо вкладку [Debug] [2]. У [3] — ключ з'єднувача з рядка 14 файлу [App.config]. У [4] — ключ ланцюга з'єднання з рядка 6 файлу [App.config]. Результати виконання такі:

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

З базою даних SQL Server Compact:

У [1] — ключ з'єднувача в рядку 13 запису [App.config]. У [2] — ключ ланцюга з'єднання з рядка 4 файлу [App.config]. Результати виконання такі:

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

Читачеві пропонується перевірити інші бази даних.

9.4.6. Який коннектор вибрати?

Повернемося до архітектури додатка з базами даних:

Ми розглянули різні типи коннекторів ADO.NET:

• пропрієтарні коннектори є найефективнішими, але роблять рівень [dao] залежним від пропрієтарних класів. Зміна SGBD означає необхідність зміни рівня [dao].
• Коннектори ODBC, OLE або DB дають змогу працювати з різними базами даних без зміни шару [dao]. Вони менш ефективні, ніж власницькі коннектори.
• Універсальний коннектор базується на власницьких коннекторах, водночас надаючи стандартний інтерфейс до шару [dao].

Отже, здається, що універсальний коннектор є ідеальним варіантом. Однак на практиці універсальний коннектор не в змозі приховати всі особливості SGBD за стандартним інтерфейсом. У наступному розділі ми розглянемо поняття параметризованого запиту. У SQL Server параметризований запит має такий вигляд:

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

У MySQL5 той самий запит писатиметься так:

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

Отже, існує різниця у синтаксисі. Властивість інтерфейсу IDbCommand, описана в параграфі 9.3.3, пов’язана з параметрами, має такий вигляд:

Parameters

список параметрів налаштованого запиту SQL. Запит update articles set prix=prix*1.1 where id=@id має параметр @id.

Властивість Parameters має тип IDataParameterCollection — інтерфейс. Вона представляє сукупність параметрів наказу SQL CommandText. Властивість Parameters має метод Add для додавання параметрів типу IDataParameter, що знову є інтерфейсом. Цей інтерфейс має такі властивості:

• ParameterName: ім’я параметра
• DbType: тип SQL параметра
• Value: значення, присвоєне параметру
• ...

Тип IDataParameter добре підходить для параметрів порядку SQL

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

, оскільки в ньому є іменовані параметри. Можна використовувати властивість ParameterName.

Тип IDataParameter не підходить для замовлення SQL

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

оскільки параметри не мають імен. У такому разі враховується порядок додавання параметрів до колекції [IDbCommand.Parameters]. У цьому прикладі потрібно ввести 4 параметри в порядку nom, prix, stockactuel, stockminimum. У запиті з іменованими параметрами порядок додавання параметрів не має значення. Зрештою, розробник не може повністю абстрагуватися від SGBD, який він використовує під час ініціалізації параметрів параметризованого запиту. Це є одним із поточних обмежень універсального коннектора.

Існують frameworks, які дозволяють подолати ці обмеження та, крім того, надають нові функціональні можливості шару [dao]:

Фреймворк — це набір бібліотек класів, призначених для спрощення певного підходу до архітектури додатка. Існує кілька фреймворків, які дозволяють писати шари [dao], що є одночасно ефективними та нечутливими до змін у SGBD:

• Spring.Net [http://www.springframework.net/], вже представлений у цьому документі, пропонує еквівалент розглянутого універсального коннектора без його обмежень, а також різноманітні засоби, що спрощують доступ до даних. Існує версія для Java.
• iBatis.Net [http://ibatis.apache.org] є старішим і функціональнішим, ніж Spring.Net. Існує версія на Java.
• NHibernate [http://www.hibernate.org/] — це порт версії Java Hibernate, дуже відомої у світі Java. NHibernate дозволяє шару [dao] взаємодіяти з SGBD без відправлення команд SQL. Рівень [dao] працює з об’єктами Hibernate. Мова запитів HBL (Hibernate Query Language) дозволяє виконувати запити до об’єктів, що керуються Hibernate. Саме вони надсилають команди SQL. Hibernate вміє адаптуватися до власних SQL, що належать до SGBD.
• LINQ (Language INtegrated Query), інтегрована у версію 3.5 .NET та доступна в C# 2008. LINQ слідує за NHibernate, але на даний момент (травень 2008 року) підтримується лише сервер SGBD SQL. З часом ситуація має змінитися. LINQ має ширші можливості, ніж NHibernate: його мова запитів дозволяє стандартним чином здійснювати запити до трьох різних типів джерел даних:
  • колекції об’єктів (LINQ to Objects)
  • XML-файл (LINQ to Xml)
  • базу даних (LINQ to SQL)

Ці фреймворки не розглядатимуться в цьому документі. Однак настійно рекомендується використовувати їх у професійних додатках.

9.5. Запити з параметрами

У попередньому розділі ми згадували про запити з параметрами. Тут ми наведемо їх на прикладі SGBD SQL Server Compact. Проєкт виглядає так

• у [1], проект. Використовуються лише [App.config], [Article.cs] та [Parametres.cs]. Слід також звернути увагу на базу SQL Server та [dbarticles.sdf].
• У [2] проект налаштований на виконання [Parametres.cs]
• у [3] — посилання проекту

Файл конфігурації [App.config] визначає рядок підключення до бази даних:

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

Файл [Article.cs] визначає клас [Article]. Об’єкт Article використовуватиметься для інкапсуляції інформації одного рядка таблиці ARTICLES у базі даних [dbarticles.sdf]:

namespace Chap7 {
    class Article {
        // властивості
        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; }

        // конструктори
        public Article() { 
        }

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

    }
}

Додаток [Parametres.cs] реалізує запити з параметрами:

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

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

            // обробка файлу конфігурації
            string connectionString = null;
            try {
                // рядок підключення
                connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // відображення
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // створення таблиці товарів
            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);
            }

            // обробка можливих винятків
            try {

                // видалення існуючих товарів з бази даних
                ExecuteUpdate(connectionString, "delete from articles");

                // виведення товарів із таблиці
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");

                // вставлення таблиці товарів у базу даних
                InsertArticles(connectionString, articles);

                // виводить записи з таблиці
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // вставлення таблиці товарів
        static void InsertArticles(string connectionString, Article[] articles) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // відкриття з'єднання
                connexion.Open();
                // налаштування команди
                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);
                // компіляція замовлення
                sqlCommand.Prepare();
                // вставлення рядків
                for (int i = 0; i < articles.Length; i++) {
                    // ініціалізація параметрів
                    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;
                    // виконання запиту
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
...
        }

        // виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // відображення читача
        static void AfficheReader(IDataReader reader) {
...
    }
}

Новим порівняно з тим, що розглядалося раніше, є процедура [InsertArticles] у рядках 51–75:

• рядок 51: процедура отримує два параметри:
  • рядок підключення connectionString, який дозволить процедурі підключитися до бази даних
  • масив об’єктів Article, які потрібно додати до таблиці Articles у базі даних
• рядок 56: запит на вставку об’єкта [Article]. Він має чотири параметри:
  • @nom: назва товару
  • @prix: її ціна
  • @sa: її поточний запас
  • @sm: мінімальний запас товару

Синтаксис цього запиту з параметрами є власним для SQL Server Compact. У попередньому абзаці ми бачили, що для MySQL5 синтаксис мав би такий вигляд:

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

У SQL Server Compact перед кожним параметром має стояти символ @. Назви параметрів можна вибирати довільно.

• рядки 58–61: визначаються характеристики кожного з 4 параметрів, і їх по черзі додають до списку параметрів об’єкта SqlCeCommand, який інкапсулює замовлення SQL, що буде виконано.

Тут використовується метод [SqlCeCommand].Parameters.Add, який має шість сигнатур. Ми використовуємо дві наступні:

Add(string parameterName, SQLDbType type)

додає та налаштовує параметр із назвою parameterName. Ця назва має бути однією з тих, що містяться у налаштованому параметризованому запиті: (@name, ...). type позначає тип SQL стовпця, до якого відноситься параметр. Існує багато типів, серед яких такі:

тип SQL	тип C#	коментар
BigInt	Int64
DateTime	DateTime
Decimal	Decimal
Float	Подвійна точність
Int	Int32
Money	Decimal
NChar	String	рядок фіксованої довжини
NVarChar	String	рядок змінної довжини
Real	Single

Add(string parameterName, SQLDbType type, int size)

третій параметр size визначає розмір стовпця. Ця інформація корисна лише для певних типів, наприклад, типу SQL або типу NVarChar.

• рядок 63: компілюється запит із параметрами. Цю операцію також називають підготовкою запиту, звідки й походить назва методу. Ця операція не є обов’язковою. Вона призначена для підвищення продуктивності. Коли SGBD виконує команду SQL, він проводить певну оптимізацію перед її виконанням. Параметризований запит призначений для багаторазового виконання з різними параметрами. Сам текст запиту при цьому не змінюється. Отже, роботу з оптимізації можна виконати лише один раз. Деякі SGBD мають можливість «підготувати» або «скомпілювати» параметризовані запити. У цьому випадку для даного запиту визначається план виконання. Це і є та сама фаза оптимізації, про яку йшлося вище. Після компіляції запит виконується повторно, щоразу з новими ефективними параметрами, але за тим самим планом виконання.

Компіляція — не єдина перевага параметризованих запитів. Повернімося до розглянутого запиту:

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

Можливо, ми захочемо побудувати текст запиту програмно:

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

У наведеному вище прикладі, якщо (nom,prix,sa,sm) дорівнює ("article1",100,10,1), попередній запит перетворюється на:

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

Тепер, якщо (назва, ціна, са, см) дорівнює («стаття1», 100, 10, 1), попередній запит стає таким:

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

і стає синтаксично некоректним через апостроф у назві l'article1. Якщо nom походить від введених користувачем даних, це означає, що нам доводиться перевіряти, чи введене значення не містить апострофів, а якщо містить — нейтралізувати їх. Ця нейтралізація залежить від SGBD. Перевага підготовленого запиту полягає в тому, що він сам виконує цю роботу. Ця зручність сама по собі виправдовує використання підготовленого запиту.

• рядки 65–73: елементи таблиці вставляються по одному
• рядки 67–70: кожен із чотирьох параметрів запиту отримує своє значення через властивість Value.
• рядок 72: готовий запит на вставку виконується у звичний спосіб.

Ось приклад виконання:

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

• рядок 3: повідомлення після видалення всіх рядків з таблиці
• рядки 5–7: показують, що таблиця порожня
• рядки 10–18: показують таблицю після вставки 5 товарів

9.6. Transactions

9.6.1. Загальні відомості

Транзакція — це послідовність команд SQL, що виконується «атомарно»:

• або всі операції виконуються успішно
• або одна з них завершується невдало, і тоді всі попередні скасовуються

У підсумку операції транзакції або всі були успішно виконані, або жодна з них не була виконана. Коли користувач самостійно керує транзакцією, він підтверджує її за допомогою команди COMMIT або скасовує за допомогою команди ROLLBACK.

У наших попередніх прикладах ми не використовували транзакції. Проте вони все ж були, оскільки в SGBD наказ SQL завжди виконується в рамках транзакції. Якщо клієнт .NET самостійно не ініціює явну транзакцію, SGBD використовує неявну транзакцію. У цьому випадку існує два типові випадки:

1. кожна окрема команда SQL є предметом транзакції, яка ініціюється командою SGBD перед виконанням команди та закривається після цього. Кажуть, що це режим автокомміту. Отже, все відбувається так, ніби клієнт .NET виконує транзакції для кожного замовлення SQL.
2. SGBD не перебуває в режимі автокомміту та розпочинає неявну транзакцію з першого запиту SQL, який клієнт .NET надсилає поза транзакцією, і дозволяє клієнту її закрити. Усі команди SQL, відправлені клієнтом .NET, стають частиною цієї неявної транзакції. Ця транзакція може завершитися внаслідок різних подій: клієнт закриває з’єднання, розпочинає нову транзакцію тощо, але в такому разі ми опиняємося в ситуації, що залежить від SGBD. Цього режиму слід уникати.

Режим за замовчуванням зазвичай встановлюється під час конфігурації SGBD. Деякі SGBD за замовчуванням працюють у режимі autocommit, інші — ні. SQLServer Compact за замовчуванням працює в режимі autocommit.

Команди SQL різних користувачів виконуються одночасно в транзакціях, що працюють паралельно. Операції, що виконуються однією транзакцією, можуть впливати на операції, що виконуються іншою транзакцією. Вирізняють чотири рівні ізоляції між транзакціями різних користувачів:

• Uncommitted Read
• Зчитування з фіксацією
• Повторюване читання
• Серіалізоване

Uncommitted Read

Цей режим ізоляції також називають «Dirty Read». Ось приклад того, що може статися в цьому режимі:

1. користувач U1 розпочинає транзакцію з таблицею T
2. користувач U2 починає транзакцію з цією ж таблицею T
3. користувач U1 змінює рядки таблиці T, але ще не підтверджує ці зміни
4. користувач U2 «бачить» ці зміни та приймає рішення на основі того, що бачить
5. користувач скасовує свою транзакцію за допомогою ROLLBACK

Бачимо, що в пункті 4 користувач U2 прийняв рішення на основі даних, які згодом виявляться помилковими.

Committed Read

Цей режим ізоляції дозволяє уникнути описаної вище проблеми. У цьому режимі користувач U2 на кроці 4 не «побачить» змін, внесених користувачем U1 до таблиці T. Він побачить їх лише після того, як U1 завершить свою транзакцію.

У цьому режимі, який також називають «Unrepeatable Read», можуть, однак, траплятися такі ситуації:

1. користувач U1 розпочинає транзакцію над таблицею T
2. користувач U2 починає транзакцію над тією самою таблицею T
3. користувач U2 виконує операцію SELECT, щоб отримати середнє значення стовпця C для рядків таблиці T, що відповідають певній умові
4. користувач U1 змінює (UPDATE) певні значення стовпця C таблиці T та підтверджує їх (COMMIT)
5. користувач U2 повторно виконує ту саму операцію SELECT, що й у пункті 3. Він виявить, що середнє значення стовпця C змінилося внаслідок змін, внесених користувачем U1.

Тепер користувач U2 бачить лише зміни, «підтверджені» користувачем U1. Але, залишаючись у тій самій транзакції, дві однакові операції (3 і 5) дають різні результати. Цю ситуацію позначає термін «Unrepeatable Read». Це неприємна ситуація для того, хто прагне отримати стабільне зображення таблиці T.

Повторюване читання

У цьому режимі ізоляції користувач має гарантію, що отримає однакові результати при читанні даних з бази, доки залишається в межах однієї транзакції. Він працює з «знімком», у якому ніколи не відображаються зміни, внесені іншими транзакціями, навіть підтвердженими. Він побачить ці зміни лише тоді, коли сам завершить свою транзакцію командою COMMIT або ROLLBACK.

Однак цей режим ізоляції ще не є досконалим. Після операції 3, описаної вище, рядки, до яких звертається користувач U2, блокуються. Під час операції 4 користувач U1 не зможе змінити (UPDATE) значення стовпця C у цих рядках. Однак він може додавати нові рядки (INSERT). Якщо деякі з доданих рядків задовольняють умову, перевірену в кроці 3, операція 5 дасть середнє значення, відмінне від того, що було отримано в кроці 3, через додані рядки. Такі рядки іноді називають «примарними».

Щоб вирішити цю нову проблему, потрібно перейти в режим ізоляції «Serializable».

Serializable

У цьому режимі ізоляції транзакції повністю ізольовані одна від одної. Він гарантує, що результат двох транзакцій, виконаних одночасно, буде таким самим, якби вони виконувалися одна за одною. Щоб досягти цього результату, під час операції 4, коли користувач U1 хоче додати рядки, які змінять результат операції SELECT користувача U1, йому буде це заборонено. Повідомлення про помилку вкаже, що вставка неможлива. Вона стане можливою, коли користувач U2 підтвердить свою транзакцію.

Чотири рівні ізоляції транзакцій SQL доступні не у всіх SGBD. За замовчуванням рівнем ізоляції зазвичай є рівень Committed Read. Бажаний рівень ізоляції для транзакції може бути явно вказаний під час створення явної транзакції клієнтом .NET.

9.6.2. Управління транзакціями API

З’єднання реалізує інтерфейс IDbConnection, описаний у розділі 9.3.3. Цей інтерфейс має такий метод:

BeginTransaction

M

запускає транзакцію.

Цей метод має два варіанти виклику:

1. IDbTransaction BeginTransaction(): запускає транзакцію та повертає об’єкт IDbTransaction, що дозволяє керувати нею
2. IDbTransaction BeginTransaction(IsolationLevel level): додатково визначає бажаний рівень ізоляції для транзакції. level приймає значення з наступного переліку:

ReadUncommitted	транзакція може зчитувати дані, записані іншою транзакцією, які ця транзакція ще не підтвердила — слід уникати
ReadCommitted	транзакція не може зчитувати дані, записані іншою транзакцією, які вона ще не підтвердила. Однак дані, зчитані двічі поспіль у рамках однієї транзакції, можуть змінюватися (неповторні зчитування), оскільки інша транзакція могла їх змінити в цей час (зчитані рядки не блокуються — блокуються лише оновлені рядки). Крім того, інша транзакція могла додати рядки («примарні рядки»), які будуть включені до другого зчитування.
RepeatableRead	Рядки, які зчитуються транзакцією, блокуються так само, як і рядки, що оновлюються. Це запобігає їхній зміні іншою транзакцією. Однак це не запобігає додаванню нових рядків.
Serializable	таблиці, з якими працює транзакція, блокуються, що унеможливлює додавання нових рядків іншою транзакцією. Все відбувається так, ніби транзакція працює самостійно. Це знижує продуктивність, оскільки транзакції більше не працюють паралельно.
Snapshot	транзакція працює з копією даних, створеною в момент часу T. Використовується, коли транзакція працює лише в режимі читання. Дає той самий результат, що й serializable, уникаючи при цьому пов’язаних з нею витрат.

Після запуску транзакції вона контролюється об’єктом типу IDbTransaction — інтерфейсом, для якого ми будемо використовувати такі властивості P та методи M:

Назва	Тип	Роль
Connection	P	з'єднання IDbConnection, яке підтримує транзакцію
Commit	M	підтверджує транзакцію — результати команд SQL, виданих у транзакції, копіюються в базу даних.
Rollback	M	анулює транзакцію — результати команд SQL, виданих у транзакції, не копіюються в базу даних.

9.6.3. Приклад програми

Повернемося до попереднього проєкту, щоб розглянути програму [Transactions.cs]:

• у [1] — проект.
• у [2] проект налаштований на виконання [Transactions.cs]

Код [Transactions.cs] такий:

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

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

            // обробка файлу конфігурації
            string connectionString = null;
            try {
                // ланцюг з'єднання
                connectionString = ConfigurationManager.ConnectionStrings["dbArticlesSqlServerCe"].ConnectionString;
            } catch (Exception e) {
                Console.WriteLine("Erreur de configuration : {0}", e.Message);
                return;
            }

            // відображення
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // створення масиву з 2 елементів з однаковою назвою
            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);
            }
            // обробка можливих винятків
            try {
                Console.WriteLine("Insertion sans transaction...");
                // таблицю товарів спочатку вносимо в базу без транзакції
                ExecuteUpdate(connectionString, "delete from articles");
                try {
                    InsertArticlesOutOfTransaction(connectionString, articles);
                } catch (Exception ex) {
                    // повідомлення про помилку
                    Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
                }
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");

                // повторюємо ту саму операцію, але цього разу в транзакції
                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) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // вставлення таблиці товарів без транзакції
        static void InsertArticlesOutOfTransaction(string connectionString, Article[] articles) {
....
        }

        // вставлення таблиці товарів у транзакції
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
....
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
....
        }

        // виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // відображення даних у читачі
        static void AfficheReader(IDataReader reader) {
...
            }
        }
    }
}

• рядки 12–19: рядок підключення до бази даних SQLServer зчитується з [App.config]
• рядки 25–28: створюється масив із двох об’єктів Article. Ці два об’єкти мають однакову назву «article». Однак база даних [dbarticles.sdf] має обмеження унікальності для стовпця [nom] (див. розділ 9.3.1). Отже, ці два елементи не можуть одночасно існувати в базі даних. Обидва записи з назвою «article» додаються до таблиці articles. Отже, виникне проблема — c.a.d: виняток, згенерований SGBD і переданий його з’єднувачем ADO.NET. Щоб продемонструвати ефект транзакції, обидва записи будуть вставлені у два різних середовища:
  • спочатку поза будь-якою транзакцією. Тут слід пам’ятати, що в цьому випадку SQLServer Compact працює в режимі автокомміту, c.a.d. вставляє кожну команду SQL у неявну транзакцію. Перший елемент буде вставлений. Другий — ні.
  • Потім — у явну транзакцію, що охоплює обидва вставки. Оскільки друга вставка завершиться невдачею, перша буде скасована. У підсумку жодна вставка не відбудеться.
• рядок 33: таблиця articles очищується
• рядок 35: вставлення двох записів без явної транзакції. Оскільки відомо, що друге вставлення спричинить виняток, його обробляється за допомогою блоку try/catch
• рядок 46: виведення таблиці articles
• рядки 44–46: повторюємо ту саму послідовність, але цього разу для вставлень використовується явна транзакція. Виняток, що виникає, тут обробляється методом InsertArticlesInTransaction.
• рядки 54–56: метод InsertArticlesOutOfTransaction є методом InsertArticles з програми [Parametres.cs], яку ми розглядали раніше.
• рядки 64–66: метод ExecuteUpdate є таким самим, як і раніше. Команда SQL виконується в неявній транзакції. Це можливо в даному випадку, оскільки відомо, що в цьому випадку SQLServer Compact працює в режимі автокомміту.
• рядки 69–71: те саме стосується методу ExecuteSelect.

Метод InsertArticlesInTransaction виглядає наступним чином:

// вставлення масиву товарів у транзакцію
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // відкриття з'єднання
                connexion.Open();
                // конфігурація команди
                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);
                // компіляція команди
                sqlCommand.Prepare();
                // транзакція
                SqlCeTransaction transaction = null;
                try {
                    // початок транзакції
                    transaction = connexion.BeginTransaction(IsolationLevel.ReadCommitted);
                    // команда SQL має бути виконана в цій транзакції
                    sqlCommand.Transaction = transaction;
                    // вставлення рядків
                    for (int i = 0; i < articles.Length; i++) {
                        // ініціалізація параметрів
                        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;
                        // виконання запиту
                        sqlCommand.ExecuteNonQuery();
                    }
                    // транзакцію підтверджено
                    transaction.Commit();
                    Console.WriteLine("transaction validée...");
                } catch {
                    // скасування транзакції
                    if (transaction != null)transaction.Rollback();
                    Console.WriteLine("transaction invalidée...");
                }
            }
        }

Ми детально розглянемо лише те, чим він відрізняється від методу InsertArticles програми [Parametres.cs], яку ми розглядали раніше:

• рядок 16: оголошується транзакція SqlCeTransaction.
• рядки 17, 35: блок try/catch для обробки винятку, який виникне після другого вставлення
• рядок 19: створюється транзакція. Вона належить до поточного з’єднання.
• рядок 21: налаштована команда SQL поміщається в транзакцію
• рядки 23–31: виконуються вставки
• рядок 33: все пройшло успішно — транзакція підтверджена — вставки будуть остаточно занесені до бази даних.
• рядок 37: виникла проблема. Транзакція скасовується, якщо вона існувала.

Виконання дає такі результати:

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
------------------------------------

• рядок 4: відображено ExecuteUpdate («delete from articles») — у таблиці не було рядків
• рядок 5: виняток, спричинений другим вставленням. Повідомлення вказує, що обмеження UQ__ARTICLES__0000000000000010 не було перевірено. Більше інформації можна отримати, переглянувши властивості бази даних:

• у [1] у вікні [Database Explorer] у Visual Studio було створено з’єднання [2] із базою даних [dbarticles.sdf]. Вона має індекс UQ__ARTICLES__0000000000000010. Клацнувши правою кнопкою миші на цьому індексі, можна перейти до його властивостей (Index properties)
• у [3,4] видно, що індекс UQ__ARTICLES__0000000000000010 відповідає обмеженню унікальності для стовпця [NOM]

• рядки 7–11: відображення таблиці articles після двох вставлень. Вона не порожня: перша стаття була вставлена.
• рядок 15: відображається ExecuteUpdate («delete from articles») — у таблиці був один рядок
• рядок 16: повідомлення, виведене InsertArticlesInTransaction у разі збою транзакції.
• рядки 18–20: показують, що жодного вставлення не відбулося. Команда Rollback транзакції скасувала перше вставлення.

9.7. Метод ExecuteScalar

9.7.1. Серед методів інтерфейсу IDbCommand, описаного в розділі 9.3.3, був такий метод:

ExecuteScalar

M

для виконання запиту SQL Select, що повертає лише один результат, як у запиті: select count(*) from articles.

Тут ми наводимо приклад використання цього методу. Повернемося до проекту:

• у [1], проект.
• у [2] проект налаштований на виконання [ExecuteScalar.cs]

Програма [ExecuteScalar.cs] має такий вигляд:

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

            // обробка файлу конфігурації
            string connectionString = null;
...

            // відображення
            Console.WriteLine("Chaîne de connexion à la base : [{0}]\n", connectionString);

            // створення таблиці з 5 позицій
            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);
            }

            // обробка можливих винятків
            try {
                // вставлення таблиці товарів у транзакцію
                ExecuteUpdate(connectionString, "delete from articles");
                InsertArticlesInTransaction(connectionString, articles);
                ExecuteSelect(connectionString, "select id,nom,prix,stockactuel,stockminimum from articles");
                // розраховуємо середню ціну товарів
                decimal prixMoyen = (decimal)ExecuteScalar(connectionString, "select avg(prix) from articles");
                Console.WriteLine("Prix moyen des articles={0}", prixMoyen);
                // або кількість товарів
                int nbArticles = (int)ExecuteScalar(connectionString, "select count(id) from articles");
                Console.WriteLine("Nombre d'articles={0}", nbArticles);
            } catch (Exception ex) {
                // повідомлення про помилку
                Console.WriteLine("Erreur d'accès à la base de données (" + ex.Message + ")");
            }
        }

        // вставлення таблиці товарів у транзакцію
        static void InsertArticlesInTransaction(string connectionString, Article[] articles) {
...
        }


        // виконання запиту на оновлення
        static object ExecuteScalar(string connectionString, string requête) {
            using (SqlCeConnection connexion = new SqlCeConnection(connectionString)) {
                // відкриття з'єднання
                connexion.Open();
                // виконання запиту
                return new SqlCeCommand(requête, connexion).ExecuteScalar();
            }
        }

        // виконання запиту на оновлення
        static void ExecuteUpdate(string connectionString, string requête) {
...
        }

        // виконання запиту Select
        static void ExecuteSelect(string connectionString, string requête) {
...
        }

        // відображення читача
        static void AfficheReader(IDataReader reader) {
...
        }
    }
}

• рядки 14–17: створення масиву з 5 елементів
• рядок 22: таблиця articles очищується
• рядок 23: вона заповнюється 5 товарами
• рядок 24: таблиця виводиться на екран
• рядок 26: запит середньої ціни товарів
• рядок 29: запит кількості товарів
• рядок 49: використання методу [IDbCommand].ExecuteScalar() для обчислення кожного з цих значень.

Результати виконання такі:

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

У рядках 15 і 16 показано два значення, повернуті методом ExecuteScalar.

9.8. Приклад програми — версія 7

Повернемося до прикладу програми IMPOTS. Останню версію було розглянуто в розділі 7.6. Це була така трирівнева програма:

• шар [ui] був графічним інтерфейсом [A], а шар [dao] отримував дані з текстового файлу [B].
• Інстанціювання шарів та їх інтеграція в додаток забезпечувалися Spring.

Ми модифікуємо шар [dao], щоб він отримував дані з бази даних.

9.8.1. -база даних

Вміст попереднього текстового файлу [B] заноситься до бази даних MySQL5. Покажемо, як це зробити:

• [1]: запущено програму MySQL Administrator
• [2,3]: у полі [Schemata] клацніть правою кнопкою миші та виберіть опцію [Create Schema], щоб створити нову базу даних
• [4]: база даних матиме назву [bdimpots]
• [5]: її додано до баз зони [Schemata].

• [6,7]: клацніть правою кнопкою миші на таблиці та виберіть опцію [Create New Table], щоб створити таблицю
• [8]: таблиця матиме назву [tranches]. Вона міститиме стовпці [id, limite, coeffR, coeffN].
• [9,10]: [id] є первинним ключем типу INTEGER і має атрибут AUTO_INCREMENT [10]: саме SGBD відповідатиме за заповнення цього стовпця під час додавання рядків.
• стовпці [limite, coeffR, coeffN] мають тип DOUBLE.
• [11,12]: нова таблиця з’являється на вкладці [Schema Tables] бази даних.

• [13,14]: для внесення даних у таблицю
• [15]: запущено [Query Browser]
• [16]: дані для стовпців [limite, coeffR, coeffN] введені та перевірені. Стовпець [id] було заповнено за допомогою SGBD. Перевірка відбулася за допомогою [17].

• залишаючись у [Query Browser] [18], виконується [20] запит [19]. Цей запит створює користувача «admimpots» з паролем «mdpimpots» і надає йому всі привілеї (grant all privileges) щодо всіх об’єктів бази даних bdimpots (on bdimpots.*). Це дозволить нам працювати з базою даних [bdimpots] під обліковим записом [admimpots], а не під обліковим записом адміністратора [root].

9.8.2. Рішення для Visual Studio

Ми будемо дотримуватися підходу, розглянутого для версії 5 прикладу програми (див. розділ 6.4). Ми поступово створимо таке рішення Visual Studio:

• у [1]: рішення ImpotsV7 складається з трьох проєктів, по одному для кожного з трьох рівнів додатка
• в [2]: проект [dao] шару [dao], який відтепер використовуватиме базу даних
• у [3]: проект [metier] з рівня [metier]. Тут ми використовуємо рівень [metier] з версії 5, описаний у розділі 6.4.4.
• у [4]: проект [ui] з рівня [ui]. Тут ми використовуємо рівень [ui] з версії 6, описаний у розділі 7.6.

Ми спираємося на вже напрацьоване, щоб використати два вже написані шари: [ui] та [metier]. Це стало можливим завдяки обраній багатошаровій архітектурі. Проте нам знадобляться вихідні коди шарів [ui] та [metier]. Адже не можна обмежитися лише DLL цих шарів. Коли у версії 5 було створено DLL для шару [metier], воно мало залежність від DLL шару [dao]. Ця залежність була жорстко зафіксована в DLL шару [metier] (назва DLL шару [dao], версія, ідентифікаційний токен тощо). Таким чином, DLL версії 5 [ImpotsV5-metier.dll] може працювати лише з DLL та [ImpotsV5-dao.dll], з якими вона була скомпільована. Якщо змінити DLL у шарі [dao], потрібно перекомпілювати шар [metier], щоб створити для нього новий DLL. Те саме стосується шару [ui]. Отже, шари [ui] та [metier] не будуть змінені, але їх буде перекомпільовано для роботи з DLL нового шару [dao].

9.8.3. Шар [dao]

Посилання на проект (див. [1] у проекті)

• nunit.framework: для тесту NUnit
• System.Configuration: для використання файлу конфігурації [App.config]
• System.Data: оскільки ми використовуємо базу даних.

Об’єкти (див. [2] у проєкті)

Класи [TrancheImpot] та [ImpotException] належать до попередніх версій.

Рівень [dao] (див. [3] у проєкті)

Інтерфейс [IImpotDao] не зазнав змін:

using Entites;

namespace Dao {
    public interface IImpotDao {
        // податкові групи
        TrancheImpot[] TranchesImpot{get;}
    }
}

Клас реалізації [DataBaseImpot] цього інтерфейсу має такий вигляд:

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

namespace Dao {
    public class DataBaseImpot : IImpotDao {
        // податкові шкали
        private TrancheImpot[] tranchesImpot;
        public TrancheImpot[] TranchesImpot { get { return tranchesImpot; } }

        // виробник
        public DataBaseImpot(string factory, string connectionString, string requête) {
            // фабрика: фабрика цільового об’єкта SGBD
            // connectionString: ланцюг підключення до бази даних податкових шкал
            // обробка можливих винятків
            try {
                // отримуємо загальний коннектор для SGBD
                DbProviderFactory connecteur = DbProviderFactories.GetFactory(factory);
                using (DbConnection connexion = connecteur.CreateConnection()) {
                    // конфігурація з'єднання
                    connexion.ConnectionString = connectionString;
                    // відкриття з'єднання
                    connexion.Open();
                    // конфігурація команди 
                    DbCommand sqlCommand = connecteur.CreateCommand();
                    sqlCommand.CommandText = requête;
                    sqlCommand.Connection = connexion;
                    // виконання запиту
                    List<TrancheImpot> listTrancheImpot = new List<TrancheImpot>();
                    using (DbDataReader reader = sqlCommand.ExecuteReader()) {
                        while (reader.Read()) {
                            // створення нової податкової групи
                            listTrancheImpot.Add(new TrancheImpot() { Limite = reader.GetDecimal(0), CoeffR = reader.GetDecimal(1), CoeffN = reader.GetDecimal(2) });
                        }
                    }
                    // податкові шкали додаються до екземпляра
                    tranchesImpot = listTrancheImpot.ToArray();
                }
            } catch (Exception ex) {
                // виняток інкапсулюється в тип ImpotException
                throw new ImpotException("Erreur de lecture des tranches d'impôt", ex) { Code = 101 };
            }

        }
    }
}

• рядок 7: клас [DataBaseImpot] реалізує інтерфейс [IImpotDao].
• рядок 10: реалізація методу [TranchesImpot] інтерфейсу. Він просто повертає посилання на масив податкових шкал із рядка 9. Цей масив буде побудовано конструктором класу.
• рядок 13: конструктор. Він використовує генеричний коннектор (див. розділ 9.4.5) для роботи з базою даних податкових шкал. Конструктор отримує три параметри:
  • ім’я «фабрики», до якої він звертатиметься за класами для підключення до бази даних, надсилання команд SQL та обробки результату запиту Select.
  • рядок підключення, який він повинен використовувати для підключення до бази даних
  • команда SQL Select, яку він повинен виконати для отримання податкових шкал.
• рядок 19: запит на коннектор до «фабрики»
• рядок 20: створює з’єднання з цим коннектором. З’єднання створено, але ще не працює
• рядок 22: ініціалізується рядок підключення. Тепер можна встановити з’єднання.
• рядок 24: встановлюється з’єднання
• рядок 26: запит до коннектора на об’єкт [DbCommand] для виконання замовлення SQL
• рядок 27: визначається замовлення SQL для виконання
• рядок 28: визначає з’єднання, через яке його слід виконати
• рядок 30: створюється порожній список [listTrancheImpot] з об’єктів типу [TrancheImpot].
• рядок 31: виконується команда SQL Select
• рядки 32–35: об’єкт [DbDataReader], отриманий у результаті Select, обробляється. Кожен рядок таблиці результатів Select використовується для створення екземпляра об’єкта типу [TrancheImpot], який додається до списку [listTrancheImpot].
• рядок 38: список об’єктів типу [TrancheImpot] передається в масив у рядку 9.
• рядки 40–43: можливе виключення інкапсулюється в тип [ImpotException] і йому присвоюється код помилки 101 (довільний).

Тест [Test1] (див. [4] у проєкті)

Клас [Test1] лише відображає податкові шкали на екрані. Це той самий клас, що вже використовувався у версії 5 (параграф 6.4.3), за винятком інструкції, яка створює екземпляр шару [dao] (рядок 14).

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

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

            // створюється шар [dao]
            IImpotDao dao = null;
            try {
                // створення шару [dao]
                dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
            } catch (ImpotException e) {
                // виведення повідомлення про помилку
                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);
                // зупинка програми
                Environment.Exit(1);
            }
            // відображення податкових шкал
            TrancheImpot[] tranchesImpot = dao.TranchesImpot;
            foreach (TrancheImpot t in tranchesImpot) {
                Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
            }
        }
    }
}

У рядку 14 використовується такий файл конфігурації [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>

• рядок 4: рядок підключення до бази даних MySQL5. Слід зазначити, що підключення встановлюватиме користувач [admimpots].
• рядок 8: «фабрика» для роботи з SGBD MySQL5
• рядок 7: запит SQL Select для отримання податкових груп.

Проєкт налаштовано на виконання [Test1.cs]:

Виконання тесту дає такі результати:

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

Тест NUnit [NUnit1] (див. [4] у проєкті)

Модульний тест [NUnit1] — це той самий тест, що вже використовувався у версії 5 (параграф 6.4.3), за винятком інструкції, яка створює екземпляр шару [dao] (рядок 16).

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

namespace Tests {
    [TestFixture]
    public class NUnit1 : AssertionHelper{
        // шар [dao] для тестування
        private IImpotDao dao;

        // розробник
        public NUnit1() {
            // ініціалізація шару [dao]
            dao = new DataBaseImpot(ConfigurationManager.AppSettings["factoryMySql5"], ConfigurationManager.ConnectionStrings["dbImpotsMySql5"].ConnectionString, ConfigurationManager.AppSettings["requete"]);
        }

        // тест
        [Test]
        public void ShowTranchesImpot(){
            // відображення податкових шкал
            TrancheImpot[] tranchesImpot = dao.TranchesImpot;
            foreach (TrancheImpot t in tranchesImpot) {
                Console.WriteLine("{0}:{1}:{2}", t.Limite, t.CoeffR, t.CoeffN);
            }
            // деякі тести
            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));
        }
    }
}

Для виконання цього модульного тесту проект повинен бути типу [Class Library]:

• у [1]: тип проекту було змінено
• на [2]: згенерований файл DLL матиме назву [ImpotsV7-dao.dll]
• у [3]: після генерації (F6) проекту папка [dao/bin/Release] містить файли DLL та [ImpotsV7-dao.dll]. Він також містить файл конфігурації [App.config], перейменований на [nom DLL].config. Це стандартна процедура у Visual Studio.

Потім DLL та [ImpotsV7-dao.dll] завантажуються у фреймворк NUnit і виконуються:

• у [1]: тести пройшли успішно. Тепер ми вважаємо рівень [dao] працездатним. Його DLL містить усі класи проєкту, включаючи тестові класи. Останні є непотрібними. Ми перебудовуємо DLL, щоб виключити з нього тестові класи.
• у [2]: папка [tests] виключена з проєкту
• у [3]: новий проєкт. Він перегенерується за допомогою F6 для створення нового DLL. Саме цей файл DLL використовуватиметься шарами [metier] та [ui] додатка.

9.8.4. Рівень [metier]

• у [1] проект [metier] став активним проектом рішення
• у [2]: посилання на проект. Зверніть увагу на посилання на DLL у шарі [dao], створеному раніше. Ця процедура додавання посилання була описана у версії 5, у розділі 6.4.4.
• у [3]: шар [metier]. Це той самий шар з версії 5, описаний у розділі 6.4.4.

Проєкт [metier] налаштований на генерацію DLL:

• [1]: проект є «бібліотекою класів»
• [2]: у результаті генерації проекту будуть створені файли DLL, [ImpotsV7-metier.dll] та [3].

Проєкт згенеровано (F6).

9.8.5. Шар [ui]

• у [1], проект [ui] став активним проектом рішення
• у [2]: посилання на проект. Зверніть увагу на посилання у DLL на шари [dao] та [metier].
• у [3]: шар [ui]. Це шар версії 6, описаний у розділі 7.6.
• у [4] файл конфігурації [App.config] аналогічний файлу версії 6. Він відрізняється від нього лише тим, як Spring створює екземпляр шару [dao]:

<?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>

• рядки 11–25: конфігурація Spring
• рядки 15–24: об’єкти, інстанційовані Spring
• рядки 16–20: інстанціювання шару [dao]
• рядок 16: шар [dao] інстанціюється класом [Dao.DataBaseImpot], який знаходиться в DLL [ImpotsV7-Dao]
• рядки 17–19: три параметри (фабрика використовуваного SGBD, рядок підключення, запит SQL), які потрібно передати конструктору класу [Dao.DataBaseImpot]
• рядки 21–23: створення екземпляра шару [metier]. Це та сама конфігурація, що й у версії 6.

Тестування

Проєкт [ui] налаштований наступним чином:

• [1]: проект типу «Windows Application»
• [2]: у результаті генерації проекту буде створено виконуваний файл [ImpotsV7-ui.exe]

Приклад виконання наведено у файлі [3].

9.8.6. Зміна бази даних

Вищезазначений шар [dao] було створено з використанням універсального з’єднувача та бази MySQL5. Тут ми пропонуємо перейти на базу SQL Server Compact, щоб продемонструвати, що зміниться лише конфігурація.

База даних SQL Server Compact матиме такий вигляд:

• [1]: база даних [dbimpots.sdf] у поданні [DataBase Explorer] у Visual Studio [2]. Вона була створена без пароля.
• [3]: таблиця [data], яка містить дані. Ми навмисно вибрали для таблиці та стовпців імена, відмінні від тих, що використовуються в базі даних MySQL5, щоб ще раз підкреслити доцільність розміщення таких деталей у файлі конфігурації, а не в коді.
• [4]: стовпець [id] є первинним ключем і має атрибут Identity: саме SGBD буде присвоювати йому значення.
• [5]: вміст таблиці [data].

• [6]: база даних [dbimpots.sdf] була розміщена в папці проєкту [ui] та інтегрована в цей проєкт.
• [7]: база даних [dbimpots.sdf] буде скопійована до папки виконання проекту.

Файл конфігурації [App.config] для нової бази даних має такий вигляд:

<?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>

• рядки 23–27: конфігурація шару [dao] для роботи з базою даних [dbimpots.sdf].

Результати виконання ідентичні попереднім. Варто відзначити переваги використання універсального коннектора, щоб зробити шар [dao] незалежним від змін у SGBD. Однак ми побачили, що цей коннектор підходить не для всіх ситуацій, зокрема для тих, де використовуються параметризовані запити. У такому разі існують інші рішення, наприклад, згадані сторонні фреймворки для доступу до даних (Spring, iBatis, NHibernate, LINQ, ...).

9.9. Щоб дізнатися більше...

• LINQ розглядається в багатьох виданнях, зокрема в книзі: «C# 3.0 in a Nutshell», автори — Джозеф та Бен Альбахарі, видавництво O'Reilly, про яку вже згадувалося у вступі до цього документа.
• iBatis розглядається у книзі: «iBatis in Action», автор — Клінтон Бегін, видавництво Manning
• Видання «Nhibernate in Action» видавництва Manning заплановано на липень 2008 року

Для Spring, iBatis та NHibernate доступні довідники на веб-сайтах цих фреймворків.