Разработка телефонного справочника на основе Web-служб

Во время официальной презентации Microsoft .NET Framework для рекламы Web-служб Билл Гейтс продемонстрировал набор инструментов SQL Server 2000 Web Services Toolkit, в состав которого входит SQLXML 3.0 — третья редакция средства построения XML-приложений для SQL Server 2000. Web Services Toolkit — это превосходный набор инструментов, позволяющий графически создавать Web-службы для таких важных объектов баз данных, как хранимые процедуры (stored procedures, SP) и определяемые пользователем функции (user-defined functions, UDF).

С момента появления SQLXML 3.0. Microsoft выпустила уже два пакета обновлений для данного продукта. Последнее обновление, SP2, можно загрузить с сайта компании. В этой статье мы рассмотрим создание реального приложения, корпоративного телефонного справочника, построенного на базе Web-служб. В данном примере серверная часть телефонного справочника демонстрирует, как вызывать хранимые процедуры и пользовательские функции, передавая параметры и получая возвращаемые значения. Изучение данного примера показывает, как создать гибкую Web-службу, расширяющую возможности использования существующих объектов базы данных SQL Server. Некоторые организации, возможно, даже смогут использовать данный пример для построения собственных телефонных справочников, которые могут применяться партнерами по ту сторону брандмауэра.

Настройка

Для создания Web-службы требуются серверный и клиентский компоненты. Предлагаемая вниманию читателей статья посвящена созданию серверного компонента путем упаковки сценария T-SQL в оболочку SQLXML 3.0. Также здесь будет показано, каким образом написанное с помощью Visual Basic .NET (VB.NET) клиентское приложение Windows Forms может обращаться к созданной Web-службе.

Для написания сценария T-SQL, применяемого для создания Web-службы, можно воспользоваться утилитой Query Analyzer. Загружаемый с нашего сайта файл SSMWSEmpPhones.sql создает базу данных и добавляет в нее таблицы, хранимые процедуры и пользовательские функции. После того как сценарий будет исполнен и создание базы данных завершено, нужно запустить инструмент SQLXML 3.0 IIS Virtual Directory Management, реализованный в качестве оснастки в консоли управления MMC (Microsoft Management Console). Этот инструмент подготавливает приложение для работы с базой данных таким образом, что все хранимые процедуры, пользовательские функции и шаблоны становятся доступными как Web-методы для создания Web-службы.

Инструмент IIS Virtual Directory Management позволяет создать виртуальный каталог, выступающий в качестве промежуточного звена между клиентской частью приложения и объектами базы данных. Находясь в виртуальном каталоге, необходимо создать объект SOAP (Simple Object Access Protocol), описывающий Web-службу. Настройка объекта SOAP осуществляется путем выбора объектов базы данных, которые должны быть представлены как Web-методы. Виртуальный каталог подключается к базе данных через указанную администратором учетную запись с правами, необходимыми для работы данной Web-службы. Используемая виртуальным каталогом учетная запись должна иметь как минимум доступ к используемой Web-службой базе данных и разрешение на исполнение EXECUTE для любого из объектов базы данных, которые предоставляются в качестве Web-метода. Рассмотрим более детально отдельные фрагменты кода T-SQL, используемого в демонстрационном приложении.

T-SQL для телефонного справочника

Код T-SQL для телефонного справочника включает в себя набор объектов базы данных, предназначенных для поиска телефонов сотрудников организации. В данной модели пользователь может указать свой идентификационный номер и пароль для изменения личных реквизитов — телефонного номера, имени, фамилии и пароля. При этом пользователь не может изменить сведения о других сотрудниках. Менять пароли любых сотрудников имеет право только администратор приложения.

Сценарий T-SQL, который можно загрузить с нашего сайта, создает базу данных SSMWSEmpPhones. Если на компьютере SQL Server установлен в каталог, отличный от предлагаемого по умолчанию на диске C:, потребуется модифицировать сценарий и указать подходящее место размещения базы данных. Затем сценарий создает и заполняет данными две таблицы. Таблица EmpPhones включает пять столбцов, содержащих сведения о сотрудниках — идентификационный номер (employeeID), имя (first name), фамилию (last name), телефон (phone number) и пароль (password). В сценарии создания таблицы CREATE TABLE указываются три ограничения — создание первичного ключа, в роли которого выступает EmployeeID, формирование проверочных ограничений на значения и уникальность телефонного номера. Во второй таблице, AppVariables, хранится пароль администратора.

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

CREATE PROCEDURE LikeLname

@Start nvarchar(40)

