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