Usando parâmetros com o controle SqlDataSource

Artigo
10/15/2014

Instruções SQL e procedimentos armazenados frequentemente incluem parâmetros que são avaliados em tempo de execução.Uma instrução SQL escrita com parâmetros é chamada de uma instrução SQL parametrizada.

Ao usar o controle SqlDataSource, você pode especificar consultas SQL e instruções que usam parâmetros.Isso ajuda a tornar seus cenários de vinculação de dados mais flexíveis pela leitura e gravação de informações do banco de dados com base nos valores que são avaliados em tempo de execução.Você pode obter valores de parâmetros de várias fontes, inclusive as variáveis de aplicativo do ASP.NET, identidades de usuários, e valores selecionados pelo usuário.Você pode utilizar parâmetros para fornecer critérios de pesquisa para a recuperação de dados; para fornecer valores a serem inseridos, atualizados, ou excluídos em um armazenamento de dados; e para fornecer valores para classificação, paginação, e filtragem.

Utilizando Parâmetros

Como todos os controles fonte de dados, o controle SqlDataSource aceita parâmetros de entrada em tempo de execução e os gerencia em coleções de parâmetros.Cada operação de dados tem uma coleção de parâmetros relacionada.Para operações de seleção, você pode usar a coleção SelectParameters; para obter atualizações, você pode usar a coleção UpdateParameters; e assim por diante.

Você pode especificar um nome, tipo, direção, e valor padrão para cada parâmetro.Parâmetros que obtêm valores de um objeto específico, como um controle, variável de sessão, ou o perfil do usuário, exigem que você defina propriedades adicionais.Por exemplo, um ControlParameter requer que você defina ControlID para identificar o controle para tirar o valor do parâmetro e defina a propriedade PropertyName para especificar a propriedade que contém o valor do parâmetro.Para obter mais informações, consulte Usando parâmetros com controles de fonte de dados.

O controle SqlDataSource também criará parâmetros automaticamente com base nos valores passados por um controle vinculado a dados (como um controle GridView ou FormView) que oferece suporte às operações de atualização automática, inserção e exclusão.Para obter mais informações, consulte Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados.

Especificando parâmetros em comandos

Quando você usa o controle SqlDataSource, você pode definir as propriedades do comando de controle para instruções parametrizadas SQL ou para o nome de um procedimento armazenado.Se você especificar um procedimento armazenado para um comando, você deve especificar que o tipo de linha de comando do comando é StoredProcedure.

Nomes de parâmetro

O controle SqlDataSource adiciona o valor da propriedade ParameterPrefix para o início de todos os nomes de parâmetro.(O prefixo padrão é " @ ".)

Se um controle vinculado a dados como um controle GridView é vinculado ao controle SqlDataSource, durante uma atualização ou operação de exclusão o controle vinculado a dados passa tanto valores atuais quanto originais de registro para o controle SqlDataSource.Os valores atuais são passados no dicionário Values.Os valores originais são passados em Keys ou dicionários OldValues.O conteúdo desses dicionários é acrescentado à subsequente coleção Parameters do objeto DbCommand de uma operação de dados fornecidos.

Nos comandos do controle SQL SqlDataSource, você usa uma convenção de nomeclatura para corresponder ao parâmetro espaços reservados para os valores antigos passados para o comando.Você estabelece o formato do nome do espaço reservado, definindo a propriedade OldValuesParameterFormatString do controle SqlDataSource.Set the OldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field.Por exemplo, se você definir a propriedade OldValuesParameterFormatString para "old_ {0}", os nomes dos parâmetros de valor original serão resolvidos para o campo nome prefixado com "@old_".Consider an update operation that involves a field named LastModifiedDate.The current value for the field is passed in the Values dictionary and the original value for the field is passed in the OldValues dictionary.A parameter named @LastModifiedDate is created to pass the current value and a parameter named @old\_LastModifiedDate is created to pass the original value.Em seguida, você pode incluir ambos os parâmetros em um Instrução SQL para diferenciar entre os valores atuais e originais do campo, conforme mostrado no exemplo a seguir:

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

A capacidade de separar valores atuais e originais em um comando é essencial ao executar verificação de concorrência otimista ou ao trabalhar com uma fonte de dados onde a chave primária pode ser modificada.

Para obter mais informações sobre os dicionários passados por um controle vinculado a dados, consulte Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados.

Usando parâmetros com o provedor SqlClient

Por padrão, o controle SqlDataSource usa o provedor de dados System.Data.SqlClient para trabalhar com Servidor SQL como a fonte de dados.O provedor System.Data.SqlClient oferece suporte a parâmetros nomeados como espaços reservados, como mostrado no exemplo a seguir:

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

Com parâmetros nomeados, a ordem especificada dos parâmetros na coleção de parâmetros do comando é importante.Entretanto, você deve garantir que os nomes de parâmetro que você usar no comando SQL correspondem aos nomes dos parâmetros na coleção associada.

O exemplo a seguir mostra como usar parâmetros nomeados em um comando SQL para um controle SqlDataSource que usa o provedor 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>

Usando parâmetros com os provedores OLEDB e ODBC

Se você estiver se conectando a uma fonte de dados OLE DB ou ODBC, você pode configurar o controle SqlDataSource para usar o provedor System.Data.OleDb ou System.Data.Odbc para trabalhar com sua fonte de dados, respectivamente.Os provedores System.Data.OleDb e System.Data.Odbc oferecem suporte somente a parâmetros posicionais identificados pelo caractere "? ", como mostrado no exemplo o seguir:

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

Quando você usa os provedores System.Data.OleDb e System.Data.Odbc com instruções SQL parametrizadas, a ordem em que você especificar espaços reservados de parâmetro deve coincidir com a ordem dos parâmetros na coleção de parâmetros relacionada.Você pode controlar a ordem dos parâmetros explicitamente especificando-os na coleção para a operação de dados relacionados, como a coleção UpdateParameters para os relacionados UpdateCommand.Quando você cria explicitamente uma coleção de parâmetro para parâmetros criados automaticamente a partir dos valores passados por um controle vinculado a dados,os parâmetros criados explicitamente substituirão os parâmetros que são gerados automaticamente.Dessa forma você garante que os parâmetros são passados na ordem que você deseja.Se você chamar um procedimento armazenado que retorna um valor, você deve especificar um parâmetro com um valor Direction de ReturnValue como o primeiro parâmetro na coleção de parâmetros de linha de comando.

Observação:
Por padrão, parâmetros com base nos campos acoplados de um controle limite a dados são adicionados ao comando dos dicionários de parâmetros na seguinte ordem: Values, Keys, OldValues.Para as operações de exclusão, somente o dicionário Keys é usado.Para operações de inserção, somente o dicionário Values é usado.Para obter mais informações sobre os dicionários passados por um controle vinculado a dados, consulte Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados.

Por padrão, parâmetros com base nos campos acoplados de um controle limite a dados são adicionados ao comando dos dicionários de parâmetros na seguinte ordem: Values, Keys, OldValues.Para as operações de exclusão, somente o dicionário Keys é usado.Para operações de inserção, somente o dicionário Values é usado.Para obter mais informações sobre os dicionários passados por um controle vinculado a dados, consulte Como um Controle da Fonte de Dados Cria Parâmetros para Campos Ligados a Dados.

O exemplo a seguir mostra como especificar parâmetros para um controle SqlDataSource que usa o provedor System.Data.OleDb.Os parâmetros são especificados explicitamente para garantir que a ordem dos parâmetros na coleção corresponda à ordem dos espaços reservados nas instruções 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>