public static string GetMergeCommand(this SqlServerDestiny destiny, List <string> keys)
{
string sql = $" Merge {destiny.Table} as Destiny \n";
sql += $" USING #{destiny.Table} as Origin \n";
for (int i = 0; i < keys.Count; i++)
{
string key = keys[i];
sql += $" {(i == 0 ? "ON" : "AND")} Destiny.[{key}] = Origin.[{key}] ";
sql += "\n";
}
var columns = destiny.GetColumns();
var columnsWhithoutKeys = columns.Where(x => !keys.Any(y => y == x)).ToList();
sql += $" WHEN MATCHED THEN \n";
sql += $" UPDATE SET \n";
for (int i = 0; i < columnsWhithoutKeys.Count; i++)
{
string column = columnsWhithoutKeys[i];
sql += $" [{column}] = Origin.[{column}]";
sql += (i < columnsWhithoutKeys.Count - 1) ? "," : "";
sql += "\n";
}
sql += $" WHEN NOT MATCHED THEN \n";
sql += $" INSERT ( { string.Join(", ", columns.Select(x => $"[{x}]"))} ) \n";
sql += $" VALUES ( { string.Join(", ", columns.Select(x => $"Origin.[{x}]")) } ) \n";
sql += ";";
return(sql);
}
public void DadoUmDestinoVálidoRetornaAsColunasDaTabela()
{
using (destiny.Connection)
{
destiny.Connection.Open();
var keys = destiny.GetColumnKeys();
var result = destiny.GetColumns();
var columns = new List <string>()
{
"Identidade",
"Sigla",
"NomeDaRegiao"
};
Assert.Equal(result, columns);
}
}
