Разработка концептуальных приложений .NET, часть 1: Создание объектов базы данных в DB2 Viper с использованием .NET

Оглавление

• Перед началом работы
• Обзор
• Подключение к DB2
• Создание таблицы
• Создание представления
• Создание триггера
• Создание хранимой процедуры
• Заключение

Перед началом работы

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

Данная серия всключает три учебных руководства (части). В части 1 содержатся спецификации концептуального приложения и требования к его базе данных. Представлен процесс создания объектов реляционной базы данных в DB2 Viper с использованием VS.NET 2005. В части 2 описано, как связать серверную базу данных с приложением Windows и Web-приложением, и как передать данные в пользовательский интерфейс. В части 3 показано, как в DB2 Viper использовать хранилище XML-данных для расширения емкости приложения и обеспечения функций приложения в Web-службе.

О данном учебном руководстве

Часть 1 серии учебных руководств. Представлен типичный сценарий создания концептуального приложения с именем Carpooler. Это приложение предназначено для поиска сотрудниками корпорации коллег, с кем можно уехать домой на машине. Для приложения требуются данные для хранения информации о сотрудниках и совместно эксплуатируемых автомобилях. Для этого с помощью VS.NET 2005 создайте объекты базы данных DB2 Viper для приложения.

Требования к системе

• ОС Windows с поддержкой Visual Studio.NET 2005;
• Visual Studio.NET 2005 Professional edition или выше. Учебное руководство разработано с использованием версии 8.0.50727.42 и Microsoft .NET Framework Version 2.0.50727;
• DB2 Viper Учебное руководство разработано с использованием DB2 Viper Release Candidate 1.

Обзор

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

Пользовательский сценарий

Представьте, что вы работаете в консалтинговой компании, предоставляющей услуги по разработке программного обеспечения. Вы занимались разработкой в среде Microsoft Visual Studio и начали использовать VS.NET 2005. Вы хорошо разбираетесь с SQL server и для своего приложения можете создавать такие объекты базы данных, как таблицы, представления, триггеры и хранимые процедуры.

Ваша компания претендует на заключение с клиентом контракта на проект. Этим клиентом является предприятие, на котором для хранения данных используется DB2. Проект заключается в разработке приложения для сотрудников клиента, желающих найти коллег для совместной эксплуатации автомобиля. По требованию клиента приложение Carpooler должно использовать DB2 в качестве серверного хранилища данных, чтобы можно было использовать информацию о сотрудниках из системы отдела кадров. Клиент переходит на последнюю версию DB2, поэтому требуется использовать DB2 Viper. Можно использовать любой тип среды разработки приложений. Так как вы являетесь разработчиком .NET, то будете использовать для создания приложения последнюю версию .NET IDE, Visual Studio 2005.

Для создания концептуального приложения и демонстрации своих способностей использовать .NET и DB2 для передачи данных в пользовательский интерфейс предоставляется 8 часов (один рабочий день). На основе успешного представления инфраструктуры приложения клиент может принять решение, что вы способны выполнить интеграцию между приложением и хранилищем данных клиента. Затем клиент предлагает решить другие проблемы. Решение этих проблем описано в частях 2 и 3 данной серии учебных руководств. А сейчас займемся рассмотрением .NET и DB2.

Ваша компания работает с предприятиями малого и среднего бизнеса, поэтому разработчики имеют большой опыт работы с SQL server. Никто в компании не работал с DB2. Вы назначены на этот проект благодаря своей адаптивности и навыкам по решению проблем. Вы уверены, что опыт работы с SQL server поможет перейти на DB2. Кроме того, различные реляционные базы данных обслуживают схожие функции, для доступа к ним в среде .NET используются аналогичные механизмы. Нужно просто приспособиться к различиям баз данных.

Спецификации приложения

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

Рис. 1. Конечный результат в виде приложения Windows

Рис. 2. Конечный результат в виде приложения Web-приложения

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

Проектирование базы данных

Чтобы сэкономить время, воспользуемся таблицей EMPLOYEE в базе данных DB2 SAMPLE. Для хранения данных, связанных с совместной эксплуатацией автомобилей, например, домашних адресов сотрудников, предпочтительного времени поездок и др. требуется новая таблица. Назовем эту таблицу CARPOOL. В таблице CARPOOL для ссылки на таблицу EMPLOYEE будет использоваться внешний ключ. Целостность ссылочных данных гарантирует, что в совместной эксплуатации автомобилей участвуют только пользователи с легальными идентификаторами.

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

