ADO.NET: Introdução

Com o advento Framework.Net, se possibilitou novos recursos de acessos a dados, de forma a facilitar do desenvolvimento até migração entre plataformas de SGDB.

O ADO.Net, principal recurso de acesso a banco de dados na plataforma .Net, se popularizou por sua arquitetura orientada a objeto e o suporte as principais plataforma de SGDB do mercado, como Oracle e SQL Server, além de suporte a OleDb e ODBC, que possibilitam a integração com Excel, DB2, Interbase, PostGree, Firebird e MySQL, sem a necessidade de utilizar qualquer componente de terceiros.

Também existem outros recursos desenvolvidos que estendem ou reimplementam a arquitetura de acessos a SGDB de forma especializada, como no caso da Oracle, do Firebird e do MySQL, que desenvolvem suas próprias extensões do ADO.Net, afim de atender necessidade específicas de suas plataformas. Em relação ao SQL Server, a Framework.Net possui outros recursos exclusivos para prover integração com CLR.

Existem duas formas básicas de trabalhar com o ADO.Net, por meio de classes conectadas e desconectadas.

As classes conectadas permitem o acesso ao SGBD, por meio do .Net Data Provider, que constitui basicamente das seguintes entidades:

Connection: Responsável pela conexão ao SGDB, esta classe varia SGDB para SGDB (ex. SQLConnection, OracleConnection, OleDbConnection e OdbcConnection), possuindo métodos e propriedades que facilitam criar a conexão com cada um dos SGDB.

Por questões de arquitetura, para exigir que a conexão se feche depois de realizada a operação é recomendado o uso do bloco “Using”, exemplo:

// Criando o objeto de conexão ao banco de dados
using (SqlConnection conn = new SqlConnection("string de conexão"))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
}

Recomendável não utilizar nenhum retorno de métodos dentro de blocos “Using”:

// Criando o objeto de conexão ao banco de dados
using (SqlConnection conn = new SqlConnection("string de conexão"))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
  // Nunca faça isso:
  return true;
}

String de conexão: É responsável por identificar o endereço do SGDB, formas de autenticação e configurações de acesso.

Em aplicações Web, normalmente esta string é armazenada no arquivo web.config ou aplicações Windows e Windows Mobile no arquivo app.config, por questões de reaproveitamento e para facilitar alterações. Exemplo:

<!--web.config-->
<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="nome" connectionString="Data Source=in;Initial Catalog=db;User=us;Password=sn;"/>
  </connectionStrings>
</configuration>

Método responsável por retornar a string de conexão do web.config, as vezes se faz necessário adicionar a referência a System.Configuration ao projeto.

ConfigurationManager.ConnectionStrings["nome"].ToString();

// Criando o objeto de conexão ao banco de dados
string connectionString = ConfigurationManager.ConnectionStrings["nome"].ToString()
using (SqlConnection conn = new SqlConnection(connectionString))
{
  // Chamando o método responsável pela abertura da conexão.
  conn.Open();
}

Também existem formas de gerar a connection string em tempo de execução.
(at. http://msdn.microsoft.com/en-us/library/ms254947(VS.80).aspx )

Alguns exemplos de string de conexão para SQL Server e outros bancos de dados:
https://sqlfromhell.wordpress.com/2009/08/04/connection-strings/
http://msdn.microsoft.com/en-us/library/ms254500.aspx

Command: Depois de estabelecida a conexão com o SGDB, esta classe é o responsável por executar comandos e consultas no banco de dados. Seus objetos podem ser instanciado da forma tradicional por meio de um construtor ou chamando o método CreateCommand de objetos de conexão.Exemplo:

using (SqlConnection conn= new SqlConnection(""))
{
  using (SqlCommand cmd = new SqlCommand())
  {
    cmd.Connection = conn;
  }
}

Ou

using (SqlConnection conn= new SqlConnection(""))
{
  using (SqlCommand cmd = conn.CreateCommand())
  {
  }
}

Estes comandos podem ser comandos em SQL, nomes de StoredProcedures ou nomes de tabelas, para define qual o tipo comando é possível definir por meio do enumerador CommandType. Ex.:

// Para comandos SQL
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM tabela";

// Para stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SpTabelaSel";

// Para tabela
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "tabela";

Parâmetros: É a forma ideal de passar valores para comandos SQL e Stored Procedures. São eficazes para combater possíveis ataques de “SQL-Injection”. Exemplos:

//Forma de errada de passar valores para os comandos
cmd.CommandText = "INSERT INTO tabela (valor1) VALUES " + valor + ")";

//Utilizando parâmetros para inserir dados
cmd.CommandText = "INSERT INTO tabela (valor1) VALUES (@nome_parametro)";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);

//Utilizando parâmetros para excluir
cmd.CommandText = "DELETE FROM tabela WHERE valor1 = @nome_parametro";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);

//Utilizando parâmetros para alterar
cmd.CommandText = "UPDATE tabela SET valor1 = @nome_parametro1 WHERE valor2 = @nome_parametro2";
cmd.Parameters.Add(new SqlParameter("@nome_parametro1", valor1);
cmd.Parameters.Add(new SqlParameter("@nome_parametro2", valor2);

//Utilizando parâmetros para consultas
cmd.CommandText = "SELECT * FROM tabela WHERE valor1 = @nome_parametro";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);

//Utilizando parâmetros para StoreProcedures
cmd.CommandText = " SpTabelaSel";
cmd.Parameters.Add(new SqlParameter("@nome_parametro", valor);

A forma ideal de criar parâmetros é definir o tipo, tamanho e valor de forma separada.

//Forma ideal de criar um parâmetro
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@nome_parametro";
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Size = 90;
parameter.Value = valor;

ou

SqlParameter parameter = new SqlParameter("@nome_parametro", SqlDbType.VarChar, 90);
parameter.Value = valor;

Métodos de execução: Para comandos que não são esperados retornos é recomendado o uso do método ExecuteNonQuery, cujo o retorno é um inteiro especificando o número de linhas afetadas. Exemplo:

cmd.CommandText = "INSERT INTO tabela (coluna) VALUES ('valor')";
cmd.ExecuteNonQuery();

Para comandos que se espera uma única célula, exemplo a execução das funções SQL, COUNT, SUM, MAX, MIN ou qualquer outra consulta que ternar somente um valor, é recomendado o uso do método ExecuteScalar. Exemplo:

cmd.CommandText = "SELECT COUNT(*) FROM tabela";
int count = (int)cmd.ExecuteScalar();