ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ

УЧРЕЖДЕНИЕ

ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ВОРОНЕЖСКИЙ ГОСУДАРСТВЕННЫЙ

УНИВЕРСИТЕТ»

В.Г. Рудалев

ТЕХНОЛОГИЯ ADO

И ДОСТУП К ДАННЫМ

MS SQL SERVER

Учебно-методическое пособие для вузов Издательско-полиграфический центр Воронежского государственного университета 2008 Утверждено научно-методическим советом факультета прикладной математики, информатики и механики 14 марта 2008 г., протокол № Рецензент зав. каф. ПО и АИС ВГУ проф. М.А. Артемов Учебное пособие подготовлено на кафедре технической кибернетики и автоматического регулирования факультета прикладной математики, информатики и механики Воронежского государственного университета.

Пособие рекомендуется для студентов факультета ПММ Воронежского государственного университета, сдающих экзамен по курсу «Технологии проектирования информационных систем».

Для специальности: 010501 – Прикладная математика и информатика Введение MS SQL Server – семейство серверов БД от фирмы Microsoft. В настоящее время наиболее широко распространена версия MS SQL Server 2000. По совокупности показателей и функциональных возможностей при очень скромных системных требованиях SQL Server 2000 превосходит InterBase, уступая Oracle.

Постепенно набирает популярность версия SQL Server 2005, приближающаяся по своим характеристикам к Oracle, доступна для скачивания пробная версия SQL Server 2008. Существует также «облегченная»

бесплатная версия SQL Server 2005 Express Edition. Тем не менее, мы будем рассматривать более старую версию SQL Server 2000, которая, будучи удобнее в эксплуатации, при гораздо более низких системных требованиях не имеет (по кругу базовых вопросов, рассматриваемых в данном пособии) существенных отличий от последних версий.

По утверждениям разработчиков, приведенным в онлайновой документации, SQL Server 2000 может обслуживать базы данных терабайтных объемов при доступе тысяч пользователей. Однако главным достоинством MS SQL Server (а также его главным недостатком) является тесная интеграция с Windows и сопутствующими программными продуктами Microsoft (Back Office, Visual Studio.NET, IIS). Общими являются модель защиты, базирующаяся на защите Windows, консоль администрирования, набор программных интерфейсов и др. Важным фактором, сдерживающим распространение MS SQL Server, является отсутствие его версий для альтернативных операционных систем.

По данным опроса посетителей сайта www.sql.ru SQL Server является наиболее популярной СУБД в РФ. На втором месте – Oracle, на третьем – InterBase. Мировая статистика объемов продаж (в денежном выражении) свидетельствует о безусловном лидерстве Oracle.

Доступ к базам данных сервера возможен из программ на любых языках программирования через универсальные интерфейсы ADO, ADO.NET, ODBC, JDBC.

1. Технология ADO. Общая характеристика Для доступа к данным SQL Server из клиентских приложений, написанных на языке Delphi для платформы Win32 можно применять хорошо известные универсальные технологии Borland Database Engine (BDE) и DBExpress, разработанные фирмой Borland. Однако рекомендуемый подход – использование технологии Microsoft ActiveX Data Objects (ADO), оптимизированной для SQL Server.

Технология ADO основана на возможностях СОМ, а именно интерфейсов OLE DB. Базовый набор интерфейсов OLE DB предустановлен во всех версиях Microsoft Windows, поэтому при переносе приложения на другой компьютер для его работоспособности достаточно лишь правильно настроить провайдер OLE DB. Провайдер OLE DB представляет собой СОМ-сервер, предоставляющий набор интерфейсов для доступа к данным, и «скрывающий» особенности конкретных источников данных. Провайдеры OLE DB разработаны для большинства СУБД (MS Access, Oracle, Interbase и др.) и многих других источников данных. Часть провайдеров (например, OLE DB Provider for SQL Server) уже установлена в системе, другие доступны для скачивания в Internet. Технология ADO – надстройка над интерфейсом OLE DB, облегчающая его использование прикладными программистами.

Технология ADO и интерфейсы OLE DB предоставляют приложениям единый способ доступа к источникам данных различных типов. Приложение, использующее ADO, может однотипно работать с данными, хранящимися на сервере SQL, с электронным таблицами и локальными СУБД (MS Access). Согласно терминологии ADO, любой источник данных (база данных, электронная таблица, файл) называется хранилищем данных, с которым при помощи провайдера взаимодействует приложение.

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

Набор объектов и соответствующий провайдер могут быть созданы для любого хранилища данных без изменения исходной структуры ADO. При этом существенно расширяется само понятие данных. Можно разработать набор объектов и интерфейсов и для не табличных данных, например, графических данных, древовидных структур, данных CASE-инструментов и др. [4].

В Delphi на странице ADO палитры компонентов расположены компоненты доступа к данным, инкапсулирующие технологию ADO. Общая методика их использования построена по тем же принципам, что и у остальных компонентов доступа к данным (BDE, IBExpress, DBExpress и др.), однако внутренняя организация совсем другая. Это удобно, так как программист может с успехом использовать ранее имевшиеся навыки и опыт работы с другими СУБД. Например, компоненты ADO поддерживают навигацию, работу с наборами данных, кэшируемые изменения (здесь они называются пакетными обновлениями), управление транзакциями.

Наиболее серьезное препятствие здесь – научиться мыслить категориями архитектуры клиент-сервер, и не пытаться переносить методы и приемы создания персональных баз данных в многопользовательскую клиентсерверную среду.

