Comparar aquivo TXT com Tabela no banco de dados

Question

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:

 [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 na view, da seguinte forma:

@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.

Answer 1

Bom, consegui otimizar o tempo de 5 minutos, para 24 segundos. E até então, estou satisfeito com o tempo. Afinal estou trabalhando com uma lista de mais de 100 mil dados.

Em meu controller, eu salvo todos os dados em uma lista, e utilizo o Bulk Insert para inserir os dados no Database.

//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 Banco utilizando o Bulk Insert
            BulkInsert(context.Database.Connection.ConnectionString, "PortalRH_Sisobi", listSisobi);



            //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();

            //Instancia do contexto
            RHContext dc = new RHContext();

            //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();

        }

E segue o método BulkInsert:

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);
            }
        }

Esta foi a melhor solução que encontrei, caso alguém possua uma outra alternativa, ficaria feliz de utiliza-lá.

Answer 2

Renilson, boa noite. Como faço para ter acesso a esse arquivo? Obrigado.