Лекция № 19

Тема: «Работа с базами данных»

План

1. Создание соединения с базой данных

2. Работа с таблицей базы данных

3. Создание форм для работы с данными БД

4. Методы и свойства для работы с данными в таблице

1. Создание соединения с базой данных

Для доступа к базе данных в Delphi есть несколько способов. Каждый из способов использует свои компоненты, размещенные на различных закладках. Самой распространенной является технология ADO. Данная технология обеспечивает возможность подключения как локальных, так и сетевых баз данных. Отличается легкостью и простотой работы, поддерживает все распространенные форматы баз данных, позволяет публиковать данные в Интернет и т.п.

Все компоненты, необходимые для работы с данными находятся на вкладке ADO.

Для работы с базой данных необходимо выполнить подключение к физической базы данных, расположенной на диске компьютера или в сети. Для этого служит компонент ADOConnection, который при запуске программы не видим на форме. Для установки соединения с БД нанесите на форму компонентов ADOConnection (ADO). В его свойства ConnectionString щелкните на кнопке с тремя точками. Откроется окно настройки параметров соединения. В этом окне нажмите кнопку «Build» для открытия окна ввода параметров подключения к БД.

Для подключения базы Access укажите тип базы данных «Microsoft Jet» и щелкните «Далее», на следующей закладке укажите файл базы данных (после выбора файла рекомендуется удалить путь к файлу БД при условии, что БД находится в одной папке с проектом). С помощью кнопки «Проверить подключение» можно проверить соединение. Если БД Access имеет пароль, то при нажатии на кнопку «Проверить подключение» будет выдано окно с сообщением о неверном пароле. Для задания пароля перейдите на закладку «Все» и в строке «Jet OLEDB: Database Password» введите пароль доступа.

Для подключения базы SQL Server тип базы данных «Microsoft OLE DB Provider for SQL Server» и нажмите «Далее», на следующей закладке укажите имя сервера, в качестве значения для переключателя «Для входа на сервер использовать" выберите "Учетные сведения Windows NT» и уажите имя пользователя и пароль, из списка выберите базу данных, размещенную на сервере. С помощью кнопки «Проверить подключение» можно проверить соединение.

Компонент ADOConnection при запуске программы всегда отображает окно для регистрации пользователя: надо ввести логин и пароль. Если этого делать не нужно, то измените свойство LoginPrompt компонента на значение false. Это отключит появление окна регистрации.

2. Работа с таблицей базы данных

Каждая база данных имеет как минимум одну таблицу, а в реальных программах таблиц обычно более одной. После соединения с базой данных с помощью компонента ADOConnection мы должны указать имя таблицы, с которой будет работать текущая форма. Для доступа к таблице базы данных используйте компонент ADOTable (ADO). Данный компонент не виден на форме во время работы программы. Его основные свойства:

- Connection - указывается имя соединения с базой данных;

- TableName - имя таблицы БД;

- Active - если ровно true, то таблица подключена к программе и ее данные доступны для просмотра и редактирования. Данное свойство не рекомендуется устанавливать при проектировании, подключение таблицы следует проводить при запуске программы.

Для подключения нужной таблицы в события OnCreate соответствующей формы необходимо вставить команду:

ADOTable1.Active:=true;

При удалении формы из памяти необходимо отключать таблицу. Для этого в события OnDestroy формы необходимо вставить команду:

ADOTable1.Active:=false;

Работа с полями таблицы

По умолчанию на форме отображаются все поля таблицы. При этом заголовок поля и его ширина соотвествуют значениям, которые были указаны при проектировании таблицы в СУБД. Это не всегда удобно и можно данные параметры менять.

Для работы с полями таблицы выполните двойной щелчок на компонента ADOTable. В результате откроется окно редактора полей таблицы. Щелкните в этом окне правой кнопкой мыши и выберите команду «ADD ALL FIELDS». В окне редактора полей отобразятся все поля той таблицы, которая связана с текущим компонентом ADOTable.

Поля в списке можно перемещать, меняя тем самым их порядок отражения на форме.

Выделив нужное поле можно для него задать следующие свойства:

· Alignment - выравнивание текста;

· DefaultExpression - значение по умолчанию;

· DisplayFormat - формат отображения данных. Используется для числовых полей и полей типа дата/время;

