SQL Server и .NET: динамичный дуэт

Создаем мощное приложение на базе Web

Сегодня большинство разработчиков уже рассматривают SQL Server и Microsoft .NET как своеобразный дуэт. Microsoft .NET Framework становится интегральной составляющей нескольких важных серверных продуктов Microsoft, в том числе SQL Server. Даже теперь разработчики могут создавать собственные решения на базе SQL Server при помощи Visual Studio .NET. Но как с помощью этой связки проще всего создавать несложные приложения, что называется, на каждый день? В предлагаемой статье показан пример создания такого прикладного приложения, основанного на использовании ASP.NET и базы данных SQL Server. База данных SQL Server содержит некий список пользователей (участников базы), для которых в нескольких связанных таблицах хранится идентификационная информация членов списка, сведения об их контактах и сроки полномочий. Кроме того, в базе имеются хранимые процедуры и представления для обработки прикладных данных. С помощью ASP.NET определен графический интерфейс, а базовая функциональность, такая как работа в Web, реализована при помощи ссылок на объекты SQL Server. Кроме того, приложение ASP.NET отслеживает статус регистрации посетителей, устанавливает разрешения при обращении к тем или иным представлениям и выполняет определенные задачи, связанные с этим статусом. В статье используется код на Visual Basic .NET (VB.NET) для демонстрации техники ASP.NET, но, естественно, может применяться и любой другой язык программирования .NET.

Проект приложения

В файле MemberApp.zip, который можно найти на нашем сайте, содержится сценарий .sql для создания таблиц базы данных, хранимых процедур и представлений, с помощью которых хранятся, обрабатываются и раскрываются данные, и два каталога для приложения ASP.NET. В одном каталоге находятся страницы Web-сайта, в другом — файл решения. Его Visual Studio .NET открывает для получения доступа к каталогу страниц Web-сайта. Web-приложение SqlMagMemberApp выполняет четыре базовые функции для сайта. Во-первых, оно позволяет пользователям регистрироваться и идентифицировать себя как членов группы. После регистрации приложение сохраняет эту информацию в продолжение всей Web-сессии. Во-вторых, пользователям разрешено проверять контент других членов группы. Если кто-либо пытается посмотреть страницу члена группы до регистрации в качестве рядового члена группы, приложение отвергает такую попытку. В-третьих, приложение позволяет членам группы просматривать контактную информацию других членов. И наконец, члены группы могут изменять собственную контактную информацию и пароли.

Рисунок 1. Схема Web-страниц в приложении ASP.NET базы данных SqlMagMemberApp

Пример приложения состоит из шести Web-страниц, демонстрирующих базовую функциональность приложения. На рис. 1 представлены отношения между названными страницами. Страница Default.aspx — это Web-форма, в которой используются четыре элемента управления LinkButton. Пользователи нажимают на эти кнопки для перехода на одну из четырех страниц: Login.aspx, Page1OfContent.htm, CurrentMembers.aspx и EditMyMemberInfo.aspx. Код Login.aspx принимает идентификатор участника группы, ID и его пароль. Если переданные значения присутствуют в базе SQL, код прописывает в качестве статуса посетителя сайта значение logged in. Если переданных значений в базе нет, управление передается шестой странице приложения, NotLoggedIn.htm. Вы всегда можете воспользоваться собственным .htm-файлом в приложении ASP.NET, когда по каким-то причинам решите отказаться от сопоставленной той или иной странице программы на сервере.

Контент страницы Default.aspx динамически меняется после регистрации посетителя сайта. Когда страница только открывает Web-сессию, на самом верхнем элементе LinkButton появляется подсказка о необходимости выполнить регистрацию в системе и указывается ссылка на Login.aspx. После успешно выполненной регистрации код для страницы меняет значение свойства Text верхнего элемента LinkButton и удаляет ссылку на Login.aspx. Остальные страницы приложения также настраивают свое визуальное представление в соответствии с конкретными действиями пользователя и контентом базы данных.

Обзор технологии

Архитектура рассматриваемого приложения ASP.NET основывается на технологии SQL Server. В то время как пользователь работает с приложением через браузер, код Web-страниц взаимодействует через ASP.NET и IIS с хранимыми процедурами и представлениями базы данных SqlMagMemberApp. Эти объекты базы данных в свою очередь основываются на трех взаимосвязанных таблицах, в которых представлены сведения о членах группы.

Экран 1. Диаграмма базы данных показывает таблицы в базе SqlMagMemberApp.

