Artigo da MSDN Magazine 12 - MS-Access com ADO.NET

MS-Access com ADO.NET

por Cezar Guimarães Neto

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

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

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

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.