Array
(
)

Comparar aquivo TXT com Tabela no banco de dados

Randrade
   - 25 mar 2015

Possuo um arquivo TXT gerado pelo SISOBI, onde contém uma relação de pessoas que vieram ao óbito.
Esse txt é gerado, e necessito ler o arquivo e comparar o CPF, que é a única chave presente no arquivo, com o meu banco de dados e verificar se possui algum funcionário que possui o mesmo CPF.
Realizo o Upload do arquivo, separo as pessoas pela quantidades de caracteres (210) e retiro o CPF através do SubString.
O meu problema é como fazer para comparar o CPF com os dados retornados de minha tabela.
Meu Controller ue realiza essas ações está assim:
#Código

 [HttpPost]
        public ActionResult Index(HttpPostedFileBase file, Sisobi sisobi)
        {

         RHContext dc = new RHContext();
            dc.Database.ExecuteSqlCommand("TRUNCATE TABLE PORTALRH_SISOBI");

            //verifica se o arquivo está nulo
            if (file == null)
            {
                TempData["MensagemError"] = "Erro ao realizar o upload do arquivo!";
                return View("Index");
            }

            //Salvar o arquivo txt
            string path = Path.Combine(Server.MapPath("~/App_Data/Uploads/" + Path.GetFileName(file.FileName)));
            file.SaveAs(path);                          

            //Realiza a leitura do arquivo txt
            var fileContents = System.IO.File.ReadAllText(path);

            //Separa o texto em blocos de 210 caracteres, conforme o Layout
            var partes = SplitBlocks(fileContents, 212);

            //busca todos os blocos
            foreach (var parte in partes)
            {
                
                var Nome = parte.Substring(39, 76);
                var NomeMae = parte.Substring(115, 32);
                var DataNascimento = parte.Substring(147, 8);
                var DataMorte = parte.Substring(155, 8);
                var Cpf = parte.Substring(163, 11);
                
                //converte cpf to double
                double CpfConvertido = Convert.ToInt64(Cpf);

                //converte data para o formato dd/MM/yyyy
                var dtMorte = DataMorte.Substring(6, 2) + "/" + DataMorte.Substring(4, 2) + "/" + DataMorte.Substring(0, 4);
                var DtNascimento = DataNascimento.Substring(6, 2) + "/" + DataNascimento.Substring(4, 2) + "/" + DataNascimento.Substring(0, 4);
                
                //Verifica se o modelo é válido
                if (ModelState.IsValid)
                {
                    try
                    {
                        sisobi.Cpf = CpfConvertido;
                        sisobi.DtObito = dtMorte;
                        sisobi.NomeFalecido = Nome;
                        sisobi.DtNascimento = DtNascimento;
                        sisobi.NomeMae = NomeMae;

                        //salva a informação no banco
                        sisobiRepository.Inserir(sisobi);

                    }
                    catch (Exception ex)
                    {

                        TempData["MensagemError"] = ex.Message;
                    }
                }
            }

         RHContext context = new RHContext();

            //Consulta dos usuários com o mesmo cpf
            var usuarios = context.Sisobis.Join(context.Usuarios,
                p => p.Cpf,
                c => c.NrCpf,
                (p, c) => new {p.DtNascimento, p.Cpf, p.DtObito, p.NomeFalecido, p.NomeMae}).Distinct();

            //retorna os dados para a View
            var usuariosEncontrados = usuarios.Select(u => new SisobiViewModel
            {
                DtNascimento = u.DtNascimento,
                Cpf = u.Cpf,
                DtObito = u.DtObito,
                NomeMae = u.NomeMae,
                NomeFalecido = u.NomeFalecido

            }).ToList();

            //Limpa a tabela do banco de dados
            dc.Database.ExecuteSqlCommand("TRUNCATE TABLE PORTALRH_SISOBI");

            //verifica se encontrou usuários
            if (usuariosEncontrados.Count > 0)
            {
                //retorna os usuários encontrados para a View
                TempData["UsuarioEncontrado"] = "Existe funcionário.";
                return View(usuariosEncontrados);
            }

            TempData["Usuario"] = "Nenhum funcionário encontrado.";
            return View();

        }

      public static List<String> SplitBlocks(string texto, int tamanho)
        {
            var partes = new List<String>();
            var posicao = 0;
            var total = texto.Length;
            while (total >= posicao + tamanho)
            {
                partes.Add(texto.Substring(posicao, tamanho));
                posicao += tamanho;
            }
            return partes;
        }
    }

