Редактирование и удаление строк GridView через SqlDataSource

Когда SqlDataSource действительно удобен

В Web Forms есть два способа наполнить GridView: вручную, через DataSource и DataBind в code-behind, или декларативно, через контрол источника данных. SqlDataSource относится ко второму варианту. Он хранит SQL-команды прямо в разметке страницы и сам вызывает их, когда пользователь нажимает «Изменить», «Сохранить» или «Удалить».

Для внутренних справочников, админок и простых таблиц это очень быстрый путь: страница с полноценным редактированием собирается за десять минут, а code-behind остаётся почти пустым. GridView и SqlDataSource договариваются между собой через события, поэтому вам не нужно вручную искать изменённые TextBox в строке, переключать EditIndex и заново привязывать данные.

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

Настраиваем источник данных

Источнику нужны строка подключения и по одной команде на каждую операцию: SelectCommand, UpdateCommand, DeleteCommand и при необходимости InsertCommand. Строку подключения держите в web.config и ссылайтесь на неё выражением ConnectionStrings, тогда смена сервера не потребует правки страниц.

Параметры в командах записываются с префиксом @. Для Update и Delete их нужно объявить в коллекциях UpdateParameters и DeleteParameters с правильным типом. Если тип не указать, значение придёт строкой, и SQL Server будет преобразовывать его сам, что для дат и десятичных чисел легко заканчивается ошибкой формата.

<asp:SqlDataSource ID="dsProducts" runat="server"
    ConnectionString="<%$ ConnectionStrings:Northwind %>"
    SelectCommand="SELECT ProductID, ProductName, UnitPrice, UnitsInStock
                   FROM Products ORDER BY ProductName"
    UpdateCommand="UPDATE Products SET ProductName = @ProductName,
                   UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock
                   WHERE ProductID = @ProductID"
    DeleteCommand="DELETE FROM Products WHERE ProductID = @ProductID">
    <UpdateParameters>
        <asp:Parameter Name="ProductName" Type="String" />
        <asp:Parameter Name="UnitPrice" Type="Decimal" />
        <asp:Parameter Name="UnitsInStock" Type="Int16" />
        <asp:Parameter Name="ProductID" Type="Int32" />
    </UpdateParameters>
    <DeleteParameters>
        <asp:Parameter Name="ProductID" Type="Int32" />
    </DeleteParameters>
</asp:SqlDataSource>

Подключаем GridView

Сам грид связывается с источником через DataSourceID. Ключевой атрибут здесь DataKeyNames: он сообщает GridView, какие поля составляют первичный ключ. Значения ключа сохраняются в ViewState и при обновлении передаются в параметр @ProductID. Без DataKeyNames команда UPDATE выполнится с пустым ключом и не изменит ни одной строки, причём без всякой ошибки.

Кнопки управления даёт CommandField. Поле ключа делаем ReadOnly, чтобы пользователь не мог его исправить, а для остальных колонок BoundField сам создаст TextBox в режиме редактирования.

<asp:GridView ID="gvProducts" runat="server"
    DataSourceID="dsProducts" DataKeyNames="ProductID"
    AutoGenerateColumns="False" AllowPaging="True" PageSize="15">
    <Columns>
        <asp:CommandField ShowEditButton="True" ShowDeleteButton="True"
            EditText="Изменить" UpdateText="Сохранить"
            CancelText="Отмена" DeleteText="Удалить" />
        <asp:BoundField DataField="ProductID" HeaderText="Код"
            ReadOnly="True" />
        <asp:BoundField DataField="ProductName" HeaderText="Товар" />
        <asp:BoundField DataField="UnitPrice" HeaderText="Цена"
            DataFormatString="{0:N2}" ApplyFormatInEditMode="False" />
        <asp:BoundField DataField="UnitsInStock" HeaderText="На складе" />
    </Columns>
</asp:GridView>

Как значения попадают в параметры

При нажатии «Сохранить» GridView собирает три словаря: Keys с ключом строки, OldValues со значениями до правки и NewValues с введёнными. Затем он вызывает Update у источника, а SqlDataSource сопоставляет элементы словарей с параметрами по имени.

Отсюда типичные сюрпризы. Колонка с ReadOnly не попадает в NewValues, поэтому для неё параметр получит значение по умолчанию. Колонка, которой нет в гриде вовсе, тоже ничего не передаст. Если в SelectCommand есть поле, которое не показывается, но должно сохраниться, его нужно либо вывести скрытым TemplateField, либо убрать из UpdateCommand.

Для оптимистичной блокировки у SqlDataSource есть режим ConflictDetection=«CompareAllValues». Тогда старые значения тоже передаются в команду, а их имена строятся по шаблону OldValuesParameterFormatString, например original_{0}. В WHERE остаётся сравнить их с текущими данными: если другой пользователь успел изменить запись, UPDATE затронет ноль строк.

Вмешиваемся из code-behind

Декларативная схема не мешает добавить свою логику. Событие RowUpdating грида срабатывает до вызова команды и даёт доступ к NewValues: здесь удобно обрезать пробелы, проверить диапазон и при необходимости отменить сохранение. Событие Updated источника сообщает, сколько строк реально изменилось и было ли исключение.

Protected Sub gvProducts_RowUpdating(sender As Object, _
        e As GridViewUpdateEventArgs) Handles gvProducts.RowUpdating
    ' Чистим ввод и проверяем цену до обращения к базе
    Dim name = CStr(e.NewValues("ProductName")).Trim()
    e.NewValues("ProductName") = name
    Dim price As Decimal
    If Not Decimal.TryParse(CStr(e.NewValues("UnitPrice")), price) _
            OrElse price < 0 Then
        lblMessage.Text = "Цена указана неверно"
        e.Cancel = True
    End If
End Sub

Protected Sub dsProducts_Updated(sender As Object, _
        e As SqlDataSourceStatusEventArgs) Handles dsProducts.Updated
    If e.Exception IsNot Nothing Then
        lblMessage.Text = "Не удалось сохранить: " & e.Exception.Message
        e.ExceptionHandled = True   ' страница не упадёт
    ElseIf e.AffectedRows = 0 Then
        lblMessage.Text = "Запись изменена другим пользователем"
    End If
End Sub

Удаление и частые ошибки

Удаление работает по той же схеме: кнопка Delete берёт ключ из DataKeys и передаёт его в DeleteCommand. Подтверждение лучше добавить на клиенте, заменив CommandField на TemplateField с LinkButton и атрибутом OnClientClick, который вызывает confirm. Если на запись ссылаются другие таблицы, SQL Server вернёт ошибку внешнего ключа, и её стоит перехватить в событии Deleted так же, как выше.

Список проблем, на которые чаще всего жалуются при первой настройке:

• «Must declare the scalar variable @ProductID»: не задан DataKeyNames или параметр называется иначе;
• исключение о форматировании числа: DataFormatString применён в режиме редактирования, отключите ApplyFormatInEditMode;
• сохраняются старые значения: грид заново привязывается в Page_Load при каждом postback и затирает ввод, вызывайте DataBind только при Not IsPostBack или не вызывайте вовсе;
• пустое поле превращается в NULL: за это отвечает ConvertEmptyStringToNull у BoundField и параметра.

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

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