MS-Access com ADO.NET

por Cezar Guimarães Neto

Para ler todas as matérias da MSDN Magazine, assine a revista no endereço www.neoficio.com.br/msdn

Este artigo discute

Este artigo usa as seguintes tecnologias:

  • Conexão e acesso a dados MS-Access

  • Uso da classe OleDb

  • Manutenção de dados no OleDb

ADO.NET, C#, MS-Access

Download:
CodeAccessADONET.zip (90KB)

Chapéu
MS-Access

 

Está precisando acessar uma base de dados no Access a partir do .Net? Que tal criar uma camada de acesso aos seus dados no Access? Neste artigo mostrarei como você pode criar uma camada de acesso a dados utilizando as classes do namespace System.Data.OleDb para acessar uma base Access, SQL Server 6.5 ou até uma planilha excel, além de algumas sugestões que são válidas, como a validação da query utilizando expressão regular, quando fechar a conexão e a utilização da cláusula “using”. Assim, você será capaz de criar a sua camada de acesso a dados utilizando OleDb (ver Tabela 1).

Tabela 1: Principais classes do System.Data.OleDb

Classe

Interface

OleDbConnection

IDbConnection

OleDbDataAdapter

IDbDataAdapter

OleDbDataCommand

IDbDataCommand

OleDbDataReader

IDbDataReader

Criando uma conexão

Para acessar os dados, o primeiro passo é criar uma conexão com a base desejada. Para isto, utilize a classe OleDbConnection. Basicamente você precisa trabalhar com os parâmetros da conexão, conhecidos como “connectionString” ou string de conexão. O objeto OleDbConnection não permite que você atribua dinamicamente valores as propriedades específicas do provider. Você pode atribuí-las somente através da “string de conexão”. A string de conexão pode ser informada como parâmetro no momento em que você instancia um novo objeto ou através do atributo ConnectionString do objeto.

O formato típico de uma “string de conexão” é: Provider=Microsoft.Jet.OLEDB.4.0;DataSource=nomeDatabase;User ID = meuUsuario; Password = minhaSenha;". Note que é usado o Microsoft.Jet.OLEDB.4.0 como provider para acessar ao repositório em Access. Este provider permite acessarmos também uma planilha excel. Veja os parâmetros na Tabela 2.

Tabela 2: Parâmetros de uma ConnectionString típica

Parâmetro

Descrição

Provider

Específica o provider OleDb a ser utilizado.

DataSource

Específica o diretório ou nome do arquivo (P.E.: c:\Meus Documentos\Teste.mdb

User Id

Específica o usuário do arquivo. Se não for especificado utiliza “Admin” como default.

Password

Específica a senha do usuário. Se não for especificado utiliza uma string vazia como default.

O ADO.Net oferece um recurso muito útil que é o pool de conexões. Este recurso também está disponível para conexões OleDb. Porém, um importante cuidado deve ser tomado. As conexões devem ser fechadas logo após a sua utilização, podendo ser utilizado tanto o método close como o método dispose. Isto é necessário, porque mesmo que você não esteja utilizando a conexão, ela só retorna ao pool após ser liberada através dos métodos close ou dispose ou quando atingir o número máximo de conexões. Portanto, se você deseja aproveitar todos os benefícios do pool de conexões abra a conexão somente no momento anterior a sua utilização e não se esqueça de liberar a conexão logo após utilizá-la. A Listagem 1 mostra como utilizar estes métodos para liberar a conexão e, mais a frente você verá uma outra boa maneira de fazer isso quando estiver utilizando o C# como linguagem de programação. Não chame o método close ou dispose do método Finalize da sua classe. Use este método apenas para os objetos não gerenciados que foram instanciados na sua classe.

Listagem 1. Criando e fechando a conexão

//Declare o objeto de conexão
OleDbConnection _connection =  new OleDbConnection(connectionString);

//Declare os objetos que serão utilizados
OleDbCommand _command = new OleDbCommand(queryUpdate,_connection);
					
//Abra a conexão
_connection.Open();
//Execute o código desejado
_command.ExecuteNonQuery();
//Feche a conexão
_connection.Close();

Acessando os dados

O segundo passo é criar uma instância de um DataAdapter utilizando OleDbDataAdapter. Existem quatro opções de constructors. Sendo que, em uma delas você pode passar como parâmetro a query e o objeto OleDbConnection. Mas se preferir, poderá passar a string de conexão ao invés do objeto de conexão. A Listagem 2 mostra como utilizar cada uma destas duas opções.

Listagem 2. Instanciando um DataAdaper

//Instanciando um DataAdapter utilizando uma conexão existente
OleDbConnection _conn =  new OleDbConnection (stringConnection);
OleDbDataAdapter _dtAdap =  new OleDbDataAdapter("Select * from contato", _conn);
				
//Instanciando um DataAdapter utilizando a connectinString
OleDbDataAdapter _dtAdap2 =  new OleDbDataAdapter"Select * from contato", stringConnection);

