Exportando dados de um DataTable para o Microsoft Access (Exporting data to Microsoft Access)

Alguns anos atrás passei pela necessidade de exportar um conjunto de dados vindo do SQL Server para o Microsoft Access, e nesses últimos dias encontrei uma pessoa no Fórum do MSDN com o mesmo problema que eu passei. Na época pesquisei por muitas soluções mas não encontrei nada referente a isso, apenas encontrei muitas pessoas com o mesmo problema que eu. Na época acabei criando minha própria solução: uma classe que recebia como parâmetro um DataTable, lia os metadados das colunas, construía a tabela junto com suas colunas no banco de dados e então exportava os dados. Esta solução ficou na “gaveta” esses últimos anos e agora vou dividir essa solução com a comunidade.

O algoritmo de exportação dos dados segue os seguintes passos:
– Criar o arquivo MDB caso o mesmo ainda não existe. Se o arquivo já existir então ele é mantido é alterado com a inclusão da nova tabela.
– Apaga a tabela caso já exista no banco de dados. Na época que criei este algoritmo tentei fazer acesso à tabela MSysObjects, que armazena os metadados do arquivo Microsoft Access, para validar se a tabela já existe, mas apenas recebi mensagens de acesso negado e não consegui dar continuidade a este recurso.
– Cria uma nova tabela no banco de dados com a estrutura contida no DataTable.
– Exporta os dados para o banco de dados.

A solução ficou desta forma:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

class CoversorAccess {
    private string _erro;
    public string Message { get; set; }

    private string VerificaTipoCampo(System.Type tipoColuna)
    {
        // O tipo de dados default é MEMO pois o DataType das colunas // não informa a quantidade de caracteres contido em cada coluna, // exceto em DataTables tipados. if (tipoColuna.FullName == typeof(String).FullName)
        {
            return "MEMO";
        }
        else if (tipoColuna.FullName == typeof(DateTime).FullName)
        {
            return "DATETIME";
        }
        else if (tipoColuna.FullName == typeof(Int32).FullName)
        {
            return "INTEGER";
        }
        else if (tipoColuna.FullName == typeof(Decimal).FullName)
        {
            return "DECIMAL (18,5)";
        }
        else if (tipoColuna.FullName == typeof(Double).FullName)
        {
            return "DECIMAL (18,2)";
        }

        return "MEMO";
    }

    /// <summary> /// Verifica se o tipo de dados deve utilizar aspas ou não, /// se sim, então aspas simples são retornadas, senão um caracter /// de espaço é retornado. /// </summary> private char VerificarUtilizacaoAspas(Type targetType)
    {
        char aspaSimples = ' ';

        if (targetType.FullName == typeof(String).FullName)
            aspaSimples = '\'';
        else if (targetType.FullName == typeof(DateTime).FullName)
            aspaSimples = '\'';
        else if ((targetType.FullName == typeof(Int32).FullName) ||
                    (targetType.FullName == typeof(Decimal).FullName) ||
                    (targetType.FullName == typeof(Double).FullName))
            aspaSimples = ' ';
        else aspaSimples = '\'';

        return aspaSimples;
    }

