Parâmetros em comandos do adaptador de dados

Artigo
08/25/2010

Os comandos de uma adaptador de dados — aqueles definidos na propriedade CommandText dos objetos SelectCommand,InsertCommand,UpdateCommand e DeleteCommand — geralmente envolvem parâmetros.Em tempo de execução, os parâmetros são usados para passar valores às instruções SQL ou procedimentos armazenados representados pelos comandos.

Observação:
Na versão anterior do Visual Studio, adaptadores de dados eram usados para comunicação entre um aplicativo e um banco de dados. Enquanto adaptadores de dados são ainda um componente principal do .NET Framework Providers Dados, TableAdapters são componentes gerados pelo designer que simplificam o processo de mover dados entre seu aplicativo e um banco de dados.Para obter mais informações sobre como trabalhar com TableAdapters, consulte Visão geral do TableAdapter.

Na versão anterior do Visual Studio, adaptadores de dados eram usados para comunicação entre um aplicativo e um banco de dados. Enquanto adaptadores de dados são ainda um componente principal do .NET Framework Providers Dados, TableAdapters são componentes gerados pelo designer que simplificam o processo de mover dados entre seu aplicativo e um banco de dados.Para obter mais informações sobre como trabalhar com TableAdapters, consulte Visão geral do TableAdapter.

Parâmetros são usados em dois contextos:

Parâmetros de seleção - em aplicativos de produção, você frequentemente busca somente um subconjunto dos dados em um banco de dados.Para fazer isso, use um Instrução SQL ou procedimento armazenado que inclui uma cláusula WHERE com um parâmetro para critério de seleção que você obtém em tempo de execução.Além disso, quando você atualizar ou excluir registros, você usa uma cláusula WHERE que localiza o registro ou os registros a serem alterados.Os valores usados na cláusula WHERE geralmente são derivados em tempo de execução.

Parâmetros de atualização - quando você atualiza um registro existente ou insere um novo, os valores para as colunas no registro alterado ou novo são estabelecidos em tempo de execução.Além disso, os valores usados durante verificação de concorrência otimista são estabelecidos usando parâmetros.

Observação:
Para Oracle, quando usar parâmetros nomeados em uma instrução SQL ou stored procedure, você deve preceder o nome do parâmetro com um dois-pontos (:).Entretanto, quando se faz referência a um parâmetro nomeado em outro lugar no seu código (por exemplo, ao chamar o método Add), não preceda o parâmetro nomeado com dois-pontos (:).O provedor de dados fornece o dois-pontos automaticamente.Para obter mais informações, consulte Classe OracleParameter.

Para Oracle, quando usar parâmetros nomeados em uma instrução SQL ou stored procedure, você deve preceder o nome do parâmetro com um dois-pontos (:).Entretanto, quando se faz referência a um parâmetro nomeado em outro lugar no seu código (por exemplo, ao chamar o método Add), não preceda o parâmetro nomeado com dois-pontos (:).O provedor de dados fornece o dois-pontos automaticamente.Para obter mais informações, consulte Classe OracleParameter.

Parâmetros de seleção

Ao selecionar registros para preencher um DataSet, você geralmente inclui um ou mais parâmetros na cláusula WHERE para que você possa especificar em tempo de execução os registros a serem buscados.Por exemplo, os usuários podem procurar, em um banco de dados de livros, por um palavra-chave específica de título que eles digitam em uma página da Web.Para permitir isso, você pode especificar um Instrução SQL como a seguir como a propriedade CommandText de um SelectCommand.Os parâmetros são indicados com um espaço reservado (uma ponto de interrogação) ou com uma variável nomeada parâmetro.Parâmetros de consultas envolvendo objetos OleDbCommand e OdbcCommand usam pontos de interrogação; as consultas que usam objetos SqlCommand usam parâmetros nomeados que começam com um símbolo @, enquanto objetos OracleCommand usam parâmetros nomeados que começam com dois-pontos (:).

Uma consulta que usa espaços reservados pode parecer com o seguinte:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE ?)

Uma consulta que usa parâmetros nomeados SqlCommand pode ser semelhante ao seguinte:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE @title)

