Упорядочение нестандартно разбитых по страницам данных (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 s, чтобы результаты были упорядочены по категориям или именам поставщиков.

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

Уделите немного времени, чтобы протестировать эту хранимую процедуру с различными значениями для параметров @sortExpression, @startRowIndex и @maximumRows. В обозревателе серверов щелкните правой кнопкой мыши имя хранимой процедуры и выберите "Выполнить". Откроется диалоговое окно "Запуск хранимой процедуры", в которое можно ввести входные параметры (см. рис. 1). Чтобы отсортировать результаты по имени категории, используйте CategoryName для @sortExpression значения параметра; для сортировки по имени компании поставщика используйте Имя компании. После предоставления значений параметров нажмите кнопку "ОК". Результаты отображаются в окне вывода. На рисунке 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 и просто вызывать метод GetProductsPagedAndSorted в DAL, как показано ниже.

<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, и это приводит к тому, что заголовочный текст для каждого столбца отображается как LinkButton. Когда конечный пользователь щелкает по LinkButton в заголовке, происходит постбэк, и происходят следующие действия:

1. GridView обновляет его SortExpression свойство до значения SortExpression поля, ссылку заголовка которого щелкнули
2. ObjectDataSource вызывает метод BLL GetProductsPagedAndSorted , передав свойство GridView SortExpression в качестве значения входного параметра метода sortExpression (а также соответствующие startRowIndex и maximumRows входные значения параметров)
3. BLL вызывает метод DAL 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. Однако условие, которое использует функция OVERROW_NUMBER(), не должно использовать псевдоним и должно использовать фактическое имя столбца. Таким образом, измените SupplierName значение BoundField SortExpression с "Имя поставщика" на "Имя компании" (см. рис. 9). Как показано на рисунке 10, после этого изменения результаты можно отсортировать поставщиком.

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

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

Сводка

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

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

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

Сведения о авторе

Скотт Митчелл, автор семи книг ASP/ASP.NET и основатель 4GuysFromRolla.com, работает с технологиями Microsoft Web с 1998 года. Скотт работает независимым консультантом, тренером и писателем. Его последняя книга — Sams Teach Yourself ASP.NET 2.0 за 24 часа. С ним можно связаться по адресу mitchell@4GuysFromRolla.com.

Особое спасибо кому

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

НазадДалее