Упорядочение нестандартно разбитых по страницам данных (VB)

Скотт Митчелл

Загрузить PDF-файл

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

Введение

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

Примечание

Так как в этом руководстве используется предыдущий, перед началом работы скопировать декларативный синтаксис в <asp:Content> элементе с веб-страницы предыдущего руководства (EfficientPaging.aspx) и вставить его между <asp:Content> элементом на SortParameter.aspx странице. Вернитесь к шагу 1 руководства Добавление элементов управления проверкой в интерфейсы редактирования и вставки , чтобы получить более подробное описание репликации функций одной ASP.NET страницы на другую.

Шаг 1. Повторное определение пользовательского метода разбиения по страницам

Для правильной работы пользовательского разбиения по страницам необходимо реализовать некоторые методы, которые могут эффективно захватывать определенное подмножество записей с учетом параметров Индекс начальной строки и Максимальное число строк. Для достижения этой цели можно использовать несколько методов. В предыдущем руководстве мы рассмотрели эту задачу с помощью новой ROW_NUMBER() функции ранжирования Microsoft SQL Server 2005. Короче говоря, ранжирующая функция присваивает номер строки каждой строке, ROW_NUMBER() возвращаемой запросом, ранжированный по указанному порядку сортировки. Затем соответствующее подмножество записей получается путем возврата определенного раздела нумеруемых результатов. В следующем запросе показано, как использовать этот метод для возврата этих продуктов с номерами от 11 до 20 при ранжировании результатов, упорядоченных в алфавитном порядке по :ProductName

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

Этот метод хорошо подходит для разбиения по страницам с использованием определенного порядка сортировки (ProductName в данном случае сортировка по алфавиту), но запрос необходимо изменить, чтобы показать результаты, отсортированные по другому выражению сортировки. В идеале приведенный выше запрос можно переписать для использования параметра в предложении OVER следующим образом:

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

К сожалению, параметризованные ORDER BY предложения не допускаются. Вместо этого необходимо создать хранимую процедуру, которая принимает входной @sortExpression параметр, но использует одно из следующих обходных решений:

• Написание жестко заданных запросов для каждого из выражений сортировки, которые могут использоваться; затем используйте IF/ELSE инструкции T-SQL, чтобы определить, какой запрос следует выполнить.
• Используйте инструкцию для CASE предоставления динамических ORDER BY выражений на @sortExpressio основе входного параметра n. Дополнительные сведения см. в разделе Используется для динамической сортировки результатов запроса статьи Инструкции T-SQLCASE.
• Создайте соответствующий запрос в виде строки в хранимой процедуре, а затем используйте системную sp_executesql хранимую процедуру для выполнения динамического запроса.

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

Хотя ни один из этих подходов не является совершенным, я думаю, третий вариант является лучшим из трех. Благодаря использованию динамического SQL он обеспечивает уровень гибкости, который не имеет других двух. Кроме того, атакой путем внедрения кода SQL можно воспользоваться, только если злоумышленник может выполнить хранимую процедуру, передав входные параметры по своему выбору. Так как DAL использует параметризованные запросы, ADO.NET защитит те параметры, которые отправляются в базу данных через архитектуру. Это означает, что уязвимость атаки путем внедрения кода SQL существует только в том случае, если злоумышленник может напрямую выполнить хранимую процедуру.

Чтобы реализовать эту функцию, создайте в базе данных Northwind хранимую процедуру с именем GetProductsPagedAndSorted. Эта хранимая процедура должна принимать три входных параметра: @sortExpression, входной параметр типа nvarchar(100), который указывает способ сортировки результатов и внедряется непосредственно после ORDER BY текста в OVER предложении; и @startRowIndex@maximumRows, те же два целочисленных входных параметра из GetProductsPaged хранимой процедуры, рассмотренной в предыдущем руководстве. Создайте хранимую GetProductsPagedAndSorted процедуру с помощью следующего скрипта:

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
                   UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
                   CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                         QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                         ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

Хранимая процедура начинается с указания значения @sortExpression параметра. Если он отсутствует, результаты ранжируются по ProductID. Далее создается динамический SQL-запрос. Обратите внимание, что динамический SQL-запрос здесь немного отличается от предыдущих запросов, используемых для получения всех строк из таблицы Products. В предыдущих примерах мы получали все связанные категории продуктов и названия поставщиков с помощью вложенного запроса. Это решение было принято еще в учебнике Создание уровня доступа к данным и было сделано вместо использования JOIN s, так как TableAdapter не может автоматически создать связанные методы вставки, обновления и удаления для таких запросов. Однако хранимая GetProductsPagedAndSorted процедура должна использовать JOIN для упорядочения результатов по категориям или именам поставщиков.

