C# - ADO .NET para iniciantes - Usando Parâmetros

C# - Trabalhando com parâmetros em consultas SQL

Esta chegando agora ???

Então acompanhe os primeiros artigos:

Se você já conhece VB .NET e esta querendo a aprender C# sugiro que você leia o meu artigo:

VB.NET e C# - Guia rápido de referência comparativa

O material necessário para acompanhar o curso é: (Irei utilizar o Visual C# 2008 Express Edition)

1- Visual C# 2008 Express Edition (vou dar preferência ao Visual C#)
2- SharpDevelop 2.2 (opcional)
3- SQL Server 2005 Express Edition

Nos dois últimos artigos tratamos do DataSet e do DataSet tipado e neste artigo veremos como usar parâmetros nos comandos usados para selecionar e efetuar operações de atualização em banco de dados usando o objeto SqlCommand/OledbCommand.

Nota: Embora eu esteja dando ênfase a classe SqlCommand as mesmas propriedades e sintaxe se aplicam a classe OledbCommand. (As exceções serão comentadas)

Veremos quais os benefícios em usar parâmetros , como criar e como atribuir parâmetros aos comandos.

O modelo de acesso a dados usado pela ADO .NET pode ser resumido da seguinte forma:

1- Você abre uma conexão;(SqlConnection)
2- Cria um comando (SqlCommand)
2- Obtêm os dados ou realiza uma operação;
3- Você fecha a conexão;

Muito simples não é mesmo ?

Quando criamos um comando a intenção é usá-lo para obter informações ou realizar uma operação de atualização ou exclusão na base de dados.

Ao trabalharmos com dados com frequência desejamos filtrar os resultados baseados em algum critério. Geralmente esta tarefa é feita permitindo que o usuário informe um valor para o parâmetro para em seguida criar uma instrução SQL com esse valor.

Os objetos Command usam parâmetros para passar valores para as instruções SQL ou procedimentos armazenados, fornecendo uma verificação de tipo e validação.

Ao contrário do texto de comando, a entrada de parâmetros é tratada como um valor literal, não como código executável. Isso ajuda na proteção contra ataques "injeção de SQL", no qual um invasor insere um comando em uma instrução SQL o que pode causar o comprometimento da segurança no servidor.

Por exemplo, para obter todos os pedidos da tabela Customers do banco de dados Northwind o comando poderia ser criado da seguinte forma:

SqlCommand cmd = new SqlCommand("select * from Customers", conn);

cmd - instância da classe SqlCommand
select * from Customers - instrução sql para selecionar todos os clientes da tabela Customers;
conn - a conexão com o banco de dados;

Para filtrar os clientes por cidade poderíamos construir o seguinte comando:

SqlCommand cmd = new SqlCommand("select * from Customers Where City =" + txtCidade.text , conn);

cmd - instância da classe SqlCommand
select * from Customers where city = instrução sql para selecionar todos os clientes da tabela Customers usando o critério da cidade;
txtCidade.text = O valor informado pelo cliente em um controle TextBox e usado para montar a instrução SQL;
conn - a conexão com o banco de dados;

Antes de ir direto ao ponto preciso dizer que você nunca deve usar este tipo de construção em seus comandos. Esse tipo de construção abre uma porta que seja feita um ataque conhecido como injeção SQL, onde um código malicioso pode ser injetado no seu código.

Para evitar esse tipo de problema devemos usar parâmetros pois qualquer informação colocada como parâmetro será tratada como um campo de dados, e não como parte da instrução SQL tornando assim sua aplicação mais segura.

Na construção de uma consulta parametrizada devemos seguir 3 etapas:

Construir uma string para o comando SqlCommand/OledbCommand com parâmetros;
Declarar o objeto SqlParameter/OledbParameter, atribuindo os valores aproprieados;
Atribrui o objeto SqlParameter/OedbParameter para a propriedade Parameters do objeto SqlCommand/OledbCommand;

Vamos então construir uma comando seguindo essas etapas.

O primeiro passo para utilizar parâmetros em consultas SQL é construir uma string para o comando contendo o nome parâmetro, o qual será preenchido com o valor do parâmetro quando o SqlCommand/OledbCommand for executado. A sintaxe padrão para um parâmetro é usar o símbolo @ como prefixo do nome do parâmetro.

Quando utilizamos SQL Server (SqlClient), o correto é utilizarmos o caracter "@" seguido do nome do parametro.
No Oracle (OracleClient), ao invés do caracter "@", utilizamos o ":" também seguido do nome do parametro.
Nesses dois casos, a ordem de inserção dos parametros na coleção dos mesmos no XXXCommand não importa.
Quando trabalhamos com OleDb, ou com o banco de dados MySQL o caractere usado é o "?" seguido do nome do parâmetro.
No caso da utilização do OleDb ordem de inserção é importante e deve ser considerada.

A seguir temos uma tabela com a sintaxe de nomeação para os provedores mais importantes:

Provedor de Dados	Sintaxe de nomeação do Parâmetro
SqlClient	Usa Parâmetros Nomeados mp formato @ParameterName
OleDB	Usa os marcadores de parâmetro posicional indicados por um ponto de interrogação (?)
Odbc	Usa os marcadores de parâmetro posicional indicados por um ponto de interrogação (?)
OracleClient	Usa Parâmetros Nomeados no :parmname (ou parmname)

Ex: Declarando um objeto command com parâmetro:

SqlCommand cmd = new SqlCommand("select * from Customers Where City = @cidade", conn);

No construtor do SqlCommand usado acima o primeiro argumento contém uma declaração de parâmetro, @cidade.

Este exemplo usa um único parâmetro, mas você pode ter tantos parâmetros quanto precisar para personlizar a sua consulta.

Cada parâmetro irá coincidir com o objeto SqlParameter que precisa ser atribuido a este objeto SqlCommand.

Cada parâmetro na instrução SQL precisa ser definido. Este é o propósito do tipo SqlParameter. O seu código precisar definir uma instância de SqlParameter para cada parâmetro no comando SQL do objeto SqlCommand.

Para criar um parâmetro basta criar uma instância da classe SqlParameter class e fornecer a informação necessária
como : name, value, type, size, direction, e assim por diante. Abaixo temos as propriedades da classe Sqlparameter

Propriedade

Descrição

ParameterName

Obtêm ou define o nome do parâmetro

SqlDbType

Obtêm ou define o tipo de dados do valor do parâmetro.Ex: VarChar, Char, Money, Int, Float, Decimal, Bit, Variant, NVarChar, DateTime, etc.

Size

Obtêm ou define o tamanho do valor do parâmetro.

Direction

Obtêm ou define a direção do parâmetro: Input, Output, ou InputOutput

Nome	Descrição
Input	O parâmetro é um parâmetro de entrda. Este é o padrão
Output	O parâmetro é um parâmetro de saída.
InputOutput	O parâmetro é de entrada e saída.
ReturnValue	O parâmetro representa um valor de retorno de um procedimento armazenado.

SourceColumn

Mapeia a coluna a partir de um DataTable para o parâmetro.Permite que você execute múltiplos comandos usando o objeto SqlDataAdapter e pegue os valores corretos dos parâmetros da coluna do DataTable durante a execução.

Value

Obtêm ou define o valor fornecido para o objeto parâmetro.

O código abaixo define um parâmetro para o parâmetro @cidade , o nome do parâmetro e o valor que o mesmo vai receber:

SqlParameter param = new SqlParameter();
param.ParameterName = "@cidade";
param.Value = txtCidade.text;

Observe que a propriedade ParameterName da instância SqlParameter deve ser corretamente informada da mesma forma que foi usada na string de comando do SqlCommand.

O valor, que também deve ser definido, irá substituir o parâmetro quando da execução do objeto SqlCommand.

A seguir temos um exemplo de utilização de parâmetros com a sintaxe usada para definir parâmetros para os banco de dados SQL Server(@), MySQL(?) e Oracle(:);

C# .NET
// Define o comando SQL Server para obter o produto com ID = 100 .
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Product WHERE Product.ID=@PROD_ID";

command.Parameters.Add(new SqlParameter("@PROD_ID", 100));

// Executa o comando SQL Server...
SqlDataReader reader = command.ExecuteReader();
DataTable tblProducts = new DataTable();
tblProducts.Load(reader);

foreach (DataRow rowProduct in tblProducts.Rows)
{
// Usa os dados...
}

//Define o comando MySQL para obter o produto com ID = 100 .
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT * FROM Product WHERE Product.ID=?PROD_ID";

command.Parameters.Add(new MySqlParameter("?PROD_ID", 100));

// Executa o comando MySQL...
MySqlDataReader reader = command.ExecuteReader();
DataTable tblProducts = new DataTable();
tblProducts.Load(reader);

foreach (DataRow rowProduct in tblProducts.Rows)
{
// Usa os dados...
}

//Define o comando Oracle para obter o produto com ID = 100 ...
OracleCommand command = new OracleCommand();
command.CommandText = "SELECT * FROM Product WHERE Product.ID=:PROD_ID";

command.Parameters.Add(new OracleParameter(":PROD_ID", 100));

// Executa o comando Oracle...
OracleDataReader reader = command.ExecuteReader();
DataTable tblProducts = new DataTable();
tblProducts.Load(reader);

foreach (DataRow rowProduct in tblProducts.Rows)
{
// Usa os dados...
}

Obs: Quando você envia um valor do parâmetro nulo para o servidor, você deve especificar DBNull, não null (Nothing no Visual Basic).

Para cada parâmetro definido no argumento string do comando SQL para um objeto SqlCommand você precisa definir um SqlParameter e também tem que permitir que o objeto SqlCommand conheça o SqlParameter através da atribuição da instância SqlParameter para a propriedade Parameters do objeto SqlCommand.

cmd.Parameters.Add(param);

A instância SqlParameter é o argumento para o método Add.

Agora vamos juntar todas as partes e usar um exemplo completo para mostrar a utilização de parâmetros.

O exemplo a seguir usa objetos SqlParameter para selecionar os clientes da tabela Customers por cidade exibindo o nome da empresa e o nome do contato.

Criando uma consulta SQL com parâmetro

Para este exemplo vou usar o banco de dados Northwind.mdf que esta instalado em minha máquina local.

Abra o Visual C# 2008 Express Edition e crie um novo projeto Windows Forms Application com o nome de usandoParametrosSQL;

No formulário padrão form1.cs inclua os controles : Listbox - lstClientes , Button - btnCarregar e ComboBox - cboCidade, conforme o leiaute abaixo;

Vamos definir o namespace System.Data.SqlClient no formulário pois vamos acessar um banco de dados SQL Server;

using System.Data.SqlClient;

A seguir vamos cadastrar alguns itens no controle ComboBox - cboCidade conforme a figura abaixo:

Agora no evento Click do botão Carregar Dados inclua o código abaixo:

      private void btnCarregar_Click(object sender, EventArgs e)
        {
            //Verifica se foi selecionada uma cidade
            if (cboCidade.SelectedIndex == -1)
            {
                MessageBox.Show("Selecione uma cidade.");
                return;
            }
            //limpa o controle ListBox
            lstClientes.Items.Clear();

            //define os objetos connection e datareader
            SqlConnection conn = null;
            SqlDataReader reader = null ;

            try
            {
                // instancia e abre uma conexão
                conn = new
                    SqlConnection("Server=.\\SQLEXPRESS;DataBase=Northwind;Integrated Security=SSPI");
                conn.Open();

                // JAMAIS UTILIZE INSTRUÇÕES SQL COMO ESTA , perigo de injeção SQL com código malicioso
                //SqlCommand cmd = new SqlCommand(Select * from Customers where city = '" + txtcidade.text + "'";

                // 1. declara o objeto command com parâmetro
                SqlCommand cmd = new SqlCommand("select * from Customers where city = @Cidade", conn);

                // 2. define os parêmetros usados no objeto command
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@Cidade";
                param.Value = cboCidade.Text;

                // 3. inclui um novo parâmetro ao comando
                cmd.Parameters.Add(param);

                // obtem os dados
                reader = cmd.ExecuteReader();

                // exibe no listbox os dados para o codigo do cliente, empresa e nome do contato
                while (reader.Read())
                {
                    lstClientes.Items.Add(reader["CustomerID"] + "  " + reader["CompanyName"] + "    " + reader["ContactName"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro : " + ex.Message);
            }
            finally
            {
                // fecha o leitor
                if (reader != null)
                {
                    reader.Close();
                }

                // fecha a conexão
                if (conn != null)
                {
                    conn.Close();
                }
            }

Pegue o projeto completo aqui : usandoParametrosSQL.zip