Диаграмма базы данных (см. экран 1) показывает три таблицы, в которых содержатся данные, накопленные в базе данных SqlMagMemberApp. Основная таблица, IDInfo, содержит столбцы для идентификаторов членов группы (MemberID) и их паролей (MemberPW). Еще две таблицы, ContactInfo и ExpiryDates, связаны с таблицей IDInfo через внешние ключи в столбце MemberID. Таблица ContactInfo содержит контактную информацию, например почтовые адреса и номера телефонов. Приложение использует значения столбца smalldatetime в таблице ExpiryDates для отключения учетных записей тех членов группы, чей срок пребывания в группе истек.

Помимо этих таблиц, база данных SqlMagMemberApp содержит четыре хранимые процедуры и одно представление для реализации функций приложения. Хранимая процедура IsValidMember оценивает, существуют ли идентификатор и пароль, указанные пользователем на Web-странице, в таблице IDInfo. Программа ASP.NET принимает код возврата хранимой процедуры и реагирует в зависимости от этого значения по-разному. Хранимая процедура EditMyPassword дает пользователю возможность изменить пароль, связанный с ID пользователя в поле MemberID. Процедуры ShowMyRow и EditMyRow, соответственно, возвращают и обновляют значения столбца для строки таблицы ContactInfo, которая соответствует MemberID посетителя сайта текущей Web-сессии. Представление CurrentMembers возвращает записи всех членов группы, чьи значения в столбце ExpiryDate равны или превосходят текущую дату. То же касается и хранимых процедур. Поэтому, например, процедура IsValidMember не возвращает код статуса для членов группы, чей срок полномочий (expiration date) истек.

Объекты базы данных

Приложение ASP.NET требует, чтобы пользователи, прежде чем начать работу, прошли процедуру регистрации на странице Default.aspx. При открытии Web-сессии, при самом первом появлении этой страницы, пользователь видит ссылку на Login.aspx, которая связана с хранимой процедурой IsValidMember. Далее я покажу, как следует писать программу VB.NET для вызова хранимой процедуры и обрабатывать значение, которое она возвращает. Но сначала давайте посмотрим, как работают объекты базы данных.

В листинге 1 показан код T-SQL для хранимой процедуры IsValidMember. В случае успешной регистрации параметры @inID и @inpassword определяют значения столбцов MemberID и MemberPW в строке таблицы IDInfo. За одним исключением процедура возвращает 1, когда для @inID и @inpassword находятся соответствия в IDInfo. В противном случае (то есть если введенные значения не совпадают со значениями в IDInfo) процедура IsValidMember возвращает 0. Код возврата будет равен 0 и в том случае, когда соответствие значений имеет место, но значение поля ExpiryDate для строки MemberID в таблице ExpiryDates меньше текущей даты.

Для программирования описанной логики процедура IsValidMember использует предложение SELECT с указанием соединения (inner join) таблиц IDInfo и ExpiryDates. Аргументы WHERE проверяют необходимые условия соответствия. Оператор IF передает управление последнему оператору, возвращающему 1 (logged in) или 0 (not logged in).

Код Login.aspx не использует T-SQL для вызова хранимой процедуры IsValidMember. Как бы то ни было, при отладке приложений ASP.NET, которые вызывают хранимые процедуры, может потребоваться протестировать ту или иную процедуру при помощи Query Analyzer. Пример кода, который может использоваться для этих целей, приводится ниже:

Значение @return_status будет равно 1 всякий раз, когда HN1 и HDoe1 соответствуют правильным значениям MemberID и Password в строке таблицы IDInfo.

Представление CurrentMembers использует то же самое предложение SELECT, что и в процедуре IsValidMember. В частности, оба предложения SELECT объединяют таблицу ExpiryDates с другой таблицей, а предложение WHERE включает критерий на основе сравнения значения ExpiryDate и текущей даты. Между хранимой процедурой и предложением существует одно отличие. Оно состоит в том, что представление возвращает из таблицы ContactInfo некоторый результирующий набор, содержащий значения столбцов для FirstName, LastName, EmailAddr и Phone. Исходя из этого результирующего набора, можно заполнить элемент управления DataGrid контактной информацией для всех действующих членов группы.

Помимо проверки статуса членства в группе, приложение SqlMagMemberApp позволяет пользователям менять свои пароли и персональные контактные данные. На листинге 2 приведен код T-SQL хранимой процедуры EditMyPassword, который реализует изменение пароля. Процедура принимает на вход два параметра. Первый, @inID, указывает значение столбца MemberID из таблицы IDInfo, в которой будет изменено значение столбца MemberPW. Второй параметр, @inpassword, указывает значение нового пароля. С помощью предложения UPDATE назначается новый пароль. Хотя в хранимой процедуре для подстановки в выражения в операторах ON и WHERE используются специальные описатели для обозначения IDInfo и ExpiryDates (ii и ed, соответственно), для оператора SET в предложении UPDATE подобные описатели неприменимы, поскольку имена столбцов в операторе SET должны быть взяты непосредственно из таблицы, указанной сразу после ключевого слова UPDATE.