Uma consulta que usa parâmetros nomeados OracleCommand pode ser semelhante ao seguinte:

SELECT BookId, Title, Author, Price from BOOKS
WHERE (Title LIKE :title)

Em seu aplicativo, você solicita ao usuário uma palavra-chave de título.Em seguida, você define o valor do parâmetro e executa o comando.

Observação:
Ocasionalmente você pode desejar obter todo o conteúdo de uma tabela de banco de dados — por exemplo, se você estiver configurando uma tabela de pesquisa — mas em geral, você deseja buscar somente os dados necessários para manter o seu aplicativo eficiente.

Em Visual Studio é possível criar instruções SQL com parâmetros usando o construtor de consultas.Se você arrastar elementos do Server Explorer, o Visual Studio pode configurar parâmetros em alguns casos, mas não todos, e você precisará concluir a configuração manualmente.

Atualizar Parâmetros

Se um objeto SelectCommand de um adaptador contém ou não um comando parametrizado, os comandos para as propriedades UpdateCommand, InsertCommand e DeleteCommand sempre contêm.

Os comandos para as propriedades UpdateCommand e InsertCommand precisam de parâmetros para cada coluna no banco de dados a ser atualizado.Além disso, as instruções UpdateCommand e DeleteCommand exigem uma cláusula WHERE parametrizada que identifica o registro a ser atualizado, semelhante à forma como o objeto SelectCommand é geralmente configurado.

Imagine um aplicativo onde os usuários podem comprar livros.Como os usuários compram, eles mantêm um carrinho de compras, que é implementado como uma tabela de dados.Na tabela ShoppingCart, os usuários mantêm um registro para cada livro que desejam comprar, com a identificação do livro e do cliente juntas atuando como a chave do registro do carrinho de compras.

Quando os usuários adicionam um livro ao seu carrinho de compras, o aplicativo pode chamar uma instrução INSERT SQL.No adaptador, a sintaxe da instrução pode ser semelhantes ao seguinte:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (?, ?, ?)

Os três pontos de interrogação representam espaços reservados para parâmetro que serão preenchidos em tempo de execução com valores para a identificação de cliente, identificação do livro e a quantidade.Se você estiver usando parâmetros nomeados, a mesma consulta pode ter esta aparência:

INSERT INTO ShoppingCart
   (BookId, CustId, Quantity)
Values (@bookid, @custid, @quantity)

Se o usuário decide alterar algo sobre um item no carrinho de compras — por exemplo, alterando a quantidade — o aplicativo pode chamar uma instrução UPDATE SQL.A sintaxe da instrução pode ser da seguinte maneira:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Ou se você estiver usando parâmetros nomeados, ela pode ser da seguinte maneira:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Nesta instrução, os parâmetros na cláusula SET são preenchidos com valores atualizados para o registro alterado.Os parâmetros na cláusula WHERE identificam qual registro a ser atualizado e são preenchidos com os valores originais do registro.

Um usuário pode também remover um item do carrinho de compras.Nesse caso, o aplicativo pode chamar uma instrução DELETE SQL com sintaxe como a seguir, se você estiver usando espaços reservados para parâmetro :

DELETE FROM ShoppingCart
WHERE (BookId = ? AND CustId = ?)

Ou o seguinte se você estiver usando parâmetros nomeados:

DELETE FROM ShoppingCart
WHERE (BookId = @bookid AND CustId = @custid)

A coleção de parâmetros e objetos de parâmetro

Para permitir que você passe valores de parâmetro em tempo de execução, cada um dos quatro objetos de comando para um adaptador de dados suporta uma propriedade Parameters.A propriedade contém uma coleção de objetos de parâmetro individuais que correspondem um-a-um com os espaços reservados em uma instrução.

A tabela a seguir mostra a coleção de parâmetro correspondente para cada adaptador de dados:

Adaptador de dados	Coleção de parâmetro
SqlDataAdapter	SqlParameterCollection
OleDbDataAdapter	OleDbParameterCollection
OdbcDataAdapter	OdbcParameterCollection
OracleDataAdapter	OracleParameterCollection

