private static List <DroppedDependencyDbo> ModifyColumn(ProcessColumnDelegate processor,
Table aTable, DataColumnDbo column, bool disableDependencies, Database db)
{
var rzlt = new List <DroppedDependencyDbo>();
IndexKeyType oldIndexKeyType;
bool oldIsClustered;
string oldIndexName;
for (int i = aTable.Indexes.Count - 1; i >= 0; i--)
{
Index currentInd = aTable.Indexes[i];
string[] indexColumns = GetIndexColumns(currentInd);
if (indexColumns.Contains(column.Name))
{
if (disableDependencies)
{
DropDependentForeignKeys(currentInd.Name, db, rzlt);
}
oldIndexName = currentInd.Name;
oldIndexKeyType = currentInd.IndexKeyType;
oldIsClustered = currentInd.IsClustered;
currentInd.Drop();
processor(aTable, column);
CreateNewPrimaryKey(aTable, oldIndexName, oldIndexKeyType, indexColumns);
return(rzlt);
}
}
// No index contains column
processor(aTable, column);
return(rzlt);
}
/// <summary>
/// Creates column in the table
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column description <see cref="DataColumnDbo"/></param>
/// <returns>SMO column, <see cref="Column"/></returns>
private static Column CreateColumn(Table aTable, DataColumnDbo column)
{
SqlDataType sqlType = (SqlDataType)Enum.Parse(typeof(SqlDataType), column.SqlDataType, true);
int length;
if (column.MaximumLength.HasValue && column.MaximumLength.Value > 0)
{
length = column.MaximumLength.Value;
}
else
{
length = column.NumericPrecision ?? 0;
}
var newColumn = new Column(aTable, column.Name,
GetSmoType(sqlType, length, column.NumericPrecision ?? 0, column.NumericScale ?? 0))
{
Identity = column.IsIdentity,
Nullable = column.IsNullable,
};
if (!String.IsNullOrEmpty(column.Default))
{
newColumn.AddDefaultConstraint();
newColumn.DefaultConstraint.Text = column.Default;
}
if (newColumn.Identity)
{
newColumn.IdentityIncrement = 1;
newColumn.IdentitySeed = 1;
}
aTable.Columns.Add(newColumn);
return(newColumn);
}
public static DataColumnDbo CreateDataColumnDbo(Column clmn, IList <string> primaryKeyColumns)
{
var rzlt = new DataColumnDbo()
{
Name = clmn.Name,
SqlDataType = clmn.DataType.Name,
MaximumLength = clmn.DataType.MaximumLength,
NumericPrecision = clmn.DataType.NumericPrecision,
NumericScale = clmn.DataType.NumericScale,
IsNullable = clmn.Nullable,
IsIdentity = clmn.Identity,
IsPrimaryKey = primaryKeyColumns.Contains(clmn.Name),
};
if (clmn.DefaultConstraint != null)
{
string dfText = clmn.DefaultConstraint.Text;
while (dfText.StartsWith("(") && dfText.EndsWith(")"))
{
dfText = dfText.Substring(1, dfText.Length - 2);
}
rzlt.Default = dfText;
}
return(rzlt);
}
/// <summary>
/// The procedure manages processing of the column updating.
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="column">Column description </param>
/// <param name="disableDependencies"><code>true</code> - remove dependencies</param>
/// <param name="singleUserMode"><code>true</code> - single user mode</param>
/// <param name="droppedKeys">List of dropped foreign keys</param>
/// <returns>Description of the updated column</returns>
public static DataColumnDbo UpdateColumn(string tableName, DataColumnDbo column, bool disableDependencies, bool singleUserMode,
out List <DroppedDependencyDbo> droppedKeys)
{
Database db = null;
bool isSingleUserMode = false;
var droppedForeignKeys = new List <DroppedDependencyDbo>();
try
{
Table aTable = GetTable(tableName, out db);
if (singleUserMode && !column.IsPrimaryKey)
{
isSingleUserMode = SetSingleMode(db);
}
Column currentColumn;
DataColumnDbo oldValues = GetDataColumnDbo(aTable, column.Name, out currentColumn);
if (oldValues.IsNullable && !column.IsNullable)
{
RemoveNullCondition(aTable, currentColumn);
}
#region Primary Key block
if (!oldValues.IsPrimaryKey && column.IsPrimaryKey)
{
// Include into primary key
droppedForeignKeys.AddRange(InsertColumnIntoPrimarykey(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
else if (oldValues.IsPrimaryKey && !column.IsPrimaryKey)
{
// Remove from primary key
droppedForeignKeys.AddRange(RemoveColumnFromPrimarykey(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
#endregion
if (!oldValues.IsNullable && column.IsNullable)
{
droppedForeignKeys.AddRange(RestoreNullCondition(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
if (DataColumnDbo.RequiresRecreating(oldValues, column))
{
droppedForeignKeys.AddRange(RemoveOrRestoreIdentity(aTable, column, disableDependencies, db));
}
droppedKeys = droppedForeignKeys;
return(GetDataColumnDbo(aTable, column.Name, out currentColumn));
}
finally
{
if (isSingleUserMode && db != null)
{
SetMultiUserMode(db);
}
}
}
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)
});
}
}
private static void RecreateColumn(Table aTable, DataColumnDbo column)
{
// Create new column
bool oldIsNullable = column.IsNullable;
string oldName = column.Name;
column.IsNullable = true;
column.Name = String.Format("{0}_{1}", oldName, CreateUniqueAppendix());
Column newColumn = CreateColumn(aTable, column);
aTable.Alter();
// Copy data into new column
string queryString = String.Format("UPDATE [{0}] SET [{1}] = [{2}]", aTable.Name, column.Name, oldName);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
// Drop old column and rename new one.
aTable.Columns[oldName].Drop();
aTable.Alter();
Column clmn = aTable.Columns[column.Name];
if (!oldIsNullable)
{
clmn.Nullable = false;
}
column.IsNullable = oldIsNullable;
clmn.Rename(oldName);
clmn.Alter();
aTable.Alter();
column.Name = oldName;
}
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)
});
}
}
/// <summary>
/// Inserts new column into the table
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="column">Column definition, <see cref="DataColumnDbo"/></param>
/// <param name="singleUserMode"><code>true</code> - switch to single user mode</param>
/// <param name="disableDependencies">
/// <code>true</code> - drop foreign keys that references primary key if column is new member of priamry key
/// </param>
/// <param name="droppedKeys">Foreign keys, that were removed for recreating primary key.</param>
/// <returns>Column description, <see cref="DataColumnDbo"/></returns>
public static DataColumnDbo InsertColumn(string tableName, DataColumnDbo column, bool singleUserMode, bool disableDependencies,
out List <DroppedDependencyDbo> droppedKeys)
{
Database db = null;
bool isSingleUserMode = false;
try
{
Table aTable = GetTable(tableName, out db);
if (singleUserMode && !column.IsPrimaryKey)
{
isSingleUserMode = SetSingleMode(db);
}
Column newColumn = CreateColumn(aTable, column);
aTable.Alter();
if (column.IsPrimaryKey)
{
droppedKeys = InsertColumnIntoPrimarykey(aTable, newColumn, disableDependencies, db);
aTable.Alter();
}
else
{
droppedKeys = new List <DroppedDependencyDbo>();
}
List <string> primaryKeyColumns = GetPrimaryKeyColumns(aTable);
return(CreateDataColumnDbo(newColumn, primaryKeyColumns));
}
finally
{
if (isSingleUserMode && db != null)
{
SetMultiUserMode(db);
}
}
}
/// <summary>
/// Removes Identity constraint
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column, <see cref="DataColumnDbo"/></param>
/// <param name="disableDependencies"><code>true></code> - drop dependencies</param>
/// <param name="db">Current database, <see cref="Database"/></param>
public static List <DroppedDependencyDbo> RemoveOrRestoreIdentity(
Table aTable, DataColumnDbo column, bool disableDependencies, Database db)
{
return(ModifyColumn(new ProcessColumnDelegate(RecreateColumn), aTable, column, disableDependencies, db));
}
/// <summary>
/// The procedure manages processing of the column updating.
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="column">Column description </param>
/// <param name="disableDependencies"><code>true</code> - remove dependencies</param>
/// <param name="singleUserMode"><code>true</code> - single user mode</param>
/// <param name="droppedKeys">List of dropped foreign keys</param>
/// <returns>Description of the updated column</returns>
public static DataColumnDbo UpdateColumn(string tableName, DataColumnDbo column, bool disableDependencies, bool singleUserMode,
out List<DroppedDependencyDbo> droppedKeys)
{
Database db = null;
bool isSingleUserMode = false;
var droppedForeignKeys = new List<DroppedDependencyDbo>();
try
{
Table aTable = GetTable(tableName, out db);
if (singleUserMode && !column.IsPrimaryKey)
isSingleUserMode = SetSingleMode(db);
Column currentColumn;
DataColumnDbo oldValues = GetDataColumnDbo(aTable, column.Name, out currentColumn);
if (oldValues.IsNullable && !column.IsNullable)
RemoveNullCondition(aTable, currentColumn);
#region Primary Key block
if (!oldValues.IsPrimaryKey && column.IsPrimaryKey)
{
// Include into primary key
droppedForeignKeys.AddRange(InsertColumnIntoPrimarykey(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
else if (oldValues.IsPrimaryKey && !column.IsPrimaryKey)
{
// Remove from primary key
droppedForeignKeys.AddRange(RemoveColumnFromPrimarykey(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
#endregion
if (!oldValues.IsNullable && column.IsNullable)
{
droppedForeignKeys.AddRange(RestoreNullCondition(aTable, currentColumn, disableDependencies, db));
aTable.Alter();
}
if (DataColumnDbo.RequiresRecreating(oldValues, column))
droppedForeignKeys.AddRange(RemoveOrRestoreIdentity(aTable, column, disableDependencies, db));
droppedKeys = droppedForeignKeys;
return GetDataColumnDbo(aTable, column.Name, out currentColumn);
}
finally
{
if (isSingleUserMode && db != null)
SetMultiUserMode(db);
}
}
/// <summary>
/// Inserts new column into the table
/// </summary>
/// <param name="tableName">Table name</param>
/// <param name="column">Column definition, <see cref="DataColumnDbo"/></param>
/// <param name="singleUserMode"><code>true</code> - switch to single user mode</param>
/// <param name="disableDependencies">
/// <code>true</code> - drop foreign keys that references primary key if column is new member of priamry key
/// </param>
/// <param name="droppedKeys">Foreign keys, that were removed for recreating primary key.</param>
/// <returns>Column description, <see cref="DataColumnDbo"/></returns>
public static DataColumnDbo InsertColumn(string tableName, DataColumnDbo column, bool singleUserMode, bool disableDependencies,
out List<DroppedDependencyDbo> droppedKeys)
{
Database db = null;
bool isSingleUserMode = false;
try
{
Table aTable = GetTable(tableName, out db);
if (singleUserMode && !column.IsPrimaryKey)
isSingleUserMode = SetSingleMode(db);
Column newColumn = CreateColumn(aTable, column);
aTable.Alter();
if (column.IsPrimaryKey)
{
droppedKeys = InsertColumnIntoPrimarykey(aTable, newColumn, disableDependencies, db);
aTable.Alter();
}
else
droppedKeys = new List<DroppedDependencyDbo>();
List<string> primaryKeyColumns = GetPrimaryKeyColumns(aTable);
return CreateDataColumnDbo(newColumn, primaryKeyColumns);
}
finally
{
if (isSingleUserMode && db != null)
SetMultiUserMode(db);
}
}
/// <summary>
/// Creates column in the table
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column description <see cref="DataColumnDbo"/></param>
/// <returns>SMO column, <see cref="Column"/></returns>
private static Column CreateColumn(Table aTable, DataColumnDbo column)
{
SqlDataType sqlType = (SqlDataType)Enum.Parse(typeof(SqlDataType), column.SqlDataType, true);
int length;
if (column.MaximumLength.HasValue && column.MaximumLength.Value > 0)
length = column.MaximumLength.Value;
else
length = column.NumericPrecision ?? 0;
var newColumn = new Column(aTable, column.Name,
GetSmoType(sqlType, length, column.NumericPrecision ?? 0, column.NumericScale ?? 0))
{
Identity = column.IsIdentity,
Nullable = column.IsNullable,
};
if (!String.IsNullOrEmpty(column.Default))
{
newColumn.AddDefaultConstraint();
newColumn.DefaultConstraint.Text = column.Default;
}
if (newColumn.Identity)
{
newColumn.IdentityIncrement = 1;
newColumn.IdentitySeed = 1;
}
aTable.Columns.Add(newColumn);
return newColumn;
}
public static DataColumnDbo CreateDataColumnDbo(Column clmn, IList<string> primaryKeyColumns)
{
var rzlt = new DataColumnDbo()
{
Name = clmn.Name,
SqlDataType = clmn.DataType.Name,
MaximumLength = clmn.DataType.MaximumLength,
NumericPrecision = clmn.DataType.NumericPrecision,
NumericScale = clmn.DataType.NumericScale,
IsNullable = clmn.Nullable,
IsIdentity = clmn.Identity,
IsPrimaryKey = primaryKeyColumns.Contains(clmn.Name),
};
if (clmn.DefaultConstraint != null)
{
string dfText = clmn.DefaultConstraint.Text;
while (dfText.StartsWith("(") && dfText.EndsWith(")"))
dfText = dfText.Substring(1, dfText.Length - 2);
rzlt.Default = dfText;
}
return rzlt;
}
private static void RecreateColumn(Table aTable, DataColumnDbo column)
{
// Create new column
bool oldIsNullable = column.IsNullable;
string oldName = column.Name;
column.IsNullable = true;
column.Name = String.Format("{0}_{1}", oldName, CreateUniqueAppendix());
Column newColumn = CreateColumn(aTable, column);
aTable.Alter();
// Copy data into new column
string queryString = String.Format("UPDATE [{0}] SET [{1}] = [{2}]", aTable.Name, column.Name, oldName);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
// Drop old column and rename new one.
aTable.Columns[oldName].Drop();
aTable.Alter();
Column clmn = aTable.Columns[column.Name];
if (!oldIsNullable)
clmn.Nullable = false;
column.IsNullable = oldIsNullable;
clmn.Rename(oldName);
clmn.Alter();
aTable.Alter();
column.Name = oldName;
}
private static List<DroppedDependencyDbo> ModifyColumn(ProcessColumnDelegate processor,
Table aTable, DataColumnDbo column, bool disableDependencies, Database db)
{
var rzlt = new List<DroppedDependencyDbo>();
IndexKeyType oldIndexKeyType;
bool oldIsClustered;
string oldIndexName;
for (int i = aTable.Indexes.Count - 1; i >= 0; i--)
{
Index currentInd = aTable.Indexes[i];
string[] indexColumns = GetIndexColumns(currentInd);
if (indexColumns.Contains(column.Name))
{
if (disableDependencies)
DropDependentForeignKeys(currentInd.Name, db, rzlt);
oldIndexName = currentInd.Name;
oldIndexKeyType = currentInd.IndexKeyType;
oldIsClustered = currentInd.IsClustered;
currentInd.Drop();
processor(aTable, column);
CreateNewPrimaryKey(aTable, oldIndexName, oldIndexKeyType, indexColumns);
return rzlt;
}
}
// No index contains column
processor(aTable, column);
return rzlt;
}
/// <summary>
/// Removes Identity constraint
/// </summary>
/// <param name="aTable">Table, <see cref="Table"/></param>
/// <param name="column">Column, <see cref="DataColumnDbo"/></param>
/// <param name="disableDependencies"><code>true></code> - drop dependencies</param>
/// <param name="db">Current database, <see cref="Database"/></param>
public static List<DroppedDependencyDbo> RemoveOrRestoreIdentity(
Table aTable, DataColumnDbo column, bool disableDependencies, Database db)
{
return ModifyColumn(new ProcessColumnDelegate(RecreateColumn), aTable, column, disableDependencies, db);
}