С другой стороны, с помощью свойств и методов упомянутых компонентов при необходимости легко обратиться к дополнительным возможностям ADO для более «тонкой» настройки приложения (см. п. 2.8).

Более подробную информацию можно получить в [4].

Предполагая, что читатель уже знаком с компонентной методикой создания приложений БД [6], рассмотрим основные особенности компонентов ADO для доступа к данным.

На странице ADO Палитры компонентов Delphi расположены компоненты, инкапсулирующие набор данных ADO и приспособленные для работы с ADO соединениями. Это пять компонентов:

TADODataSet — универсальный набор данных;

TАDOTаblе — таблица БД;

TADOQuery — запрос SQL;

TADOStoredProc — хранимая процедура;

TADOCommand – команда ADO.

Компонент ТАDOTаblе предназначен для использования таблиц БД, подключенных через провайдеры OLE DB. По своим функциональным возможностям и применению он подобен стандартному компоненту BDE TTable и возвращает на клиентский компьютер все записи таблицы, формируя в адресном пространстве приложения набор данных. В клиентсерверной среде обычно требуется доставлять клиенту ограниченное число записей, поэтому от ТАDOTаblе по возможности следует отказываться и TADOCommand и встраивать в них оператор SELECT с ограничительным условием WHERE.

Имя таблицы БД задается свойством property TableName: WideString;

Свойство property Readonly: Boolean; позволяет включить или отключить для таблицы режим «только для чтения». Набор данных открывается методом Open и закрывается методом Close. Также можно использовать свойство property Active: Boolean;

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

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

TADOConnection, в котором хранится имя компонента-соединения.

Также допускается непосредственная запись параметров соединения в свойство property СonnectionString: WideString;.

Компонент TADOQuery предназначен для выполнения запросов языка SQL. Текст запроса хранится в свойстве property SQL: TStrings;

Параметры передаются в запрос с помощью свойства property Parameters: TParameters;

Если запрос должен возвращать набор данных, для его открытия используется свойство property Active: Boolean;

procedure Open;

В противном случае достаточно использовать метод function ExecSQL: Integer;

Число обработанных запросом записей возвращает свойство property RowsAffected: Integer;

Универсальный компонент TADODataSet предназначен для представления набора данных ADO и может применяться несколькими способами, в зависимости от типа команды ADO и ее текста. Например, получать данные из таблиц, запросов SQL, хранимых процедур и т. д. В терминах ADO к текстовым командам ADO относятся операторы DML (в том числе, SELECT), DDL, операторы вызова исполняемых хранимых процедур.

Для управления командами ADO используются свойства CommandText: WideString и CommandType: TCommandType.

Например, можно задать CommandType = cmdText и занести в свойство CommandText текст запроса SQL. Здесь можно использовать только оператор SELECT. Если задать тип команды cmdTable, то в CommandText следует занести имя таблицы и т. п.

Connectionstring или Connection. Набор данных открывается и закрывается свойством Active или методами Open и Close.

Компонент TADOStoredProc позволяет использовать хранимые процедуры. Имя хранимой процедуры определяется свойством property ProcedureName: WideString;

Для определения входных и выходных параметров используется свойство property Parameters: TParameters;

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

Для этого свойству property Prepared: Boolean присваивается значение True.

Компонент TADOCommand соответствует команде ADO. Это упрощенный компонент, предназначенный для выполнения операций, которые не возвращают наборы данных (например, модификация данных, исполняемые хранимые процедуры). Поэтому у TADOCommand нет необходимости работать c наборами записей, а его непосредственным предком является класс TComponent, к функциональности которого добавлены механизм соединения с БД через ADO и средства представления команды.

Команда передается в хранилище данных ADO через собственное соединение или через компонент TADOConnection аналогично другим компонентам ADO. Текстовое представление выполняемой команды должно содержаться в свойстве property CommandText: WideString;

Если для выполнения команды необходимо задать параметры, используется свойство property Parameters: TParameters;

Команда выполняется методом Execute.

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

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

Для этого предназначено свойство TADOConnection property ConnectionString: WideString;

Набор параметров зависит от типа провайдера и записывается в это свойство как вручную, так и при помощи специального редактора параметров соединения, который вызывается двойным щелчком на компоненте TADOConnection, перенесенным на форму, или щелчком на кнопке в поле редактирования свойства ConnectionString в Инспекторе объектов.

Подробно методика настройки рассматривается далее на примере в разделе 3. Здесь отметим некоторые особенности и дополнительные возможности.

Параметры соединения могут храниться в файле с расширением udl (радиокнопка Use Data Link File в окне настройки). Файл UDL представляет собой обычный текстовый файл, в котором указываются название параметра и через знак равенства его значение. Параметры разделяются точкой с запятой:

[oledb] Everything after this line is an OLE DB initstring Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=test;Use Procedure for Prepare=1;

Auto Translate=True;Packet Size=4096;Workstation ID=COMP;

Use Encryption for Data=False;

Если файл параметров соединения отсутствует, настройку осуществляется вручную. Для этого следует нажать кнопку Build. В результате появляется диалоговое окно Data Link Properties.

Первая страница «Поставщик данных» (Provider) этого окна позволяет выбрать провайдер OLE DB для конкретного типа источника данных из числа провайдеров, установленных в системе.

Состав элементов управления следующих страниц зависит от типа источника данных. На странице «Подключение» для случая SQL Server задается имя сервера, имя базы данных, способ аутентификации пользователя (в зависимости от способа – имя и пароль пользователя).