Этот динамический запрос создается путем объединения частей статического запроса и @sortExpressionпараметров , @startRowIndexи @maximumRows . Так как @startRowIndex и @maximumRows являются целочисленными параметрами, их необходимо преобразовать в nvarchars для правильного объединения. После создания динамического SQL-запроса он выполняется с помощью sp_executesql.

Проверьте эту хранимую процедуру с различными @sortExpressionзначениями для параметров , @startRowIndexи @maximumRows . В Обозреватель сервера щелкните правой кнопкой мыши имя хранимой процедуры и выберите команду Выполнить. Откроется диалоговое окно Запуск хранимой процедуры, в котором можно ввести входные параметры (см. рис. 1). Чтобы отсортировать результаты по имени категории, используйте CategoryName в @sortExpression значении параметра; для сортировки по названию компании поставщика — CompanyName. После указания значений параметров нажмите кнопку ОК. Результаты отображаются в окне Вывод. На рисунке 2 показаны результаты при возврате продуктов с 11 по 20 при заказе UnitPrice по в порядке убывания.

Рис. 1. Попробуйте использовать разные значения для трех входных параметров хранимой процедуры

Рис. 2. Результаты хранимой процедуры отображаются в окне вывода (щелкните для просмотра полноразмерного изображения)

Примечание

При ранжировании результатов по указанному ORDER BY столбцу в предложении OVER SQL Server должны отсортировать результаты. Это быстрая операция, если есть кластеризованный индекс по столбцам, по которым упорядочены результаты, или если имеется покрывающий индекс, но в противном случае это может быть более дорогостоящим. Чтобы повысить производительность для достаточно больших запросов, рассмотрите возможность добавления неклатеризованного индекса для столбца, по которому упорядочены результаты. Дополнительные сведения см. в статье Ранжирование функций и производительности в SQL Server 2005 г.

Шаг 2. Расширение уровней доступа к данным и бизнес-логики

После создания хранимой GetProductsPagedAndSorted процедуры следующим шагом является предоставление средств для выполнения этой хранимой процедуры с помощью нашей архитектуры приложения. Это влечет за собой добавление соответствующего метода как к DAL, так и к BLL. Начнем с добавления метода в DAL. Откройте типизированный Northwind.xsd набор данных, щелкните правой ProductsTableAdapterкнопкой мыши элемент и выберите в контекстном меню пункт Добавить запрос. Как и в предыдущем руководстве, мы хотим настроить этот новый метод DAL для использования существующей хранимой процедуры — GetProductsPagedAndSortedв данном случае . Начните с указания, что новый метод TableAdapter будет использовать существующую хранимую процедуру.

Рис. 3. Выбор использования существующей хранимой процедуры

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

Рис. 4. Использование хранимой процедуры GetProductsPagedAndSorted

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

Рис. 5. Указание на то, что хранимая процедура возвращает табличные данные

Наконец, создайте методы DAL, использующие шаблоны Fill a DataTable и Return a DataTable, назвав методы FillPagedAndSorted и GetProductsPagedAndSortedсоответственно.

Рис. 6. Выбор имен методов

Теперь, когда мы расширили DAL, мы готовы обратиться к BLL. ProductsBLL Откройте файл класса и добавьте новый метод . GetProductsPagedAndSorted Этот метод должен принимать три входных параметра sortExpression, startRowIndexи maximumRows и должен просто вызывать метод DAL GetProductsPagedAndSorted , как показано ниже:

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
    ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function

Шаг 3. Настройка ObjectDataSource для передачи параметра SortExpression

После добавления DAL и BLL методов, использующих GetProductsPagedAndSorted хранимую процедуру, остается только настроить ObjectDataSource на SortParameter.aspx странице для использования нового метода BLL и передать параметр на SortExpression основе столбца, по которому пользователь запросил сортировку результатов.

