internal void AtualizarDadosDestino(string destinationSchema, string destinationTable, string columnPK, bool pkIdentity, DataTable colunasDestino, DataTable dados)
{
var time = new System.Diagnostics.Stopwatch();
var tempGuid = "##" + Guid.NewGuid().ToString().Replace("-", "");
var listTypeString = new List <String>()
{
"nvarchar", "varchar", "varbinary"
};
_connection = validaConexao(_connection);
var command = _connection.CreateCommand();
var camposTable = string.Join(", ", colunasDestino.AsEnumerable().Select(s => s["COLUMN_NAME"] + " " + (s["DATA_TYPE"].ToString() == "decimal" ? "money" : s["DATA_TYPE"]) + $"{(listTypeString.Any(x => x == s["DATA_TYPE"].ToString()) ? "(max)" : string.Empty)}").ToList());
var queryTempTable = $@"
CREATE TABLE {tempGuid}(
{camposTable}
)
";
Program.AtualizarStatus(server, $"Criando tabela Temporaria {tempGuid}", null);
time.Start();
command.CommandText = queryTempTable;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
time.Stop();
Program.AtualizarStatus(server, $"tabela Temporaria {tempGuid} criada", time.Elapsed);
time = new System.Diagnostics.Stopwatch();
Program.AtualizarStatus(server, $"Inserindo dados na tabela {tempGuid}, qtd de registro {dados.Rows.Count}", null);
time.Start();
_connectionFactory.BulkCopy(tempGuid, dados);
time.Stop();
Program.AtualizarStatus(server, $"Dados inserido na tabela {tempGuid}", time.Elapsed);
time = new System.Diagnostics.Stopwatch();
Program.AtualizarStatus(server, $"Iniciando os insert e update na tabela {destinationSchema}.{destinationTable}", null);
time.Start();
var queryMerge = Query.GenerateMerge(tempGuid, destinationSchema, destinationTable, columnPK, colunasDestino);
_connection = validaConexao(_connection);
command = _connection.CreateCommand();
command.CommandText = queryMerge;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
time.Stop();
Program.AtualizarStatus(server, $"Atualização finalizada", time.Elapsed);
time = new System.Diagnostics.Stopwatch();
}
