Использование параметров с элементом управления SqlDataSource

Статья
10/22/2014

Обновлен: Ноябрь 2007

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

При использовании элемента управления SqlDataSource разработчик может определить SQL-запросы и инструкции SQL, использующие параметры. Это помогает сделать сценарии привязки данных более гибкими, позволяя считывать и записывать данные базы данных в зависимости от значений, вычисляемых во время выполнения. Для получения значений параметров можно использовать различные источники, включая параметры приложения ASP.NET, удостоверения пользователей и выбираемые пользователем значения. Параметры можно использовать, чтобы предоставить условия поиска для извлечения данных, чтобы предоставить значения для выполнения операций вставки, изменения или удаления с хранилищем данных и чтобы предоставить значения для сортировки, разбиения по страницам и фильтрации.

Использование параметров

Как и все элементы управления источниками данных, элемент управления SqlDataSource принимает входные параметры во время выполнения и управляет ими в коллекциях параметров. С каждой операцией данных связана определенная коллекция параметров. Для операций выбора можно использовать коллекцию SelectParameters, для обновлений можно использовать коллекцию UpdateParameters и так далее.

Для каждого параметра можно задать имя, тип, направление и значение по умолчанию. Для параметров, которые получают значения от конкретного объекта, например, от элемента управления, переменной сеанса или профиля пользователя, необходимо задать дополнительные свойства. Например, параметр ControlParameter требует, чтобы разработчик задал свойство ControlID, определив тем самым элемент управления для получения значения параметра, и свойство PropertyName, определив тем самым свойство, содержащее значение параметра. Дополнительные сведения см. в разделе Использование параметров с элементами управления источников данных.

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

Задание параметров в командах

При использовании элемента управления SqlDataSource разработчик может установить свойства команды элемента управления равными параметризованным инструкциям SQL или имени хранимой процедуры. Если для команды задана хранимая процедура, необходимо определить, что тип этой команды равен StoredProcedure.

Имена параметров

Элемент управления SqlDataSource добавляет в начало имен всех параметров значение свойства ParameterPrefix. (префиксом по умолчанию является «@»).

Если элемент управления с привязкой к данным, например элемент управления GridView, связан с элементом управления SqlDataSource, то в ходе операции обновления или удаления элемент управления с привязкой к данным передает элементу управления SqlDataSource как текущее, так и исходное значение записи. Текущие значения передаются в словаре Values. Исходные значения передаются в словарях Keys или OldValues. Содержимое этих словарей добавляется к коллекции Parameters базового объекта DbCommand для данной операции с данными.

Чтобы местозаполнители параметров соответствовали старым значениям, переданным в команду, в командах SQL элемента управления SqlDataSource следует использовать соглашение об именах. Задать формат имени местозаполнителя можно, устанавливая свойство OldValuesParameterFormatString элемента управления SqlDataSource. Установите свойство OldValuesParameterFormatString равным строке, содержащей «{0}» в качестве местозаполнителя для имени поля. Например, если свойство OldValuesParameterFormatString установлено равным «old_{0}», то имена параметров исходных значений будут разрешаться в имя поля с префиксом «@old_». Рассмотрим операцию обновления для поля с именем LastModifiedDate. Текущее значение поля передается в словарь Values, а исходное значение поля передается в словарь OldValues. Параметр с именем @LastModifiedDate создается для передачи текущего значения, а параметр с именем @old\_LastModifiedDate — для передачи исходного значения. Затем можно включить оба параметра в инструкцию SQL, чтобы различать текущие и исходные значения поля, как показано в следующем примере:

UPDATE Table1 SET LastModifiedDate = @LastModifiedDate
  WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate

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

Дополнительные сведения о словарях, передаваемых элементом управления с привязкой к данным, см. в разделе Создание параметров для полей, привязанных к данным, элементом управления источником данных.

Использование параметров с поставщиком SqlClient