E retorno os dados encontrados na consulta para minha View, da seguinte forma:
#Código
@model IEnumerable<PortalRH.WebUI.Models.SisobiViewModel>
<div class="panel panel-default">
                <div class="panel-heading">
                    <h5><strong>Relatório de Óbito</strong></h5>
                </div>

                <table class="table">
                    <tr>
                        <th>
                            Nome
                        </th>
                        <th>
                            CPF
                        </th>
                        <th>
                            Nome da Mãe
                        </th>
                        <th>
                            Data de Nascimento
                        </th>
                        <th>
                            Data de Óbito
                        </th>
                    </tr>

                    @foreach (var item in Model)
                    {
                        <tr>
                            <td>
                                @Html.DisplayFor(modelItem => item.NomeFalecido)
                            </td>
                            <td>
                                @item.Cpf.ToString(@"000\.000\.000\-00")
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.NomeMae)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.DtNascimento)
                            </td>
                            <td>
                                @Html.DisplayFor(modelItem => item.DtObito)
                            </td>
                        </tr>
                    }

                </table>
            </div>

Desta forma estou salvando os arquivos no banco, comparando se existem algum dado e retornando os encontrados. Porém, o arquivo txt possui cerca de 100 mil dados, demorando em média 5 minutos para realizar a inserção no banco para poder concluir a operação.
Gostaria de saber se existe alguma forma de realizar esta comparação sem inserir dados no banco de dados, ou uma forma de otimizar a inserção, para não demorar tanto tempo.

Jothaz
   - 25 mar 2015

Fala brother tudo na paz!

Você est usando o SQL Server né? Sempre que precisei trabalhar com importação/exportação massiva de registros e usando o SQL Server eu utilizava o Bulk Insert e aqui.

É muito rápido e você pode inclusive desligar o log o que deixa mais rápido ainda.

Depois que tudo esta em tabelas dai é só usar as facilidades dos bd´s relacionais.para verificar os dados.

Randrade
   - 25 mar 2015


Citação:
Fala brother tudo na paz!

Você est usando o SQL Server né? Sempre que precisei trabalhar com importação/exportação massiva de registros e usando o SQL Server eu utilizava o Bulk Insert e aqui.

É muito rápido e você pode inclusive desligar o log o que deixa mais rápido ainda.

Depois que tudo esta em tabelas dai é só usar as facilidades dos bd´s relacionais.para verificar os dados.


Boa Tarde Jothaz.

Na verdade, eu nem preciso trabalhar com insert. Necessito somente verificar todos os funcionários cadastrados, e comparar com os que estou importando com o meu arquivo txt.
Eu realizei o insert, pois foi a forma que imaginei para comparar. Tanto que após eu realizo um "Truncate", pois são 100 mil dados, que não terá utilidade em meu DB.

Mas irei verificar o Bulk Insert, para ver se atende minhas necessidades.

Obrigado pela ajuda, novamente. rsrsrs

Jothaz
   - 25 mar 2015

A sugestão de inserir em um tabela é justamente por ser a forma mais rápida que conheço.

Desenvolvi um sistema de controle de gastos com Taxi para um banco e recebia toda a informação em forma de txt e foi a melhor solução que consegui.

É possível usar o Linq para manipular TXT, mas sinceramente não sei quanto a performance.

Randrade
   - 25 mar 2015

Eu testei com Linq, e a performance foi pior.

A consulta em si, está rápida. O problema está sendo em inserir 100 mil dados de uma vez, dentro do foeach.

Pesquisando eu li, rapidamente, sobre trabalhar com KeyedCollection , assim não precisando inserir dados no Data Base. E para inserir, inserir os dados como uma lista, e não individual. Mas ainda não testei essas duas opções, para ver se realmente seria melhor.

Mas testarei e informo após.

Lembrando que qualquer informação adicional, será bem vinda ainda.

Jothaz
   - 25 mar 2015


Citação:
Eu testei com Linq, e a performance foi pior.

A consulta em si, está rápida. O problema está sendo em inserir 100 mil dados de uma vez, dentro do foeach.

Pesquisando eu li, rapidamente, sobre trabalhar com KeyedCollection , assim não precisando inserir dados no Data Base. E para inserir, inserir os dados como uma lista, e não individual. Mas ainda não testei essas duas opções, para ver se realmente seria melhor.

Mas testarei e informo após.

Lembrando que qualquer informação adicional, será bem vinda ainda.


O Linq para certas funções não é o melhor.

Já o O KeyedCollection não conheço.

O bulk insert sempre resolveu meus problemas e a opção de desligar o log deixa a execução mais rápida ainda.

Só testando mesmo!

Randrade
   - 25 mar 2015

Realizei o teste com Listas, onde salvei todos os dados em uma lista, e realizei um único insert.

Meu tempo de quase 5 minutos, abaixou para 1 minuto e 13 segundos. Está muito melhor, porém gostaria que fosse mais rápido ainda.

Caso alguém tenha dúvidas, irei adicionar meu código até o momento, para ajudar quem necessita.