Рис. 3. Проектирование базы данных

Настройка среды

В данном учебном руководстве необходимо использовать VS.NET 2005 и DB2 Viper (см. раздел "Системные требования"). Установка этих программ не вызывает особой сложности. Рекомендуется сначала установить VS.NET, а затем DB2. Запишите идентификатор пользователя и пароль, введенный при установке DB2, эта информация потребуется позднее при подключении к DB2.

Во время установки DB2 убедитесь, что включен протокол TCP/IP. Для проверки протокола TCP/IP после установки DB2 выполните следующее:

1. В меню Windows "Пуск" выберите Programs -> IBM DB2 -> DB2 (default) [или имя экземпляра DB2] -> Command Line Tools -> Command Window;
2. В окне командной строки DB2 должен отображаться запрос C:\Program Files\IBM\SQLLIB\BIN> (если использовался путь установки по умолчанию). Введите команду db2set. Результат должен быть следующим "DB2COMM=tcpip". Если требуется узнать используемый порт TCP/IP, то следует ввести команду db2 get dbm cfg и просмотреть строку "TCP/IP Service name". По умолчанию DB2 использует порт 50000.

Если TCP/IP для DB2 не используется, его можно включить с помощью следующих команд:

1. db2set db2comm=tcpip
2. db2 update dbm cfg using svcename 50000
3. db2stop
4. db2start

После установки DB2 выдается запрос на установку базы данных DB2 SAMPLE. Выберите установку примерной базы данных и парамеры по умолчанию.

Для проверки успешной настройки системы запустите VS.NET 2005. В меню VS.NET's File выберите New -> Project.... В диалоговом окне New Project в левой панели должны находиться IBM Projects.

Подключение к DB2

После проектирования структуры базы данных и настройки среды разработки перейдем к подключению к DB2.

1. В VS.NET Server Explorer нажмите правой кнопкой мыши Data Connections -> Add Connection...;
2. В диалоговом окне Add Connection нажмите кнопку Change...;
3. В диалоговом окне Change Data Source в списке источников данных выберите <other>. В поле Data provider: выберите IBM DB2 Data Provider for .NET Framework 2.0, затем нажмите кнопку OK;
4. В диалоговом окне Add Connection введите следующие данные (используйте идентификатор пользователя и пароль, введенные при установке DB2).

   

   Рис. 4. Добавление подключения к базе данных

   Имя сервера localhost. Предполагается, что DB2 установлен на тот же компьютер, на который установлен пакет VS.NET. Также предполагается, что для DB2 по умолчанию используется TCP/IP порт 50000. Если настройки среды разработки другие, их необходимо исправить;

5. Нажмите Test Connection, должно быть подтверждение об успешном подключении. В противном случае, еще раз внимательно проверьте все параметры;
6. В Server Explorer должно появиться добавленное подключение к DB2. Разверните узлы древовидной структуры и ознакомьтесь со структурой объектов базы данных DB2 SAMPLE. Нажмите правой кнопкой мыши объект и изучите возможные операции;

   

   Рис. 5. Структура базы данных DB2

7. Ознакомьтесь с таблицей EMPLOYEE table. Используйте команды контекстного меню Open Definition и Show Data. Эта таблица будет использоваться в приложении.

Создание таблицы

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

1. В Server Explorer в подключении DB2 нажмите правой кнопкой мыши папку Tables. Имеется два варианта создания таблицы: Add New Table with Designer и Add New Table with Wizard. Первый вариант предназначен для пользователей, знакомых с пользовательским интерфейсом Designer в VS.NET 2005. Второй для начинающих, которым требуется больше подсказок. Вы работаете с VS.NET 2005, поэтому будем использовать Designer;
2. Введите в Designer следующие данные, за исключением имени схемы. Укажите схемы из варианта вместо использования "UDB" (UDB может быть пользовательским идентификатором в Windows; используйте свой идентификатор). Обратите внимание, что Designer содержит разделы, которые можно свернуть или развернуть. В правой стороне раздела Columns имеется набор кнопок. Установите на них курсор мыши и прочитайте подсказку для каждой кнопки. Кнопку Import (последняя в наборе) используйте для импорта столбца EMPNO из таблицы EMPLOYEE, это будет ключевой столбец для соединения таблиц EMPLOYEE и CARPOOL. Сделайте EMPNO первичным ключом в разделе Column Properties, задав свойству Primary Key значение True (имеется несколько других способов создания первичного ключа, если интересно, попробуйте найти их самостоятельно);

   

   Рис. 6. Создание таблицы CARPOOL

