7. Доступ до баз даних
7.1. Загальні відомості
Існує безліч баз даних для платформ Windows. Для доступу до них програми використовують спеціальні програми, які називаються драйверами (drivers).
 |
На схемі вище драйвер має два інтерфейси:
- інтерфейс I1, що надається програмі
- інтерфейс I2, що з’єднує програму з базою даних
Щоб уникнути необхідності переписання програми, написаної для бази даних B1, у разі переходу на іншу базу даних B2, було проведено роботу з стандартизації інтерфейсу I1. Якщо використовуються бази даних із «нормалізованими» драйверами, база B1 буде постачатися з драйвером P1, база B2 — з драйвером P2, і інтерфейс I1 для цих двох драйверів буде однаковим. Отже, переписати додаток не доведеться. Таким чином, наприклад, можна буде перенести базу даних ACCESS у базу даних MySQL без внесення змін у програму.
Існує два типи стандартизованих драйверів:
- драйвери ODBC (Open DataBase Connectivity)
- драйвери OLE та DB (Object Linking and Embedding DataBase)
Драйвери ODBC забезпечують доступ до баз даних. Джерела даних для драйверів OLE та DB є більш різноманітними: бази даних, поштові системи, довідники тощо. Обмежень немає. Будь-яке джерело даних може стати об’єктом драйвера OLE або DB, якщо розробник вирішить це зробити. Переваги, звісно, значні: ми отримуємо уніфікований доступ до великої різноманітності даних.
Платформа .NET постачається з двома типами класів доступу до даних:
- класи SQL та Server.NET
- класи OLE та Db.NET
Перші класи забезпечують прямий доступ до сервера Microsoft SGBD SQL без проміжного драйвера. Другі класи забезпечують доступ до джерел даних OLE DB.
Платформа .NET постачається (травень 2002 р.) з трьома драйверами OLE, DB відповідно для: SQL Server, Oracle та Microsoft Jet (Access). Якщо потрібно працювати з базою даних, для якої є драйвер ODBC, але відсутні драйвери OLE та DB, це неможливо. Таким чином, неможливо працювати з SGBD та MySQL, які (станом на травень 2002 року) не надають драйверів OLE та DB. Однак існує низка класів, що забезпечують доступ до джерел даних ODBC, — це класи odbc.net. Вони не входять до стандартного комплекту SDK, і їх потрібно завантажити з веб-сайту Microsoft. У наведених нижче прикладах ми будемо використовувати переважно ці класи ODBC, оскільки більшість баз даних у Windows постачаються з таким драйвером. Ось, наприклад, список драйверів ODBC, встановлених на комп’ютері з ОС Windows 2000 (Меню «Пуск»/«Параметри»/«Панель керування»/«Адміністративні інструменти»):
Вибираємо піктограму «Джерело даних» ODBC:
7.2. Два режими роботи з джерелом даних
Платформа .NET дозволяє використовувати джерело даних двома різними способами:
- режим з підключенням до мережі
- автономний режим
У режимі з підключенням додаток
- відкриває з'єднання з джерелом даних
- працює з джерелом даних у режимі читання/запису
- закриває з’єднання
У автономному режимі додаток
- відкриває з'єднання з джерелом даних
- отримує копію в пам'яті всіх або частини даних із джерела
- закриває з'єднання
- працює з копією даних у пам’яті в режимі читання/запису
- коли робота завершена, відкриває з’єднання, надсилає змінені дані до джерела даних для їхнього врахування, закриває з’єднання
В обох випадках саме операція обробки та оновлення даних займає час. Уявімо, що ці оновлення виконує користувач, який вводить дані; така операція може тривати десятки хвилин. Протягом усього цього часу в режимі з підключенням з’єднання з базою даних підтримується, а зміни відображаються негайно. У режимі без підключення під час оновлення даних з’єднання з базою даних відсутнє. Зміни вносяться виключно в копію даних у пам’яті. Вони відображаються у джерелі даних одноразово, коли все завершено.
Які переваги та недоліки цих двох методів?
- Підключення вимагає значних системних ресурсів. Якщо одночасно відбувається багато підключень, автономний режим дозволяє скоротити їхню тривалість до мінімуму. Це стосується веб-додатків, які мають тисячі користувачів.
- Недоліком автономного режиму є складність управління одночасними оновленнями. Користувач U1 отримує дані в момент часу T1 і починає їх редагувати. У момент часу T2 користувач U2 також отримує доступ до джерела даних і отримує ті самі дані. Тим часом користувач U1 змінив деякі дані, але ще не передав їх до джерела даних. Отже, U2 працює з даними, частина яких є помилковою. Класи .NET пропонують рішення для управління цією проблемою, але її не так просто вирішити.
- У режимі підключення одночасне оновлення даних кількома користувачами зазвичай не створює проблем. Оскільки з’єднання з базою даних підтримується, саме база даних сама керує цими одночасними оновленнями. Так, Oracle блокує рядок у базі даних, щойно користувач його змінює. Він залишатиметься заблокованим, а отже, недоступним для інших користувачів, доти, доки той, хто його змінив, не підтвердить (commit) свою зміну або не скасує її (rollback).
- Якщо дані мають передаватися по мережі, слід вибрати автономний режим. Він дозволяє отримати знімок даних у об’єкті, який називається набором даних (dataset) і який сам по собі є повноцінною базою даних. Цей об’єкт може передаватися по мережі між комп’ютерами.
Спочатку розглянемо режим з підключенням.
7.3. Доступ до даних у підключеному режимі
7.3.1. Бази даних у прикладі
Розглянемо базу даних ACCESS, яка називається articles.mdb і містить лише одну таблицю з назвою ARTICLES, що має таку структуру:
ім’я | тип |
код | 4-символьний код товару |
назва | назва (рядок символів) |
ціна | ціна (фактична) |
stock_actuel | його поточний запас (ціле число) |
stock_minimum | мінімальний запас (ціле число), при недосягненні якого товар потрібно поповнити |
Початковий вміст такий:
Ми будемо використовувати цю базу як у пілотному проекті ODBC, так ідрайвера OLE DB, щоб продемонструвати схожість цих двох підходів, а також тому, що ми маємо обидва типи драйверів для ACCESS.
Ми також використаємо базу даних MySQL DBARTICLES, що містить ту саму єдину таблицю ARTICLES, однаковий вміст і доступ до якої здійснюється через драйвер ODBC, щоб продемонструвати, що додаток, написаний для роботи з базою даних ACCESS, не потребує модифікації для використання бази даних MySQL. Доступ до бази даних DBARTICLES має користувач із ім’ям admarticles та паролем mdparticles. На наступному знімку екрана показано вміст бази даних MySQL:
C:\mysql\bin>mysql --database=dbarticles --user=admarticles --password=mdparticles
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+----------------------+
| Tables_in_dbarticles |
+----------------------+
| articles |
+----------------------+
1 row in set (0.01 sec)
mysql> select * from articles;
+------+--------------------------------+------+--------------+---------------+
| code | nom | prix | stock_actuel | stock_minimum |
+------+--------------------------------+------+--------------+---------------+
| a300 | vÚlo | 2500 | 10 | 5 |
| b300 | pompe | 56 | 62 | 45 |
| c300 | arc | 3500 | 10 | 20 |
| d300 | flÞches - lot de 6 | 780 | 12 | 20 |
| e300 | combinaison de plongÚe | 2800 | 34 | 7 |
| f300 | bouteilles d'oxygÞne | 800 | 10 | 5 |
+------+--------------------------------+------+--------------+---------------+
6 rows in set (0.02 sec)
mysql> describe articles;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| code | text | YES | | NULL | |
| nom | text | YES | | NULL | |
| prix | double | YES | | NULL | |
| stock_actuel | smallint(6) | YES | | NULL | |
| stock_minimum | smallint(6) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> exit
Bye
Щоб визначити базу даних ACCESS як джерело даних ODBC, виконайте такі дії:
- запустіть диспетчер джерел даних ODBC, як показано вище, і виберіть вкладку «User» DSN (DSN = «Data Source Name»)
- додайте джерело за допомогою кнопки Add, вкажіть, що доступ до цього джерела здійснюється через драйвер Access, і виконайте Terminer:
- Назвіть джерело даних articles-access, вкажіть довільний опис та скористайтеся кнопкою Sélectionner, щоб вказати файл .mdb із бази даних. Завершіть операцію, вказавши OK.
Нове джерело даних з’явиться у списку джерел користувача DSN:
Щоб визначити базу даних MySQL DBARTICLES як джерело даних ODBC, виконайте такі дії:
- увімкніть диспетчер джерел даних ODBC, як показано вище, і виберіть вкладку «Користувач» (User) DSN. Додайте нове джерело даних із Add та виберіть драйвер ODBC із MySQL.
- Виконайте Terminer. З’явиться сторінка налаштування джерела MySQL:
54321
- у полі (1) вкажіть ім’я вашого джерела даних ODBC
- у полі (2) вказуємо комп’ютер, на якому розташований сервер MySQL. Тут ми вказуємо localhost, щоб зазначити, що він знаходиться на тому ж комп’ютері, що й наша програма. Якби сервер MySQL знаходився на віддаленому комп’ютері M, ми б вказали тут його ім’я, і наша програма працювала б із віддаленою базою даних без жодних змін.
- У полі (3) вказуємо ім’я бази даних. У даному випадку вона називається DBARTICLES.
- у полі (4) вказуємо логін admarticles, а в полі (5) — пароль mdparticles.
7.3.2. Використання драйвера ODBC
У програмі, що використовує базу даних у режимі підключення, зазвичай виконуються такі кроки:
- Підключення до бази даних
- Відправлення запитів SQL до бази даних
- Отримання та обробка результатів цих запитів
- Закриття з’єднання
Етапи 2 і 3 виконуються повторно, а закриття з’єднання відбувається лише після завершення роботи з базою даних. Це відносно класична схема, до якої ви, можливо, вже звикли, якщо раніше працювали з базою даних в інтерактивному режимі. Ці етапи є однаковими незалежно від того, чи база даних використовується через драйвер ODBC, чи через драйвер OLE або DB. Нижче наведено приклад із класами .NET для управління джерелами даних ODBC. Програма має назву liste і приймає як параметр ім’я DSN джерела даних ODBC, що містить таблицю ARTICLES. Після цього вона виводить вміст цієї таблиці:
dos>liste
syntaxe : pg dsnArticles
dos>liste articles-access
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
dos>liste mysql-artices
Erreur d'exploitation de la base de données (ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified)
dos>liste mysql-articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
З наведених вище результатів бачимо, що програма вивела вміст як бази ACCESS, так і бази MySQL. Тепер розглянемо код цієї програми:
' параметри
Option Explicit On
Option Strict On
' простори імен
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Module db1
Sub main(ByVal args As String())
' консольний додаток
' відображає вміст таблиці ARTICLES з бази даних DSN
', ім’я якої передано як параметр
Const syntaxe As String = "syntaxe : pg dsnArticles"
Const tabArticles As String = "articles" ' la table des articles
' перевірка параметрів
' чи є 1 параметр
If args.Length <> 1 Then
' повідомлення про помилку
Console.Error.WriteLine(syntaxe)
' кінець
Environment.Exit(1)
End If
' отримано параметр
Dim dsnArticles As String = args(0) ' la base DSN
' підготовка до підключення до БД
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
' спроба доступу до бази даних
Try
' рядок підключення до бази даних
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' виконання команди SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
' Обробка отриманої таблиці
' виведення стовпців
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' виведення даних
While myReader.Read()
' обробка поточного рядка
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données " + ex.Message + ")"))
Environment.Exit(2)
Finally
' закриття зчитувача
myReader.Close()
' закриття з'єднання
articlesConn.Close()
End Try
End Sub
End Module
Класи для роботи з джерелами ODBC знаходяться в просторі імен Microsoft.Data.Odbc, який, отже, потрібно імпортувати. Крім того, певна кількість класів знаходиться в просторі імен System.Data.
Imports System.Data
Imports Microsoft.Data.Odbc
Простори імен, які використовує програма, знаходяться в різних збірках. Програму компілюють за допомогою такої команди:
dos>vbc /r:microsoft.data.odbc.dll /r:microsoft.visualbasic.dll /r:system.dll /r:system.data.dll db1.vb
7.3.2.1. Етап підключення
З'єднання ODBC використовує клас OdbcConnection. Конструктор цього класу приймає як параметр так званий рядок з'єднання. Це рядок символів, який визначає всі параметри, необхідні для встановлення з'єднання з базою даних. Ці параметри можуть бути дуже численними, а отже, рядок може бути складним. Рядок має вигляд "param1=valeur1;param2=valeur2;...;paramj=valeurj;". Ось кілька можливих параметрів paramj:
ім’я користувача, який отримає доступ до бази даних | |
пароль цього користувача | |
назва DSN бази даних, якщо вона має таку | |
назва бази даних, до якої здійснюється доступ | |
Якщо джерело даних визначено як ODBC за допомогою диспетчера джерел даних ODBC, ці параметри вже вказано та збережено. Тоді достатньо передати параметр DSN, який визначає ім’я джерела даних DSN. Саме це робиться тут:
' підготовка до підключення до БД
Dim articlesConn As OdbcConnection = Nothing ' la connexion
Dim myReader As OdbcDataReader = Nothing ' le lecteur de données
Try
' спроба доступу до бази даних
' рядок підключення до бази даних
Dim connectString As String = "DSN=" + dsnArticles + ";"
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
Після створення об'єкта OdbcConnection відкривається з'єднання за допомогою методу Open. Це відкриття може завершитися невдачею, як і будь-яка інша операція з базою даних. Саме тому весь код доступу до бази даних міститься в об'єкті try-catch. Після встановлення з’єднання можна надсилати запити SQL до бази даних.
7.3.2.2. Відправлення запитів SQL
Щоб відправити запити SQL, нам потрібен об’єкт Command, а точніше — об’єкт OdbcCommand, оскільки ми використовуємо джерело даних ODBC. Клас OdbcCommand має кілька конструкторів:
- OdbcCommand(): створює порожній об’єкт Command. Щоб ним скористатися, згодом потрібно буде вказати різні властивості:
- CommandText: текст запиту SQL, який потрібно виконати
- Connection: об’єкт OdbcConnection, що представляє з’єднання з базою даних, до якої буде виконано запит
- CommandType: тип запиту SQL. Можливі три значення
- CommandType.Text: властивість CommandText містить текст запиту SQL (значення за замовчуванням)
- CommandType.StoredProcedure: властивість CommandText містить ім’я збереженої процедури в базі даних
- CommandType.TableDirect: властивість CommandText містить ім’я таблиці T. Еквівалентно оператору select * from T. Існує лише для драйверів OLE та DB.
- OdbcCommand(string sqlText): параметр sqlText буде присвоєно властивості CommandText. Це текст запиту SQL, який потрібно виконати. З’єднання слід вказати у властивості Connection.
- OdbcCommand(string sqlText, OdbcConnection з’єднання): параметр sqlText буде присвоєно властивості CommandText, а параметр connexion — властивості Connection.
Для відправлення запиту SQL існують два методи:
- OdbcdataReader ExecuteReader(): надсилає запит SELECT з CommandText до з’єднання Connection та створює об’єкт OdbcDataReader, що забезпечуєдоступ до всіх рядків таблиці результатів select
- int ExecuteNOnQuery() : надсилає запит на оновлення (INSERT, UPDATE, DELETE) від CommandText до з’єднання Connection і повертає кількість рядків, на які впливає це оновлення.
У нашому прикладі після відкриття з’єднання з базою даних ми надсилаємо запит SQL SELECT, щоб отримати вміст таблиці ARTICLES:
' виконання команди SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOdbcCommand As New OdbcCommand(sqlText)
myOdbcCommand.Connection = articlesConn
myReader = myOdbcCommand.ExecuteReader()
Запит на пошук зазвичай має такий вигляд:
select col1, col2,... from table1, table2,...
where condition
order by expression
...
Обов’язковими є лише ключові слова першого рядка, решта — необов’язкові. Існують також інші ключові слова, які тут не наведено.
- Здійснюється з'єднання з усіма таблицями, що знаходяться після ключового слова from
- Зберігаються лише стовпці, що знаходяться після ключового слова «select»
- Зберігаються лише ті рядки, які відповідають умові ключового слова «where»
- Отримані рядки, впорядковані за виразом ключового слова «order by», утворюють результат запиту.
Результатом запиту select є таблиця. Якщо розглянути попередню таблицю ARTICLES і отримати назви товарів, поточний запас яких нижчий за мінімальний поріг, слід написати:
select nom from articles where stock_actuel<stock_minimum
Якщо потрібно отримати їх у алфавітному порядку за назвами, слід ввести:
select nom from articles where stock_actuel<stock_minimum order by nom
7.3.2.3. Використання результату запиту SELECT
Результатом запиту SELECT у режимі без підключення є об’єкт DataReader, у даному випадку — об’єкт OdbcDataReader. Цей об’єкт дозволяє послідовно отримувати всі рядки результату та отримувати інформацію про стовпці цих результатів. Розглянемо деякі властивості та методи цього класу:
кількість стовпців у таблиці | |
Item(i) представляє стовпець № i поточного рядка результату | |
значення стовпця № i поточного рядка, представлене у вигляді типу XXX (Int16, Int32, Int64, Double, String, Boolean, ...) | |
назва стовпця № i | |
закриває об’єкт OdbcdataReader та звільняє пов’язані з ним ресурси | |
переміщається на один рядок в таблиці результатів. Повертає значення «false», якщо це неможливо. Новий рядок стає поточним рядком зчитувача. |
Обробка результату функції select зазвичай відбувається послідовно, аналогічно до обробки текстових файлів: можна лише просуватися в таблиці вперед, а не назад:
While myReader.Read()
' отримано рядок — обробляємо його
....
' наступний рядок
end while
Цих пояснень достатньо, щоб зрозуміти наступний код із нашого прикладу:
' Обробка отриманої таблиці
' відображення стовпців
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' відображення даних
While myReader.Read()
' аналіз поточного рядка
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Єдина складність полягає в операторі, де об’єднуються значення різних стовпців поточного рядка:
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Запис рядок+=myReader(i).ToString перекладається як рядок+=myReader.Item(i).ToString(), де Item(i) — значення стовпця i поточного рядка.
7.3.2.4. Звільнення ресурсів
Класи OdbcReader та OdbcConnection мають метод Close(), який звільняє ресурси, пов’язані з об’єктами, що закриваються.
7.3.3. Використання драйвера OLE DB
Розглянемо той самий приклад, але цього разу з базою даних, до якої здійснюється доступ через драйвер OLE DB. Платформа .NET надає такий драйвер для баз даних ACCESS. Тому ми будемо використовувати ту саму базу даних articles.mdb, що й раніше. Ми прагнемо показати, що навіть якщо класи змінюються, концепції залишаються тими самими:
- з'єднання представлено об'єктом OleDbConnection
- запит SQL формується за допомогою об’єкта OleDbCommand
- якщо цей запит є умовою SELECT, у відповідь буде отримано об’єкт OleDbDataReader для доступу до рядків таблиці результатів
Ці класи знаходяться в просторі імен System.Data.OleDb. Попередню програму можна легко переробити для роботи з базою даних OLE DB:
- всюди замінити OdbcXX на OleDbXX
- змінюємо рядок підключення. Для бази даних ACCESS без логіна та пароля рядок підключення має вигляд Provider=Microsoft.JET.OLEDB.4.0;Data Source=[fichier.mdb]. Параметричною частиною цього рядка є ім’я файлу ACCESS, який потрібно використовувати. Ми змінимо нашу програму так, щоб вона приймала ім’я цього файлу як параметр.
- Простір імен, який потрібно імпортувати, тепер має вигляд System.Data.OleDb.
Наша програма виглядає наступним чином:
' параметри
Option Explicit On
Option Strict On
' простори імен
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Module db2
Public Sub Main(ByVal args() As String)
' консольний додаток
' відображає вміст таблиці ARRTICLES у базі даних DSN
', ім’я якої передано як параметр
Const syntaxe As String = "syntaxe : pg base_access_articles"
Const tabArticles As String = "articles" ' la table des articles
' перевірка параметрів
' чи є 1 параметр
If args.Length <> 1 Then
' повідомлення про помилку
Console.Error.WriteLine(syntaxe)
' кінець
Environment.Exit(1)
End If
' отримано параметр
Dim dbArticles As String = args(0) ' la base de données
' підготовка до підключення до БД
Dim articlesConn As OleDbConnection = Nothing ' la connexion
Dim myReader As OleDbDataReader = Nothing ' le lecteur de données
' спроба доступу до бази даних
Try
' рядок підключення до бази даних
Dim connectString As String = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + dbArticles + ";"
articlesConn = New OleDbConnection(connectString)
articlesConn.Open()
' виконання команди SQL
Dim sqlText As String = "select * from " + tabArticles
Dim myOleDbCommand As New OleDbCommand(sqlText)
myOleDbCommand.Connection = articlesConn
myReader = myOleDbCommand.ExecuteReader()
' Обробка отриманої таблиці
' виведення стовпців
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' виведення даних
While myReader.Read()
' обробка поточного рядка
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
Console.WriteLine(ligne)
End While
Catch ex As Exception
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
Environment.Exit(2)
Finally
' закриття зчитувача
myReader.Close()
' закриття з'єднання
articlesConn.Close()
End Try
' завершення
Environment.Exit(0)
End Sub
End Module
Отримані результати:
dos>vbc liste.vb
E:\data\serge\MSNET\vb.net\adonet\6>dir
07/05/2002 15:09 2 325 liste.CS
07/05/2002 15:09 4 608 liste.exe
20/08/2001 11:54 86 016 ARTICLES.MDB
dos>liste articles.mdb
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
7.3.4. Оновлення таблиці
У попередніх прикладах ми лише перелічували вміст таблиці. Ми модифікуємо нашу програму управління базою товарів, щоб вона могла її змінювати. Програма називається sql. Як параметр їй передається ім’я DSN бази товарів, яку потрібно обробити. Користувач безпосередньо вводить команди SQL з клавіатури, які програма виконує, як показано в наведених нижче результатах, отриманих на базі даних товарів MySQL:
dos>vbc /r:microsoft.data.odbc.dll sql.vb
dos>sql mysql-articles
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles where stock_actuel<stock_minimum
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
Requête SQL (fin pour arrêter) : insert into articles values ("1","1",1,1,1)
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 1 1 1 1
Requête SQL (fin pour arrêter) : update articles set nom="2" where nom="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
1 2 1 1 1
Requête SQL (fin pour arrêter) : delete from articles where code="1"
Il y a eu 1 ligne(s) modifiée(s)
Requête SQL (fin pour arrêter) : select * from articles
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
a300 vélo 2500 10 5
b300 pompe 56 62 45
c300 arc 3500 10 20
d300 flèches - lot de 6 780 12 20
e300 combinaison de plongée 2800 34 7
f300 bouteilles d'oxygène 800 10 5
Requête SQL (fin pour arrêter) : select * from articles order by nom asc
----------------------------------------
code,nom,prix,stock_actuel,stock_minimum
----------------------------------------
c300 arc 3500 10 20
f300 bouteilles d'oxygène 800 10 5
e300 combinaison de plongée 2800 34 7
d300 flèches - lot de 6 780 12 20
b300 pompe 56 62 45
a300 vélo 2500 10 5
Requête SQL (fin pour arrêter) : fin
Програма така:
' параметри
Option Explicit On
Option Strict On
' простори імен
Imports System
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Collections
Imports Microsoft.VisualBasic
Module db3
Public Sub Main(ByVal args() As String)
' консольний додаток
' виконує запити SQL, введені з клавіатури, у
' таблиці ARTICLES бази даних DSN, ім’я якої передається як параметр
Const syntaxe As String = "syntaxe : pg dsnArticles"
' перевірка параметрів
' чи є 2 параметри
If args.Length <> 1 Then
' повідомлення про помилку
Console.Error.WriteLine(syntaxe)
' кінець
Environment.Exit(1)
End If 'if
' отримано параметр
Dim dsnArticles As String = args(0)
' рядок підключення до бази даних
Dim connectString As String = "DSN=" + dsnArticles + ";"
' підготовка з'єднання з базою даних
Dim articlesConn As OdbcConnection = Nothing
Dim sqlCommand As OdbcCommand = Nothing
Try
' спроба доступу до бази даних
articlesConn = New OdbcConnection(connectString)
articlesConn.Open()
' створено об’єкт команди
sqlCommand = New OdbcCommand("", articlesConn)
'try
Catch ex As Exception
' повідомлення про помилку
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
' звільнення ресурсів
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(2)
End Try 'catch
' будується словник прийнятних SQL-команд
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
' читання та виконання команд SQL, введених з клавіатури
Dim requête As String = Nothing ' texte de la requête SQL
Dim champs() As String ' les champs de la requête
Dim modèle As New Regex("\s+")
' цикл введення та виконання команд SQL, введених з клавіатури
While True
' на початку немає помилок
Dim erreur As Boolean = False
' запит
Console.Out.Write(ControlChars.Lf + "Requête SQL (fin pour arrêter) : ")
requête = Console.In.ReadLine().Trim().ToLower()
' закінчено?
If requête = "fin" Then
Exit While
End If
' розбиття запиту на поля
champs = modèle.Split(requête)
' запит дійсний?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' повідомлення про помилку
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' наступний запит
erreur = True
End If
If Not erreur Then
' підготовка об'єкта Command для виконання запиту
sqlCommand.CommandText = requête
' виконання запиту
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If
Catch ex As Exception
' повідомлення про помилку
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
End If
End While
' звільнення ресурсів
Try
articlesConn.Close()
Catch
End Try
Environment.Exit(0)
End Sub
' виконання запиту на оновлення
Sub executeUpdate(ByVal sqlCommand As OdbcCommand)
' виконує sqlCommand, запит на оновлення
Dim nbLignes As Integer = sqlCommand.ExecuteNonQuery()
' відображення
Console.Out.WriteLine(("Il y a eu " & nbLignes & " ligne(s) modifiée(s)"))
End Sub
' виконання запиту Select
Sub executeSelect(ByVal sqlCommand As OdbcCommand)
' виконує sqlCommand, запит SELECT
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Обробка отриманої таблиці
' виведення стовпців
Dim ligne As String = ""
Dim i As Integer
For i = 0 To (myReader.FieldCount - 1) - 1
ligne += myReader.GetName(i) + ","
Next i
ligne += myReader.GetName(i)
Console.Out.WriteLine((ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf + ligne + ControlChars.Lf + "".PadLeft(ligne.Length, "-"c) + ControlChars.Lf))
' виведення даних
While myReader.Read()
' обробка поточного рядка
ligne = ""
For i = 0 To myReader.FieldCount - 1
ligne += myReader(i).ToString + " "
Next i
' Виведення
Console.WriteLine(ligne)
End While
' звільнення ресурсів
myReader.Close()
End Sub
End Module
Тут ми коментуємо лише те, що є новим порівняно з попередньою програмою:
- Ми створюємо словник прийнятних SQL-команд:
' створення словника прийнятних SQL-команд
Dim commandesSQL() As String = {"select", "insert", "update", "delete"}
Dim dicoCommandes As New Hashtable
Dim i As Integer
For i = 0 To commandesSQL.Length - 1
dicoCommandes.Add(commandesSQL(i), True)
Next i
що дозволяє нам потім легко перевірити, чи перше слово (поле [0]) введеного запиту є однією з чотирьох допустимих команд:
' запит дійсний?
If champs.Length = 0 Or Not dicoCommandes.ContainsKey(champs(0)) Then
' повідомлення про помилку
Console.Error.WriteLine("Requête invalide. Utilisez select, insert, update, delete")
' наступний запит
erreur = True
End If 'if
- Раніше запит було розбито на поля за допомогою методу Split класу RegEx:
Dim modèle As New Regex("\s+")
....
' розбиття запиту на поля
champs = modèle.Split(requête)
Слова, що складають запит, можуть бути розділені будь-якою кількістю пробілів.
- Виконання запиту select відбувається не так само, як виконання запиту на оновлення (insert, update, delete). Тому необхідно провести тестування та виконати різні функції для кожного з цих двох випадків:
' підготовка об’єкта Command для виконання запиту
sqlCommand.CommandText = requête
' виконання запиту
Try
If champs(0) = "select" Then
executeSelect(sqlCommand)
Else
executeUpdate(sqlCommand)
End If 'try
Catch ex As Exception
' повідомлення про помилку
Console.Error.WriteLine(("Erreur d'exploitation de la base de données (" + ex.Message + ")"))
End Try
Виконання запиту SQL може спричинити виняток, який тут обробляється.
- Функція executeSelect повторює все, що було розглянуто в попередніх прикладах.
- Функція executeUpdate використовує метод ExecuteNonQuery класу OdbcCommand, який повертає кількість рядків, на які впливає команда.
7.3.5. IMPOTS
Ми використовуємо об’єкт impôt, створений у попередньому розділі:
' параметри
Option Strict On
Option Explicit On
' простори імен
Imports System
Public Class impôt
' дані, необхідні для розрахунку податку
' походять із зовнішнього джерела
Private limites(), coeffR(), coeffN() As Decimal
' виробник
Public Sub New(ByVal LIMITES() As Decimal, ByVal COEFFR() As Decimal, ByVal COEFFN() As Decimal)
' перевіряється, чи всі 3 таблиці мають однаковий розмір
Dim OK As Boolean = LIMITES.Length = COEFFR.Length And LIMITES.Length = COEFFN.Length
If Not OK Then
Throw New Exception("Les 3 tableaux fournis n'ont pas la même taille(" & LIMITES.Length & "," & COEFFR.Length & "," & COEFFN.Length & ")")
End If
' все гаразд
Me.limites = LIMITES
Me.coeffR = COEFFR
Me.coeffN = COEFFN
End Sub
' розрахунок податку
Public Function calculer(ByVal marié As Boolean, ByVal nbEnfants As Integer, ByVal salaire As Integer) As Long
' розрахунок кількості часток
Dim nbParts As Decimal
If marié Then
nbParts = CDec(nbEnfants) / 2 + 2
Else
nbParts = CDec(nbEnfants) / 2 + 1
End If
If nbEnfants >= 3 Then
nbParts += 0.5D
End If
' розрахунок оподатковуваного доходу та сімейного коефіцієнта
Dim revenu As Decimal = 0.72D * salaire
Dim QF As Decimal = revenu / nbParts
' розрахунок податку
limites((limites.Length - 1)) = QF + 1
Dim i As Integer = 0
While QF > limites(i)
i += 1
End While
' повернення результату
Return CLng(revenu * coeffR(i) - nbParts * coeffN(i))
End Function
End Class
Додамо до нього новий конструктор, що дозволяє ініціалізувати масиви limites, coeffR, coeffN на основі бази даних ODBC:
Imports System.Data
Imports Microsoft.Data.Odbc
Imports System.Collections
...
' конструктор 2
Public Sub New(ByVal DSNimpots As String, ByVal Timpots As String, ByVal colLimites As String, ByVal colCoeffR As String, ByVal colCoeffN As String)
' ініціалізує три таблиці обмежень: coeffR, coeffN на основі
' вмісту таблиці Timpots з бази даних ODBC DSNimpots
' colLimites, colCoeffR, colCoeffN — це три стовпці цієї таблиці
' може викликати виняток
Dim connectString As String = "DSN=" + DSNimpots + ";" ' chaîne de connexion à la base
Dim impotsConn As OdbcConnection = Nothing ' la connexion
Dim sqlCommand As OdbcCommand = Nothing ' la commande SQL
' запит SELECT
Dim selectCommand As String = "select " + colLimites + "," + colCoeffR + "," + colCoeffN + " from " + Timpots
'— таблиці для отримання даних
Dim tLimites As New ArrayList
Dim tCoeffR As New ArrayList
Dim tCoeffN As New ArrayList
' здійснюється спроба доступу до бази даних
impotsConn = New OdbcConnection(connectString)
impotsConn.Open()
' створюється об’єкт команди
sqlCommand = New OdbcCommand(selectCommand, impotsConn)
' виконується запит
Dim myReader As OdbcDataReader = sqlCommand.ExecuteReader()
' Обробка отриманої таблиці
While myReader.Read()
' дані поточного рядка заносяться в масиви
tLimites.Add(myReader(colLimites))
tCoeffR.Add(myReader(colCoeffR))
tCoeffN.Add(myReader(colCoeffN))
End While
' звільнення ресурсів
myReader.Close()
impotsConn.Close()
' динамічні масиви записуються у статичні масиви
Me.limites = New Decimal(tLimites.Count) {}
Me.coeffR = New Decimal(tLimites.Count) {}
Me.coeffN = New Decimal(tLimites.Count) {}
Dim i As Integer
For i = 0 To tLimites.Count - 1
limites(i) = Decimal.Parse(tLimites(i).ToString())
coeffR(i) = Decimal.Parse(tCoeffR(i).ToString())
coeffN(i) = Decimal.Parse(tCoeffN(i).ToString())
Next i
End Sub
Програма тестування працює наступним чином: вона отримує в якості аргументів параметри, які потрібно передати конструктору класу impôt. Після створення об’єкта impôt вона виконує розрахунки податку до сплати:
Option Explicit On
Option Strict On
' простори імен
Imports System
Imports Microsoft.VisualBasic
' тестова сторінка
Module testimpots
Sub Main(ByVal arguments() As String)
' інтерактивна програма розрахунку податку
' користувач вводить три значення з клавіатури: одружений nbEnfants заробітна плата
' після чого програма відображає суму податку до сплати
Const syntaxe1 As String = "pg DSNimpots tabImpots colLimites colCoeffR colCoeffN"
Const syntaxe2 As String = "syntaxe : marié nbEnfants salaire" + ControlChars.Lf + "marié : o pour marié, n pour non marié" + ControlChars.Lf + "nbEnfants : nombre d'enfants" + ControlChars.Lf + "salaire : salaire annuel en F"
' перевірка параметрів програми
If arguments.Length <> 5 Then
' повідомлення про помилку
Console.Error.WriteLine(syntaxe1)
' кінець
Environment.Exit(1)
End If 'if
' отримуємо аргументи
Dim DSNimpots As String = arguments(0)
Dim tabImpots As String = arguments(1)
Dim colLimites As String = arguments(2)
Dim colCoeffR As String = arguments(3)
Dim colCoeffN As String = arguments(4)
' створення об’єкта податку
Dim objImpôt As impôt = Nothing
Try
objImpôt = New impôt(DSNimpots, tabImpots, colLimites, colCoeffR, colCoeffN)
Catch ex As Exception
Console.Error.WriteLine(("L'erreur suivante s'est produite : " + ex.Message))
Environment.Exit(2)
End Try
' нескінченний цикл
While True
' спочатку помилок немає
Dim erreur As Boolean = False
' запитуються параметри розрахунку податку
Console.Out.Write("Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :")
Dim paramètres As String = Console.In.ReadLine().Trim()
' що робити?
If paramètres Is Nothing Or paramètres = "" Then
Exit While
End If
' перевірка кількості аргументів у введеному рядку
Dim args As String() = paramètres.Split(Nothing)
Dim nbParamètres As Integer = args.Length
If nbParamètres <> 3 Then
Console.Error.WriteLine(syntaxe2)
erreur = True
End If
Dim marié As String
Dim nbEnfants As Integer
Dim salaire As Integer
If Not erreur Then
' перевірка правильності параметрів
' одружений
marié = args(0).ToLower()
If marié <> "o" And marié <> "n" Then
Console.Error.WriteLine((syntaxe2 + ControlChars.Lf + "Argument marié incorrect : tapez o ou n"))
erreur = True
End If
' nbEnfants
nbEnfants = 0
Try
nbEnfants = Integer.Parse(args(1))
If nbEnfants < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument nbEnfants incorrect : tapez un entier positif ou nul")
erreur = True
End Try
' зарплата
salaire = 0
Try
salaire = Integer.Parse(args(2))
If salaire < 0 Then
Throw New Exception
End If
Catch
Console.Error.WriteLine(syntaxe2 + "\nArgument salaire incorrect : tapez un entier positif ou nul")
erreur = True
End Try
End If
If Not erreur Then
' параметри правильні — розраховується податок
Console.Out.WriteLine(("impôt=" & objImpôt.calculer(marié = "o", nbEnfants, salaire).ToString + " F"))
End If
End While
End Sub
End Module
Використовується база даних MySQL з іменем DSN mysql-impots:
C:\mysql\bin>mysql --database=impots --user=admimpots --password=mdpimpots
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.49-max-debug
Type 'help' for help.
mysql> show tables;
+------------------+
| Tables_in_impots |
+------------------+
| timpots |
+------------------+
mysql> select * from timpots;
+---------+--------+---------+
| limites | coeffR | coeffN |
+---------+--------+---------+
| 12620 | 0 | 0 |
| 13190 | 0.05 | 631 |
| 15640 | 0.1 | 1290.5 |
| 24740 | 0.15 | 2072.5 |
| 31810 | 0.2 | 3309.5 |
| 39970 | 0.25 | 4900 |
| 48360 | 0.3 | 6898 |
| 55790 | 0.35 | 9316.5 |
| 92970 | 0.4 | 12106 |
| 127860 | 0.45 | 16754 |
| 151250 | 0.5 | 23147.5 |
| 172040 | 0.55 | 30710 |
| 195000 | 0.6 | 39312 |
| 0 | 0.65 | 49062 |
+---------+--------+---------+
Виконання тестової програми дає такі результати:
dos>D:\data\devel\vbnet\poly\chap6\impots>vbc /r:system.data.dll /r:microsoft.data.odbc.dll /r:system.dll /t:library impots.vb
dos>vbc /r:impots.dll testimpots.vb
dos>test mysql-impots timpots limites coeffr coeffn
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 2 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 2 200000
impôt=33388 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :o 3 200000
impôt=16400 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 300000
impôt=50082 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :n 3 200000
impôt=22506 F
Paramètres du calcul de l'impôt au format marié nbEnfants salaire ou rien pour arrêter :