Страница «Дополнительно» задает дополнительные параметры соединения. В зависимости от типа хранилища данных некоторые элементы этой страницы могут быть недоступны.

В поле «Время ожидания подключения» можно задать время ожидания соединения в секундах. По истечении этого времени процесс прерывается.

Список «Права доступа» задает права доступа к отдельным видам выполняемых операций. Последняя страница «Все» позволяет просмотреть и при необходимости изменить все сделанные настройки для выбранного провайдера.

Соединение с хранилищем данных ADO открывается и закрывается при помощи свойства property Connected: Boolean;

При открытии соединения необходимо вводить имя пользователя и его пароль. Появление стандартного диалога подавляется, если установить свойство property LoginPrompt: Boolean;

в False. Если для SQL Server использовалась интегрированная аутентификация Windows, то такой диалог не требуется. В любом случае параметры входа можно записать в свойство Connectionstring.

Общим предком наборов данных ADO является класс TDataSet, предоставляющий базовые функции управления наборами данных. Непосредственным потомком TDataSet (и предком ADO компонентов) является класс TCustomADODataSet, инкапсулирующий всю специфику ADO. На уровне TCustomADODataSet определены также новые (для программистов, работавших ранее с технологиями BDE и IBExpress) очень полезные свойства и методы для управления блокировками и курсорами из приложений, которые мы сейчас и рассмотрим.

Перед открытием набора данных необходимо установить тип используемой при редактировании записей блокировки. Для этого применяется свойство property LockType: TADOLockType;

значениями которого могут быть следующие константы.

ltUnspecified Блокировка задается источником данных (сервером), а не компонентом ltReadOnly Набор данных откроется в режиме только для ltPessimistic Редактируемая запись блокируется на все время редактирования от ее чтения до момента сохранения в хранилище данных ltOptimistic Запись блокируется только при сохранении изменений в хранилище данных ltBatchOptimistic Запись блокируется на время сохранения в хранилище данных при вызове метода UpdateBatch Для того чтобы установка блокировки подействовала, свойство LockType должно быть обязательно модифицировано до открытия набора данных.

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

Для набора данных ADO необходимо выбрать тип и местоположение используемого курсора, с помощью которого набор данных будет формироваться. Курсор – это указатель на набор строк, возвращаемых SQL-запросом. Местоположение курсора задается свойством type TCursorLocation = (clUseServer, clUseClient);

property CursorLocation: TCursorLocation;

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

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