3. Сделайте столбец EMPNO внешним ключом и укажите на EMPNO в таблице EMPLOYEE. Убедитесь, что Designer активен, нажмите кнопку Keys в панели инструментов под строкой меню или в локальной панели инструментов в верхней части Designer (Keys - второй кнопка в панели инструментов). После нажатия кнопки Keys выполняется перемещение в панель Keys. Первичный ключ должен отображаться в разделе List of Keys. Нажмите кнопку + для добавления внешнего ключа. Введите параметры как указано ниже:

   

   Рис. 7. Создание внешнего ключа

4. Если при использовании Designer возникают проблемы, для создания таблицы можно использовать мастер;
5. Создайте индекс для столбца CITY, так как большинство сотрудников выполняют поиск совместной эксплуатации автомобилей по городу. Нажмите кнопку Indexes в панели инструментов под строкой меню или в локальной панели инструментов в верхней части Designer (Indexes - третья кнопка в панели инструментов);

   

   Рис. 8. Создание индекса для столбца CITY

6. Дополнительно можно создать другие столбцы, например, с информацией о типе автомобиля, и добавить индекс к столбцу STARTTIME или к комбинации столбцов CITY и STARTTIME. Для простоты пропустим эти шаги;
7. Затем сохраните результат (Ctrl + S).

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

Рис. 9. Сохранение в Designer

После запуска DLL можно проверить сообщение обратной связи из DB2 в панели Output в Designer. Если таблица создана успешно, ее можно будет найти в Server Explorer в папке Tables.

Создание представления

Теперь для хранения информации о совместном использовании автомобилей имеется таблица CARPOOL. Как указано выше, можно повторно использовать существующую таблицу EMPLOYEE для хранения данных о сотрудниках. В таблице EMPLOEE содержатся конфиденциальные данные, например, SALARY, BIRTHDATE и т.д. Нужно создать представление для ограничения доступа приложения Carpooler только к соответствующей информации. Как правило, для ограничений безопасности используются представления. Представление также требуется для объединения таблиц EMPLOYEE и CARPOOL для предоставления приложению всей необходимой информации.

1. В Server Explorer в подключении DB2 нажмите правой кнопкой мыши папку Views. Имеется два варианта создания представления: Add New View with Designer и Add New View with Wizard. Используйте Designer.;
2. В Designer введите следующие данные:

   

   Рис. 10. Создание представления

3. Можно ввести запрос для представления или воспользоваться кнопкой Query Builder. Если используется Query Builder, создайте запрос следующим образом. Используйте среднюю часть снимка экрана, приведенного ниже, для создания связей между столбцами таблиц EMPLOYEE и CARPOOL;

   

   Рис. 11. Создание запроса для представления

4. Сохраните представление. Представление в DB2 создано успешно;
5. В панели инструментов Designer нажмите кнопку Privileges. Предоставьте своему идентификатору пользователя привилегии Insert и Update. Это позволит вставлять и обновлять представления на основе своих учетных данных;
6. В Server Explorer найдите новое созданное представление CARPOOLER. Нажмите его правой кнопкой мыши и выберите Show Data. В Designer отображается пустой результат, потому что в таблице CARPOOL нет данных, представление CARPOOLER является объединением таблиц CARPOOL и EMPLOYEE. Следует ввести некоторые тестовые данные, используя свой родной город и время отправления. Убедитесь, что введено правильное значение EMPNO (это значение уже должно существовать в таблице EMPLOYEE, так как внешний ключ таблицы CARPOOL соответствует столбцу EMPNO). Попытайтесь ввести одну строку данных в представлении CARPOOLER и сохраните результат. Что происходит?

Выводится сообщение об ошибке:

Рис. 12. Ошибка вставки данных с помощью представления

Причина заключается в том, что представление связывает две таблицы, и DB2 не распознает таблицу для соответствующей вставки. Другими словами можно смешивать черное (Таблица 1) и белое (Таблица 2), чтобы получить серое (Представление); но если только предоставить серое, DB2 не может разложить его на черное и белое. Для вставки с помощью представления используйте триггер.

Создание триггера