AS

SELECT Fname, Lname, Phone

FROM EmpPhones

WHERE Lname LIKE @Start + ?%?

LikeLname может возвращать строки из таблицы EmpPhones, которые содержат фамилии, начинающиеся с указанной пользователем строки символов, которая передается в хранимую процедуру в параметре @Start.

В листинге 1 приведен сценарий T-SQL для второй хранимой процедуры, UpdateEmpPhonesRow, выполняющей обновление полей имени, фамилии, телефона и пароля. Выполняя данную процедуру, пользователь может обновить любую комбинацию этих полей таблицы. Впрочем, даже если никакие данные не обновляются, ошибки не произойдет. Параметры @IDToEdit и @Password в сочетании со значением столбца Phone позволяют хранимой процедуре однозначно определить строку таблицы, данные в которой необходимо изменить, и убедиться, что пользователь имеет право на изменение данных. Аргумент SELECT оператора EXISTS в начале процедуры точно идентифицирует строку таблицы, поскольку значения в столбце Phones являются ненулевыми и уникальными.

Если UpdateEmpPhonesRow находит строку, соответствующую критериям @IDToEdit и @Password, процедура последовательно проверяет значения параметров @NewFname, @NewLname и @NewPhone. Если значение параметра не равно значению по умолчанию, выполняется предложение UPDATE для задания нового значения соответствующего поля Fname, Lname и Phone.

Приложение телефонного справочника позволяет менять пароль путем редактирования соответствующего поля Password в таблице EmpPhones. В листинге 2 приведен код пользовательской функции и хранимой процедуры, функция проверяет новый пароль, а процедура обновляет значение пароля в таблице. Пользовательская функция ValidateNewPW() возвращает новый пароль в виде скалярного значения, позволяя таким образом задействовать пользовательскую функцию как Web-метод, а затем сохраняет полученное скалярное значение и передает его в качестве параметра в хранимую процедуру UpdatePassword.

Сценарии T-SQL для создания процедуры UpdatePassword и функции ValidateNewPW объединены в листинге 2, поскольку их работа тесно взаимосвязана. Многие, наверное, обратили внимание, что идентификаторы пользователей и пароли передаются здесь без шифрования. Это сделано для сохранения простоты и ясности нашего примера Web-службы, но в реальном приложении шифрование, конечно, необходимо.

Теперь рассмотрим, как устроены пользовательская функция ValidateNewPW() и хранимая процедура UpdatePassword и как они работают вместе. Функция ValidateNewPW() возвращает новый пароль, если указан правильный старый пароль пользователя или пароль администратора. Внешнее предложение IF ... ELSE, содержащее вложенные конструкции IF ... ELSE, реализует ветвление для предыдущего предложения T-SQL. Функция возвращает значение параметра @NewPassword, если в параметре @OldPassword был указан корректный старый пароль пользователя. Текущее значение пароля администратора содержится в столбце SuperPassword таблицы AppVariables. Если значение параметра @OldPassword не соответствует значению в столбце Password, а значение @SuperPassword не является паролем администратора, функция ValidateNewPW() возвращает значение ?Failed?.

Хранимая процедура UpdatePassword выполняет обновление пароля, правильность которого проверена ранее пользовательской функцией ValidateNewPW(), и принимает два параметра. Параметр @IDToEdit однозначно определяет строку таблицы EmpPhones. Этот параметр используется вместо номера телефона, поскольку каждый может узнать телефонный номер любого сотрудника, но никто не хочет, чтобы каждый, кто знает телефон администратора, мог поменять его пароль. Значение параметра @NewPassword задает новый пароль сотрудника, номер employeeID которого совпадает со значением параметра @IDToEdit.

В листинге 3 приведен последний элемент кода T-SQL, файла .sql для построения базы SSMWSEmpPhones, отвечающий за создание учетной записи, которую будет задействовать приложение. Учетная запись SSMWSLogin обладает минимальным набором прав, необходимых для исполнения приложения Web-службы, поскольку нельзя допустить, чтобы любой пользователь мог выполнять неавторизованные действия на сервере базы данных. Минимальный набор разрешений для Web-службы включает доступ к базе данных SSMWSEmpPhones и исполнение уже разобранных хранимых процедур и пользовательской функции.

Виртуальный каталог Web-службы