property MarshalOptions: TmarshalOption, где TMarshalOption = (moMarshalAll, По умолчанию задано значение moMarshalAll, разрешающее возврат серверу всех записей набора данных. При плохом соединении с сервером для ускорения работы компонента применяется свойство moMarshalModifiedOnly, обеспечивающее возврат только модифицированных клиентом записей.

Тип курсора определяется свойством CursorType со следующими значениями ctUnspecified Курсор не задан, тип курсора определяется возможностями источника данных ctOpenForwardOnly Однонаправленный курсор, допускающий перемещение только вперед; используется при необходимости быстрого одноразового прохода по ctKeyset Двунаправленный локальный курсор, не отображающий добавленные и удаленные другими ctDynamic Двунаправленный курсор, отображает все изменения. Требует наибольших затрат ресурсов ctStatic Двунаправленный курсор, полностью игнорирует Замечание: если курсор расположен на клиенте (CursorType = clUseClient), то для него доступен только один тип ctStatic, другие значения курсора при открытии НД будут автоматически заменены значением ctStatic.

После передачи клиенту записи набора данных размещаются в локальном буфере, размер которого определяется свойством property CacheSize: Integer;

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

Также можно ограничить максимальный размер набора данных.

Свойство property MaxRecords: Integer задает максимальное число записей набора данных. По умолчанию свойство имеет значение 0 и набор данных не ограничен. Общее число записей property RecordCount: Integer;

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

В других технологиях Borland (BDE, IBExpress, DBExpress) вместо термина «пакетные обновления» используется термин «кэширование изменений».

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

в наборе данных установить клиентский курсор:

CursorLocation := clUseClient;

задать оптимистическую блокировку:

LockType := ItBatchOptimistic;

В результате все измененные клиентом записи будут храниться в специальной области памяти (дополнительном буфере, области delta [3]).

Набор данных будет выглядеть так, как будто данные были изменены, однако на самом деле эти изменения хранятся в памяти клиента, а не на сервере. В режиме пакетных обновлений метод Post компонентов наборов данных изменяет только содержимое набора данных, но не влияет на таблицы сервера. Чтобы перенести изменения из памяти в базу данных, необходимо вызвать метод procedure UpdateBatch(AffectRecords:

Используемый в методах тип TAffectRecords позволяет задать тип записей, к которым применяется данная операция:

tAffectRecords = (arCurrent, arFiltered, arAll, arCurrent — операция выполняется только для текущей записи;

arFiltered — операция выполняется для записей из работающего фильтра;

arAll — операция выполняется для всех записей;

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

Вызов метода без параметров UpdateBatch() эквивалентен использованию arAll.

Для отмены всех сделанных, но не сохраненных методом UpdateBatch изменений применяется метод procedure CancelBatch(AffectRecords:

В ADO допускаются два способа управления транзакциями – неявный и явный. При неявном управлении каждый оператор, модифицирующий данные (например, метод Post, UpdateBatch) рассматривается как отдельная транзакция. В случае успеха выполнения оператора транзакция применяется, в случае неудачи транзакция откатывается и генерируется исключительная ситуация. В случае модификации нескольких взаимосвязанных таблиц данный способ неизбежно приведет к нарушению целостности БД.

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

Для явного управления транзакциями применяются методы компонента TADOConnection. Методы function BeginTrans: Integer;

procedure CommitTrans;

procedure RollbackTrans;

означают старт, фиксацию и откат транзакции, соответственно.

property IsolationLevel: TIsolationLevel;

позволяет задать уровень изоляции транзакции (см. таблицу).

ilUnspecifled Уровень изоляции не задается ilReadUncommitted Незафиксированные изменения других ilChaos Изменения более защищенных транзакций ilBrowse Незафиксированные изменения других ilCursorStability Изменения других транзакций видны только после фиксации ilReadCommitted Изменения других транзакций видны только после фиксации ilRepeatableRead Изменения других транзакций не видимы, ilSerializable Транзакция выполняется изолированно от ilIsolated Транзакция выполняется изолированно от Примерная схема транзакции в задаче отпуска товара со склада выглядит следующим образом:

var otpusk, ostatok: integer;

Otpusk := NewValue; //Новое значение из строки редактирования // Выбираем значение поля Rest текущей записи таблицы Product Ostatok := tProductRest.value;

if Otpusk = 0), PRIMARY KEY (Prod_ID)

CREATE TABLE ORDERDETAILS (

PRIMARY KEY (Prod_ID, Ord_ID), FOREIGN KEY (Ord_ID) REFERENCES ORDERS, FOREIGN KEY (Prod_ID) REFERENCES PRODUCTS Ключевыми для приложения являются:

хранимая процедура AddOrder (добавление нового заказа и получение его «дружественного» номера) CREATE PROCEDURE AddOrder @OrdID UniqueIdentifier, @SaleDate DateTime, @OrdNum int OUTPUT INSERT INTO Orders (Ord_ID, Cust_ID, Total, SaleDate) SELECT @OrdNum = OrdNum FROM ORDERS WHERE Ord_ID = @OrdID триггер OnOrders для таблицы OrderDetails (уменьшение остатка на складе при вставке нового заказа) CREATE TRIGGER OnOrders ON [dbo].[OrderDetails]

INSTEAD OF INSERT

DECLARE @ost AS int, SELECT @ost=products.rest - i.prodcount, WHERE products.Prod_id=i.Prod_id INSERT INTO OrderDetails UPDATE Products SET Rest = @ost RAISERROR ('На складе нет нужного количества!', 16, 1) 3.3. Клиентская часть приложения. Компоненты доступа к данным Работа будет состоять из четырех этапов:

настройка компонентов доступа;

настройка логических полей наборов данных;

настройка компонентов управления данными;

реализация бизнес-правил.

Для настройки доступа к данным нам потребуются следующие компоненты:

Компонент Страница Назначение TADOConnection ADO Соединение с сервером TADOStoredProc ADO Выполнение хранимой процедуры Общепринятый стиль в создании программ для баз данных требует, в частности, разделения кода для настройки доступа к данным и кода для управления данными. Компоненты доступа к данным разместим в модуле данных (рис. 3.2).

Расположим в модуле данных и настроим сначала компонент conn: TADOConnection. Двойной щелчок по нему вызывает окно редактирования строки соединения (рис. 3.3).

Для формирования строки соединения удобнее воспользоваться мастером, запускаемым по кнопке Build. На первой вкладке выбираем имя поставщика данных – Microsoft OLE DB Provider for SQL Server (рис. 3.4).

На следующей вкладке (рис. 3.5) указываем имя сервера (можно использовать кнопку Обзор), способ входа в сервер (учетные записи Windows NT) и в ниспадающем списке – имя базы данных на сервере. Проверяем подключение, чтобы убедиться, что все сделано правильно и нажимаем OK.

Дополнительно, чтобы отключить запрашивание имени и пароля (их мы уже вводили при входе в Windows) укажем для TADOConnection свойство LoginPrompt = False.

Свойства adsProducts и adsCust настраиваются однотипно в соответствии со следующими таблицами.

LockType ltOptimistic Оптимистическая блокировка CursorLocation clUseClient По умолчанию CommandText Select * from Текст оператора LockType ltOptimistic Оптимистическая блокировка CursorLocation clUseClient По умолчанию CommandText Select * from Текст оператора Типичная ошибка программиста, привыкшего к разработке персональных БД, состоит в применении такой же методики по отношению к таблицам Orders и OrdersDetails, хотя даже для таблиц Products и Customers она далеко не всегда пригодна.

Дело в том, что таблицы Orders и OrdersDetails, являющиеся дочерними, содержат потенциально огромный объем текущей информации, которая оператору для оформления заказа не требуется. В архитектуре клиент-сервер формирование НД из таких таблиц методами Select * from Orders оказывает крайне негативное влияние на производительность информационной системы в целом.

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

select * from OrderDetails where Ord_ID = :OrdID В качестве значения параметра :OrdID в запрос будем передавать идентификатор GUID нового заказа, сгенерированный в приложении.

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

В итоге компонент adsOrdersDetails конфигурируется следующим образом:

LockType ltBatchOptimistic Оптимистическая блокировка c пакетными tion Замечание. Если таблицы Products и Customers могут содержать большой объем информации, то для выборки данных из них также необходимо использовать выборку с ограничением, например, по ценовой группе или категории товара, по адресу заказчика и т. п. Необходимые изменения в проект внесите самостоятельно.

Чтобы настроить доступ к хранимой процедуре, разместим на форме компонент spAddOrder: TAdoStoredProc, соединим его с объектом AdoConnection. В раскрывающемся списке свойства ProcedureName выберем имя хранимой процедуры AddOrder. Выбрав свойство Parameters, удостоверимся, что компонент включает все параметры хранимой процедуры, и запомним их названия.

Для дальнейшей работы настроим компоненты-посредники TDataSource, связывающие наборы данных с визуальными компонентами:

dsCust:

Свойство Значение DataSet adsCust dsProducts:

Свойство Значение Name dsProducts DataSet adsProducts dsOrderDetails:

Свойство Значение Name dsOrderDetails DataSet adsOrderDetails 3.4. Настройка логических полей наборов данных Следующий этап – настройка логических полей наборов данных adsCust, adsOrderDetails, adsProducts. Наборы данных создаются в оперативной памяти клиентской машины «по образу и подобию»

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

Для настройки логических полей вызовем так называемый редактор полей, два раза щелкнув по компоненту adsCust.

Первоначально окно редактора пусто – логических полей нет. Но в контекстном меню выберем Add All Fields (добавить все поля), после чего настроим все поля из Укажем для них свойство DisplayLabel – осмысленные заголовки в таблице. Например для поля CustName Рис. 3. Для каждого созданного поля будет автоматически создан и занесен в форму компонент TNNNField, где NNN – тип поля.

Имя компонента строится так:

имя набора данных + имя поля, например, для поля Address набора данных adsCust будет создан компонент adsCustAddress: TWideStringField;

Для набора данных adsProducts логические поля создадим аналогично.

Но для набора данных adsOrderDetails проделаем более сложную и более полезную операцию – создание поля просмотра. Очевидно, при вставке нового заказа неразумно заставлять оператора вручную прописывать код покупателя и код товара.

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

Для создания поля просмотра в редакторе полей выберем New Field, после чего появится диалоговое окно. В окне введем поле Prod Name, которое должно хранить название товара, и которого нет в таблице заказов. Тип поля сделаем строковым. Отметим переключатель Lookup (просмотр) – это будет поле просмотра.

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

Key Fields должно совпадать с полем Lookup Keys из таблицы Products, откуда будет выбираться значение и подставляться в Orders. В качестве DataSet выбирается таблица просмотра adsProducts, а в качестве ResulField – поле из таблицы товаров, которое будет отображаться среди полей таблицы заказов. Очевидно, это поле – ProductName (см. рис. 3.7) и тип его должен совпадать с типом поля выбора ProdName.

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

FieldKind fkLookUp Поведение поля (поле просмотра) LookupKey- Prod_ID Поле, значение которого будет LookupDataset adsPro- Таблица, из которой происходит LookupResult- Product- Поле из adsProducts, значение Чтобы потребовать обязательности ввода количества товаров, установим для компонента-поля adsOrdersProdCount свойство Required в True.

Для ввода покупателей поля просмотра создавать не будем, а сконфигурируем компонент TDBLookUpComboBox, выполняющий аналогичную функцию (см. п. 3.5). Так как согласно бизнес-правилам безымянные заказы допускаются, то в дальнейшем предусмотрим лишь опциональную возможность открытия НД adsCust.

3.5. Взаимодействие с пользователем Главную форму приложения создадим в соответствии с рис. 3.8.

В главном меню формы предусмотрим пункты «ФайлСоединение/Выход» и «Операции-Заказчики/Заказы/Товары». Продублируем их кнопками на форме.

Функциональность программы будет обеспечивать форма TabForm, с расположенным на ней компонентом PC:TPageControl, разбитым на три вкладки TabSheet – tsCust, tsOrders, tsProducts, с соответствующими заголовками. Каждая закладка будет автоматически отображаться при открытии формы, в зависимости от нажатой кнопки, например:

procedure TMainForm.sbProductsClick(Sender: TObject);

TabForm.PC.ActivePage := TabForm.tsProducts;

TabForm.ShowModal;

Рассмотрим логику формирования заказов. На закладке tsOrders расположены сетка DBGrid и навигатор, связанные через свойство DataSource с компонентом adsOrderDetails. Благодаря наличию поля просмотра в сетке отображается текстовое название товара, см. рис. 3.9.

Сетка специально настроена с помощью Columns Editor, чтобы отображать только нужную оператору информацию – название, количество и сумму товара. Остальные столбцы таблицы OrderDetails заполняются автоматически и в сетке не показываются. Отметим, что в состав типизированных полей обычно включаются все столбцы таблицы, а в состав столбцов сетки – только те, которые необходимы оператору.

В данном случае «лишними» для оператора являются идентификатор покупателя, идентификаторы заказа и товара Ord_ID, Prod_ID в НД asOrders. Чтобы их убрать из «сеток», следует два раза щелкнуть по компоненту TDBGrid и в появившемся редакторе столбцов (он немного напоминает ранее использовавшийся редактор полей) добавить в «сетку»

только необходимые столбцы.

Для отображения остатка и цены выбранного товара над сеткой расположены два DBEdit, связанные через свойство DataSource с компонентом adsProducts.

Заказ будем формировать в два приема: занесение одной строки с первичным ключом Ord_ID в таблицу Orders и занесение группы строк с внешним ключом Ord_ID в таблицу OrderDetails. Первая операция выполняется с помощью хранимой процедуры AddOrder, вторая – отправкой на сервер пакета изменений в НД adsOrderDetails.

В качестве первичного ключа будем использовать значение глобального уникального идентификатора (GUID), хранимое в поле формы OrdID: TGUID. Генерация GUID и занесение его в параметр запроса происходят при открытии формы:

procedure TTabForm.tsOrdersShow(Sender: TObject);

begin // Показываем текущую дату lbDate.Caption := DateToStr(Date);

// Получаем уникальный номер для нового заказа CreateGUID(OrdID);

// Открываем пустой список деталей заказа dm.adsOrderDetails.Close;;

dm.adsOrderDetails.Parameters.ParamByName('OrdID').Value dm.adsOrderDetails.Open;

end;

При редактировании элементов заказа в сетке неизбежно возникнет проблема, связанная с отсутствием значения внешнего ключа OrdID в НД adsOrderDetails (он является частью первичного ключа). Чтобы этого не происходило, будем своевременно подставлять сгенерированное нами значение OrdID в обработчике BeforePost НД:

procedure TDM.adsOrderDetailsBeforePost(DataSet: TDataSet);

begin dm.adsOrderDetailsORD_ID.value:=GUIDToString(TabForm.OrdID);

end;

Пересчет суммы элемента заказа в денежном выражении (столбец Amount в таблице OrderDetails) при изменении количества товара нуждается в автоматизации. Для этого в модуле данных два раза щелкнем по компоненту adsOrderDetails, в редакторе полей выделим поле ProdCount и создадим для него обработчик для события OnChange (изменение значения):

procedure TDM.adsOrderDetailsProdCountChange(Sender: TField);

begin if not adsOrderDetailsProdName.IsNull then if dm.adsOrderDetailsPRODCOUNT.Value > ShowMessage ('На складе нет нужного количества!');

dm.adsOrderDetails.Cancel;

adsOrderDetailsAmount.Value:= adsProductprice.Value * adsOrderDetailsPRODCOUNT.value;

end;

Расчет суммы заказа выполняется в обработчике щелчка кнопки со значком :

procedure TTabForm.bCalcClick(Sender: TObject);

begin lbTotal.Caption := FloatToStr (GetTotal(dm.adsOrderDetails));

ChangeAccess;

end;

Здесь используется процедура расчета по данным, еще не переданным на сервер.

function GetTotal (ds: TDataSet): Currency;

// Подсчет суммы заказа begin // Так как заказ еще не перенесен на сервер, // суммируем Amount из набора данных Result:=0;

ds.First;

while not ds.Eof do begin Result := Result + ds.FieldByName ('Amount').Value;

end;

end;

Метод TTabForm.ChangeAccess используется для блокировки кнопок, использование которых не допускается состоянием НД:

procedure TTabForm.ChangeAccess;

var Ins: Boolean;

begin // Разрешаем доступ к кнопкам в зав-ти от состояния НД Ins := dm.adsOrderDetails.State in [dsBrowse];

bOrderApply.Enabled := Ins;

bOrderCancel.Enabled := Ins;

bCalc.Enabled := Ins;

end;

Если сумма и содержание заказа покупателя устраивают, то нажимается кнопка «Применить», стартующая транзакцию.

procedure TTabForm.bOrderApplyClick(Sender: TObject);

begin dm.conn.BeginTrans; // Старт транзакции // В рамках одной транзакции сначала записываем // заказа в родительскую таблицу Orders.

// Для этого вызываем хранимую процедуру AddOrder dm.spAddOrder.Parameters.ParamByName('@OrdID').Value := GUIDToString(OrdID);

if cbCust.Checked then dm.spAddOrder.Parameters.ParamByName('@CustID').Value := dm.spAddOrder.Parameters.ParamByName('@Total').Value := dm.spAddOrder.Parameters.ParamByName('@SaleDate').Value := dm.spAddOrder.ExecProc; // Вызов хранимой процедуры // Получение с сервера "дружественного" номера заказа lbNum.Caption := dm.spAddOrder.Parameters.ParamByName('@OrdNum').Value;

// Затем пересылаем пакетом содержание заказа // в дочернюю таблицу OrderDetails, // остаток на складе уменьшается в триггере dm.adsOrderDetails.UpdateBatch;

dm.Conn.CommitTrans; // Фиксация транзакции except ShowMessage ('Ошибка транзакции!');

dm.adsOrderDetails.CancelBatch;

dm.conn.RollbackTrans; // Откат транзакции end;

ChangeAccess;

end;

Здесь для реализации бизнес-правила уменьшения остатка используется триггер, приведенный ранее в п. 3.2. При попытке передачи на сервер неправильного заказа будет выдано сообщение:

Кнопка «Отмена» откатывает транзакцию:

procedure TTabForm.bOrderCancelClick(Sender: TObject);

begin dm.adsOrderDetails.CancelUpdates;

if dm.conn.InTransaction then dm.conn.RollbackTrans;

ChangeAccess;

end;

Дополнительно в обработчике OnClick навигатора запишем procedure TTabForm.nOrderDetailsClick(Sender: TObject;

Button: TNavigateBtn);

begin ChangeAccess;

end;

Согласно сформулированными нами бизнес-правилам анонимные заказы допускаются, т. е. ключ Cust_ID в таблице Orders заполнять необязательно. Поэтому для экономии трафика при оформлении заказа набор данных с заказчиками adsCust по умолчанию не открывается. Чтобы его все-таки открыть, следует отметить CheckBox, расположенный рядом со списком выбора заказчика в верхней части формы, после чего последний список будет заполнен данными:

procedure TTabForm.cbCustClick(Sender: TObject);

begin if cbCust.Checked then if dm.adsCust.Active then dm.adsCust.Refresh else dm.adsCust.Open else dm.adsCust.Close;

end;

Компонент dblcCust: TDBLookUpComboBox служит для занесения в заказы кода заказчика, выбираемого из таблицы клиентов, и настраивается следующим образом:

ListField CustName;Address Что отображается в списке При попытке закрытия формы проверяется активность транзакции и выдается окно с предупреждением.

procedure TTabForm.FormCloseQuery(Sender: TObject; var CanClose: Boolean);

begin if dm.conn.InTransaction then if MessageDlg('Имеются незавершенные транзакции.

mtConfirmation, [mbYes, mbNo], 0) = mrYes then dm.conn.CommitTrans;

dm.conn.RollbackTrans;

ShowMessage('Ошибка транзакции!');

dm.conn.RollbackTrans;

CanClose:=True;

end;

3.6. Форма соединения Очевидный недостаток программы – соединение жестко «зашито» в код. При изменении названия, местоположения сервера или способа аутентификации программу придется изменять и перекомпилировать.

Поэтому добавим форму соединения, которая будет вызываться при старте программы (рис. 3.11). На форме имеется кнопка bBrowse:

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

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

Необходимый для реализации данного алгоритма код выглядит следующим образом:

uses dmUnit, Registry;

{$R *.dfm} var Reg: TRegistry;

KeyName: string;

DBS: string;

procedure TfrmConnect.btnBrowseClick(Sender: TObject);

begin if EditConnectionString(dm.Conn) then ConnectionString.Text := dm.Conn.ConnectionString;

end;

procedure TfrmConnect.FormShow(Sender: TObject);

begin DBS:='';

Reg := TRegistry.Create; // Создаем объект реестр // Устанавливаем корневой раздел Reg.RootKey := HKEY_CURRENT_USER;

KeyName := 'Software\AdoMinSklad\Path';

// Ищем ключ Path в подразделе Software\IbSklad Reg.OpenKey(KeyName, True);

// если его нет, параметр true заставляет создать DBS := Reg.ReadString('Path'); // Читаем значение ключа Reg.CloseKey;

finally Reg.Free;

end;

ConnectionString.Text := DBS;

end;

procedure TfrmConnect.bConnectClick(Sender: TObject);

begin with dm do begin Conn.Connected:=false;

dm.Conn.ConnectionString := ConnectionString.Text;

dm.Conn.Connected := True;

// Сохраняем параметры соединения if dm.Conn.Connected then KeyName := 'Software\AdoMinSklad\Path';

// Ищем ключ Path в подразделе Software\IbSklad if Reg.OpenKey(KeyName, False) then MessageDlg('Не удается создать соединение. Проверьте end;

Теперь очистим свойство ConnectionString в компоненте AdoConnection (оно будет считываться из реестра) и запишем в обработчик события выбора пункта меню «Соединение» следующий код:

procedure TMainForm.miConnectClick(Sender: TObject);

var isConnected: boolean;

begin if FrmConnect.ShowModal=mrOK then begin isConnected := dm.conn.Connected;

// Кнопки доступны только после успешного соединения miOper.Enabled := isConnected;

sbCust.Enabled := isConnected;

sbOrders.Enabled := isConnected;

sbProducts.Enabled := isConnected;

end;

В заключение рассмотрим еще два способа доступа к данным MS SQL Server – обращение к хранимым процедурам и использование компонентов, чувствительных к данным.

3.7. Дополнительные функции Функциональность вкладок tsCust и tsProducts реализуйте самостоятельно. Первая вкладка должна включать поиск покупателя по начальным буквам и добавление покупателя в базу, если его там нет.

Вторая вкладка должна включать просмотр товаров на складе и добавление товара на склад. Добавление товара должно происходить с учетом категории товара, хранящейся в дополнительной таблице Category со столбцами CatID и CategoryName.

Если товар с указанным именем уже имеется в таблице Products, то при добавлении необходимо увеличить его остаток, если нет – добавить в таблицу строку c новым товаром. Подобные бизнес-правила лучше всего реализовывать в виде хранимых процедур, выполняемых на сервере.

Примерный код хранимой процедуры:

CREATE PROCEDURE dbo.Add_Product /* Входными параметрами являются новые название товара, */ /* цена, количество, производитель, идентификатор категории */ /* Выходным параметром, помечаемым служебным словом OUTPUT, */ DECLARE @count as int /* Поиск товара */ SELECT @count=Count(*) FROM dbo.Product IF @Count = 0 /* Если не найден, добавить */ INSERT INTO dbo.Product (ProductName, Price, Rest, CountryName, Category) VALUES(@NewName, @NewPrice, @newRest, @CountryName, @CatID) ELSE /* Иначе увеличить количество */ UPDATE dbo.Product SET Rest = Rest+ @NewRest /* Выбрать идентификатор (ключ ProdName уникален) */ SELECT @CurrentID = Prod_ID FROM dbo.Product Вызов хранимой процедуры:

procedure tsProducts.bApplyClick(Sender: TObject);

begin with DM.spAddProduct do begin Parameters.ParamByName('@NewName').Value := Parameters.ParamByName('@NewPrice').Value := Parameters.ParamByName('@NewRest').Value := Parameters.ParamByName('@CountryName').Value := Parameters.ParamByName('@CatID').Value := ExecProc; // Выполнение хранимой процедуры MessageDlg('Ошибка записи!', mtError, [mbOk],0);

end;

end;

Указание. Для выбора данных из таблицы Category используйте компонент DBLookUpComboBox.

В качестве задания повышенного уровня сложности предусмотрите хранение в таблице Category дерева категорий и выборку листа дерева для занесения в таблицу Products.

Решение каждой задачи должно включать ER-модель указанной предметной области, серверную и клиентскую часть приложения.

1. Библиотека.

2. Видеотека.

3. Информационно-поисковая система.

4. Музыкальные альбомы.

5. Отдел кадров.

6. Распределение учебной нагрузки.

7. Бронирование авиабилетов.

8. Гостиница.

9. Прокуратура.

10. Факультет.

12. Кулинарные рецепты.

13. Народные депутаты.

14. Регистратура лечебного учреждения.

15. Автозапчасти.

16. Заказы на сборку компьютеров.

17. Автосалон.

18. F1 Word Grand Prix.

19. Биржа труда.

20. Турагентство.

21. Аренда помещений.

22. Риэлтерская фирма.

23. CIA.

24. Финансовая организация.

25. Управление госимуществом.

26. Ордена и награды.

27. Охранное агентство.

28. Hollywood.

29. Российский футбол.

30. Городские достопримечательности.

31. Дома на продажу.

32. Альпинистский клуб.

33. Управление проектами.

34. Катера и яхты.

35. Ресторан.

36. Заповедник.

SMALLINT 16-битное целое число в диапазоне от 32, DECIMAL[(P[,S])], Десятичное число с фиксированной точноNUMERIC, стью в диапазоне от –10 38 –1 до 1038 – 1. P – DEC SMALLMONEY Аналогичен типу Money, но 32-разрядный и DOUBLE PRECISION –1.79E + 308 до 1.79E + 308.

SMALLDATETIME Дата и время в диапазоне от 1 января 1900 г.

TIMESTAMP Уникальный в пределах БД идентификатор.

Этот тип данных не содержит времени и гарантирует лишь, что поле этого типа уникально в рамках базы данных UNIQUEIDENTIFIER Глобальный уникальный идентификатор.

CHAR[(N)], Строка фиксированной длины. N – длина CHARACTER строки. Максимальная длина – 8000 символов VARCHAR[(N)], CHARAC- Строка переменной длины. N – длина строTER VARYING(N) ки. Максимальная длина – 8000 символов TEXT Строка произвольной (до 2,147,483,647 символов) длины NCHAR[(N)], Строка фиксированной длины в формате NATIONAL CHARACTER UNICODE. N – длина строки. Максимальная NVARCHAR[(N)], Строка переменной длины в формате UNINATIONAL CHARACTER CODE. N – длина строки. Максимальная VARYING(N) NTEXT, Строка произвольной (до 1,073,741,823 симNATIONAL TEXT волов) длины BINARY[(N)], Двоичные данные фиксированной длины, до VARYING VARBINARY 8000 байт. N – длина данных VARBINARY[(N)] Двоичные данные переменной длины, до SQL_VARIANT Может хранить данные произвольного типа 1. Кренке Д. Теория и практика построения баз данных / Д. Кренке.– СПб. : Питер, 2005. – 859 с.

2. Вьейра Р. SQL Server 2000. Программирование : в 2 ч. / Р. Вьейра. – М. : БИНОМ. Лаборатория знаний, 2004. – Часть 1. – 735 с.

3. Кэнту М. Delphi 7 для профессионалов / М. Кэнту. – СПб. : Питер, 2004. – 1101 с.

4. Дарахвелидзе П.Г. Разработка WEB-служб средствами Delphi / П.Г. Дарахвелидзе, Е.П. Марков. – СПб. : BHV-Петербург, 2003. – 672 с.

5. Рудалев В.Г. Клиент-серверные приложения баз данных : учебное пособие для вузов / В.Г. Рудалев, С.С. Пронин. – Воронеж : ИПЦ ВГУ, 2007. – 82 с.

6. Рудалев В.Г. Создание приложений для MS SQL Server: учебнометодическое пособие для вузов / В.Г. Рудалев. – Воронеж : ИПЦ ВГУ, 2006. – 58 с.

СОДЕРЖАНИЕ

Введение

1. Технология ADO. Общая характеристика

2. Компоненты ADO в Delphi

2.1. Наборы данных ADO

2.2. Компонент TADOTable

2.3. Компонент TADOQuery

2.4. Компонент TAdoDataSet

2.5. Компонент TADOStoredProc

2.6. Команды ADO

2.7. Настройка соединения

2.8. Класс TCustomADODataSet

2.9. Пакетные обновления (Batch updates)

2.10. Управление транзакциями

2.11.Компоненты управления данными

3. Пример программирования

3.1. Модель предметной области

3.2. Серверная часть приложения

3.3. Клиентская часть приложения. Компоненты доступа к данным …………………………………………………….. 3.4.Настройка логических полей наборов данных

3.5. Взаимодействие с пользователем

3.6. Форма соединения

3.7. Дополнительные функции

Задания для самостоятельной работы

Приложение. Типы данных SQL Server

Литература

ТЕХНОЛОГИЯ ADO

И ДОСТУП К ДАННЫМ MS SQL SERVER

Учебно-методическое пособие для вузов Подписано в печать 22.05.08 Формат 6084/16. Усл. печ. л. 3,1.

Издательско-полиграфический центр Воронежского государственного университета.

394000, г. Воронеж, пл. им. Ленина, 10. Тел. 208-298, 598-026 (факс) http://www.ppc.vsu.ru; e-mail: [email protected] Отпечатано в типографии Издательско-полиграфического центра Воронежского государственного университета.

394000, г. Воронеж, ул. Пушкинская, 3. Тел. 204-133.