private static void FillAuxiliaryColumns(ISql sql, TableDescription tableDescription)
{
using var connection = sql.CreateConnection();
connection.Open();
var tableName = tableDescription.TableName();
var command = "WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY newid() ASC) AS ROWNUMBER, [@DF1] FROM {0}) UPDATE T2 SET T2.[@DF2] = T1.[@DF1] FROM {0} T2, CTE T1 WHERE T1.ROWNUMBER = T2.[@DF1]".FormatInvariant(tableName);
sql.NonQuery(connection, command);
}
private static void UpdateForeignKeys(ISql sql, ForeignKeyDescription foreignKeyDescription)
{
var parentTableName = foreignKeyDescription.Parent.TableName();
var referencedTableName = foreignKeyDescription.Referenced.TableName();
var columnRelationshipDescriptions = foreignKeyDescription.ColumnRelationshipDescriptions;
var referencedColumnNames = columnRelationshipDescriptions.Select(_ => _.Referenced.Name);
var parentColumnNames = columnRelationshipDescriptions.Select(_ => _.Parent.Name);
var updatePairs = parentColumnNames.Zip(referencedColumnNames, (p, r) => $"T1.{p} = T2.{r}");
var sb = new StringBuilder("UPDATE T1 SET ")
.AppendJoin(", ", updatePairs)
.AppendFormatInvariant(" FROM {0} T1, {1} T2 WHERE T2.[@DF1] = T1.[@DF2];", parentTableName, referencedTableName)
.ToString();
sql.NonQuery(sb);
}
private static void CreateTable(ISql sql, TablePrescription tablePrescription) => sql.NonQuery(CreateTableDefinition(tablePrescription));