По умолчанию элемент управления SqlDataSource использует поставщик данных System.Data.SqlClient для работы с сервером SQL Server в качестве источника данных. Поставщик System.Data.SqlClient поддерживает в качестве местозаполнителей именованные параметры, как показано в следующем примере:

SELECT * FROM Employees WHERE LastName = @LastName 
  AND FirstName = @FirstName

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

В следующем примере показано, как использовать именованные параметры в команде SQL для элемента управления SqlDataSource, использующего поставщик System.Data.SqlClient.

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

<asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>

Использование параметров с поставщиками OLE DB и ODBC

При подключении к источнику данных OLE DB или ODBC можно настроить элемент управления SqlDataSource, чтобы использовать поставщик System.Data.OleDb или System.Data.Odbc, соответственно, для работы с источником данных. Поставщики System.Data.OleDb и System.Data.Odbc поддерживают позиционные параметры, заданные символом «?», как показано в следующем примере:

SELECT * FROM Employees WHERE LastName = ? AND FirstName = ?

При использовании поставщиков System.Data.OleDb и System.Data.Odbc с параметризованными инструкциями SQL, порядок, в котором заданы местозаполнители параметров, должен соответствовать порядку параметров в связанной коллекции параметров. Разработчик может управлять порядком параметров, явно задавая их в коллекции для связанной операции с данными, например в коллекции UpdateParameters для команды UpdateCommand. При явном задании коллекции для параметров, автоматически создаваемых из значений, переданных элементом управления с привязкой к данным, явно созданные параметры заменяют все автоматически созданные параметры. Это позволяет гарантировать передачу параметров в нужном порядке. При вызове хранимой процедуры, возвращающей значение, в качестве первого параметра коллекции параметров команды необходимо задать параметр со значением Direction, равным ReturnValue.

Примечание.
По умолчанию параметры, основанные на присоединенных полях элемента управления с привязкой к данным, добавляются в команду из словарей параметров в следующем порядке: Values, Keys, OldValues. Для операций удаления используется только словарь Keys. Для операций вставки используется только словарь Values. Дополнительные сведения о словарях, передаваемых элементом управления с привязкой к данным, см. в разделе Создание параметров для полей, привязанных к данным, элементом управления источником данных.

По умолчанию параметры, основанные на присоединенных полях элемента управления с привязкой к данным, добавляются в команду из словарей параметров в следующем порядке: Values, Keys, OldValues. Для операций удаления используется только словарь Keys. Для операций вставки используется только словарь Values. Дополнительные сведения о словарях, передаваемых элементом управления с привязкой к данным, см. в разделе Создание параметров для полей, привязанных к данным, элементом управления источником данных.

В следующем примере показывается, как задать параметры для элемента управления SqlDataSource, использующего поставщик System.Data.OleDb. Параметры задаются явно, чтобы гарантировать соответствие порядка параметров в коллекции порядку местозаполнителей в инструкциях SQL.

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>

<Fields>                  
  <asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>                    
  <asp:BoundField DataField="FirstName"  HeaderText="First Name"/>
  <asp:BoundField DataField="LastName"   HeaderText="Last Name"/>                    
  <asp:TemplateField HeaderText="Birth Date">
    <ItemTemplate> 
      <asp:Label ID="BirthDateLabel" Runat="Server" 
                 Text='<%# Eval("BirthDate", "{0:d}") %>' />
    </ItemTemplate>
    <InsertItemTemplate>
      <asp:Calendar ID="InsertBirthDateCalendar" Runat="Server"
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </InsertItemTemplate>
    <EditItemTemplate>
      <asp:Calendar ID="EditBirthDateCalendar" Runat="Server"
                    VisibleDate='<%# Eval("BirthDate") %>'
                    SelectedDate='<%# Bind("BirthDate") %>' />
    </EditItemTemplate>
  </asp:TemplateField>                    
</Fields>