    private bool TransferirDados(string tableName, DataTable table, string caminhoBancoDados)
    {
        int j;
        int quantidadeColunas = 0;
        char aspaSimples = '\'';
        int quantidadeLinhas = 0;
        string tabela = tableName;
        string campos = "";
        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + caminhoBancoDados + ";";
        string commandInsert = "";
        string valor;
        string valores = "";
        Type tipoCol = null;
        const string commandAccess = "Insert Into ";
        List<string> commandsToExecute = new List<string>();

        try {  
            quantidadeColunas = table.Columns.Count;

            for (int i = 0; i < quantidadeColunas; i++)
            {
                campos += "[" + table.Columns[i].ColumnName.ToString() + "],";
            }
            campos = campos.Substring(0, campos.Length - 1);

            quantidadeLinhas = table.Rows.Count;
            for (j = 0; j < quantidadeLinhas; j++)
            {
                valores = "";
                for (int i = 0; i < quantidadeColunas; i++)
                {
                    tipoCol = table.Columns[i].DataType;

                    aspaSimples = VerificarUtilizacaoAspas(tipoCol);

                    valor = ((table.Rows[j].ItemArray.GetValue(i) == null) ? " " : table.Rows[j].ItemArray.GetValue(i).ToString());

                    if ((tipoCol.FullName == typeof(Decimal).FullName) || 
                        (tipoCol.FullName == typeof(Double).FullName))
                    {
                        if (valor.Trim() == "")
                            valor = "0";
                        else valor = valor.Replace(",", ".");
                    }

                    valor = ((tipoCol.FullName == typeof(Int32).FullName) && 
                             (valor.Trim() == "") ? "0" : valor);

                    if ((tipoCol.FullName == typeof(DateTime).FullName) && (valor.Trim() == ""))
                        valores += aspaSimples + "01/01/1900 00:00:00" + aspaSimples + ",";
                    else {
                        if (tipoCol.FullName == typeof(String).FullName)
                            valor = valor.Replace("'", "");
                        valores += aspaSimples + valor + aspaSimples + ",";
                    }
                }
                valores = valores.Substring(0, valores.Length - 1);
                commandInsert = commandAccess + tabela + " (" + campos + ") Values (" + valores + ") ";

                commandsToExecute.Add(commandInsert);
            }

            ExecutarComando(caminhoBancoDados, commandsToExecute.ToArray());
        }
        catch (Exception ex)
        {
            this._erro = ex.Message;
            return false;
        }

        return true;
    }

    /// <summary> /// Executa comandos SQL no arquivo da fonte de dados /// </summary> private void ExecutarComando(string caminhoArquivo, string comandosSql)
    {
        ExecutarComando(caminhoArquivo, new string[1] { comandosSql });        
    }

