private static DataTable MontarTabelaTemporaria(MapeamentoTabela mapeamentoTabela) { var datatable = new DataTable(mapeamentoTabela.Nome); foreach (var column in mapeamentoTabela.Colunas) { datatable.Columns.Add(column.Item1, column.Item2); } return(datatable); }
private void CopiarDados(MapeamentoTabela tabela, SqlBulkCopy sqlBulkCopy, DbDataReader sqlDataReader, long totalRegistros) { try { sqlBulkCopy.BulkCopyTimeout = 1800 /*30 minutos*/; sqlBulkCopy.BatchSize = TAKE; sqlBulkCopy.NotifyAfter = TAKE; sqlBulkCopy.SqlRowsCopied += (sender, eventArgs) => OnRegistrosMigrados?.Invoke(Math.Min(eventArgs.RowsCopied, totalRegistros)); sqlBulkCopy.DestinationTableName = tabela.Nome; sqlBulkCopy.WriteToServer(sqlDataReader); } catch (Exception ex) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine($"Erro de conversão na {tabela} => {ex.Message}"); Console.ResetColor(); } }
private void CopiarDadosComTratamentoDeBlob(MapeamentoTabela tabela, SqlBulkCopy sqlBulkCopy, DbDataReader sqlDataReader, long totalRegistros) { var inserirDados = true; var fieldCount = sqlDataReader.FieldCount; var inseridos = 0L; do { var contador = 0; var datatable = MontarTabelaTemporaria(tabela); while (contador < TAKE && (inserirDados = sqlDataReader.Read())) { var valores = new object[fieldCount]; for (int i = 0; i < fieldCount; i++) { var value = sqlDataReader.GetValue(i); valores[i] = tabela.Colunas[i].Item3 != null ? tabela.Colunas[i].Item3.Converter(value) : value; } datatable.Rows.Add(valores); contador++; } inseridos += TAKE; try { if (contador > 0) { sqlBulkCopy.BulkCopyTimeout = 1800 /*30 minutos*/; sqlBulkCopy.DestinationTableName = tabela.Nome; sqlBulkCopy.WriteToServer(datatable); OnRegistrosMigrados?.Invoke(Math.Min(inseridos, totalRegistros)); } } catch (Exception ex) { OnError?.Invoke(ex.Message); } } while (inserirDados); }
private void MigraTabela(DbConnection conexaoFonte, SqlConnection conexaoDestino, MapeamentoTabela mapeamentoTabela) { var sqlCommand = conexaoFonte.CreateCommand(); sqlCommand.CommandText = $"SELECT COUNT({mapeamentoTabela.Colunas.First().Item1}) FROM {mapeamentoTabela.Nome}"; var totalRegistros = (int)sqlCommand.ExecuteScalar(); OnInicioMigracaoTabela?.Invoke(mapeamentoTabela.Nome, totalRegistros); sqlCommand.CommandText = $"SELECT {string.Join(",", mapeamentoTabela.Colunas.Select(c => c.Item1))} FROM {mapeamentoTabela.Nome}"; var dataReader = sqlCommand.ExecuteReader(); using (dataReader) using (var sqlBulkCopy = new SqlBulkCopy(conexaoDestino)) { mapeamentoTabela.Colunas.Select(c => new SqlBulkCopyColumnMapping(c.Item1, c.Item1)) .ToList() .ForEach(sqlBulkCopyColumnMapping => sqlBulkCopy.ColumnMappings.Add(sqlBulkCopyColumnMapping)); if (mapeamentoTabela.Customizada) { CopiarDadosComTratamentoDeBlob(mapeamentoTabela, sqlBulkCopy, dataReader, totalRegistros); } else { CopiarDados(mapeamentoTabela, sqlBulkCopy, dataReader, totalRegistros); } } }