Observação:
Para Oracle, quando usar parâmetros nomeados em uma instrução SQL ou stored procedure, você deve preceder o nome do parâmetro com um dois-pontos (:).Entretanto, quando se faz referência a um parâmetro nomeado em outro lugar no seu código (por exemplo, ao chamar o método Add), não preceda o parâmetro nomeado com dois-pontos (:).O Provedor de Dados .Net Framework para Oracle fornece automaticamente os dois-pontos.

Para Oracle, quando usar parâmetros nomeados em uma instrução SQL ou stored procedure, você deve preceder o nome do parâmetro com um dois-pontos (:).Entretanto, quando se faz referência a um parâmetro nomeado em outro lugar no seu código (por exemplo, ao chamar o método Add), não preceda o parâmetro nomeado com dois-pontos (:).O Provedor de Dados .Net Framework para Oracle fornece automaticamente os dois-pontos.

Usando a coleção de parâmetros, você poupa o trabalho de ter que criar manualmente um comando SQL como uma sequência de caracteres com valores em tempo de execução; além disso, você se beneficia da checagem de tipo em seus parâmetros.

Se você usar o Data Adapter Configuration Wizard para configurar o adaptador, a coleção de parâmetros é definida e configurada automaticamente para todos os quatro comandos do adaptador.Se você arrastar elementos do Server Explorer para o formulário ou um componente, o Visual Studio pode executar as seguintes configurações:

Se você arrastar uma tabela ou algumas colunas até o designer, o Visual Studio gera um objeto SelectCommand (especificamente, uma instrução SQL SELECT) sem parâmetros e objetos UpdateCommand, InsertCommand e DeleteCommand parametrizados.Se você desejar que a instrução do objeto SelectCommand tenha parâmetros, você deve configurá-los manualmente.
Se você arrastar um procedimento armazenado para o designer, o Visual Studio gera um objeto SelectCommand com parâmetros conforme requerido pelo procedimento armazenado.No entanto, se você precisar deles, você deve configurar os objetos UpdateCommand,InsertCommand e DeleteCommand você mesmo, juntamente com os parâmetros deles.

Em termos gerais, se você desejar criar consultas parametrizadas para o adaptador, você deve usar o Data Adapter Configuration Wizard.No entanto, caso seja necessário, você pode configurar os parâmetros manualmente usando a janela Propriedades.

Estrutura da coleção de parâmetros

Os itens na coleção de parâmetros de um comando correspondem um-a-um aos parâmetros necessários para o objeto de comando correspondente.Se o objeto de comando for uma Instrução SQL, os itens na coleção correspondem aos espaços reservados (pontos de interrogação) na instrução.A seguinte instrução UPDATE requer uma coleção de cinco itens de parâmetros:

UPDATE ShoppingCart
   SET (BookId = ?, CustId = ?, Quantity = ?)
WHERE (BookId = ? AND CustId = ?)

Aqui é a mesma instrução com parâmetros nomeados:

UPDATE ShoppingCart
   SET (BookId = @bookid, CustId = @custid, Quantity = @quantity)
WHERE (BookId = @bookid AND CustId = @custid)

Se o objeto de comando faz referência a um procedimento armazenado, o número de itens de parâmetro na coleção é determinado pelo próprio procedimento.Os parâmetros podem não corresponder exatamente aos espaços reservados em uma Instrução SQL.

Nos procedimentos armazenados, parâmetros também podem ser nomeados.Nesse caso, a posição de um parâmetro na coleção não é importante.Em vez disso, cada item de parâmetro na coleção possui uma propriedade ParameterName que é usada para ligá-lo ao parâmetro correspondente no procedimento armazenado.

Se você estiver configurando a coleção de parâmetros manualmente, você deve compreender exatamente quais parâmetros o procedimento armazenado requer.Muitos procedimentos armazenados retornam um valor; se isso acontecer, o valor é passado de volta para seu aplicativo na coleção de parâmetros, portanto você deve permitir isso.Além disso, alguns procedimentos armazenados incluem várias instruções SQL, e você deve se certificar de que a coleção de parâmetros reflete todos os valores passados para todas as instruções no procedimento.

