Робота з базами даних в Visual Basic
База даних - це сукупність даних, яка призначена для машинної обробки і служить для задоволення потреб багатьох користувачів.
Зазвичай під базою даних (БД) розуміється деяким чином впорядкований набір уніфікованої інформації. Припустимо у нас є телефонна книга (це, до речі, класичний приклад), в якій знаходиться інформація про всіх абонентів телефонної станції. Природно, в цьому випадку інформація буде представлена у вигляді регулярної структури, наприклад, за допомогою таблиці.
Кожен рядок такої таблиці називається записом (record). Кожен стовпець називається полем (Field). На перетині рядків і стовпців розташовані елементи даних, в яких, власне, і зберігається інформація.
База даних - це сукупність даних, яка призначена для машинної обробки і служить для задоволення потреб багатьох користувачів.
При роботі з такими таблицями часто виникають питання, связвнниє з пошуком інформації, її сортуванням і так далі Ці питання розглянуті в даному розділі.
Робота з БД через елемент Data
Елемент управління Data в Visual Basic забезпечує можливість формувати могутні і різноманітні застосування бази даних з дуже невеликим об'ємом програмування.
Доступ до даним за допомогою елементу управління Data
Елемент управління Data реалізує доступ до даним з використанням процесора бази даних Microsoft Jet. Ця технологія забезпечує доступ до баз даних багатьох форматів.
Процес створення застосування бази даних із застосуванням елементу управління Data включає наступні два кроки:
1. Додати елемент управління Data до форми і встановити властивості, що специфікують базу даних і таблицю, з якої відбиратимуться дані.
2. Додати до форми зв'язані елементи управління і встановити їх властивості, щоб пов'язати їх з елементом управління Data; після цього в них можуть відображатися дані.
Використання зв'язаних елементів управління
Після розміщення зв'язаного елементу управління у формі і установки його властивостей в цьому елементі управління автоматично відображаються дані з бази даних. Якщо користувач змінює дані в зв'язаному елементі управління, ці зміни автоматично фіксуються в базі даних, коли користувач переміщається на інший запис. З даними може бути зв'язаний ряд вбудованих елементів управління Visual Basic, включаючи прапорець (Checkbox), образ (Image), напис (Label), зображення (Picturebox), текстове поле (Textbox), простій список (Listbox), поле із списком (Combobox) і OLE-контейнер.
Деякі властивості і методи елементу управління Data
Властивість Connect
Властивість Connect визначає тип бази даних, яку потрібно відкрити. Ця властивість може включати такі параметри домена захисту, як ідентифікатор користувача і пароль.
Властивість Exclusive
Властивість Exclusive визначає, чи отримає користувач виняткове право доступу до бази даних. Якщо властивість Exclusive встановлена в True і потім користувачі успішно відкриває базу даних, ніяке інше застосування не зможе відкрити базу даних, поки вона не буде закрита даним користувачем.
Властивість Readonly
Властивість Readonly визначає, чи може користувач модифікувати базу даних. Якщо модифікувати базу даних не планується, ефективніше буде встановити властивість Readonly в True.
Властивість Recordset
Властивість Recordset - об'єкт, який містить набір записів, повернених елементом управління Data. З цією властивістю пов'язані властивості і методи, які можна використовувати при роботі з поверненими записами.
Властивості Bofaction і Eofaction
Ці властивості визначають, яку дію повинен виконати елемент управління Data, коли властивості BOF або EOF набору записів приймають значення True.
Наприклад, якщо властивість Eofaction елементу управління Data встановлена в vbactionaddnew і елементі управління Data використовується для переміщення нижче останнього запису набору записів, буде автоматично виконаний метод Addnew, що дозволяє вводити новий запис.
Метод Refresh
Метод Refresh регенерує об'єкт Recordset. Якщо в період виконання змінюється властивість Recordsource, необхідно викликати метод Refresh, щоб відновити набір записів. У наступному фрагменті показано, як використовувати метод Refresh:
Data1.RecordSource = "SELECT * FROM Employees " & "WHERE [Employee ID] = " & txtEmpID.text
Data1.Refresh
Об'єкт Recordset
У застосуванні бази даних користувачі переміщаються по записах бази даних, використовуючи кнопки елементу управління Data. Нижче показано, що "ховається" за елементам управління Data і його кнопками
Що таке Recordset?
Об'єкт Recordset - весь набір записів, до яких звертається елемент управління Data. Набір записів зберігається в пам'яті, у разі потреби вивантажуючись на диск.
Для маніпулювання набором записів використовується властивість Recordset елементу управління Data. Один запис набору Recordset є поточним. Інформація з поточного запису відображається в зв'язаних елементах управління. Позицію поточного запису можна змінити, клацнувши кнопку елементу управління Data або програмно в коді за допомогою методів об'єкту Recordset.
Визначення меж набору записів
Якщо для зміни позиції поточного запису використовується програмний код, необхідно визначити початок і кінець набору записів, контролюючи властивості EOF і BOF об'єкту Recordset. При переміщенні до запису EOF або BOF виконується дія, встановлена значенням властивості Bofaction або Eofaction.
Наприклад, можна встановити Eofaction так, щоб автоматично додавався новий запис. Якщо властивість Eofaction встановлена до EOF, то при переміщенні до запису EOF ніякої дії виконуватися не буде. У разі ж переміщення на один запис до BOF або після EOF повертається помилка виконання.
Використовувати об'єкт Recordset певного елементу управління Data в програмному коді можна через властивість Recordset елементу управління Data, як показано в наступному фрагменті:
Data1.Recordset.MoveNext 'Перемещение на следующую запись набора
If Data1.Recordset.EOF Then Data1.Recordset.MoveLast
Властивості і методи об'єкту Recordset
Для відбору інформації з набору записів використовуються властивості і методи об'єкту Recordset. Вони дозволяють переміщатися по записах, додавати, модифікувати або видаляти записи.
Властивості BOF і EOF
Властивості BOF і EOF об'єкту Recordset указують, чи встановлена позиція поточного запису в наборі перед першим записом або після останнього запису. Якщо в наборі записів немає ніяких записів, то і властивість BOF, і властивість EOF - True.
Метод Addnew
Додати новий запис до набору записів можна методом Addnew. При виконанні методу Addnew Visual Basic очищає зв'язані елементи управління і встановлює для властивості Editmode елементу управління Data значення dbeditadd.
Новий запис не буде доданий до бази даних, поки не буде явно виконаний метод Updaterecord або Update або поки користувач не переміститься на інший запис.
У наступному фрагменті показано, як додати новий запис до набору:
Data1.Recordset.AddNew
Метод Updaterecord елементу управління Data
Для збереження поточного запису в базі даних використовується метод Updaterecord. У наступному фрагменті база даних модифікується збереженням значень поточного запису:
Datal.UpdateRecord
Метод Cancelupdate елементу управління Data
Щоб відмінити метод Addnew або Edit і відновити зв'язані елементи управління даними з поточного набору записів, можна використовувати метод Cancelupdate.
Наприклад, якщо користувач змінив поля на формі, але ще не модифікував їх і вирішив відмінити зміни, метод Cancelupdate відмінить операцію, відновить поля оригінальними даними з набору записів і виведе на екран поточний запис.
Нижче показано, як відмінити додавання або редагування запису:
Datal.CancelUpdate
Метод Delete
Для видалення запису з бази даних використовується метод Delete. Видалений запис залишається поточним, поки користувач не переміщається на інший запис, як показано в наступному коді:
Data1.Recordset.Delete Data1.Recordset.MoveNext If Data1.Recordset.EOF Then Data1.Recordset.MoveLast End If
Події елементу Data
Елемент управління Data підтримує три події, які можна використовувати для розширення застосування бази даних: Validate, Reposition і Error. Ці події дозволяють перевизначати деякі моменти умолчательного поведінки елементу управління Data.
Елемент управління Data підтримує три події, які можна використовувати для розширення застосування бази даних: Validate, Reposition і Error. Ці події дозволяють перевизначати деякі моменти умолчательного поведінки елементу управління Data.
Подія Validate
Щоб перевірити дані до того, як запис буде збережений в базі даних, можна використовувати подію Validate. Ця подія відбувається безпосередньо перед тим, як Visual Basic переносить зміни із зв'язаних елементів управління в базу даних і переміщає покажчик поточного запису на інший запис бази даних.
Подію Validate можна використовувати для виводу користувачам запиту на підтвердження змін.
Оголошення процедури події Validate має наступний синтаксис:
Private Sub Data1_validate(Action As Integer, Save As Integer)
Параметр Action
Параметр Action указує операцію, яка ініціювала подію Validate. Подія Validate може відбутися в результаті однієї з наступних операцій:
метод Movefirst, Moveprevious, Movenext, Movelast;
метод Addnew;
метод Update;
метод Delete;
метод Find;
установка властивості Bookmark;
закриття бази даних;
визгрузка форми.
Щоб відмінити ці дії, потрібно встановити для параметра Action значення vbdataactioncancel.
Параметр Save
Параметр Save вказує чи змінені зв'язані дані. Якщо Save — True, зв'язані дані були змінені. Щоб відмінити збереження, можна встановити Save в False. У наступному фрагменті коду у користувача запрошується підтвердження змін в базі даних. Якщо користувач відповідає Немає, зміни відміняються.
Private Sub Data1_Validate(Action As Integer, Save As Integer)
Dim iResponse As Integer
If Save = True Then
iResponse = MsgBox ("Сохранить изменения?", vbYesNo)
If iResponse = vbNo Then
Save = False
Save = False
Data1.UpdateControls 'Восстановить значения полей
End If
End If
End Sub
Подія Reposition
Подія Reposition використовується для зміни виду форми або виконання деяких дій при переміщенні до нового запису.
Ця подія відбувається, коли Visual Basic переміщає покажчик поточного запису на інший запис в базі даних або при первинному відкритті бази даних.
У наступному фрагменті показано, як вивести на екран номер поточного запису з використанням властивості Absoluteposition об'єкту Recordset:
Private Sub Data1_Reposition()
Data1.Caption = "Запись № " & Data1.Recordset.AbsolutePosition + 1
End Sub
Коли користувач переміщається на новий запис, використовуючи елемент управління Data, може потрібно відображати дані у форми по-різному, залежно від конкретних значень поточного запису.
Наприклад, у формі для роботи із записами службовців можуть по-різному відображатися дані для постійних службовців, підрядчиків, службовців з відрядною платнею або службовців з окладом. Щоб задіювати вибір правильного режиму відображення для кожного запису, можна помістити відповідний код в подію Reposition.
У наступному фрагменті подія Reposition використовується для зміни стану форми:
Private Sub Data1_Reposition()
Data1.Caption = "Запись № " & Data1.Recordset.AbsolutePosition + 1
End Sub
Коли користувач переміщається на новий запис, використовуючи елемент управління Data, може потрібно відображати дані у форми по-різному, залежно від конкретних значень поточного запису.
Наприклад, у формі для роботи із записами службовців можуть по-різному відображатися дані для постійних службовців, підрядчиків, службовців з відрядною платнею або службовців з окладом. Щоб задіювати вибір правильного режиму відображення для кожного запису, можна помістити відповідний код в подію Reposition.
У наступному фрагменті подія Reposition використовується для зміни стану форми:
Private Sub Data1_Reposition()
Data1.Caption = Datal.Recordset.AbsolutePosition
If Data1.Recordset("EmployeeID") > 5 Then 'Если № служащего > 5
Option1.Value = True 'Служащий - руководитель
Else
Option2.Value = True 'Иначе, служащий - подчиненный
End If
Data1.Caption = "Запись № " & Data1.Recordset.AbsolutePosition + 1
End Sub
Подія Error
Подія Error виникає, коли користувач взаємодіє з елементом управління Data і відбувається помилка доступу до даним. Для додавання до елементу управління Data обробки помилок можна використовувати подію Error.
Наприклад, якщо користувач змінює поле і потім клацає кнопку елементу управління Data, щоб переміститися до наступного запису, елемент управління Data модифікує поточний запис. Якщо протягом модифікації має місце помилка доступу до даним, відбувається подія Error.
Після того, як відбулася помилка, значення в зв'язаних полях не змінюватимуться. Користувач може виправити значення і потім клацнути елемент управління Data, щоб спробувати модифікувати запис знову.
Вивід на екран повідомлення про помилку
Якщо до події Error не доданий код обробки помилок і, коли користувач взаємодіє з елементом управління Data, повертається помилка, Visual Basic виведе на екран повідомлення про помилку і продовжить виконання застосування.
Якщо потрібно подавити виведення стандартного повідомлення про помилку, можна встановити параметр Response процедури Еггог() у нуль і виводити на екран призначене для користувача повідомлення про помилку, як показано в наступному фрагменті:
SQL запити
Всі операції з даними в БД виконуються за допомогою операторів SQL (SQL statement). Якщо працювати через елемент Data, то для виконання оператора SQL необхідно привласнити властивості Recordsource рядок, що містить запит і потім відновити БД.
Ось список основних операторів SQL:
SELECT і FROM
Оператор SELECT використовується для витягання інформації з БД. Він складається з директиви SELECT за яким через кому перераховуються поля, з яких повинна бути виведена інформація. Після них обов'язково слідує оператор FROM, який указує на таблицю в БД. Наприклад: "SELECT Телефон, Прізвище, Код FROM Телефони".
В даному прикладі на екран будуть виведений 3 стовпці в порядку, вказаному в запиті. Тут і далі можна вживати замість точних назв зірочку. Наприклад "SELECT Телефон, Прізвище, Код FROM *".
ORDER BY
Для впорядковування даних в стовпці по возврастанію або убуванню використовується опрератор ORDER BY. У наступному прикладі відбувається сортування за збільшенням в стовпці Прізвище: "SELECT * FROM Телефони ORDER BY [Прізвище] ASC" або замість ASC поставити DESC (тоді по убуванню).
DISTINCT
Щоб виключити дублюючі значення в стовпці, використовується оператор DISTINCT. Наприклад: "SELECT DISTINCT Прізвище FROM Телефони".
Булеві оператори
У SQL допустимі стандартні булеві оператори, такі як =, >, <, <>, <=, >=. Також можна використовувати комбінації цих операторів через AND, OR або XOR. Приклад: "SELECT * FROM Телефони WHERE ([ID] < 3000) AND ([Прізвище] LIKE 'Вол*')".
IN
Цей оператор дозволяє використовувати множину як умову відбору значень. Наприклад, наступний запит виведе абонентів, у яких номери телефонів 2-22-22 і 2-27-22.
"SELECT * FROM Телефони WHERE [Телефон] IN ('2-22-22','2-27-22')".
BETWEEN
На відміну від попереднього оператора, цей дозволяє вказати діапазон значень для вибору. У наступному прикладі будуть виведені всі записи, в яких поле ID
більше 2000 але менше 5000.
"SELECT * FROM Телефони WHERE [ID] BETWEEN 2000 AND 5000".
LIKE
Цей оператор застосовується виключно до полів строкового типу. Він дозволяє вибрати записи по вказаній масці. Наприклад: "SELECT * FROM Телефони WHERE [Телефон] LIKE '2-2*'".
IS NULL
За допомогою цього оператора можна відобразити записи, які містять порожні поля. У наступному прикладі створюється новий запис, в якому поле ID не задане і оператор SQL виведе на екран цей запис
Data1.Recordset.AddNew
Data1.Recordset.AddNew
Data1.Recordset![Прізвище] = "Новий абонент"
Data1.Recordset![Телефон] = "2-15-65"
Data1.Recordset.Update
Data1.RecordSource = "SELECT * FROM Телефони WHERE [ID] IS NULL"
Data1.Refresh
Агрегатні функції
Існують декілька функцій для роботи над групою вибраних полів:
COUNT - підрахунок рядків, отриманих в ході запиту;
· COUNT - підрахунок рядків, отриманих в ході запиту;
· SUM - сума значень вибраного поля;
· AVG - середнє арифметичне поля;
· MAX - виводить найбільше значення з поля; ·
MIN - виводить найменше значення з поля;
Приклад: "SELECT SUM(ID), MAX(ID), COUNT(Прізвище) FROM Телефони".
GROUP BY
Цей оператор використовується для угрупування декілька полів. Приклад: "SELECT Телефон, COUNT(ID) FROM Телефони GROUP BY Телефон".
AS
Щоб змінити ім'я поля використовується оператор AS. У наступному прикладі змінюється ім'я поля ID на ім'я Ідентифікатор: "SELECT Код, Прізвище, ID AS [Ідентифікатор], Телефон FROM Телефони".
Додавання виразів
У результат запиту можна додавати арифметичні вирази. У них допустиме використання операцій +, - *, /. Наприклад, для того, щоб вивести таблицю з подвоєним полем Id потрібно: "SELECT Код, Прізвище, Id * 10, Телефон FROM Телефони".