O DataAdapter oferece uma grande ajuda para preencher controles com os dados. Utilizando um simples método, é possível se conectar com o repositório de dados, buscar os dados desejados e transferi-los para um dataset. Este método é o “Fill”. Recebendo como parâmetro um DataSet, ele cria um DataTable (de nome “Table”) contendo o resultado da query de select e retorna um inteiro com o número de linhas acrescentadas no DataTable (Veja a Listagem 3).

Listagem 3. Usando o método Fill

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";
			
//Declare o DataSet	
DataSet _dtSet =  new DataSet();

//Instancie o DataAdapter passando como parâmetro da query e a string de conexão
OleDbDataAdapter _dtAdap = new OleDbDataAdapter("Select * from  contato", _connectionString);

//Use o método Fill para carregar o DataSet com o retorno da query
_dtAdap.Fill(_dtSet);

Uma outra opção é utilizar o DataReader. O OleDbDataReader permite que você acesse os dados em um único sentido (foward-only) e somente para leitura (read-only). Assim, você pode melhorar a performance da aplicação por acessar os dados assim que a primeira linha estiver disponível e consumir menos memória por manter somente uma linha por vez nela. Também estão disponíveis métodos específicos para você acessar o dado no seu tipo nativo. Uma desvantagem é que a conexão deve ser mantida aberta durante a utilização dos dados. Para acessar as suas linhas você utilizará o método Read. A versão do .Net Framework 1.1 incluiu o atributo HasRows que nos permite verificar se o resultado da query utilizada retornou linhas ou não. A decisão de utilizar um DataSet ou um DataReader dependerá da sua necessidade e os prós e contras de cada um devem ser considerados na sua tomada de decisão. Porém, se você precisa preencher uma combobox ou uma listbox ou ler o resultado de uma query, o DataReader provavelmente será a melhor opção, principalmente pelos fatores relacionados a performance. Para criar um OleDbDataReader você precisa de uma conexão e de um DataCommand. O DataCommand contém um método especifico que retorna um DataReader com os resultado da query passada com parâmetro. A Listagem 4 contém exemplo de um método que utiliza um OleDbDataReader para carregar uma comboBox.

Listagem 4. Utilizando o DataReader

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";

//Determine a query desejada
String _query = "Select * from email";

//Declare o objeto de conexão passando como parâmetro a string de conexão
//e declare o objeto DataCommand passando a query e o objeto de conexão
OleDbConnection _dataConn =  new OleDbConnection(_connectionString);
OleDbCommand _dataCommand = new OleDbCommand(_query,_dataConn);
				
//Declare o DataReader, abra a conexão em seguida
//e execute o método ExecuteReader que retornará um DataReader
OleDbDataReader _dataReader;
dataConn.Open();
dataReader = _dataCommand.ExecuteReader();
				
//Teste para verificar se retornaram linhas
if (_dataReader.HasRows)
{
  //Loop para carregar a combobox com os dados do DataReader
  while (_dataReader.Read())
  {
    //Acesso o dado no seu tipo nativo
this.cmbDataReader.Items.Add(_dataReader.GetString(1));
  }
}

this.cmbDataReader.Refresh();
			

//Não se esqueça de fechar a conexão e o DataReader
dataReader.Close();
dataConn.Close();

Atualizações

Para efetivar atualizações no banco de dados, você pode utilizar o próprio DataAdapter através das propriedades UpdateCommand, InsertCommand, DeleteCommand e do método Update. No entato, abordarei uma outra opção que é o DbCommand. Esta é uma opção a ser usada ao invés de criar um dataAdapter, buscar os dados no repositório e atualizá-los.

A classe OleDbCommand oferece várias opções de constructor para criar uma nova instância. Uma destas recebe como parâmetro uma query SQL, ou o nome de uma procedure, e o objeto de conexão. Se você preferir, crie uma instância sem passagem de parâmetros e utilize as propriedades CommandText e Connection para determinar a query SQL e a conexão, respectivamente, que serão utilizados pelo objeto. Após isso, escolha alguns dos métodos disponíveis para a execução da query: ExecuteNonQuery, ExecuteReader e ExecuteScalar (ver listagem 5).

Listagem 5. Usando o OleDbCommand

//Criando um OleDbCommand com passagem de parâmetros

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";

//Determine a query
System.String _query = "UPDATE CONTATO SET NOME = 'Tom' WHERE IDENTIFICADOR = 1";
				