Для решения проблемы вставки с помощью представления CARPOOLER, связывающего две таблицы, создадим триггер. Вместо того, чтобы DB2 выполнял попытку вставки и выводил сообщение об ошибке, срабатывает триггер, вставка выполняется только в таблицу CARPOOL. При этом не требуется полномочий для вставки и обновления таблицы EMPLOYEE, так как она управляется непосредственно отделом кадров (предположительно говоря, DB2 установлен на компьютере от имени администратора базы данных, поэтому можно вставлять любые данные в таблицу EMPLOYEE).

1. Если представление CARPOOLER еще не открыто в Designer, нажмите его правой кнопкой мыши в Server Explorer, чтобы открыть его описание. В панели инструментов Designer нажмите кнопку Triggers;
2. В Trigger Designer введите следующие данные. В правой панели в разделе Reference убедитесь, что имя ссылки New Row. Это имя будет использоваться для триггера в качестве переменной в SQL. В нашем случае назовем переменную NewRow. В разделе Trigger Properties убедитесь, что Event to Trigger задано значение Insert, а Time to Trigger - значение Instead Of. Такая настройка означает, что при вставке будет срабатывать триггер. В разделе Trigger Text ниже показано, что триггер передает значения из представления CARPOOLER в таблицу CARPOOL;

   

   Рис. 13. Создание триггера

3. Проверьте триггер, вставив строку в представлении CARPOOLER. Убедитесь, что используется правильный формат даты, например, формат типа данных TIME должен быть следующим 09:00:00. Введите несколько строк данных для будущего тестирования приложения. При появление сообщения о неожиданной ошибке при вставке данных, попробуйте закрыть Designer и снова его открыть.

Создание хранимой процедуры

Создано представление CARPOOLER, с помощью которого добавлено нескольких строк тестовых данных в базовую таблицу CARPOOL. Эти объекты базы данных позволяют приложению вводить информацию о сотрудниках, совместно использующих автомобили, и хранить ее в DB2. Теперь подумаем о том, как представить информацию о сотрудниках, совместно использующих автомобили, в пользовательском интерфейсе приложения, когда такую информацию ищут другие пользователи. Для извлечения данных из DB2 имеется множество способов. Один из простых способов заключается в использовании хранимой процедуры. Хранимая процедура позволяет централизовать бизнес-логику в базе данных, в будущем хранимую процедуру можно повторно использовать для других приложений.

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

1. В Server Explorer в подключении DB2 нажмите правой кнопкой мыши папку Procedures. Выберите Add New SQL Procedure with Designer;
2. В Procedure Designer введите следующее описание процедуры и входные параметры;

   

   Рис. 14. Описание процедуры

3. В разделе SQL Body удалите следующее SQL-предложение по умолчанию: Select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES; Нажмите правую кнопку мыши и выберите Query Builder... или Insert SQL для создания собственного запроса SQL. Убедитесь, что хранимая процедура и запрос выглядят следующим образом:

   

   Рис. 15. Процедура SQL

   В приведенном выше примере функция upper() преобразует переменную VARCHAR (String) в верхний регистр. Это используется для сравнения строк независимо от регистра;
4. Протестируйте процедуру. В Server Explorer нажмите процедуру правой кнопкой мыши и выберите Run. В диалоговом окне Run Options введите параметры MYCITY и MYTIME, подобные следующим (но используя собственные тестовые данные). Нажмите кнопку Run для тестирования хранимой процедуры. Убедитесь, что значения параметров находятся в допустимом диапазоне в соответствии с введенными ранее тестовыми данными. Это требуется для того, чтобы в результирующем наборе вашей хранимой процедуры содержались данные;

   

   Рис. 16. Параметры запуска процедуры

5. Проверка результата. В панели Designer в разделе Result Data должен находиться результат, возвращенный хранимой процедурой. Этот результат должен быть похож на следующий (тестовые данные могут отличаться).

   

   Рис. 17. Результат выполнения процедуры

Заключение

В заключение можно сказать, что для потребностей приложения успешно созданы объекты базы данных DB2. Эти объекты базы данных включают таблицу для хранения информации о совместном использовании автомобилей, представление, объединяющее таблицу Carpool и существующую в DB2 таблицу EMPLOYEE, триггер в представлении для вставки данных и хранимую процедуру для поиска сотрудников, совместно использующих автомобили, с помощью задания в представлении города и времени. Теперь можно связать эти объекты базы данных с приложением Windows и Web-приложением. Это будет показано в следующем учебном руководстве данной серии.