Хранимые процедуры ShowMyRow и EditMyRow в приложении ASP.NET работают совместно. Процедура ShowMyRow обеспечивает данные для заполнения текстовых полей, которые отражают контактные данные члена группы в текущей сессии ASP.NET. Процедура EditMyRow принимает входные параметры, считываемые приложением из текстовых полей, и производит обновление строки в таблице ContactInfo на основе значения столбца MemberID, которое соответствует члену группы, открывшему текущую сессию.

Метка A листинга 3 представляет код T-SQL хранимой процедуры ShowMyRow. В нем используются те же выражения в операторах FROM и WHERE, что и в метке B в хранимой процедуре EditMyRow (см. листинг 3). Но процедура ShowMyRow задействует предложение SELECT для возврата строки, в то время как процедура EditMyRow использовала предложение UPDATE для модификации строки. Параметрический список для процедуры EditMyRow назначает по умолчанию значение NULL для @inPh, если программа, вызывающая хранимую процедуру, опускает этот аргумент. В приведенном примере приложение ASP.NET использует преимущества аналогичной техники, присваивая значения NULL столбцу Phone, когда текстовое поле Phone на странице Web-формы — пустая текстовая строка («»), которая не является значением NULL.

Обзор кода ASP.NET

Одно из преимуществ Web-приложений состоит в той простоте, с которой пользователи могут перемещаться между страницами. Приложения ASP.NET способны облегчить это перемещение с помощью гипертекстовых ссылок на страницах .htm или .html, но при этом возможно и программное управление навигацией при помощи элемента управления LinkButton. На странице .aspx этот элемент управления выглядит как гипертекстовая ссылка, но программируется как командная кнопка на VB.NET. Когда пользователь щелкает на кнопке, событие Click для элемента управления LinkButton посылает страницу из сессии браузера, запущенного на рабочей станции пользователя, на Web-сервер. Когда страница достигает Web-сервера, записывается событие Page_Load. Код ASP.NET обрабатывает событие Page_Load перед связанными с элементом управления событиями, инициирующими отправку страницы на Web-сервер. Следовательно, можно обработать событие Click, используя или процедуру Page_Load, или процедуру Click.

Страница Default.aspx использует элемент управления LinkButton, связанное с ним событие Click и событие Page_Load для управления навигацией на главной Web-странице. Листинг 4 содержит код процедур обработки события для страницы Default.aspx. Можно использовать Visual Studio .NET 2003 для просмотра кода Default.aspx.vb. Можно открыть Default.aspx.vb из Default.aspx в среде Visual Studio IDE, используя команды меню View, Code. Метка A в листинге 4 показывает процедуру обработки события Page_Load, которая выполняет задачи, связанные с событием Click для элемента управления LinkButton (hylLogin), первоначально указывавшего на страницу Login.aspx. Если значение переменной сессии LoggedIn равно False, процедура настраивает ссылку hylLogin на Login.aspx. Если значение переменной равно True, процедура удаляет ссылку из hylLogin и изменяет текст элемента управления, сообщая, что пользователь уже зарегистрировался.

Оставшиеся три процедуры листинга 4 представляют собой процедуры обработки событий для трех элементов управления LinkButton страницы Default.aspx. Метка B реализует условную навигацию на стандартную HTML Web-страницу, Page1OfContent.htm. Для этой страницы код ASP.NET отсутствует. Если пользователь еще не зарегистрировался и щелкнул по ссылке Page 1, управление будет передано на Login.aspx, чтобы пользователь мог сначала зарегистрироваться. Такое переназначение предотвращает попытки просмотра Page1OfContent.htm из Default.aspx без первоначальной идентификации себя как члена группы. Можно использовать альтернативные технические приемы для обеспечения безопасного доступа к Web-страницам. Например, разместив контент Page1OfContent.htm на странице .aspx, можно написать специальный код и включать в список пользователей, которым разрешается просматривать что-либо, только тех, кто успешно прошел регистрацию в системе.

Метка C листинга 4 показывает процедуры обработки события Click для CurrentMembers.aspx и EditMyMemberInfo.aspx. Эти две процедуры выполняют безусловную передачу управления на другую Web-страницу (в каждом случае — свою). Такие страницы имеют встроенные средства проверки факта регистрации пользователя.