//Crie uma instancia do OleDbCommand passando como parâmetro
//a query SQL e a conexão
OleDbConnection _conn = new OleDbConnection(_connectionString);
OleDbCommand _cmd =  new OleDbCommand(_query,_conn);
				
//Abra a conexão, execute a query e não se esqueça
//de fechar a conexão
_conn.Open();
_cmd.ExecuteNonQuery();
_conn.Close();


//Criando um OleDbCommand utilizando as propriedades

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";

//Crie uma instancia do OleDbCommand passando como parâmetro
//a query SQL e a conexão
OleDbConnection _conn = new OleDbConnection(_connectionString);
OleDbCommand _cmd =  new OleDbCommand();
				
//Determine os valores das propriedades
_cmd.CommandText = "UPDATE CONTATO SET NOME = 'Tom' WHERE IDENTIFICADOR = 1";
_cmd.Connection = _conn;
	
//Abra a conexão, execute a query e não se esqueça
//de fechar a conexão
_conn.Open();
_cmd.ExecuteNonQuery();

_conn.Close();

Inserindo dados

Você também pode utilizar a classe OldDbCommand para inserir novos dados no seu repositório. A única diferença entre o uso anterior que fizemos da OleDbCommand é a query que será utilizada. Neste caso, passe como parâmetro uma query de insert.

Uma necessidade muito comum, para uma camada de acesso a dados, é retornar o identificador do registro que acabamos de inserir. Mas, e se você estiver utilizando um campo autonumerável para o identificador, como podemos retorná-lo? Com a versão 4.0 do provider JetOleDB, que estamos utilizando, é possível buscarmos o identificador do último registro inserido. Basta utilizarmos o @@IDENTITY em uma query junto com o OleDbCommand,conforme mostrado na Listagem 6, que é bem conhecido para aqueles que estão acostumados a trabalhar com o SQL Server.

Listagem 6. Inserindo dados

