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); }