public IEnumerable <string> RenameColumn(string tableName, string oldName, string newName)
{
Table table = GetTable(tableName);
var column = new Microsoft.SqlServer.Management.Smo.Column(table, oldName);
// rename default constraint
string oldDefaultConstraintName = ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, oldName);
string constraintName = oldDefaultConstraintName;
DefaultConstraint defaultConstraint = column.AddDefaultConstraint(constraintName);
defaultConstraint.Rename(ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, newName));
// rename column
table.Columns.Add(column);
column.Rename(newName);
// script changes
IEnumerable <string> commandTexts = ScriptChanges(table.Parent.Parent);
string renameDefaultConstraintCommandText = string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", oldDefaultConstraintName) + commandTexts.First();
yield return(Regex.Replace(renameDefaultConstraintCommandText, @"EXEC \[\w+\]\.dbo\.sp_rename", @"EXEC dbo.sp_rename")); // for some reason SMO is putting the server name in front of dbo.sp_rename which we do not have in the SqlServerProvider
foreach (string commandText in commandTexts.Skip(1))
{
yield return(commandText);
}
}
public IEnumerable <string> AlterColumn(string tableName, Column column)
{
// drop (possibly) existing default constraint
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, "dummy");
defaultConstraint.Drop();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return(string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraint.Name) + command);
}
// alter column
table = GetTable(tableName);
c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
c.Alter();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return(command);
}
}
private static DefaultConstraint AddDefaultConstraint(Microsoft.SqlServer.Management.Smo.Column column, string tableName, object value)
{
string constraintName = ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, column.Name);
DefaultConstraint defaultConstraint = column.AddDefaultConstraint(constraintName);
defaultConstraint.Text = GetDefaultValueAsString(value);
return(defaultConstraint);
}
public IEnumerable <string> DropDefault(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, column.DefaultValue);
defaultConstraint.Drop();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable<string> AddForeignKey(string tableName, string referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName)
{
Table table = GetTable(tableName);
ForeignKey foreignKey = new ForeignKey(table, constraintName) { ReferencedTable = referencedTableName };
foreach (ColumnReference columnReference in columnNames)
{
var fromColumn = new Microsoft.SqlServer.Management.Smo.Column(table, columnReference.ColumnName);
table.Columns.Add(fromColumn);
var foreignKeyColumn = new ForeignKeyColumn(foreignKey, columnReference.ColumnName, columnReference.ReferencedColumnName);
foreignKey.Columns.Add(foreignKeyColumn);
}
foreignKey.Create();
return ScriptChanges(table.Parent.Parent);
}
public IEnumerable <string> CreateTable(string tableName, IEnumerable <CreatedColumn> columns, string primaryKeyConstraintName)
{
Table table = GetTable(tableName);
foreach (CreatedColumn createdColumn in columns)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, createdColumn.Name)
{
DataType = Convert(createdColumn.DataType),
Nullable = createdColumn.IsNullable,
Identity = createdColumn.IsIdentity,
};
if (createdColumn.DefaultValue != null)
{
AddDefaultConstraint(column, tableName, createdColumn.DefaultValue);
}
table.Columns.Add(column);
}
List <CreatedColumn> primaryKeyColumns = new List <CreatedColumn>(columns.Where(c => c.IsPrimaryKey));
if (primaryKeyColumns.Count > 0)
{
Index pkIndex = new Index(table, primaryKeyConstraintName);
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
foreach (CreatedColumn primaryKeyColumn in primaryKeyColumns)
{
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, primaryKeyColumn.Name));
}
table.Indexes.Add(pkIndex);
}
foreach (var uniqueColumns in columns
.Where(c => !string.IsNullOrEmpty(c.UniqueConstraint))
.GroupBy(c => c.UniqueConstraint))
{
Index uniqueIndex = new Index(table, uniqueColumns.Key);
uniqueIndex.IndexKeyType = IndexKeyType.DriUniqueKey;
foreach (var column in uniqueColumns)
{
uniqueIndex.IndexedColumns.Add(new IndexedColumn(uniqueIndex, column.Name));
}
table.Indexes.Add(uniqueIndex);
}
table.Create();
ScriptingOptions options = new ScriptingOptions {
Indexes = true, DriDefaults = true
};
return(TransformScript(table.Script(options)));
}
public IEnumerable<string> AddColumn(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
table.Columns.Add(c);
table.Alter();
return ScriptChanges(table.Parent.Parent);
}
public IEnumerable <string> AddColumn(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
table.Columns.Add(c);
table.Alter();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable <string> AddForeignKey(string tableName, string referencedTableName, IEnumerable <ColumnReference> columnNames, string constraintName)
{
Table table = GetTable(tableName);
ForeignKey foreignKey = new ForeignKey(table, constraintName)
{
ReferencedTable = referencedTableName
};
foreach (ColumnReference columnReference in columnNames)
{
var fromColumn = new Microsoft.SqlServer.Management.Smo.Column(table, columnReference.ColumnName);
table.Columns.Add(fromColumn);
var foreignKeyColumn = new ForeignKeyColumn(foreignKey, columnReference.ColumnName, columnReference.ReferencedColumnName);
foreignKey.Columns.Add(foreignKeyColumn);
}
foreignKey.Create();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable<string> AddIndex(string tableName, IEnumerable<string> columnNames, string indexName)
{
Table table = GetTable(tableName);
Index index = new Index(table, indexName);
table.Indexes.Add(index);
foreach (string columnName in columnNames)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName)
{
DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant
};
table.Columns.Add(column);
IndexedColumn indexedColumn = new IndexedColumn(index, columnName);
index.IndexedColumns.Add(indexedColumn);
}
index.Create();
return ScriptChanges(table.Parent.Parent);
}
private static IEnumerable <string> AddConstraint(string tableName, string constraintName, IndexKeyType keyType, IEnumerable <string> columnNames)
{
Table table = GetTable(tableName);
Index uniqueConstraint = new Index(table, constraintName)
{
IndexKeyType = keyType
};
foreach (string columnName in columnNames)
{
Microsoft.SqlServer.Management.Smo.Column column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName);
column.DataType = Microsoft.SqlServer.Management.Smo.DataType.Bit;
table.Columns.Add(column);
uniqueConstraint.IndexedColumns.Add(new IndexedColumn(uniqueConstraint, columnName));
}
uniqueConstraint.Create();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable <string> AddIndex(string tableName, IEnumerable <string> columnNames, string indexName)
{
Table table = GetTable(tableName);
Index index = new Index(table, indexName);
table.Indexes.Add(index);
foreach (string columnName in columnNames)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName)
{
DataType = Microsoft.SqlServer.Management.Smo.DataType.Variant
};
table.Columns.Add(column);
IndexedColumn indexedColumn = new IndexedColumn(index, columnName);
index.IndexedColumns.Add(indexedColumn);
}
index.Create();
return(ScriptChanges(table.Parent.Parent));
}
public IEnumerable <string> DropColumn(string tableName, string columnName)
{
Table table = GetTable(tableName);
string defaultConstraintName = ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, columnName);
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName);
column.AddDefaultConstraint(defaultConstraintName);
table.Columns.Add(column);
column.Drop();
// script changes
IEnumerable <string> commandTexts = ScriptChanges(table.Parent.Parent);
string dropDefaultConstraintCommandText = string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraintName) + commandTexts.First();
yield return(dropDefaultConstraintCommandText);
foreach (string commandText in commandTexts.Skip(1))
{
yield return(commandText);
}
}
private static IEnumerable<string> AddConstraint(string tableName, string constraintName, IndexKeyType keyType, IEnumerable<string> columnNames)
{
Table table = GetTable(tableName);
Index uniqueConstraint = new Index(table, constraintName) { IndexKeyType = keyType };
foreach (string columnName in columnNames)
{
Microsoft.SqlServer.Management.Smo.Column column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName);
column.DataType = Microsoft.SqlServer.Management.Smo.DataType.Bit;
table.Columns.Add(column);
uniqueConstraint.IndexedColumns.Add(new IndexedColumn(uniqueConstraint, columnName));
}
uniqueConstraint.Create();
return ScriptChanges(table.Parent.Parent);
}
public IEnumerable<string> RenameColumn(string tableName, string oldName, string newName)
{
Table table = GetTable(tableName);
var column = new Microsoft.SqlServer.Management.Smo.Column(table, oldName);
// rename default constraint
string oldDefaultConstraintName = ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, oldName);
string constraintName = oldDefaultConstraintName;
DefaultConstraint defaultConstraint = column.AddDefaultConstraint(constraintName);
defaultConstraint.Rename(ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, newName));
// rename column
table.Columns.Add(column);
column.Rename(newName);
// script changes
IEnumerable<string> commandTexts = ScriptChanges(table.Parent.Parent);
string renameDefaultConstraintCommandText = string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", oldDefaultConstraintName) + commandTexts.First();
yield return Regex.Replace(renameDefaultConstraintCommandText, @"EXEC \[\w+\]\.dbo\.sp_rename", @"EXEC dbo.sp_rename"); // for some reason SMO is putting the server name in front of dbo.sp_rename which we do not have in the SqlServerProvider
foreach (string commandText in commandTexts.Skip(1))
{
yield return commandText;
}
}
public IEnumerable<string> DropDefault(string tableName, Column column)
{
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
DataType = Convert(column.DataType),
Nullable = column.IsNullable,
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, column.DefaultValue);
defaultConstraint.Drop();
return ScriptChanges(table.Parent.Parent);
}
public IEnumerable<string> DropColumn(string tableName, string columnName)
{
Table table = GetTable(tableName);
string defaultConstraintName = ObjectNameHelper.GetObjectName(tableName, "DF", MaximumDbObjectNameLength, columnName);
var column = new Microsoft.SqlServer.Management.Smo.Column(table, columnName);
column.AddDefaultConstraint(defaultConstraintName);
table.Columns.Add(column);
column.Drop();
// script changes
IEnumerable<string> commandTexts = ScriptChanges(table.Parent.Parent);
string dropDefaultConstraintCommandText = string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraintName) + commandTexts.First();
yield return dropDefaultConstraintCommandText;
foreach (string commandText in commandTexts.Skip(1))
{
yield return commandText;
}
}
public IEnumerable<string> CreateTable(string tableName, IEnumerable<CreatedColumn> columns, string primaryKeyConstraintName)
{
Table table = GetTable(tableName);
foreach (CreatedColumn createdColumn in columns)
{
var column = new Microsoft.SqlServer.Management.Smo.Column(table, createdColumn.Name)
{
DataType = Convert(createdColumn.DataType),
Nullable = createdColumn.IsNullable,
Identity = createdColumn.IsIdentity,
};
if (createdColumn.DefaultValue != null)
{
AddDefaultConstraint(column, tableName, createdColumn.DefaultValue);
}
table.Columns.Add(column);
}
List<CreatedColumn> primaryKeyColumns = new List<CreatedColumn>(columns.Where(c => c.IsPrimaryKey));
if (primaryKeyColumns.Count > 0)
{
Index pkIndex = new Index(table, primaryKeyConstraintName);
pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
foreach (CreatedColumn primaryKeyColumn in primaryKeyColumns)
{
pkIndex.IndexedColumns.Add(new IndexedColumn(pkIndex, primaryKeyColumn.Name));
}
table.Indexes.Add(pkIndex);
}
foreach (var uniqueColumns in columns
.Where(c => !string.IsNullOrEmpty(c.UniqueConstraint))
.GroupBy(c => c.UniqueConstraint))
{
Index uniqueIndex = new Index(table, uniqueColumns.Key);
uniqueIndex.IndexKeyType = IndexKeyType.DriUniqueKey;
foreach (var column in uniqueColumns)
{
uniqueIndex.IndexedColumns.Add(new IndexedColumn(uniqueIndex, column.Name));
}
table.Indexes.Add(uniqueIndex);
}
table.Create();
ScriptingOptions options = new ScriptingOptions { Indexes = true, DriDefaults = true };
return TransformScript(table.Script(options));
}
public IEnumerable<string> AlterColumn(string tableName, Column column)
{
// drop (possibly) existing default constraint
Table table = GetTable(tableName);
var c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
DefaultConstraint defaultConstraint = AddDefaultConstraint(c, tableName, "dummy");
defaultConstraint.Drop();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return string.Format(CultureInfo.InvariantCulture, "IF OBJECT_ID('{0}') IS NOT NULL ", defaultConstraint.Name) + command;
}
// alter column
table = GetTable(tableName);
c = new Microsoft.SqlServer.Management.Smo.Column(table, column.Name)
{
Nullable = column.IsNullable,
DataType = Convert(column.DataType),
};
if (column.DefaultValue != null)
{
AddDefaultConstraint(c, tableName, column.DefaultValue);
}
c.Alter();
foreach (string command in ScriptChanges(table.Parent.Parent))
{
yield return command;
}
}