//Determine a query de insert
StringBuilder _queryInsert =  new StringBuilder("INSERT INTO CONTATO (NOME,SOBRENOME) 
VALUES ('<%nome%>','<%sobrenome%>')");
_queryInsert.Replace("<%nome%>",txtNome.Text );
_queryInsert.Replace("<%sobrenome%>",txtSobreNome.Text );

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";
			
//Instancie a conexão
OleDbConnection _connection =  new OleDbConnection(_connectionString);
			
//Instancie o objeto DbCommand passando como parâmetro a query de insert
//e o objeto de conexão
OleDbCommand _command = new OleDbCommand(_queryInsert.ToString(),_connection);
			
//Abra a conexão e execute o comando de insert
_connection.Open();
_command.ExecuteNonQuery();
			
//Determine a query que seleciona o identificador do registro inserido
_command.CommandText = "SELECT @@IDENTITY";
this.txtId.Text = _command.ExecuteScalar().ToString();
			
//Não se esqueça de fechar a conexão
_connection.Close();

Criando a classe de Acesso a Dados

Já temos as ferramentas para criar uma camada de acesso a nossa base de dados utilizando o OleDb. Veremos agora alguns lembretes importantes relacionados à segurança que devem ser levados em consideração antes de você começar o desenvolvimento. Cada vez mais, temos sido lembrados que todos nós, mesmo os desenvolvedores, precisamos estar preocupados com este fator. Não pretendo abordar todos os aspectos necessários para criarmos um código seguro. Até porque, não é possível abrangê-los em um único artigo ou em parte dele. Um cuidado que devemos tomar é restringir o acesso aos dados. Evite utilizar uma senha de banco de dados com altos privilégios. Sempre que possível utilize autenticação integrada. Se isto não for possível, guarde o usuário e senha utilizada de modo seguro e de preferência criptografados. Por último, proteja a sua base de dados por evitar a inserção de sql por garantir o tipo de query que a camada de acesso a dados receberá como parâmetro. Utilizando expressões regulares podemos facilmente fazer isso. Na edição de março da MSDN Magazine Brasil foi publicado um artigo sobre Expressão Regular e como podemos utilizá-la de maneira simples. Com elas podemos em poucas linhas validar o formato da query de entrada e, por exemplo, executar somente uma query de select. A Tabela 3 mostra uma sugestão de expressão regular para cada tipo de query. Expressões mais elaboradas podem ser utilizadas para aumentar o controle ou permitir tipos mais complexos de querys. Com a expressão definida, basta utilizar o método IsMatch, da classe RegEx, para validar se a query corresponde ao formato esperado.

Tabela 3: Expressões Regulares

Tipo de Query

Expressão Regular de Validação

Select

SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+

Update

UPDATE\s[\w]+\sSET\s[\w\,\'\=]+

Insert

INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+

Delete

DELETE\sFROM\s[\d\w\'\=]+

Mencionei anteriormente neste artigo a necessidade de usarmos o método close ou o dispose para liberar a conexão. Na realidade, não somente a conexão, mas outros métodos do System.Data.OleDb precisam que estes métodos sejam chamados. Como por exemplo, o DataReader. A linguagem C# nos oferece um excelente recurso para evitarmos que estes métodos não sejam chamados e acarretem em problemas na performance e na utilização da memória. Este recurso é a cláusula “using”. Todos os que programam utilizando C# deveriam utilizá-lo quando estiverem trabalhando com conexões, DataAdapters e outras classes que possuam o método Dispose. Quando você utiliza o “using” o .Net se preocupa por você em chamar automaticamente o Dispose após o bloco ter sido executado. Isto o ajuda a criar um código limpo e simples. Lembre-se que pelo fato do método Dispose ser chamado automaticamente após a execução do bloco de código, os recursos não podem ser utilizados além do método. Esta cláusula é de tão grande ajuda que está previsto a criação de uma cláusula semelhante nas próximas versões do VB.Net. Para utilizar o “using” basta declarar o objeto entre parênteses após a cláusula e utilizá-lo em um bloco logo abaixo. Uma série de objetos podem ser instanciados em seqüência, um sobre o outro, e utilizados no mesmo bloco. A Listagem 7 mostra um exemplo deste recurso na utilização de um DataReader.

Listagem 7. Utilizando a cláusula using

//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";

//Determine a query desejada
String _query = "Select * from contato";

//Declare o objeto de conexão e o DataCommand com a cláusula using
using(OleDbConnection _dataConn =  new OleDbConnection(_connectionString))
using(OleDbCommand _dataCommand = new OleDbCommand(_query,_dataConn))
{
  //Utilize os objetos dentro do bloco
  dataConn.Open();
					
  //Declare o DataReader com a cláusula using
  using(OleDbDataReader _dataReader = _dataCommand.ExecuteReader())
  {
    //Teste para verificar se retornaram linhas
    if (_dataReader.HasRows)
	{
	  //Carregue a combo dentro do bloco
	  while (_dataReader.Read())
	  {
	cmbDataReader.Items.Add(_dataReader.GetString(1));
	  }
	}
	cmbDataReader.Refresh();
  } //Automáticamente o .Net chama o método Dispose para o DataReader
} //Automáticamente o .Net chama o método Dispose para a conexão e DataCommand

Na Listagem 8 você observará como utilizar as sugestões e as classes OleDb para criar um dos métodos da camada de acesso a dados.

Listagem 8. Método Exemplo

//Definição expressão regular 
System.String _queryValidator = @"SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+";
			
//Determine a string de conexão
String _connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Dados\OledbTesteBase.mde";

//Teste a query contra a expressão regular
if (Regex.IsMatch(this.txtQuery.Text.ToUpper(),_queryValidator))
{
  //Utilize o using e declare os objetos que precisam do dispose
  using (DataSet _dataSet =  new DataSet())
  using (OleDbDataAdapter _dataAdap =  new OleDbDataAdapter(this.txtQuery.Text ,_connectionString))
  {
    //Carregue o DataSet
	dataAdap.Fill(_dataSet);
	
  }
}
else
{
  //Gera a exceção de erro no teste de expressão regular
  throw new ApplicationException("O parâmetro não confere com uma query de select");
}

Conclusão

Aprendemos a utilizar as principais classes do namespace System.Data.OleDb. Outros recursos estão disponíveis e devem ser estudados, pois alguns deles podem ser úteis de acordo com a sua necessidade. Também vimos algumas boas práticas que você deve se esforçar a colocá-las em prática. Assim, você terá um código mais simples, seguro e utilizando melhor os recursos de máquina. Lembre-se que estas sugestões podem ser utilizadas com as outras classes do namespace System.Data. Utilize os exemplos como base, faça os ajustes para incrementá-los e desenvolva a sua própria classe de acesso aos dados.

Cezar Guimarães Neto (cezar_neto@hotmail.com) é Analista de Sistemas Sênior da Telemar Norte Leste atuando como Arquiteto de Soluções e Gerenciando projetos em .NET.

OLHO: As conexões devem ser fechadas logo após a sua utilização, podendo ser utilizado tanto o método close como o método dispose. Isto é necessário, porque mesmo que você não esteja utilizando a conexão, ela só retorna ao pool após ser liberada através dos métodos close ou dispose ou quando atingir o número máximo de conexões.

Mostrar: