public async Task AddStepColumnsAsync(
string sourceConnection, string destConnection, DataMigration.Step step, object parameters = null)
{
List <DataMigration.Column> result = new List <DataMigration.Column>();
await ExecuteWithConnectionsAsync(sourceConnection, destConnection, async (source, dest) =>
{
var schemaCols = await GetStepSchemaColumns(source, dest, step, parameters);
step.SourceIdentityColumn = findIdentityColumn(schemaCols.sourceColumns);
step.DestIdentityColumn = findIdentityColumn(schemaCols.destColumns);
// add matching columns
var columns = (from src in nonIdentityColumns(schemaCols.sourceColumns)
join dst in nonIdentityColumns(schemaCols.destColumns) on ColumnName(src) equals ColumnName(dst)
select new DataMigration.Column()
{
Source = ColumnName(src),
Dest = ColumnName(dst)
}).ToList();
var notInDest = nonIdentityColumns(schemaCols.sourceColumns).Select(row => ColumnName(row)).Except(nonIdentityColumns(schemaCols.destColumns).Select(row => ColumnName(row)));
columns.AddRange(notInDest.Select(col => new DataMigration.Column()
{
Source = col
}));
var notInSrc = nonIdentityColumns(schemaCols.destColumns).Select(row => ColumnName(row)).Except(nonIdentityColumns(schemaCols.sourceColumns).Select(row => ColumnName(row)));
columns.AddRange(notInSrc.Select(col => new DataMigration.Column()
{
Dest = col
}));
result.AddRange(columns);
});
step.Columns = result;
IEnumerable <DataRow> nonIdentityColumns(DataTable schemaTable) => schemaTable.AsEnumerable().Where(row => !IsIdentity(row));
string findIdentityColumn(DataTable schemaTable)
{
try
{
return(ColumnName(schemaTable.AsEnumerable().First(row => IsIdentity(row))));
}
catch
{
return(null);
}
}
}
/// <summary>
/// initialize a Step object by comparing columns from a source and destination table,
/// </summary>
public async Task <DataMigration.Step> CreateStepAsync(
string sourceConnection, string destConnection, string fromWhere, string destTable, object parameters = null)
{
DataMigration.Step step = new DataMigration.Step()
{
SourceFromWhere = fromWhere,
DestTable = destTable
};
await AddStepColumnsAsync(sourceConnection, destConnection, step, parameters);
return(step);
}
private string GetStepColumnList(DataMigration.Step step, string sourceIdAlias = null)
{
var columns = string.Join(", ", step.Columns
.Where(col => !string.IsNullOrWhiteSpace(col.Source) && !string.IsNullOrWhiteSpace(col.Dest))
.Select(col => (col.Source.StartsWith("=")) ?
$"{col.Source.Substring(1)} AS [{col.Dest}]" :
$"{SqlBuilder.ApplyDelimiter(col.Source, '[', ']')} AS [{col.Dest}]"));
string sourceId = SqlBuilder.ApplyDelimiter(step.SourceIdentityColumn, '[', ']');
if (!string.IsNullOrEmpty(sourceIdAlias))
{
sourceId += $" AS [{sourceIdAlias}]";
}
return($"{columns}, {sourceId}");
}
public async Task AddStepColumnsAsync(DataMigration migration, DataMigration.Step step) => await AddStepColumnsAsync(migration.SourceConnection, migration.DestConnection, step, migration.GetParameters());
private async Task <(DataTable table, string sql)> QuerySourceTableAsync(SqlConnection cnSource, DataMigration.Step step, object parameters = null)
{
const string columnToken = "{columns}";
var columnList = GetStepColumnList(step);
var query = (step.SourceFromWhere.Contains(columnToken)) ?
step.SourceFromWhere.Replace(columnToken, columnList) :
$"SELECT {columnList} FROM {step.SourceFromWhere}";
try
{
var dataTable = await cnSource.QueryTableAsync(query, parameters);
return(dataTable, query);
}
catch (Exception exc)
{
throw new QueryException(query, exc);
}
}
private Dictionary <string, string> GetForeignKeyMapping(DataMigration.Step step) =>
step.Columns
.Where(col => !string.IsNullOrEmpty(col.KeyMapTable) && !col.KeyMapTable.StartsWith("@"))
.ToDictionary(col => col.Dest, col => col.KeyMapTable);
private async Task <(DataTable sourceColumns, DataTable destColumns)> GetStepSchemaColumns(SqlConnection source, SqlConnection dest, DataMigration.Step step, object parameters = null)
{
var sourceCols = await GetSchemaColumns(source, $"SELECT * FROM {step.SourceFromWhere}", parameters);
var destCols = await GetSchemaColumns(dest, $"SELECT * FROM {step.DestTable}", parameters);
return(sourceCols, destCols);
}