На странице Login.aspx присутствует два текстовых поля, в которых пользователь указывает ID и пароль, и кнопка для запуска программы поиска введенных значений. Приложение сравнивает введенные значения со значениями в столбцах MemberID и MemberPW таблицы IDInfo. В листинге 5 показан фрагмент кода Login.aspx. Контент, не включенный в листинг (отмечен вертикальным многоточием), — это секция, в которой декларируются и присваиваются значения параметров объекта ADO.NET SqlCommand (cmd1). Метка A демонстрирует код ASP.NET для открытия соединения с базой данных SqlMagMemberApp. Когда приложение начнет работать в вашей среде, измените аргумент Data Source в str1 таким образом, чтобы он указывал на копию SQL Server, которая содержит базу данных SqlMagMemberApp. В моем случае при запуске приложения для управления работой группы параметр Data Source указывал на IP-адрес SQL Server.

Метка B листинга 5 показывает синтаксис, используемый при работе с объектом SqlCommand в хранимой процедуре IsValidMember и при его вызове. Этот код задействует метод ExecuteNonQuery для объекта cmd1, поскольку хранимая процедура IsValidMember не возвращает результирующего набора данных. Метка C демонстрирует код для обработки возвращенного статуса из процедуры IsValidMember. Параметр @OK — это и есть, собственно, возвращаемый статус. Если его значение равно 1, код устанавливает значение сессионной переменной LoggedIn в True; в противном случае значение переменной False.

Если посетитель сайта после успешной регистрации щелкает на третьем элементе управления LinkButton на странице Default.aspx, приложение выводит на экран страницу CurrentMembers.aspx (см. экран 2). Страница представляет в элементе DataGrid список текущих членов базы данных SqlMagMemberApp. Данные берутся из представления CurrentMembers базы данных SqlMagMemberApp. Какие именно появятся члены группы, зависит от соотношения значения текущей даты и даты истечения срока полномочий членов группы. Экран 2 отражает ситуацию, которая сложилась в моем случае на момент написания статьи.

Код CurrentMembers.aspx начинает работу с переназначения управления на страницу NotLoggedIn.htm, если к этому времени посетитель еще не зарегистрировался на сайте; переназначение ограничивает функциональность приложения рамками текущей страницы для текущих членов группы. Затем код CurrentMembers.aspx формирует соединение с базой данных, как показано в метке A листинга 5. Листинг 6 содержит фрагмент кода CurrentMembers.aspx. Код декларирует объект SqlCommand (cmd1), основанный на представлении CurrentMembers и использует объект SqlCommand в качестве настройки свойства SelectCommand для объекта ADO.NET SqlDataAdapter (dap1). Адаптер данных работает как насос для заполнения объекта DataSet (das1), который служит источником данных для элемента управления DataGrid (см. экран 2).

Экран 2. Элемент DataGrid показывает текущий состав членов группы в браузере пользователя

Экран 3 показывает представление Design страницы EditMyMemberInfo.aspx. Элементы управления этой страницы представляют значения столбцов MemberID и MemberPW для текущего члена, для ввода нового пароля служит специальное текстовое поле. При нажатии Update Password происходит проверка пароля формы и пароля в базе данных SqlMagMemberApp. Текущий член группы может изменить свои контактные данные, просмотрев их значения в четырех текстовых полях над кнопкой Update Password и щелкнув кнопку Update Contact Info.

Экран 3. Представление Design на странице EditMyMemberInfo

Нажатие каждой кнопки вызывает некоторую хранимую процедуру, которая обновляет базу данных SqlMagMemberApp. Щелчок по кнопке Update Password вызывает процедуру EditMyPassword. Щелчок по Update Contact Info — процедуру EditMyRow, одним из двух способов. Если текстовое поле для номера телефона Phone (txtPhone) пустое («»), код вызывает хранимую процедуру EditMyRow без передачи значения в параметре @inPh. Это приводит к установке NULL в столбце Phone для строки в таблице ContactInfo. Если txtPhone содержит непустое текстовое значение, код использует считанную строку в качестве значения @inPh при вызове процедуры EditMyRow. Листинг 7 демонстрирует процедуру обработки события Click для кнопки Update Contact Info.

Использование ASP.NET вместе с SQL Server

Как многие, вероятно, заметили, технология ASP.NET представляет собой гораздо более мощный инструмент, чем обычная технология ASP. Рассмотренный случай с приложением ASP.NET и SQL Server демонстрирует пример того, как можно выполнить две фундаментальные задачи базы данных — доступ к данным и манипулирование данными — с помощью ASP.NET. С помощью этого приложения можно организовать базовый доступ к данным, манипулировать данными, как того требует поставленная задача, и предоставить такую возможность всем участникам группы и большинству других приложений. Я надеюсь, что приведенный в статье пример сможет стать отправной точкой для создания более сложных приложений принадлежности или готовым решением для тех, у кого нет времени или ресурсов для создания собственных приложений.

Рик Добсон (http://www.programmingmsaccess.com) возглавляет консалтинговую компанию, специализирующуюся на Microsoft Office, базах данных и разработках для Web