Теперь, когда созданы база данных и объекты, пришло время заняться пользовательским интерфейсом, чтобы пользователи могли обратиться к базе данных через Web-службу. Виртуальный каталог Web-службы является промежуточным уровнем между уровнем базы данных и уровнем пользователя. Можно рассматривать этот уровень как отображение интерфейса Web-службы на базу данных SQL Server. Пользователи могут передавать параметры Web-методам среднего уровня, которые выполняют различные действия типа изменения строки в таблице. Таким образом, виртуальный каталог должен располагать сведениями о том, где находится база данных и какие конкретно объекты используются для выполнения действий. Виртуальный каталог должен также иметь возможность регистрироваться в базе данных, поскольку права учетной записи неявно определяют права и набор действий, которые может выполнять Web-служба. Кроме того, виртуальный каталог должен реализовывать базовую функциональность SOAP и WSDL (Web Service Description Language, язык описания Web-служб), чтобы любой клиент мог задействовать стандартные средства обращения к Web-службе.

Чтобы запустить программу управления виртуальными каталогами SQLXML 3.0 IIS Virtual Directory Management, следует выбрать в меню «Пуск» SQLXML 3.0, Configure IIS Support. Этот инструмент позволяет создавать новые виртуальные каталоги или изменять настройки существующих каталогов. На экране 1 изображена закладка General диалогового окна, позволяющая создавать виртуальные каталоги, управлять ими и выполнять соответствующие настройки для рассматриваемой в данной статье Web-службы.

Окно настройки свойств web-службы

Закладка Security диалогового окна позволяет указать, каким образом пользователи Web-службы будут подключаться к уровню базы данных. Указанная учетная запись SSMWSLogin, как уже было отмечено, обладает минимальным необходимым набором разрешений для работы приложения. Такой подход позволяет управлять параметрами безопасности системы при добавлении и удалении объектов базы данных, которые могут потребоваться в процессе развития приложения. Так, по мере добавления объектов базы данных, которые необходимо представить в виде Web-служб, я создавал дополнительные предложения GRANT EXECUTE для учетной записи SSMWSLogin.

Закладки Data Source и Settings позволяют указать, к какой базе данных предоставляет доступ виртуальный каталог и каким образом виртуальный каталог относится к базе данных (в нашем случае к базе SSMWSEmpPhones). При настройке виртуального каталога для Web-службы необходимо установить флажок Allow Post на закладке Settings (по умолчанию отключен). Этот параметр позволяет приложению отправлять значения виртуальному каталогу и таким образом взаимодействовать с Web-службой. Все остальные флажки на закладке Settings можно отключить, не мешая при этом работе Web-службы. Следует иметь в виду, что максимальный размер отправляемого пакета должен быть достаточно большим, чтобы принимать данные, которые приложение-клиент передает в Web-службу. По умолчанию максимальный размер равен 100 Кбайт. Эмпирическим путем можно найти меньшее приемлемое значение, что позволит сделать приложение более устойчивым к атакам типа «отказ в обслуживании» (DoS) благодаря сокращению объема данных, отправляемых пользователем Web-службе.

На закладке Virtual Names в списке Type нужно выбрать SOAP, чтобы показать, что виртуальный каталог содержит объект SOAP для описываемой Web-службы. При выборе объекта SOAP происходит автоматическое заполнение поля Domain name именем домена, указанным для виртуального каталога в закладке General. При этом необходимо самостоятельно ввести имя Web-службы и путь к содержащей ее папке. Для присвоения имени объекту SOAP используется текстовое поле Name. Например, в нашем проекте объекту присвоено имя SSMWSSoap. В текстовом поле Path следует ввести локальный путь для Web-службы. Внизу диалогового окна на экране 1 указано имя папки, использованной для Web-службы. Нужно нажать кнопку Save для передачи изменений, внесенных в объект SOAP в виртуальном каталоге, а затем нажать Configure.

Настройка объекта SOAP

Настройка объекта SOAP в первую очередь означает определение объектов базы данных, которые будут показаны Web-службой, и формат значений, возвращаемых службой клиентским приложениям. На экране 2 изображено диалоговое окно Soap virtual name configuration с двумя уже добавленными хранимыми процедурами и пользовательской функцией. Хранимая процедура LikeLname находится в состоянии создания Web-метода с таким же названием. Кнопка Browse, расположенная рядом с текстовым полем SP/Template, позволяет выбрать имя для того объекта базы данных, который будет предоставлен как Web-метод. Можно нажать кнопку Browse, и диалоговое окно отобразит только те объекты, для которых перечисленные на закладке имена Security имеют разрешение EXECUTE.

