Собственный пейджинг GridView через ObjectDataSource и ROW_NUMBER

Почему встроенный пейджинг медленный

Если привязать GridView к DataSet или к SqlDataSource в режиме DataSet и включить AllowPaging, таблица покажет 20 строк, но сервер прочитает из базы все записи. На справочнике из пятисот строк этого никто не заметит. На журнале операций в два миллиона строк каждый щелчок по номеру страницы превращается в многосекундную выборку и сотни мегабайт в памяти рабочего процесса.

Решение называется пользовательским пейджингом: база возвращает только строки текущей страницы, а общее количество считается отдельным лёгким запросом. GridView умеет работать в таком режиме вместе с ObjectDataSource. Сама таблица при этом не меняется: тот же пейджер, та же сортировка, только данные приходят порциями.

Как ObjectDataSource передаёт номер страницы

Когда у ObjectDataSource включено EnablePaging, он вызывает метод выборки с двумя дополнительными параметрами: индекс первой строки и количество строк. По умолчанию они называются startRowIndex и maximumRows, имена можно поменять свойствами StartRowIndexParameterName и MaximumRowsParameterName. Индекс начинается с нуля: для третьей страницы по 20 строк придёт startRowIndex = 40.

Чтобы пейджер знал, сколько всего страниц, ObjectDataSource вызывает второй метод, указанный в SelectCountMethod. Он должен принимать те же параметры фильтра, что и метод выборки (без параметров страницы), и возвращать Integer.

• SelectMethod: возвращает строки одной страницы;
• SelectCountMethod: возвращает общее число строк с учётом фильтра;
• SortParameterName: имя параметра, в который придёт выражение сортировки;
• EnablePaging=«True»: включает передачу startRowIndex и maximumRows.

Запрос страницы через ROW_NUMBER

В SQL Server 2005 появилась функция ROW_NUMBER, которая нумерует строки в заданном порядке. Оборачиваем выборку в общее табличное выражение и берём диапазон номеров. Сортировку нельзя передать параметром напрямую, поэтому выражение из GridView сверяется со списком разрешённых колонок. Так мы не открываем дыру для SQL-инъекций через параметр sortExpression.

Метод подсчёта выполняет COUNT(*) с тем же условием WHERE. На больших таблицах это тоже стоит денег, поэтому результат имеет смысл кэшировать на несколько минут, если точное число записей не критично.

Imports System.Data
Imports System.Data.SqlClient

<System.ComponentModel.DataObject()> _
Public Class OrderRepository
    Private Shared ReadOnly Cs As String = _
        ConfigurationManager.ConnectionStrings("Shop").ConnectionString

    ' Разрешённые колонки сортировки
    Private Shared Function SafeSort(ByVal expr As String) As String
        Select Case expr
            Case "Customer", "Customer DESC", "Total", "Total DESC", "CreatedAt"
                Return expr
            Case Else
                Return "CreatedAt DESC"
        End Select
    End Function

    Public Function GetPage(ByVal sortExpression As String, _
            ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As DataTable
        Dim sql As String = "WITH t AS (SELECT OrderId, Customer, Total, CreatedAt, " & _
            "ROW_NUMBER() OVER (ORDER BY " & SafeSort(sortExpression) & ") AS rn " & _
            "FROM Orders) SELECT * FROM t WHERE rn > @from AND rn <= @to ORDER BY rn"
        Using cn As New SqlConnection(Cs), cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@from", startRowIndex)
            cmd.Parameters.AddWithValue("@to", startRowIndex + maximumRows)
            Dim dt As New DataTable()
            cn.Open()
            dt.Load(cmd.ExecuteReader())
            Return dt
        End Using
    End Function

    Public Function GetCount() As Integer
        Using cn As New SqlConnection(Cs), cmd As New SqlCommand("SELECT COUNT(*) FROM Orders", cn)
            cn.Open()
            Return CInt(cmd.ExecuteScalar())
        End Using
    End Function
End Class

Разметка страницы

Разметка почти не отличается от обычной. GridView ссылается на ObjectDataSource, AllowPaging и AllowSorting включены. Важный момент: у GridView не нужно задавать VirtualItemCount или обрабатывать PageIndexChanging, всё это ObjectDataSource делает сам, получив число строк из метода GetCount.

Имя параметра сортировки указываем явно через SortParameterName. Если его не задать, ObjectDataSource отсортирует результат сам, но только внутри уже полученной страницы, а это совсем не то, что ожидает пользователь.

<asp:GridView ID="gvOrders" runat="server" DataSourceID="odsOrders"
    AllowPaging="true" PageSize="20" AllowSorting="true"
    AutoGenerateColumns="false" DataKeyNames="OrderId">
  <Columns>
    <asp:BoundField DataField="OrderId" HeaderText="№" />
    <asp:BoundField DataField="Customer" HeaderText="Клиент" SortExpression="Customer" />
    <asp:BoundField DataField="Total" HeaderText="Сумма" SortExpression="Total"
        DataFormatString="{0:N2}" />
    <asp:BoundField DataField="CreatedAt" HeaderText="Дата" SortExpression="CreatedAt"
        DataFormatString="{0:dd.MM.yyyy}" />
  </Columns>
  <PagerSettings Mode="NumericFirstLast" PageButtonCount="10" />
</asp:GridView>

<asp:ObjectDataSource ID="odsOrders" runat="server"
    TypeName="OrderRepository" SelectMethod="GetPage"
    SelectCountMethod="GetCount" EnablePaging="true"
    SortParameterName="sortExpression" />

Фильтры вместе с пейджингом

В реальном экране почти всегда есть фильтр. Параметры фильтра добавляются в SelectParameters у ObjectDataSource, например ControlParameter от выпадающего списка. Их получают оба метода: и GetPage, и GetCount. Сигнатуры методов должны совпадать по именам параметров, иначе ObjectDataSource выдаст ошибку «could not find a non-generic method» с перечнем того, что он искал.

При смене фильтра сбрасывайте PageIndex у GridView в ноль. Иначе пользователь останется на десятой странице результата, в котором после фильтрации всего три страницы, и увидит пустую таблицу.

Типичные ошибки

Большая часть проблем с пользовательским пейджингом видна сразу, если запустить SQL Server Profiler и посмотреть, какие запросы реально уходят в базу при переходе между страницами.

• Метод выборки возвращает все строки, а не страницу: пейджер работает, но выигрыша в скорости нет. Проверьте запрос профайлером.
• Нумерация ROW_NUMBER без уникального ключа в ORDER BY: при одинаковых значениях строки могут перескакивать между страницами. Добавляйте OrderId вторым полем сортировки.
• Класс репозитория создаёт соединение в конструкторе и держит его: ObjectDataSource создаёт новый экземпляр на каждый вызов, соединения начинают утекать.
• Расчёт номера последней строки как startRowIndex + maximumRows - 1 вместе с условием rn > @from: последняя строка страницы теряется.

Как это делают сегодня

Вопросы и ответы