    /// <summary> /// Executa comandos SQL no arquivo da fonte de dados /// </summary> private void ExecutarComando(string caminhoArquivo, string[] comandosSql)
    {
        Exception loadException = null;
        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                            caminhoArquivo + ";Jet OLEDB:Engine Type=5";

        using(OleDbConnection conn = new OleDbConnection(connString))
        {
            try {
                conn.Open();

                #region [ Execução dos comandos SQL na fonte de dados ]

                foreach (string comandoSql in comandosSql)
                {
                    OleDbCommand cmd;
                    cmd = new OleDbCommand(comandoSql, conn);
                    cmd.ExecuteNonQuery();
                }

                #endregion }
            catch(Exception ex)
            {
                loadException = ex;
            }
            finally {
                conn.Close();
            }                
        }

        if (loadException != null)
            throw loadException;
    }

    private bool CriarTabela(string caminhoBancoDados, string nomeTabela, 
                        string chavePrimaria, DataTable conjuntoDados)
    {
        chavePrimaria = chavePrimaria.ToUpper();
        nomeTabela = nomeTabela.Trim();

        string commandDrop = "DROP TABLE " + nomeTabela;            
        string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                            caminhoBancoDados + ";Jet OLEDB:Engine Type=5";
        const string commandAccess = "CREATE TABLE ";
        string query = "";
        string camposTabela = "";
        int quantidadeColunas = conjuntoDados.Columns.Count;
        string tipoCampo = "";

        try {
            #region [ Apagar a tabela alvo, se ela existir ]

            try {
                ExecutarComando(caminhoBancoDados, commandDrop);
            }
            catch (Exception)
            {                    
                // Erro disparado quando a tabela já existe. // Não tenho permissão de acessar a tabela MSysObjects // para verificar se a tabela existe. }

            #endregion #region [ Criar comando para construção de tabela ]

            for (int i = 0; i < quantidadeColunas; i++)
            {
                tipoCampo = VerificaTipoCampo(conjuntoDados.Columns[i].DataType);
                camposTabela += "[" + conjuntoDados.Columns[i].ColumnName.ToString() + "] " + 
                                tipoCampo + 
                                ((conjuntoDados.Columns[i].ColumnName.ToString().
                                    ToUpper().Trim() == chavePrimaria.Trim()) ? " PRIMARY KEY" : "") + ",";
            }
            camposTabela = camposTabela.Substring(0, camposTabela.Length - 1);
            query = commandAccess + nomeTabela + "(" + camposTabela + ")";

            #endregion #region [ Executa comando para criação de tabela no banco de dados ]

            ExecutarComando(caminhoBancoDados, query);

            #endregion }
        catch (Exception ex)
        {
            this._erro = ex.Message;
            return false;
        }

        return true;
    }

    private bool CriarArquivo(string caminhoBancoDados)
    {
        // Caso o arquivo Mdb passado por parâmetro não exista, então // ele será criado. ADOX.CatalogClass cat = new ADOX.CatalogClass();
        try {
            System.IO.FileInfo BancoDados = new System.IO.FileInfo(caminhoBancoDados);
            if (!BancoDados.Exists)
            {
                cat.Create(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                    caminhoBancoDados + ";Jet OLEDB:Engine Type=5");
            }
        }
        catch (Exception ex)
        {
            this._erro = ex.Message;
            return false;
        }
        finally {
            cat = null;
        }
        return true;
    }

    /// <summary> /// Método de exportação de dados para arquivos MDB (Microsoft Office Access). /// </summary> /// <param name="conjuntoDados">DataTable com os dados que devem ser copiados para o arquivo .mdb (Microsoft Office Access).</param> /// <param name="caminhoArquivoMdb">Caminho final do arquivo, isto é, o local onde o arquivo .mdb (Microsoft Office Access) será criado.</param> /// <param name="nomeTabela">Nome da tabela que será criada com os dados do DataSet.</param> /// <param name="nomeCampoChavePrimaria">Chave Primária do DataTable. Caso o dados do DataTable não possuam uma Chave Primária basta que se passe um valor em branco.</param> /// <returns></returns> public bool ConvertToMDB(DataTable conjuntoDados, string caminhoArquivoMdb, string nomeTabela, string nomeCampoChavePrimaria)
    {
        if (!caminhoArquivoMdb.ToLower().EndsWith(".mdb"))
            caminhoArquivoMdb += ".mdb";

        this._erro = "";

        // Executar comportamento para criação do arquivo MDB if (!CriarArquivo(caminhoArquivoMdb))
        {
            Message = this._erro;
            return false;
        }

        // Criação da tabela alvo para comportar os dados if (!CriarTabela(caminhoArquivoMdb, nomeTabela, nomeCampoChavePrimaria, conjuntoDados))
        {
            Message = this._erro;
            return false;
        }

        // Rotina de transferência de dados do DataTable para a // tabela do Access if (!TransferirDados(nomeTabela, conjuntoDados, caminhoArquivoMdb))
        {
            Message = this._erro;
            return false;
        }

        return true;
    }
}

Para invocar o método de conversão basta fazer uma chamada semelhante a:

CoversorAccess export = new CoversorAccess();
bool retorno = export.ConvertToMDB(myDataTable,
                                   @"C:\temp\arquivoBanco.mdb",
                                   "myTableName",
                                   "myPrimaryKey");
if (!retorno)
    throw new Exception(export.Message);

Por

Fernando Henrique Inocêncio Borba Ferreira

3 comentários

  1. Pessoal boa tarde, estou utilizando a classe citada acima, mais quando o arquivo .mdb é gerado, é como ele ficasse em aberto, e não consigo gerar ele pela segundas vez. Alguém pode me ajudar?

Deixe uma resposta para Byron Doria Cancelar resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.