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

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

Скачивание примера приложения или Загрузка PDF-файла

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

Введение

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

Note

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

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

Для правильной работы пользовательского разбиения по страницам необходимо реализовать некоторый метод, который может эффективно извлечь определенное подмножество записей, учитывая параметры "Индекс начальной строки" и "максимальное число строк". Для достижения этой цели можно использовать несколько методов. В предыдущем учебном курсе мы рассматривали эту задачу с помощью Microsoft SQL Server 2005 s New ROW_NUMBER() ранжирующие функция. Вкратце, функция ранжирования 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; Дополнительные сведения см. в разделе Использование для динамической сортировки результатов запроса статьи инструкции SQL CASE .
  • Создание соответствующего запроса в виде строки в хранимой процедуре, а затем использование 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 являются целочисленными параметрами, они должны быть преобразованы в nvarchar для правильного сцепления. После создания динамического SQL-запроса он выполняется с помощью sp_executesql.

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

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

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

результаты хранимых процедур отображаются в окно вывода

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

Note

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

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

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

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

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

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

Использование хранимой процедуры Жетпродуктспажедандсортед

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

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

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

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

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

Выбор имен методов

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

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

[System.ComponentModel.DataObjectMethodAttribute(
    System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.ProductsDataTable GetProductsPagedAndSorted(
    string sortExpression, int startRowIndex, int maximumRows)
{
    return Adapter.GetProductsPagedAndSorted
        (sortExpression, startRowIndex, maximumRows);
}

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

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

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

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

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

Note

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

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

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

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

результаты сортируются по UnitPrice

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

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

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

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

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

Измените SortExpression SupplierName BoundField s на CompanyName.

Рис. 9. изменение SupplierName BoundField s SortExpression на CompanyName

теперь результаты можно отсортировать по поставщику

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

Сводка

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

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

Поздравляем с программированием!

Об авторе

Скотт Митчелл, автор семи книг по ASP/ASP. NET и основатель 4GuysFromRolla.com, работал с веб-технологиями Майкрософт с 1998. Скотт работает как независимый консультант, преподаватель и модуль записи. Его последняя книга — Sams обучать себя ASP.NET 2,0 за 24 часа. Он доступен по адресу mitchell@4GuysFromRolla.com. или через его блог, который можно найти по адресу http://ScottOnWriting.NET.

Специальная благодарность

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