Se os parâmetros não são nomeados (como em procedimentos armazenados), os itens na coleção mapeiam posicionalmente para os parâmetros requeridos pelo comando.Se o comando for um procedimento armazenado e ele retorna um valor, o primeiro item na coleção (item zero) é reservado para este valor de retorno.

Você pode, portanto, referenciar objetos individuais de parâmetro pela posição do índice na coleção.No entanto, objetos de parâmetro também suportam uma propriedade ParameterName que fornece uma maneira de referenciar parâmetros independentemente da ordem.Por exemplo, as duas instruções a seguir podem ser equivalentes (presumindo-se que o segundo parâmetro na coleção é chamado Title_Keyword):

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters(1).Value = titleKeyword
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword

// Encloses the keyword in SQL wildcard characters.
string titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters[1].Value = titleKeyword;
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;

Usar um nome de parâmetro é geralmente uma prática de programação melhor do que referenciar parâmetros pelo valor de índice, pois ele reduz a necessidade de manutenção se o número de parâmetros for alterado e libera você de precisar lembrar se um procedimento armazenado retorna um valor.Há pequena uma sobrecarga adicional na referência a um parâmetro pelo nome em vez do valor do índice, mas isso pode ser compensado pela facilidade de programação e manutenabilidade de aplicativo.

Estabelecendo valores de parâmetro

Há duas maneiras para você estabelecer o valor de um parâmetro:

Definindo a propriedade Value do parâmetro explicitamente.
Mapeando parâmetros para colunas em uma tabela do dataset, para que os valores possam ser extraídos de linhas de dados quando necessário.

Você define o valor do parâmetro explicitamente quando está preenchendo um DataSet ou chamando um comando — ou seja, para seleção de parâmetros.Por exemplo, no exemplo acima da pesquisa de livros, o aplicativo pode ter um caixa de texto onde os usuários inserem uma palavra-chave de título.Você poderia então explicitamente definir o valor do parâmetro para o texto da caixa de texto antes de chamar o método Fill do adaptador.Código para fazer isso pode parecer com o seguinte, que estabelece o conteúdo de um caixa de texto como um parâmetro antes de preencher um DataSet.

' Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" & txtTitleKeyword.Text & "%"
OleDbDataAdapter1.SelectCommand.Parameters("Title_Keyword").Value = titleKeyword
OleDbDataAdapter1.Fill(dsAuthors1)

// Encloses the keyword in SQL wildcard characters.
titleKeyword = "%" + txtTitleKeyword.Text + "%";
this.OleDbDataAdapter1.SelectCommand.Parameters["Title_Keyword"].Value = titleKeyword;
this.OleDbDataAdapter1.Fill(dsAuthors1);

Valores mapeados de parâmetro são usados durante as atualizações.Quando você chama um método Update de um adaptador, o método percorre os registros na tabela de um DataSet, individualmente fazendo a atualização apropriada (atualizar, inserir, excluir) para cada registro.Nesse caso, os valores de parâmetro já estão disponíveis como colunas nos registros do DataSet.Por exemplo, quando o processo de atualização chega a um novo registro na tabela do DataSet — um registro para o qual ele deve chamar uma instrução INSERT no banco de dados — os valores para a cláusula VALUE da instrução INSERT podem ser lidos diretamente do registro.

Esses são cenários típicos, mas não os únicos.Os procedimentos armazenados, às vezes, retornam dados usando parâmetros de saída ou por meio do valor de retorno do procedimento.Em caso afirmativo, os valores retornados devem ser mapeados para colunas em uma tabela de DataSet.

Também é possível definir parâmetros de atualização explicitamente.O adaptador dá suporte a um evento RowUpdating chamado sempre que uma linha está sendo atualizada.Você pode criar um manipulador para este evento e definir valores de parâmetro lá.Isso permite que você tenha controle muito preciso sobre valores de parâmetros e permite que você execute processos como criar dinamicamente valores de parâmetro antes de eles serem gravados em um registro de banco de dados.