public SqlCommand BuildUpdateColumnCommand(SqlConnection connection, UpdateColumnRequest request)
{
var cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"UPDATE [{request.SchemaName}].[{request.TableName}] SET
[{request.ColumnName}] = @NewValue
WHERE [{request.ColumnName}] = @OldValue";
cmd.Parameters.AddWithValue("@OldValue", request.OldValue);
cmd.Parameters.AddWithValue("@NewValue", request.NewValue);
return(cmd);
}
public async Task UpdateColumnAsync(UpdateColumnRequest request)
{
var cmd = CommandBuilder.BuildUpdateColumnCommand(Connection, request);
try
{
await Connection.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
finally
{
await Connection.CloseAsync();
}
}
public InsertColumnResponse InsertColumn(UpdateColumnRequest columnRequest)
{
List <DroppedDependencyDbo> droppedForeignKeys;
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
DataColumnDbo column = InsertColumn(columnRequest.Table, columnRequest.Column, columnRequest.SingleUserMode,
columnRequest.DisableDependencies, out droppedForeignKeys);
int recordCounter = CountRecords(columnRequest.Table);
string logMsg = String.Format("Table {0}: inserted column '{1}' [{2}] {3} {4} {5} {6}.", columnRequest.Table,
columnRequest.Column.Name, columnRequest.Column.SqlDataType, columnRequest.Column.MaximumLength,
columnRequest.Column.NumericPrecision, columnRequest.Column.NumericScale,
columnRequest.Column.IsNullable ? "NULL" : "NOT NULL");
Guid historyRecordId = LogTableOperation(columnRequest.Table, logMsg, columnRequest.CFC_DB_Major_Version,
columnRequest.CFC_DB_Minor_Version);
trScope.Complete();
return(new InsertColumnResponse()
{
IsSuccess = true,
Column = column,
DroppedForeignKeys = droppedForeignKeys,
RecordCount = recordCounter
});
}
}
catch (Exception ex)
{
return(new InsertColumnResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
}
public DeleteColumnResponse DeleteColumn(UpdateColumnRequest columnRequest)
{
List <DroppedDependencyDbo> droppedDependencies;
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
DeleteColumn(columnRequest.Table, columnRequest.Column.Name, columnRequest.DisableDependencies,
columnRequest.SingleUserMode, out droppedDependencies);
var rzlt = new DeleteColumnResponse()
{
IsSuccess = true,
DroppedDependencies = droppedDependencies,
RecordCount = CountRecords(columnRequest.Table),
Column = columnRequest.Column,
};
string logMsg = String.Format("Column '{0}' was deleted.", columnRequest.Column.Name);
Guid historyRecordId = LogTableOperation(columnRequest.Table, logMsg, columnRequest.CFC_DB_Major_Version,
columnRequest.CFC_DB_Minor_Version);
trScope.Complete();
return(rzlt);
}
}
catch (Exception ex)
{
return(new DeleteColumnResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
}
public RenameColumnResponse RenameColumn(UpdateColumnRequest columnRequest)
{
List <AlteredDependencyDbo> alteredDependencies;
try
{
var options = new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.Serializable,
Timeout = new TimeSpan(0, TransactionTimeout, 0)
};
using (var trScope = new TransactionScope(TransactionScopeOption.Required, options))
{
DataColumnDbo column = RenameColumn(columnRequest.Table, columnRequest.OldColumnName, columnRequest.Column.Name,
columnRequest.SingleUserMode, out alteredDependencies);
var rzlt = new RenameColumnResponse()
{
IsSuccess = true, Column = column
};
rzlt.AlteredDependencies.AddRange(alteredDependencies);
rzlt.RecordCount = CountRecords(columnRequest.Table);
string logMsg = String.Format("Name of the column '{0}' was changed to {1}.", columnRequest.OldColumnName,
columnRequest.Column.Name);
Guid historyRecordId = LogTableOperation(columnRequest.Table, logMsg, columnRequest.CFC_DB_Major_Version,
columnRequest.CFC_DB_Minor_Version);
trScope.Complete();
return(rzlt);
}
}
catch (Exception ex)
{
return(new RenameColumnResponse()
{
IsSuccess = false, ErrorMessage = ParseErrorMessage(ex)
});
}
}
protected override async Task ExecuteAsync(CancellationToken stoppingToken)
{
var tables = await _metadataRepository.GetTablesAsync();
var foreignKeys = await _metadataRepository.GetForeignKeysAsync();
foreach (var table in tables)
{
if (table.PrimaryKeyType != "uniqueidentifier")
{
continue;
}
Console.Write($"Updating {table.Name} ");
var stopwatch = Stopwatch.StartNew();
var references = foreignKeys.Where(x => x.PrimaryTableId == table.Id && x.PrimaryColumnId == table.PrimaryKeyId).ToList();
await DisableForeignKeys(references);
var dataRequest = new GetDataRequest
{
SchemaName = table.Schema,
TableName = table.Name,
ColumnName = table.PrimaryKeyName
};
var data = await _dataRepository.GetDataAsync(dataRequest);
var(cursorLeft, cursorTop) = Console.GetCursorPosition();
var count = 0;
foreach (var id in data)
{
count++;
if (count % 50 == 0)
{
Console.SetCursorPosition(cursorLeft, cursorTop);
Console.Write($"{count}");
}
var newId = SequentialGuid.NewGuid();
var updateIdRequest = new UpdateColumnRequest
{
SchemaName = table.Schema,
TableName = table.Name,
ColumnName = table.PrimaryKeyName,
OldValue = id,
NewValue = newId
};
await _dataRepository.UpdateColumnAsync(updateIdRequest);
foreach (var fk in references)
{
var updateKeyRequest = new UpdateColumnRequest
{
SchemaName = fk.ForeignSchemaName,
TableName = fk.ForeignTableName,
ColumnName = fk.ForeignColumnName,
OldValue = id,
NewValue = newId
};
await _dataRepository.UpdateColumnAsync(updateKeyRequest);
}
}
await EnableForeignKeys(references);
stopwatch.Stop();
Console.SetCursorPosition(cursorLeft, cursorTop);
Console.WriteLine($" Done ({stopwatch.Elapsed})");
}
_appLifetime.StopApplication();
}