· DisplayLabel - заголовок поля, отображаемый на форме;

· DisplayWidth - ширина поля в символах;

· EditMask - маска ввода значений в поле;

· Name - имя поля для обращения к нему в коде программы. Рекомендуется, чтобы значение данного свойства совпадало с настоящим именем поля в таблице БД;

· ReadOnly - запрещает или разрешает изменение значения в поле;

· Visible - отображает или скрывает поле таблицы.

Для корректной работы рекомендуется всегда добавлять все поля, а затем ненужные скрывать от просмотра (Visible = false).

Обращение к значению нужного поля

Часто при написании кода программы нужно обратиться к значению конкретного поля текущей записи таблицы. Это значение может использоваться в расчетах, его можно проверить с помощью условия и т.п.

Для обращения к полю таблицы используется физическое имя поля в таблице БД или имя поля, указанное в свойстве Name редактора полей ADOTable.

По имени поля в таблице к нему можно обратиться за помощью записи:

ADOTable1.FieldByName('имя_поля’).Value

Пусть в таблице есть поле oklad, тогда обратиться к нему можно с помощью записи:

ADOTable1.FieldByName('oklad’).Value

Вместо свойства Value можно использовать свойство as тип:

ADOTable1.FieldByName('имя_поля’).AsТип

Тип - это тип данных, к которому автоматически конвертируется значение указанного поля таблицы.

Пусть в таблице есть поле oklad. Значение данного поля нужно получить как число для расчетов. Тогда обратиться к нему можно с помощью записи:

ADOTable1.FieldByName('oklad’).AsFloat

Пусть значение этого же поля нужно вывести в функции ShowMessage. То есть значение поля нужно из числового формата конвертировать в строчный. В этом случае запись команды будет иметь вид:

ShowMessage(ADOTable1.FieldByName('oklad'). AsString);

Все рассмотренные примеры обращаются к полю по его имени в таблице БД. Такая запись длинная. Обращение к полю можно записать короче, если обращаться к нему с помощью свойства Name. Пусть в таблице есть поле oklad, для которого через редактор полей задана свойство Name = ADOOklad. Для обращения к этому полю допустима запись:

ADOOklad.Value

Для конвертирования значение поля в нужный формат допустима запись:

ADOOklad.AsFloat - конвертирование в число

ADOOklad.AsString - конвертирование в текст

Создание вычисляемых полей

В редакторе полей можно создавать вычисляемые поля: поля, которых физически нет в таблице БД, но их значение автоматически рассчитываются на основании данных из других полей.

Для создания такого поля сделайте двойной щелчок на компоненте ADOTable для открытия окна редактора полей, в контекстном меню окна редактора выберите «New Field», укажите переключатель Calculated, имя и тип поля. Каждая таблица может иметь несколько вычисляемых полей.

Если в таблице есть вычисляемое поле, то для компонента ADOTable в событии OnCalcFields необходимо написать код, содержащий нужные формулы для получения значений таких полей. При этом обращение к значениям полей таблицы осуществляется с помощью команд, рассмотренных выше.

Пример: Пусть в таблице есть поле srbal для хранения среднего балла студента. Это поле в редакторе ADOTable имеет свойство name = ADOSrbal. Вычисляемое поле «стипендия» (stip) нужно рассчитать по формуле: средний балл * 50. При этом стипендию получают только студенты со средним баллом от 4 и выше.

Напишем код при обращении к полям таблицы по их именам в базе данных.

В событии OnCalcFields компонента ADOTable напишем код:

procedure TForm1.ADOTable1calcfields(DataSet:TDataSet)

begin

if ADOTable1.FieldByName('srbal').AsFloat> = 4 then

ADOTable1.FieldByName('stip’).Value:=ADOTable1.FieldByName('srbal’).AsFloat*50

else

ADOTable1.FieldByName('stip’).Value:=0;

end;

Напишем код при обращении к полям таблицы по их свойству Name в редакторе полей ADOTable.

В событии OnCalcFields компонента ADOTable напишем код:

procedure TForm1.ADOTable1CalcFields(DataSet:TDataSet)

begin

if ADOSrbal.AsFloat> = 4 then

Stip.value:=ADOSrbal.AsFloat*50

else

Stip.Value:=0;

end;

Ограничения значения поля

Для полей можно проверять условия на значения. Это позволит защитить программу от ввода недопустимых данных. Двойным щелчком на компоненте ADOTable откройте редактор полей, выделите нужное поле и в его события OnValidate напишите код, проверяющий его значение. Обращаться к значению поля можно одним из рассмотренных выше способов.

Пример. Пусть в таблице есть поле cena. Нужно запретить вводить цену менее 20. Запишем код через обращение к значению поля по его имени в таблице.

// если цена меньше 20

if ADOTable1.FieldByName('cena’).AsFloat<20 then

begin

// выводим сообщение об ошибке

Application.MessageBox(...)

// отменяем ввод

Abort;

end;

Пример. Пусть поле cena в редакторе полей ADOTable имеет свойство name = ADOCena. Нужно запретить вводить цену менее 20. Запишем код через обращение к значению поля по его имени в редакторе полей.

// если цена меньше 20

if ADOCena.AsFloat<20 then

begin

// выводим сообщение об ошибке

Application.MessageBox(...)

// отменяем ввод

Abort;

end;

3. Создание форм для работы с данными БД

После подключения нужной таблицы необходимо создать «промежуточное звено» - конвертер, которое приводит таблицы из разныз СУБД к единому формату Delphi для дальнейшей обработки. В качестве такого конвертера используется компонент DataSource (DataAccess), в свойства Dataset которого указывают имя компонента ADOTable, связанного с нужной таблицей БД.

После создания компонента DataSource можно приступать к проектированию внешнего вида формы. Все визуальные компоненты для работы с БД находятся на вкладке DataControls. Данная вкладка имеет множество компонентов, которые по внешнему виду и набору свойств полностью дублируют обычные визуальные компоненты. Но у этих компонентов есть два свойства, которыми они отличаются от своих обычных аналогов:

· DataSource - имя компонента DataSource, связанного с нужной таблицей ADOTable;

· DataFileld - имя поля таблицы, значение которого будут отображаться в данном компоненте.

Создание табличных форм

На табличных формах пользователь видит сразу множество записей таблицы БД. Если данные еще и отсортированы по нужному полю, то поиск нужной записи значительно облегчается и переход на нужную запись значительно ускоряется.

Для создания табличных форм используется компонент DbGrid (DataControls), который с помощью свойства DataSource связывается с компонентом DataSource (DataAccess), указывающий на нужную таблицу БД. В сетке автоматически отображаются все поля таблицы с теми параметрами, которые они получили при проектировании в СУБД. Для изменения количества, порядка и других параметров полей нужно открыть редактор полей ADOTable, добавить нужные поля и поменять их свойства.

Создание страничных форм

Страничная форма позволяет просматривать данные из таблицы по одной записи. Такая форма может содержать компоненты, которые максимально облегчают пользовательский ввода данных. Например, помимо текстовых полей можно использовать флажки, выпадающие списки, графические поля для просмотра графических изображений, алендари и т.п.

Так как страничная форма создается на основе таблицы, то для ее создания достаточно открыть двойным щелчком редактор полей компонента ADOTable, выделить нужные поля таблицы и перетащить их на страничную форму. В результате Delphi автоматически нанесет на форму нужные компоненты и задаст им свойства. В дальнейшем часть полей можно заменить на более подходящие.

Работа с графическими полями

Если в таблице предусмотрено хранение изображений, то для отображения таких данных используют компонент DbImage, а для заполнения данного компонента используют компонент OpenPictureDialog.

Пример: Пусть в компоненте DbImage отображается содержимое поле foto. Данный компонент заполняется двойным щелчком на нем. В событии OnDblClick компонента DbImage напишем код:

if OpenPictureDialog1.Execute then

begin

// переводим запись в режим редактирования

ADOTable1.Edit;

// загружаем в поле foto выбранное изображение

TBlobField(ADOTable1.FieldByName('foto')).LoadFromFile(OpenPictureDialog1.FileName)

end;

Компонент DbImage может работать только в BMP файлами. Для защиты программы от неверного ввода необходимо у компонента OpenPictureDialog в свойстве Filter удалить все типы файлов кроме BMP.

Работа с полями дата/время

Заполнять дату с клавиатуры в обычном текстовом поле нежелательно, так как, в случае ошибки ввода, данные не сохранятся и программа завершит работу. Для ввода дат рекомендуется использовать компонент DateTimePicker (Win32), который представляет собой выпадающий список с календарем. Нанесите на форму компонент DateTimePicker, сделайте его невидимым и укажите такой же размер, что и у текстового поля, связанного с полем дата/время..

При переходе в текстовое поле с датой будет отражать DateTimePicker. Для этого для поля в события OnEnter необходимо написать код:

// делаем поле с календарем видимым

DateTimePicker1.Visible:=true;

// если поле c датой не пустое, то загружаем в календарь дату, сохраненную в поле

if DBEdit1.Text <> '' then

DateTimePicker.Date:=StrToDate(DBEdit.Text);

При выходе из поля нужно скрыть календарь, а в текстовом поле показать выбранную дату. Для этого в события OnExit для DateTimePicker напишем такой код:

// делаем календарь невидимым

DateTimePicker.Visible:=false;

// отражаем в поле выбранную дату

DBEdit.Text:=DateToStr(DateTimePicker.Date);

После написания всех обработчиков разместите компонент DateTimePicker точно над текстовым полем с датой, чтобы календарь закрыл собой поле.

Создание полей-справочников

Если значение поля таблицы может быть заполнено значением из некоторого фиксированного набора (пол, месяцы года, дни недели, разряды и т.п.), то для таких полей целесообразно использовать компонент DBComboBox, в свойстве Items которого можно ввести перечень допустимых значений. Использование такого компонента с одной стороны освобождает пользователя от ввода данных с клавиатуры, а с другой защищает программу от ошибок ввода, опечаток и т.п.

Однако часто текстовые поля заполняются значениями из динамических наборов данных. Такие наборы могут пополняться новыми значениями, или из них могут удаляться существующие значения. В этом случае такой перечень значений может храниться в отдельной таблице-справочнике.

В базе данных создайте таблицу-справочник для хранения некоторого перечня допустимых значений поля. Дання таблица должна быть связана с исходной таблицей по ключевому полю.

Для создания списков на основании таблицы-справочника добавьте на форму компонент ADOTable и свяжите его с таблицей-справочником. Затем нанесите компонент DataSource и свяжите его с ADOTable таблицы-справочника.

Вначале поле-справочник создается в редакторе полей компонента ADOTable исходной таблицы. Двойным щелчком откройте редактор полей, в контекстном меню выберите команду New Field. В новом окне укажите имя и тип данных поля, обязательно укажите тип Lookup (выпадающий список). В нижней части окна укажите поля, по которым связана основная таблица с таблицей-справочником (KeyField и Lookup Keys), источник данных для заполнения выпадающего списка (DataSet), поле, отображаемое с писке (Result Field). В результате, в сетке на форме появится колонка с выпадающим списком для ввода нужных значений. Если же это поле перетащить из редактора полей на страничную форму, то будет автоматически создан выпажающий список-справочник на основе компонента DBLookupComboBox (DataControls).

Навигация по записям

Для выполнения переходов по записям в таблице и выполнения операций по редактированию данных, в Delphi имеется компонент DbNavigator (DataControls), который представляет собой панель с набором кнопок для выполнения ряда действий с данными. Для связывания этого компонента с нужной таблицей в его свойстве DataSource нужно указать имя компонента DataSource, связанного с нужной таблицей БД.

У компонента есть свойства VisibleButtons, с помощью которого можно управлять набором отображаемых кнопок на нем, а соответственно и набором функций, доступных для пользователя программы.

Если пользователь не понимает назначений какой-то кнопки на панели DbNavigator, то можно отобразить всплывающую подсказку. Для этого свойство ShowHint установите равным True, а в свойстве Hints переведите имеющиеся англоязычные подсказки на язык, понятный для пользователя. При этом,если кнопки не отображается на форме, ее подсказку удалять нельзя.

По умолчанию каждая кнопка выполняет некоторое действие, которое можно заменить. Особенно актуально это для кнопки удаления, которая выдает запрос на английском языке. Для замены действия используют событие BeforeAction компонента. Событие имеет параметр Button, с помощью которого можно распознать нажатую пользователем кнопкуу (может принимать значения: nbFirst, nbPrior, nbNext, nbLast, nbInsert, nbDelete, nbEdit, nbPost, nbCancel, nbRefresh).

Например, пусть кнопка «Добавить» добавляет запись в конец таблицы (по умолчанию она добавляет запись над текущей), а кнопка «Удалить» выдает запрос на и удаляет запись.

procedure TForm1.DBNavigator1BeforeAction(Sender:TObject; Button:TNavigateBtn)

begin

// Если нажата кнопка nbInsert

if Button = nbInsert then

begin

// добавляем запись в конец таблицы

ADOtable1.Append;

// отменяем стандартное действие кнопки

Abort;

end

// если нажата кнопка nbDelete

else if Button = nbDelete then

begin

// если нет записей, то конец

if ADOTable1.RecordCount = 0 then Exit;

// выдаем запрос на удаление

if Application.MessageBox ('Удалить запись?','Подтвердите', MB_IconQuestion + MB_YesNo)=IDYes then

// Удаляем запись

ADOTable1.Delete;

// отменяем стандартное действие кнопки

Abort;

end;

Комбинирование табличных и страничных форм

Табличные формы имеют недостатки, связанные с тем, что в сетке невозможно отобразить флажки, графические поля, списки для облегчения ввода данных. Поэтому в реальных программах часто комбинируют табличные и страничные формы для создания удобного интерфейса.

Для этого в сетке отражают только основной набор полей, а рядом с сеткой размещают визуальные компоненты для просмотра и редактирования других полей. И сетка и визуальные компоненты в свойства DataSource ссылаются на один и тот же компонент DataSource, связанный с нужной таблицей БД. Это позволяет при быстром и удобном перемещении по записям в сетке просматривать и редактировать поля текущей записи с помощью подходящих визуальных компонентов.

Другим вариантом может быть тот, при котором на одной форме в сетке отображается набор из основных полей, а просмотр и редактирование всей записи выполняется на вспомагательной страничном форме, которая вызывается при некоторой событии (например, добавление новой записи происходит при щелчке на кнопке, а редактирование записи происходит после двойного щелчка на нужной записи в сетке).

Создание ленточных форм

Ленточная форма позволяет отображать данные в виде сетки, в которой можно будет использовать все визуальные компоненты, доступные для страничных форм.

Для создания ленточных форм используется компонент DbCtrlGrid (DataControls). С помощью свойства DataSource ленточная форма связывается с компонентом DataSource, указывающим на нужную таблицу. Основными свойствами ленточной формы являются:

· DbColCount - число колонок;

· DbRowCount - число строк.

После добавления компонента DbCtrlGrid на его первой строке (не заштрихована) можно размещать любые визуальные компоненты для работы с полями таблицы. При запуске программы эти компоненты автоматически повторятся для каждой записи таблицы на других строках ленточной формы.

4. Методы и свойства для работы с данными в таблице

Компонент ADOTable имеет ряд методов, позволяющих выполнить навигацию или изменение данных в таблице.

Методы навигации

Методы навигации позволяют выполнять переходы по записям. К таким методам относятся:

ADOTable1.First; // переход на первую запись

ADOTable1.Prior; // переход на предыдущую запись

ADOTable1.Next; // переход на следующую запись

ADOTable1.Last; // переход на последнюю запись

ADOTable1.MoveBy(n) // переход на запись с номером n

Также при навигации используются некоторые свойства компонента ADOTable:

ADOTable1.RecNo - номер текущей записи

ADOTable1.RecordCount - общее число записей

Существуют свойства, определяющие достижения конца или начала таблицы:

ADOTable1.BOF - начало файла (true/false)

ADOTable1.EOF - конец файла (true/false)

Эти свойства равны True, если достигнуты начало или конец файла.

Методы редактирования

Методы редактирования позволяют изменять данные в таблице. К таким методам относятся:

ADOTable1.Insert; // добавить запись над текущей

ADOTable1.Append; // добавить запись в конец таблицы

ADOTable1.Delete; // удалить запись

ADOTable1.Edit; // перевести запись в режим редактирования

ADOTable1.Post; // сохранить запись

ADOTable1.Cancel; // отменить изменения

ADOTable1.Refresh; // обновить таблицу

ADOTable1.Requery(); //перегрузить данные из БД

Перед вызовом методов редактирования, часто приходится проверять, в каком состоянии находится текущая запись. Например, если вызвать метод Post (сохранить), а запись не новая, либо не переведена в режим редактирования, то выдается сообщение об ошибке.

Для проверки состояния записи используется свойство:

ADOTable1.State

Это свойство может принимать:

dsBrowse - запись в режиме просмотра;

dsEdit - запись в режиме редактирования;

dsInsert - запись новая.

Пример: Пусть в таблице нужно увеличить оклад сотрудников на 300. Поле имеет имя oklad.

var s:string;

begin

// сохраняем в переменную метку текущей записи

s:=ADOTable1.BookMarks;

// становимся на первую запись

ADOTable1.First;

// пока не дошли до конца файла

while not ADOTable1.EOF do

begin

// переводим запись в режим редактирования

ADOTable1.Edit;

// меняем оклад

ADOTable1.FieldByName('oklad').Value:=ADOTable1.FieldByName('oklad').AsFloat+300;

// сохраняем запись

ADOTable1.Post;

// переходим на следующую запись

ADOTable1.Next;

end;

// переходим на запись с сохраненной меткой

ADOTable1.BookMarks:=s;

end;

Приведенные примеры имеют недостаток: при циклической обработке записей пользователь будет видеть, как в сетке формы выполняется переход по всем полям таблицы. Такое отображение перехода по записям на экране требует дополнительного времени работы программы, и обработка выполняется дольше.

Для решения проблемы необходимо до цикла выключать визуальное отображение перехода по записям, а после цикла - включать.

Для этого компонент ADOTable имеет методы:

ADOTable1.DisableControls; // выключить отображение перехода по записям

ADOTable1.EnableControls; // включить отображение перехода по записям

Пример. Пусть в таблице нужно посчитать сумму окладов всех сотрудников. Поле имеет имя oklad.

// описываем переменную для подсчета суммы

var sum:real; s:string;

begin

// сохраняем в переменную метку текущей записи

s:=ADOTable1.BookMarks;

// обнуляем сумму

sum:=0;

// выключаем отображение перехода по записям

ADOTable1.DisableControls;

// становимся на первую запись

ADOTable1.First;

// пока не дошли до конца файла

while not ADOTable1.EOF do

begin

// суммируем оклад

sum:=sum+ADOTable1.FieldByName( 'oklad’).AsFloat;

// переходим на следующую запись

ADOTable1.Next;

end;

// переходим на запись с сохраненной меткой

ADOTable1.BookMarks:=s;

// включаем отображение перехода по записям

ADOTable1.EnableControls;

end;

Пусть имеется некоторая форма с данными. Необходимо в компоненте Label1 отображать текущий номер записи и общее число записей. При переходе между записями для компонента ADOTable наступает событие OnAfterScroll. В этом событии нужно написать код:

procedure TForm1.ADOTable1AfterScroll (Dataset: TDataset)

begin

if ADOTable1.RecordCount> 0 then

Label1.Caption:='Запись '+IntToStr(ADOTable1.Recno)+' из '+IntToStr(ADOTable1.RecordCount)

else

Label1.Caption:='Данных нет’;

end;

Вопросы для самоконтроля

1. Как выполнить подключение таблицы в программу по технологии ADO? Опишите принципы работы с полями таблицы.

2. Как можно обратиться к значению поля, как создать вычисляемое поле и написать для него формулу? Пример.

3. Как программно задать ограничения на значение нужного поля? Приведите пример.

4. С помощью каких компонентов можно отобразить значения полей таблицы на форме, какие свойства при этом нужно указать?

5. Приведите пример кода, который помещает в Blob-поле выбранный графический файл. Какие ограничения имеет компонент DbImage?

6. Какой компонент используется для работы с полями типа дата/время? Приведите пример.

7. Как создать список-справочник. Опишите действия для создания такого справочника.

7. С помощью какого компонента можно добавить на форму набор кнопок для выполнения над данными основных операций? Какие свойства имеет этот компонент?

8. Приведите основные методы таблицы для выполнения навигации по записям. Приведите пример цикла для перехода по всем записям таблицы.

9. Приведите методы таблицы для выполнения редактирования данных таблицы.