#Código

     //Cria uma lista de usuários
            var listSisobi = new List<Sisobi>();

            //Contador equivalente a Chave Primaria
            int contador = 1;

            //busca todos os blocos
            foreach (var parte in partes)
            {


                var Nome = parte.Substring(39, 76);
                var NomeMae = parte.Substring(115, 32);
                var DataNascimento = parte.Substring(147, 8);
                var DataMorte = parte.Substring(155, 8);
                var Cpf = parte.Substring(163, 11);

                //converte cpf to double
                double CpfConvertido = Convert.ToInt64(Cpf);

                //converte data para o formato dd/MM/yyyy
                var dtMorte = DataMorte.Substring(6, 2) + "/" + DataMorte.Substring(4, 2) + "/" + DataMorte.Substring(0, 4);
                var DtNascimento = DataNascimento.Substring(6, 2) + "/" + DataNascimento.Substring(4, 2) + "/" + DataNascimento.Substring(0, 4);




                //Verifica se o modelo é válido
                if (ModelState.IsValid)
                {
                    try
                    {

                        //salva os arquivos na lista
                        listSisobi.Add(new Sisobi
                        {
                            SisobiId = contador,
                            Cpf = CpfConvertido,
                            DtObito = dtMorte,
                            NomeFalecido = Nome,
                            DtNascimento = DtNascimento,
                            NomeMae = NomeMae
                        });

                        //Incrementa o contador (chave primária)
                        contador++;
                    }
                    catch (Exception ex)
                    {

                        TempData["MensagemError"] = ex.Message;
                    }
                }

            }

            //Insere no BD
            context.Sisobis.AddRange(listSisobi);
            context.SaveChanges();


Irei adicionar o Insert Bulk agora, para ver se consigo otimizar um pouco mais este método. Após eu retorno para atualizar o posto.

Randrade
   - 25 mar 2015

Bom, adicionei o Insert Bulk, e consegui otimizar para 24 segundos toda a operação.

Sei que 24 segundos não é o tempo de espera que nenhum cliente gosta de esperar, porém levando em consideração que são mais de 100 mil dados por arquivo, creio que este tempo esteja dentro das condições.

Obrigado mais uma vez Jothaz. Eu particularmente não havia trabalhando com Insert Bulk até o momento, mas realmente é uma excelente opção para utilizar.

Sei que é pedir muito, mas se possuir algum material sobre o mesmo, que possa me enviar, seria de muita utilidade.

Segue o código final.

No controller eu apenas chamei o método BulkInsert e passei como parâmetro minha ConnectionString, a tabela que irá inserir os dados, e a lista de dados para inserir, ficando assim:

#Código

BulkInsert(context.Database.Connection.ConnectionString, "PortalRH_Sisobi", listSisobi);


E o método BulkInsert eu fiz desta forma:

#Código
        public static void BulkInsert<T>(string connection, string tableName, IList<T> list)
        {
            using (var bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tableName;

                var table = new DataTable();
                var props = TypeDescriptor.GetProperties(typeof(T))
                    //Dirty hack to make sure we only have system data types 
                    //i.e. filter out the relationships/collections
                                           .Cast<PropertyDescriptor>()
                                           .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                           .ToArray();

                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                }

                var values = new object[props.Length];
                foreach (var item in list)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].GetValue(item);
                    }

                    table.Rows.Add(values);
                }

                bulkCopy.WriteToServer(table);
            }
        }


Espero que ajude mais alguém que possuir um problema semelhante.

Jothaz
   - 25 mar 2015

Que bom que resolveu o seu problema.

E 24 segundos para 100mil não é nada, o beneficio compensa a espera. Se o camarada reclamar sugere fazer na mão.

Me lembro que desenvolvi um sistema de apuração de ponto eletrônico para uma empresa com 13000 funcionários. Era em natural, PL1 e Adabas. Por limitações técnicas do mainframe (na época tinha 127mega de RAM e era top) o programa de apuração só gerava 85% das ocorrência corretamente os outros 15% eram preciso alguma inferência do usuário para ajustar a apuração. E era uma reclamação sem fim. Então tirei a execução (batch) do apurador e deixei que os usuários apurassem manualmente. Mas gerava um relatório com as inconformidades em pouco tempo tinha usuário implorando para voltar o gerador, pois melhor fazer 15% do trabalho que 100%.

Sempre usei o Bulk Insert e o BCP. Trabalhei em um um empresa de telemarketing, sei que vou para o inferno, onde eram feitas cargas com milhões de registros sem problemas e rapidinho.

Verei se tenho algum material e posto.

Randrade
   - 25 mar 2015

É brother, seu lugarzinho está guardado no inferno mesmo... rsrsrs

É uma funcionalidade que o usuário irá usar uma vez na vida e outra na morte. Eu estava feliz com os 5 minutos, só queria melhorar, pois sei que quanto melhor, melhor será para mim no futuro.

Porém é uma exigência da empresa, então tenho que fazer.

Valeu peja ajuda, e irei analizar o BCP também. Não conheço, mas poderá ser útil para mim.