Фильтрация базы данных при помощи параметров в ASP.NET 2.0
ОГЛАВЛЕНИЕ
Результат фильтруется в SQL-запросе при помощи выражения WHERE, который содержит в себе логическое выражение. Для каждой записи в запрашиваемой таблице выражение WHERE определяет стоит ли включать запись в конечный результат, или нет.1 К примеру, такой SQL-запрос как:
SELECT ProductID, ProductName FROM Products WHERE UnitPrice < 15.00
возвратит значение колонок ProductID и ProductName для тех записей таблицы Products , для которых верно выражение UnitPrice < 15.00 (Истино (True)). Проще говоря, оно возвратит все товары дешевле 15 долларов.
Элементы управления SqlDataSource и AccessDataSource могут определять значение, по которому будет отфильтрован запрос посредством параметров, и данные параметры могут возвращать свои значения из различных источников, включая строку запроса, переменных сессии, других элементов управления на странице и т.д. Так же как строка соединения и SELECT-запрос, значения параметров фильтрации для элемента управления источником данных могут быть установлены декларативно - это означает, что вам не надо писать ни строки кода. Данные значения параметров могут также быть настроены программным путем, что может пригодиться в случаях, когда вам понадобится использовать фильтрацию значений согласно программной логике (к примеру, основываясь на имени авторизированного пользователя).
В данной статье мы рассмотрим способ декларативной фильтрации результатов SQL-запроса при помощи параметров в элементе SqlDataSource. Мы рассмотрим примеры использования жёстко запрограммированных значений фильтрации, а также примеры использующие значения фильтрации, полученные из строки запроса и других элементов управления на странице. Читайте далее, чтобы узнать больше об этом!
1 - объяснение было упрощено и представлено в более абстрактном виде ...
Основы фильтрации
При фильтрации результатов существует два интересных компонента: фильтруемая колонка и значение, по которому фильтруют. В примере, приведенном во введении, фильтруется колонка UnitPrice, и 15.00 является значением, по которому фильтруется. Обычно значение, по которому фильтруют, не является жёстко закодированным, но в то же время оно может зависеть от пользовательского ввода. Для фильтрации по значению SQL-выражения обычно используют параметры, которые указывают места заполнения, куда впоследствии может быть вставлено значение. Обычно параметры имеют формат @ParameterName или ?, в зависимости от используемой базы данных. К примеру, при использовании параметризированных запросов c базами данных Microsoft Access применяется ?; при использовании параметризированных запросов для Microsoft SQL Server применяется @ParameterName. Переписав предыдущий SQL-запрос для того, чтобы он использовал параметры, мы получим следующий код:
-- Для Microsoft SQL Server...
SELECT ProductID, ProductName
FROM Products
WHERE UnitPrice < @PriceFilter
-- Для Microsoft Access...
SELECT ProductID, ProductName
FROM Products
WHERE UnitPrice < ?
(ParameterName может иметь любое значение; я решил использовать PriceFilter.)
Определив данный параметр, можно программно установить его значение до выполнения выражения. Если вы знакомы с кодом ADO.NET, то вы знаете, что при написании кода для создания запроса к базе данных вы создаете объект команды (такой, как SqlCommand) который указывает выполняемый SQL-запрос или хранимую процедуру (посредством свойства CommandText), а также используемый объект соединения (посредством свойства Connection). Объект команды также обладает набором Parameters, которым позволяет вам программно указать значения для параметров, указанных в запросе (либо используемых в хранимой процедуре).
Используя элементы управления источником данных, вам не нужно программно устанавливать параметр (если этого от вас не требуется), так как значение параметра может быть установлено декларативно, и мы вскоре это увидим. Тем не менее, при декларативной установке значений параметра вам необходимо понимать, что элемент управления источником данных на самом деле программно добавляет декларативно указанное значение к набору Parameters нижележащего объекта команд. Вкратце, элемент управления источником данных выполняет тот же код ADO.NET, который вам необходимо было бы использовать при программном доступе к данным.
Давайте теперь обратим наше внимание на фильтрацию данных с использованием SqlDataSource. В последующих примерах (которые вы можете загрузить в конце данной статьи) мы будем использовать сокращенную версию базы данных Northwind Access. Мы увидим способ указания параметров фильтрации как при помощи мастера SqlDataSource, так и напрямую через его свойства.
Фильтрация посредством SqlDataSource
Для начала создайте ASP.NET-страницу и перейдите в режим дизайнера (Design). Далее перетащите элемент SqlDataSource из Toolbox. Как мы уже убедились во второй части, мы можем указать базу данных, к которой необходимо установить соединение, а также запрос SQL SELECT, нажав на ссылку "Configure Data Source" (настроить источник данных) в смарт-теге элемента SqlDataSource. Если помните, существует два экрана, где можно указать SELECT-запрос:
- указать колонки из таблицы либо представления - здесь вы можете выбрать таблицу или представление из выпадающего списка и выбрать те колонки, которые вы хотите получить
- указать специализированное SQL-выражение либо хранимую процедуру - при помощи данной опции вы можете выбрать хранимую процедуру из выпадающего списка либо вручную ввести SQL-запрос (или использовать Query Builder)
Если вы выбере те первый вариант, то после выбора таблицы или представления, а также колонок, нажмите на кнопку WHERE для добавления параметра фильтрации. Это выведет диалоговое окно Add WHERE Clause (как показано ниже), где вы можете указать фильтруемую колонку, используемый оператор (=, <, <=, > и т.д.) и источник значения фильтрации (жёстко запрограммированного значения из строки запроса, другого элемента управления на странице и т.д). Вскоре мы рассмотрим способ указания различных типов значений параметров.
| Ограничения в использовании диалогового окна "Add WHERE Clause" |
|---|
| При фильтрации посредством указания колонок таблицы либо представления ("Specify columns from a table or view") существует один явный недостаток - хотя вы можете добавлять множество параметров фильтрации, данные параметры объединены при помощи оператора AND ("и"). Так что если вам нужно фильтровать значения, основываясь на множестве условий, и вам необходимо их объединить также при помощи оператора OR ("или") (к примеру SELECT * FROM Products WHERE UnitPrice < 15.00 OR UnitPrice > 25.00), то вам необходимо вручную добавить SELECT-запрос и выражение WHERE посредством опции указания специализированного запроса либо хранимой процедуры ("Specify a custom SQL statement or stored procedure"). |
Если вы используете опцию указания специализированного запроса ("Specify a custom SQL statement or stored procedure"), вы можете либо ввести SQL-запрос вручную, либо выбрать хранимую процедуру из выпадающего списка. Для добавления параметров фильтрации в произвольный SQL-запрос, просто добавьте параметры при помощи соответствующего синтаксиса. Поскольку база данных Northwind, используемая в моих примерах, является базой данных Microsoft Access, то я использую символ ? для определения параметров. К примеру, если бы я хотел использовать произвольное SQL-выражение, то я бы ввел в текстовое поле SELECT:
SELECT ProductID, ProductName FROM Products WHERE UnitPrice < ?
Тем не менее, если вы используете базу данных SQL Server, то вы должны применять синтаксис @ParameterName. После указания параметров посредством правильного синтаксиса нажмите кнопку Next, и вы увидите окошко, которое спрашивает о значениях параметров.
Шаг 1: укажите произвольное параметризированное SQL-выражение
Шаг 2: нажмите Next и укажите значения для параметра(ов)
Если вы используете базу данных, которая поддерживает параметризированные хранимые процедуры (такие как Microsoft SQL Server), то вы можете выбрать хранимую процедуру из выпадающего списка и нажать далее (Next). В следующем окошке (показанном выше) вы увидите параметры хранимой процедуры и сможете указать способ назначения их значений.
Независимо от того подхода, который вы выберете, как только вы укажете значения параметров и завершите настройку посредством мастера SqlDataSource, SqlDataSource обновит свои свойства. В частности, SelectCommand теперь станет параметризированным запросом, и будет существовать серия экземпляров SelectParameter. Поскольку типы экземпляров SelectParameter различаются в зависимости от того, как значение параметра получило свое значение, мы более детально рассмотрим результирующую разметку SqlDataSource в каждом примере.
Если вы мастер своего дела, то вы всегда можете вручную указать параметризированный запрос и SelectParametersSelectQuery в панели свойств (Properties) SqlDataSource в режиме дизайнера (Design). Тем самым отобразится редактор команд и параметров (Command and Parameter Editor) как показано на следующем изображении. (посредством ввода разметки), либо нажав на свойство
| Почему мы не используем FilterExpression и FilterParameters? |
|---|
Если вы исследуете свойства SqlDataSource, то вы заметите FilterExpression и FilterParameters среди них. Вам наверняка станет интересно, почему мы их не используем. FilterExpression и FilterParameters являются теми свойствами, которые были разработаны для фильтрации результатов, возвращенных базой данных. То есть после того как записи были возвращены из базы данных, они будут далее отфильтрованы свойствами FilterExpression и FilterParameters перед тем, как они будут переданы элементу управления либо программисту, чей код запросил информацию источника данных. С другой стороны, используя параметризированный запрос, SelectParameters производит фильтрацию на стороне базы данных. Как вы уже наверняка догадались, фильтрация на стороне базы данных является более эффективной, чем возврат всей информации в элемент управления и ее последующая фильтрация. Тем не менее, может случиться так, что нам понадобится использовать свойства (бывают времена когда нам понадобилось бы использовать свойства) FilterExpression и FilterParameters - на самом деле, обе техники могут быть использованы последовательно. |
Фильтрация на основе жёстко закодированного значения
При указании значения параметров вас попросят выбрать источник параметра ("Parameter source") при помощи выпадающего списка, содержащего следующие опции:
- не указывать (None)
- файл cookie (Сookie)
- элемент управления (Control)
- форма (Form)
- профиль (Profile)
- строка запроса (QueryString)
- сессия (Session)
Чтобы указать жёстко закодированное значение, выберите опцию None и затем в текстовом поле значения введите ваше жёстко запрограммированное значение. Как только вы укажете значение параметра и завершите работу с мастером, вы получите следующую декларативную разметку элемента управления SqlDataSource:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
ProviderName="providerName"
SelectCommand="parameterizedQuery">
<SelectParameters>
<asp:Parameter DefaultValue="hardCodedValue"
Name="parameterName"
Type="dataType" />
</SelectParameters>
</asp:SqlDataSource>
В частности, существует один экземпляр <asp:Parameter> для каждого жёстко заданного значения параметра, указанного в мастере. Атрибут DefaultValue параметра содержит значение, которое вы ввели в текстовое поле, в то время как атрибут Name ссылается на название параметра. (Для Microsoft Access, который использует ? для параметров, атрибут Name?. Единственным важным моментом тут является то, что экземпляры <asp:Parameter> применяются в порядке к параметрам ? .) Наконец, параметр Type указывает тип значения параметра. ссылается на колонку базы данных которая используется параметром, хотя данное значение может быть изменено поскольку название параметра на самом деле
В приложении к данной статье вы найдете пример названный "Filter on Hard-Coded Value Demo" (фильтрация по жёстко заданному значению). В данном примере, SqlDataSource настроен на возвращение всех товаров, фильтруя их таким образом, что возвращены только те, где UnitPrice < 15.00. Финальная разметка SqlDataSource, а также результаты привязанные к GridView показаны ниже:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
ProviderName="providerName"
SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([UnitPrice] = ?)">
<SelectParameters>
<asp:Parameter DefaultValue="15.00"
Name="UnitPrice"
Type="Decimal" />
</SelectParameters>
</asp:SqlDataSource>
Фильтрация на основе значения строки запроса (QueryString)
В базе данных Northwind каждый товар принадлежит какой-либо категории. Тем самым, нам может понадобиться получить страницу со списком всех категорий в базе данных, где каждая категория имеет гиперссылку на страницу, которая отображает все продукты данной категории. Одним из путей достижения этого является создание двух страниц: Categories.aspx, на которой будет список всех категорий, и ProductsInCategory.aspx, которая перечисляет все товары категории. Препятствием тут является указание категории товаров, которую нужно отобразить на странице ProductsInCategory.aspx. Хотя существует множество техник передачи информации между страницами, одним из наиболее часто используемых является передача посредством строки запроса. Используя данный подход мы можем посетить страницу с товарами посредством ссылки на ресурс ProductsInCategory.aspx?CategoryID=categoryID и затем можем отобразить товары в категории categoryID.
Чтобы использовать значение строки запроса для фильтрации результатов просто выберите опцию QueryString в выпадающем списке "Parameter source". Затем, введите название значения строки запроса, которое вы хотите использовать в текстовом поле строки запроса. После того как вы это выполните, декларативная разметка SqlDataSource будет включать в себя экземпляр <asp:QueryStringParameter> в наборе SelectParameters. Экземпляр <asp:QueryStringParameter>, как это показано ниже, предоставляет название параметра (Name), название значения строки запроса (QueryStringField) и тип Type:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
ProviderName="providerName"
SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)">
<SelectParameters>
<asp:QueryStringParameter Type="Int32"
Name="CategoryID"
QueryStringField="CategoryID" />
</SelectParameters>
</asp:SqlDataSource>
Следующие изображения демонстрируют страницу Categories.aspx, которая содержит список всех категорий вместе с ссылкой на товары (View Products), и страницу ProductsInCategory.aspx, которая была посещена посредством ProductsInCategory.aspx?CategoryID=1 (просмотр товаров категории напитков (Beverages)).
Полный список категорий, где каждая имеет ссылку на список продуктов
Товары категории напитков (ProductsInCategory.aspx?CategoryID=1)
Фильтрация на основе значения элемента управления (Web Control)
Значения параметра для SqlDataSource также могут быть основаны на значении из элемента управления на странице. Относительно предыдущего примера с категориями и товарами, вместо того, чтобы иметь 2 страницы, мы можем создать выпадающий список с категориями, и после выбора значения в нем GridView отображает товары соответственно выбранной категории. Для воплощения данной идеи вам необходимо для начала создать страницу, которая отображает DropDownList, содержащий категории. Далее, добавьте SqlDataSource для того, чтобы получить все товары, где CategoryID равно выбранной в списке категории. Для этого выберите Control в качестве источника параметра ("Parameter source"). Это отобразит выпадающий список элементов управления страницы, тем самым позволяя вам выбрать - значение какого элемента управления будет использовано. Используйте тот же выпадающий список, который вы только что создали.
После того как вы выполните это, декларативная разметка SqlDataSource будет включать в себя экземпляр <asp:ControlParameter> в наборе SelectParameters. Экземпляр <asp:ControlParameter>, как это показано ниже, предоставляет название параметра (Name), ID элемента управления (ControlID), свойство элемента, чье значение будет использовано в качестве значения параметра (PropertyName), и тип Type:
<asp:SqlDataSource ID="ID" runat="server" ConnectionString="connectionString"
ProviderName="providerName"
SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products] WHERE ([CategoryID] = ?)">
<SelectParameters>
<asp:ControlParameter Type="Int32"
Name="CategoryID"
ControlID="categoriesDDL" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
Следующее изображение показывает список категорий в выпадающем списке и соответствующие товары в GridView. Данная страница использует два элемента управления SqlDataSource: один - для получения всех категорий (которые связаны с DropDownList), и второй - для получения соответствующих товаров (который использует <asp:ControlParameter>AutoPostBack DropDownList в True ("истина"), тем самым когда изменяется выпадающий список вызывается постбэк и GridView обновляется для отображения соответствующих значений. и привязан к GridView). Я установил свойство
Вывод
В данной статье мы рассмотрели способ использования параметризированных запросов вместе с коллекцией SelectParameters SqlDataSource для фильтрации результатов, полученных из запроса к базе данных. SqlDataSource позволяет получать значения для параметров из различных источников, включая жёстко заданные значения, значения строки запроса, значения свойства элемента управления, переменные сессии и т.д. Данные значения параметра могут быть декларативно указаны и не требуют написания кода. Данные значения параметров также могут быть заданы программно, что мы рассмотрим в будущем!
Scott Mitchell