Для начала измените objectDataSource SelectMethod с GetProductsPaged на GetProductsPagedAndSorted. Это можно сделать с помощью мастера настройки источника данных, из окно свойств или непосредственно с помощью декларативного синтаксиса. Далее необходимо указать значение для свойства ObjectDataSourceSortParameterName. Если это свойство задано, ObjectDataSource пытается передать свойство GridView SortExpression в SelectMethod. В частности, ObjectDataSource ищет входной параметр, имя которого равно значению SortParameterName свойства . Так как метод BLL GetProductsPagedAndSorted имеет входной параметр выражения сортировки с именем sortExpression, присвойте свойству ObjectDataSource значение SortExpression sortExpression .

После внесения этих двух изменений декларативный синтаксис ObjectDataSource должен выглядеть примерно так:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

Примечание

Как и в предыдущем руководстве, убедитесь, что ObjectDataSource не включает входные параметры sortExpression, startRowIndex или maximumRows в коллекцию SelectParameters.

Чтобы включить сортировку в GridView, просто проверка флажок Включить сортировку в смарт-теге GridView, который задает свойству GridView AllowSorting значение true и приводит к отображению текста заголовка для каждого столбца в виде LinkButton. Когда пользователь щелкает один из заголовков LinkButtons, выполняется обратная связь и выполняется следующее:

1. GridView обновляет свое SortExpression свойство значением SortExpression поля, ссылка на заголовок которого была щелкнуна.
2. ObjectDataSource вызывает метод BLL GetProductsPagedAndSorted , передавая свойство GridView SortExpression в качестве значения входного параметра метода sortExpression (вместе со значениями соответствующих startRowIndex и maximumRows входных параметров).
3. BLL вызывает метод DAL s GetProductsPagedAndSorted .
4. DAL выполняет хранимую GetProductsPagedAndSorted процедуру, передавая @sortExpression параметр (вместе со значениями @startRowIndex входных параметров и @maximumRows ).
5. Хранимая процедура возвращает соответствующее подмножество данных в BLL, который возвращает их в ObjectDataSource; Затем эти данные привязывается к GridView, отрисовывается в HTML и отправляется конечному пользователю.

На рисунке 7 показана первая страница результатов при сортировке UnitPrice по в порядке возрастания.

Рис. 7. Результаты сортируются по unitPrice (щелкните для просмотра полноразмерного изображения)

Хотя текущая реализация может правильно сортировать результаты по названию продукта, названию категории, количеству на единицу и цене за единицу, попытка упорядочить результаты по имени поставщика приводит к исключению среды выполнения (см. рис. 8).

Рис. 8. Попытка отсортировать результаты поставщиком приводит к следующему исключению среды выполнения

Это исключение возникает из-за того, SortExpression что для объекта BoundField GridView SupplierName задано значение SupplierName. Однако имя поставщика в Suppliers таблице на самом деле называется CompanyName . Мы получили псевдоним этого столбца как SupplierName. Однако предложение, используемое функциейROW_NUMBER(), OVER не может использовать псевдоним и должно использовать фактическое имя столбца. Поэтому измените boundField SupplierName s SortExpression с SupplierName на CompanyName (см. рис. 9). Как показано на рисунке 10, после этого изменения результаты могут быть отсортированы поставщиком.

Рис. 9. Изменение SortExpression Для Параметра BoundField поставщика на CompanyName

Рис. 10. Результаты теперь можно сортировать по поставщику (щелкните, чтобы просмотреть полноразмерное изображение)

Сводка

Реализация пользовательской разбиения на разбиение на разбиения на разбиение требуется указать порядок сортировки результатов во время разработки. Короче говоря, это означало, что реализованная нами пользовательская реализация разбиения по страницам не могла одновременно предоставлять возможности сортировки. В этом руководстве мы преодолели это ограничение, расширив хранимую процедуру с первого, чтобы включить входной @sortExpression параметр, по которому можно отсортировать результаты.

После создания этой хранимой процедуры и создания новых методов в DAL и BLL мы смогли реализовать GridView, который предлагает как сортировку, так и настраиваемую разбиение по страницам, настроив ObjectDataSource для передачи текущего SortExpression свойства GridView в BLL SelectMethod.

Счастливое программирование!

Об авторе

Скотт Митчелл (Scott Mitchell), автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с Веб-технологиями Майкрософт с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга Sams Teach Yourself ASP.NET 2.0 в 24 часа. Его можно связать по адресу mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.

Отдельная благодарность

Эта серия учебников была проверена многими полезными рецензентами. Ведущим рецензентом этого руководства был Карлос Сантос. Хотите ознакомиться с моими предстоящими статьями MSDN? Если да, опустите мне строку в mitchell@4GuysFromRolla.com.

НазадВперед