Выходной формат для значений, возвращаемых в клиентское приложение, определяется выбранным значением Output as и, конечно, объектом базы данных, выбранным для реализации Web-метода. Хранимая процедура LikeLname возвращает результирующий набор, который Web-служба представляет как набор данных с одной таблицей в виде объекта ADO.NET DataTable, соответствующей результирующему набору. Для этого типа возвращаемых значений можно выбрать переключатели Dataset objects или Single dataset Output as. Выбор кнопки Dataset objects позволяет просмотреть синтаксис для клиентского приложения, используемый для обработки множества возвращенных Web-службой объектов. Оставшиеся три объекта базы данных, которые уже были выбраны, возвращают значения в виде объектов XML. В случае Web-метода ValidateNewPW выбор XML Objects возвращает скалярное значение в виде строки в вызвавшее приложение.

Создание клиента VB.NET

После того как создание Web-службы завершено, необходимо создать клиентское приложение, которое будет обращаться к этой Web-службе. Клиентское приложение для Web-службы телефонного справочника состоит из трех форм, в папке SSM0304 соответствующих одноименному проекту VB/NET. Я использовал Visual Studio 2003 для создания клиентского приложения. Это приложение обращается к среднему уровню приложения через Web-ссылку. Web-ссылка добавляется в проект через Project, Add Web Reference, после чего в поле URL указывается ссылка на Web-службу, с суффиксом ??wsdl?. Следует нажать Go. Когда Visual Studio доходит до Web-службы и возвращает ее описание, нужно нажать Add Reference, чтобы добавить Web-ссылку в проект VisualStudio для Web-службы. На экране 3 показано диалоговое окно Add Web Reference для данного приложения со значениями моей офисной сети. Вам придется заменить имя домена cab233a на имя компьютера, на котором будет исполняться средний уровень Web-службы.

Указание ссылки на Web-службу.

Клиентское приложение можно исполнять на любом компьютере, где предусмотрена возможность подключения к машине, на которой выполняется Web-служба. Поскольку мы используем клиент Windows Forms, на компьютере должна быть установлена среда .NET Framework. Свободно распространяемый установочный комплект .NET Framework 1.1 Redistributable можно загрузить с сайта Microsoft по адресу http://msdn.microsoft.com/netframework/technologyinfo/ howtoget/default.aspx.

На экране 4 изображены формы Form1, Form2, Form3 нашего клиентского приложения. Форма Form1 отображает значения полей таблицы, для которых значения поля Lname начинаются с указанной строки, в этом примере начинающиеся с буквы D. Обратите внимание, что выбранной является вторая строка формы Form1. При нажатии кнопки Edit Selected Row в Form1 выполняется открытие формы Form2, предназначенной для редактирования значений, и заполнение текстовых полей начальными значениями. Form2 проверяет разрешения пользователя для редактирования значений данной строки таблицы, запрашивая и проверяя идентификационный номер и пароль. После корректного ввода этих значений (как показано на экране 4) пользователь может редактировать значения в первых трех текстовых полях формы Form2. Нажатие кнопки Submit передает введенные в текстовых полях данные в таблицу EmpPhones базы данных.

Три формы клиентского приложения

При нажатии кнопки Validate new password в форме Form2 вызывается форма Form3, автоматически заполняемая значениями EmployeeID и Old Password, соответствующая значениям формы Form2. После того как пользователь вводит новый пароль в текстовое поле New Password и нажимает кнопку Validate, связанное с кнопкой событие присваивает новое значение переменной в форме Form2. Если пользователь ввел недопустимые значения в полях EmployeeID и Password перед нажатием кнопки Validate, выполняемая при нажатии кнопки процедура все равно передает новый пароль из формы Form3 в форму Form2. Пользователь должен вручную ввести корректные значения в текстовые поля EmployeeID, NewPassword и Admin Password формы Form3, перед тем как нажать кнопку Validate.

Программирование форм

В листинге 4 приведен программный код формы Form1. Модуль начинается с глобального в рамках модуля описания объекта DataSet. В процедуре btnStartswithlookup_Click два описания имеют особое значение. Первое — это описание proxy xws1, указывающей на Web-службу через Web-ссылку. Второе является массивом объектов, представляемых переменной response. Значение proxy cab233a надо будет заменить на доменное имя сервера сети, на котором размещен виртуальный каталог Web-службы. Поскольку Web-метод LikeLname настроен таким образом, чтобы возвращать множество объектов dataset, для сохранения возвращаемых значений потребуется использовать массив объектов.

Остальной код процедуры btnStartswithlookup_Click относится к декларациям на уровне модуля и на уровне процедуры. Предложение Try ... Catch вызывает Web-метод LikeLname внутри конструкции Try и передает в качестве аргумента значение свойства Text текстового поля TextBox1. Предложение Catch выдает диагностическое сообщение об ошибках, которые могли возникнуть в ходе исполнения. Помимо перехвата возможных ошибок исполнения, предложение Try ... Catch может выдавать полезную диагностическую информацию для отладки приложения. После предложения Try ... Catch идет присваивание переменной das1значения первого элемента из полученного в результате массива response. Процедура завершается вызовом метода SetDataBinding для связывания первого (и единственного) объекта DataTable переменной das1 с элементом управления DataGrid1.

Процедура bntEditselectedrow_Click в листинге 4 начинается с создания экземпляра Form2 и открытия созданного экземпляра с помощью вызова метода Show. Этот метод позволяет переключаться между родительской формой (Form1) и дочерней формой (Form2) без закрытия дочерней формы. Свойство CurrentRowIndex элемента DataGrid1 позволяет процедуре получить индекс выделенной в данный момент строки. Перед закрытием процедура присваивает значения Fname, Lname и Phone выделенной строки элемента DataGrid1 элементам управления TextBox экземпляра Form2.

В листинге 5 приведен код формы Form2. Здесь используются способы, применявшиеся в форме Form1, а также некоторые новые элементы. Например, листинг начинается с определения переменной fldNewPassword. Такой способ описания переменной дает простейший способ передавать значения параметров из дочерней формы в родительскую. Форма Form3 использует переменную fldNewPassword для передачи нового пароля в форму Form2.

Процедура btnSubmit_Check формы Form2 сначала вызывает метод UpdateEmpPhonesRow. Обратите внимание на синтаксис передачи методу аргументов, используемый в первом предложении оператора Try. В частности, за каждым аргументом типа Integer следует булевский параметр со значением True. Если вслед за каждым целым аргументом не будет указан такой аргумент, вызов метода вернет ошибку. Напомню, что в листинге 1 метод возвращал значения 0 или -1. Значение 0 соответствовало неправильному значению параметров employee ID или Password. Процедура возвращает значение в первом элементе массива для сохранения результатов и при необходимости выдает диагностическое сообщение.

Процедура btnValidate_Click выполняет две функции. Во-первых, открывается форма Form3. Во-вторых, выполняется копирование свойств объектов в текстовое поле из Form2 в Form3.

Процедура btnUpdate_Click формы Form2 либо вызывает метод UpdatePassword, либо выдает диагностическое сообщение. Предложение If в конструкции If ... End If осуществляет ветвление на основе значения параметра fldNewPassword. Форма Form3 присваивает допустимое значение пароля переменной fldNewPassword или значение ?Failed?. Форма Form3, в свою очередь, присваивает переменной fldNewPassword значение ?Failed? в том случае, если не может убедиться в правильности нового пароля.

Единственная процедура, которая используется формой Form3, btnValidate_Click, представлена в листинге 6. Эта процедура вызывает метод ValidateNewPW(), который, в свою очередь, опирается на пользовательскую функцию ValidateNewPW(), возвращающую скалярное значение. Web-служба возвращает это значение как строковую переменную с названием response, а не в виде элемента массива. Процедура использует значение переменной response для выбора одного из положений переключателя в форме. Выбранное положение переключателя сообщает пользователю об успехе проверки или отказе. При завершении процедура присваивает значение переменной response переменной fldNewPassword в форме Form2.

Создание Web-служб на основе объектов базы данных

С помощью примеров, рассмотренных в данной статье, можно создать трехсоставное приложение на основе Web-служб, состоящее из базы данных промежуточного уровня и уровня клиента, работающее как единое интегрированное приложение, но при этом, по сути, являющееся слабосвязанным. Инструментарий виртуального каталога SQLXML 3.0 IIS Virtual Directory Management создает средний уровень как виртуальный каталог, позволяющий обращаться к объектам базы данных. Кроме того, можно создать в Web-службе объект SOAP, представляющий объекты базы данных как Web-методы. Такая функциональность позволяет применить имеющийся опыт разработки кода T-SQL для создания Web-приложений. Клиентский уровень выполняет отображение данных на экране, принимает запросы пользователя и обращается к базе данных через интерфейсы среднего уровня. Рассмотренные в данной статье методы могут использоваться при создании Web-приложений для любой базы данных SQL Server.

Автор, тренер и Web-мастер, специализирующийся на базах данных Microsoft SQL Server, Visual Basic .NET и Web-приложениях для работы с базами данных. www.programmingmsaccess.com