public static void AddForeignKey(this Table tb, string columnName, string refTable, string refTableSchema, string refColumn) { ForeignKey fk = new ForeignKey(tb, string.Format("FK_{0}_{1}", tb.Name, columnName)); ForeignKeyColumn fkc = new ForeignKeyColumn(fk, columnName, refColumn); fk.Columns.Add(fkc); fk.ReferencedTable = refTable; fk.ReferencedTableSchema = refTableSchema; fk.Create(); }
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); }
/// <summary> /// Add ForeignKey to table /// </summary> /// <param name="conn"></param> /// <param name="tableName"></param> /// <param name="keyField"> </param> /// <param name="masterTable"> </param> public static void AddForeignKey(SqlConnection conn, string tableName, string keyField, string masterTable, string schemaName = "dbo", string masterSchemaName = "dbo") { Server srv = new Server(new ServerConnection(new SqlConnection((conn.ConnectionString)))); Database db = srv.Databases[conn.Database]; Table tbl = db.Tables[tableName, schemaName]; ForeignKey fk = new ForeignKey(tbl, string.Format("FK_{0}_{1}", tableName, masterTable)); fk.ReferencedTable = masterTable; fk.ReferencedTableSchema = masterSchemaName; new ForeignKeyColumn(); fk.Columns.Add(new ForeignKeyColumn(fk, keyField, keyField)); fk.Create(); }
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)); }
private static ForeignKeyDbo CreateForeignKey(Table aTable, ForeignKeyDbo dbo) { ForeignKey fKey = new ForeignKey(aTable, dbo.Name) { DeleteAction = dbo.DeleteAction, UpdateAction = dbo.UpdateAction, ReferencedTable = dbo.ReferencedTable, IsChecked = dbo.IsChecked }; foreach (ForeignKeyColumnDbo clmn in dbo.Columns) { ForeignKeyColumn fkColumn = new ForeignKeyColumn(fKey, clmn.Name, clmn.ReferencedColumn); fKey.Columns.Add(fkColumn); } fKey.Create(); return(CreateForeignKeyDbo(fKey)); }
private void DwGenerateForeignKey(DsDwColumnMap_M aDsDwColumnMap) { if (aDsDwColumnMap != null && aDsDwColumnMap.Parent != null && aDsDwColumnMap.DwForeignKeyReferencedTableMap != null && !string.IsNullOrWhiteSpace(aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName)) { Table tbea; tbea = DwDb.Tables.Cast <Table>() .FirstOrDefault(tb => tb.Name.ToLower() == aDsDwColumnMap.Parent.DwTableName.ToLower() && tb.Schema.ToLower() == aDsDwColumnMap.Parent.DwSchemaName.ToLower()); DsDwTableMap_M aDwFkReferencedTableMap = aDsDwColumnMap.DwForeignKeyReferencedTableMap; //DsDwMap.DsDwTableMapList.FirstOrDefault( // tm => // tm.DwTableName.ToLower() == aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName.ToLower() && // tm.DwSchemaName.ToLower() == aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwSchemaName.ToLower()); DsDwColumnMap_M aDsDwPrimaryKeyColumnMap = null; if (aDwFkReferencedTableMap != null) { aDsDwPrimaryKeyColumnMap = aDwFkReferencedTableMap.DsDwColumnMapList.FirstOrDefault( cm => cm.Transformation == DsDwColumnTransformation.SurrogateKey); } if (tbea != null && aDsDwPrimaryKeyColumnMap != null) { //Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory as the parent table and the foreign key name in the constructor. ForeignKey fk; fk = new ForeignKey(tbea, string.Format("FK_{0}_{1}", aDsDwColumnMap.Parent.DwTableName, aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName)); //Add BusinessEntityID as the foreign key column. ForeignKeyColumn fkc; fkc = new ForeignKeyColumn(fk, aDsDwColumnMap.DwColumn.Name, aDsDwPrimaryKeyColumnMap.DwColumn.Name); fk.Columns.Add(fkc); //Set the referenced table and schema. fk.ReferencedTable = aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName; fk.ReferencedTableSchema = aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwSchemaName; //Create the foreign key on the instance of SQL Server. fk.Create(); } } }
private Field CreateField(PropertyInfo prop, FieldAttribute fieldAttribute) { Field field; if (fieldAttribute.IsPrimaryKey) { PrimaryKey = PrimaryKey.Create(this, prop, fieldAttribute as PrimaryKeyAttribute); field = PrimaryKey; } else if (fieldAttribute.IsForeignKey) { var foreignKey = ForeignKey.Create(_entities, this, prop, fieldAttribute as ForeignKeyAttribute); ForeignKeys.Add(foreignKey); field = foreignKey; } else { field = Field.Create(this, prop, fieldAttribute); } Fields.Add(field); return(field); }
private static void createRelation(DataRelation relation) { Table primaryTable = _db.Tables[relation.ParentTable.TableName]; Table childTable = _db.Tables[relation.ChildTable.TableName]; ForeignKey fkey = new ForeignKey(childTable, relation.RelationName); fkey.ReferencedTable = primaryTable.Name; fkey.DeleteAction = sQLActionTypeToSMO(relation.ChildKeyConstraint.DeleteRule); fkey.UpdateAction = sQLActionTypeToSMO(relation.ChildKeyConstraint.UpdateRule); for (int i = 0; i < relation.ChildColumns.Length; i++) { DataColumn col = relation.ChildColumns[i]; ForeignKeyColumn fkc = new ForeignKeyColumn(fkey, col.ColumnName, relation.ParentColumns[i].ColumnName); fkey.Columns.Add(fkc); } fkey.Create(); }
private static void createForeignKeys(Table sourcetable, Table copiedtable) { foreach (ForeignKey sourcefk in sourcetable.ForeignKeys) { var name = copiedtable.Name + "_" + sourcefk.Name; var foreignkey = new ForeignKey(copiedtable, name); foreignkey.DeleteAction = sourcefk.DeleteAction; foreignkey.IsChecked = sourcefk.IsChecked; foreignkey.IsEnabled = sourcefk.IsEnabled; foreignkey.ReferencedTable = sourcefk.ReferencedTable; foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema; foreignkey.UpdateAction = sourcefk.UpdateAction; foreach (ForeignKeyColumn scol in sourcefk.Columns) { var refcol = scol.ReferencedColumn; var column = new ForeignKeyColumn(foreignkey, scol.Name, refcol); foreignkey.Columns.Add(column); } foreignkey.Create(); } }
public void CreateStgFkeys() { // Apply any Foreign Key constraints on the new table that are present on the Partition Table foreach (ForeignKey fKey in partitionTable.ForeignKeys) { ForeignKey newFKey = new ForeignKey(stgTable, stgTable.Name + "_" + fKey.Name); newFKey.DeleteAction = fKey.DeleteAction; newFKey.IsChecked = fKey.IsChecked; newFKey.IsEnabled = fKey.IsEnabled; newFKey.ReferencedTable = fKey.ReferencedTable; newFKey.ReferencedTableSchema = fKey.ReferencedTableSchema; newFKey.UpdateAction = fKey.UpdateAction; foreach (ForeignKeyColumn col in fKey.Columns) { ForeignKeyColumn newCol = new ForeignKeyColumn(newFKey, col.Name, col.ReferencedColumn); newFKey.Columns.Add(newCol); } scriptChunks.Add(newFKey.Script()); if (executeCommands) { newFKey.Create(); } } }
private static void ApplyIndexesForeignKeysChecks(Database destinationDatabase, NamedSmoObject namedSmoObject, string schema) { Table destinationTable = destinationDatabase.Tables[namedSmoObject.Name, schema]; #region Indexes foreach (Index sourceIndex in (namedSmoObject as Table).Indexes) { string name = sourceIndex.Name; Index index = new Index(destinationTable, name); index.IndexKeyType = sourceIndex.IndexKeyType; index.IsClustered = sourceIndex.IsClustered; index.IsUnique = sourceIndex.IsUnique; index.CompactLargeObjects = sourceIndex.CompactLargeObjects; index.IgnoreDuplicateKeys = sourceIndex.IgnoreDuplicateKeys; index.IsFullTextKey = sourceIndex.IsFullTextKey; index.PadIndex = sourceIndex.PadIndex; index.FileGroup = sourceIndex.FileGroup; foreach (IndexedColumn sourceIndexedColumn in sourceIndex.IndexedColumns) { IndexedColumn column = new IndexedColumn(index, sourceIndexedColumn.Name, sourceIndexedColumn.Descending); column.IsIncluded = sourceIndexedColumn.IsIncluded; index.IndexedColumns.Add(column); } index.FileGroup = destinationTable.FileGroup ?? index.FileGroup; index.Create(); } #endregion #region ForeignKeys foreach (ForeignKey sourceFK in (namedSmoObject as Table).ForeignKeys) { string name = sourceFK.Name; ForeignKey foreignkey = new ForeignKey(destinationTable, name); foreignkey.DeleteAction = sourceFK.DeleteAction; foreignkey.IsChecked = sourceFK.IsChecked; foreignkey.IsEnabled = sourceFK.IsEnabled; foreignkey.ReferencedTable = sourceFK.ReferencedTable; foreignkey.ReferencedTableSchema = sourceFK.ReferencedTableSchema; foreignkey.UpdateAction = sourceFK.UpdateAction; foreach (ForeignKeyColumn sourceFKColumn in sourceFK.Columns) { string referencedColumn = sourceFKColumn.ReferencedColumn; ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, sourceFKColumn.Name, referencedColumn); foreignkey.Columns.Add(column); } foreignkey.Create(); } #endregion #region Checks foreach (Check chkConstr in (namedSmoObject as Table).Checks) { Check check = new Check(destinationTable, chkConstr.Name); check.IsChecked = chkConstr.IsChecked; check.IsEnabled = chkConstr.IsEnabled; check.Text = chkConstr.Text; check.Create(); } #endregion }
private static Table CreateTable(Database database, TableConfiguration configuration) { var table = new Table(database, configuration.ValueTableName, configuration.Schema); table.Columns.Add(new Column(table, PartIdColumn, DataType.BigInt)); table.Columns.Add(new Column(table, LocaleIdColumn, DataType.Int)); table.Columns.Add(new Column(table, KeyColumn, DataType.VarChar(500))); table.Columns.Add(new Column(table, ContentColumn, DataType.VarCharMax)); var primaryKey = new Index(table, "PK_" + configuration.ValueTableName) { IndexKeyType = IndexKeyType.DriPrimaryKey }; primaryKey.IndexedColumns.Add(new IndexedColumn(primaryKey, PartIdColumn)); primaryKey.IndexedColumns.Add(new IndexedColumn(primaryKey, LocaleIdColumn)); primaryKey.IndexedColumns.Add(new IndexedColumn(primaryKey, KeyColumn)); table.Indexes.Add(primaryKey); database.Tables.Add(table); table.Create(); var partLink = new ForeignKey(table, "FK_" + configuration.ValueTableName + "_" + configuration.PartTableName) { ReferencedTable = configuration.PartTableName, ReferencedTableSchema = configuration.Schema }; partLink.Columns.Add(new ForeignKeyColumn(partLink, PartIdColumn, PartTable.IdColumn)); table.ForeignKeys.Add(partLink); partLink.Create(); var localeLink = new ForeignKey(table, "FK_" + configuration.ValueTableName + "_" + configuration.LocaleTableName) { ReferencedTable = configuration.LocaleTableName, ReferencedTableSchema = configuration.Schema }; localeLink.Columns.Add(new ForeignKeyColumn(localeLink, LocaleIdColumn, LocaleTable.IdColumn)); table.ForeignKeys.Add(localeLink); localeLink.Create(); return table; }
public void CreateStgFkeys() { // Apply any Foreign Key constraints on the new table that are present on the Partition Table foreach (ForeignKey fKey in partitionTable.ForeignKeys) { ForeignKey newFKey = new ForeignKey(stgTable, stgTable.Name + "_" + fKey.Name); newFKey.DeleteAction = fKey.DeleteAction; newFKey.IsChecked = fKey.IsChecked; newFKey.IsEnabled = fKey.IsEnabled; newFKey.ReferencedTable = fKey.ReferencedTable; newFKey.ReferencedTableSchema = fKey.ReferencedTableSchema; newFKey.UpdateAction = fKey.UpdateAction; foreach (ForeignKeyColumn col in fKey.Columns) { ForeignKeyColumn newCol = new ForeignKeyColumn(newFKey, col.Name, col.ReferencedColumn); newFKey.Columns.Add(newCol); } scriptChunks.Add(newFKey.Script()); if (executeCommands) newFKey.Create(); } }
private static Table CreateTable(Database database, TableConfiguration configuration) { var table = new Table(database, configuration.LogTableName, configuration.Schema); table.Columns.Add(new Column(table, PartIdColumn, DataType.BigInt)); table.Columns.Add(new Column(table, LocaleIdColumn, DataType.Int)); table.Columns.Add(new Column(table, KeyColumn, DataType.VarChar(500))); table.Columns.Add(new Column(table, DateColumn, DataType.DateTime)); var index = new Index(table, "IX_" + configuration.LogTableName) { IndexKeyType = IndexKeyType.None }; index.IndexedColumns.Add(new IndexedColumn(index, PartIdColumn)); index.IndexedColumns.Add(new IndexedColumn(index, LocaleIdColumn)); index.IndexedColumns.Add(new IndexedColumn(index, KeyColumn)); table.Indexes.Add(index); database.Tables.Add(table); table.Create(); var partLink = new ForeignKey(table, "FK_" + configuration.LogTableName + "_" + configuration.PartTableName) { ReferencedTable = configuration.PartTableName, ReferencedTableSchema = configuration.Schema }; partLink.Columns.Add(new ForeignKeyColumn(partLink, PartIdColumn, PartTable.IdColumn)); table.ForeignKeys.Add(partLink); partLink.Create(); var localeLink = new ForeignKey(table, "FK_" + configuration.LogTableName + "_" + configuration.LocaleTableName) { ReferencedTable = configuration.LocaleTableName, ReferencedTableSchema = configuration.Schema }; localeLink.Columns.Add(new ForeignKeyColumn(localeLink, LocaleIdColumn, LocaleTable.IdColumn)); table.ForeignKeys.Add(localeLink); localeLink.Create(); return table; }
public static bool SwitchRelationsToMo( this Table self, Database inMemDatabase, ref string error, ILog logger ) { Table inMemTable = inMemDatabase.Tables[self.Name, self.Schema]; if (inMemTable == null) { error = $"\tIn-memory table {self.Schema}.{self.Name} does not exists"; return(false); } foreach (ForeignKey fk in self.ForeignKeys) { if (inMemTable.ForeignKeys.Contains(fk.Name)) { logger.Log("\tAlready exists", fk.Name); } else { var newFk = new ForeignKey(inMemDatabase.Tables[self.Name, self.Schema], fk.Name); newFk.CopyPropertiesFrom(fk); newFk.IsMemoryOptimized = V; foreach (ForeignKeyColumn fkc in fk.Columns) { var newfkc = new ForeignKeyColumn(newFk, fkc.Name, fkc.ReferencedColumn); logger.Log("Relation", fk.Name); newFk.Columns.Add(newfkc); } if (newFk.DeleteAction == ForeignKeyAction.Cascade) { newFk.DeleteAction = ForeignKeyAction.NoAction; logger.LogWarErr($"Warning {newFk.Name}", $" Delete action CASCADE is not supported {self.FName()}"); } if (newFk.DeleteAction == ForeignKeyAction.SetNull) { newFk.DeleteAction = ForeignKeyAction.NoAction; logger.LogWarErr($"Warning {newFk.Name}", $" Delete action SET NULL is not supported {self.FName()}"); } if (newFk.UpdateAction == ForeignKeyAction.Cascade) { newFk.UpdateAction = ForeignKeyAction.NoAction; logger.LogWarErr($"Warning {newFk.Name}", $" Update action CASCADE is not supported {self.FName()}"); } try { newFk.Create(); } catch (Exception ex) { error = string.Join($"{Environment.NewLine}\t", ex.CollectThemAll(ex1 => ex1.InnerException) .Select(ex1 => ex1.Message)); logger.LogWarErr("Ralation Error", $"{newFk.Name} {error}"); } } } return(error == ""); }
public string CreateTableBySMO(Models.Database database, string destination_tablename) { try { string conString = string.Empty; string tablename = database.table.Substring(database.table.IndexOf('.') + 1); if (database.authentication == "WA") { conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=True"; } else if (database.authentication == "SQL") { conString = "Data Source=" + database.servername + ";Initial Catalog =" + database.database + ";Integrated Security=False; User ID = " + database.username + "; Password = "******""; } SqlConnection connection = new SqlConnection(conString); var conn = new ServerConnection(connection); //Connect to the local, default instance of SQL Server. Server srv; srv = new Server(conn); //Reference the AdventureWorks2012 database. Database testDB = srv.Databases[database.database]; Table myTable = testDB.Tables[tablename]; myTable.Refresh(); Table newTable = new Table(testDB, destination_tablename); foreach (Column col in myTable.Columns) { Column newColumn = new Column(newTable, col.Name); newColumn.DataType = col.DataType; newColumn.Default = col.Default; newColumn.Identity = col.Identity; newColumn.IdentityIncrement = col.IdentityIncrement; newColumn.IdentitySeed = col.IdentitySeed; newColumn.Nullable = col.Nullable; newTable.Columns.Add(newColumn); } newTable.Create(); #region Creating Foreign Keys if ((myTable as Table).ForeignKeys.Count != 0) { foreach (ForeignKey sourcefk in (myTable as Table).ForeignKeys) { try { string name = Guid.NewGuid().ToString(); ForeignKey foreignkey = new ForeignKey(newTable, name); foreignkey.DeleteAction = sourcefk.DeleteAction; foreignkey.IsChecked = sourcefk.IsChecked; foreignkey.IsEnabled = sourcefk.IsEnabled; foreignkey.ReferencedTable = sourcefk.ReferencedTable; foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema; foreignkey.UpdateAction = sourcefk.UpdateAction; foreach (ForeignKeyColumn scol in sourcefk.Columns) { string refcol = scol.ReferencedColumn; ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, scol.Name, refcol); foreignkey.Columns.Add(column); } foreignkey.Create(); } catch (Exception ex) { throw; } } } #endregion #region Creating Indexes if ((myTable as Table).Indexes.Count != 0) { foreach (Index srcind in (myTable as Table).Indexes) { try { string name = Guid.NewGuid().ToString(); Index index = new Index(newTable, name); index.IndexKeyType = srcind.IndexKeyType; index.IsClustered = srcind.IsClustered; index.IsUnique = srcind.IsUnique; index.CompactLargeObjects = srcind.CompactLargeObjects; index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys; index.IsFullTextKey = srcind.IsFullTextKey; index.PadIndex = srcind.PadIndex; index.FileGroup = srcind.FileGroup; foreach (IndexedColumn srccol in srcind.IndexedColumns) { IndexedColumn column = new IndexedColumn(index, srccol.Name, srccol.Descending); column.IsIncluded = srccol.IsIncluded; index.IndexedColumns.Add(column); } index.FileGroup = newTable.FileGroup ?? index.FileGroup; index.Create(); } catch (Exception exc) { throw; } } } #endregion return(newTable.Schema + "." + newTable.Name); } catch (Exception) { return(string.Empty); } }
private static ForeignKeyDbo CreateForeignKey(Table aTable, ForeignKeyDbo dbo) { ForeignKey fKey = new ForeignKey(aTable, dbo.Name) { DeleteAction = dbo.DeleteAction, UpdateAction = dbo.UpdateAction, ReferencedTable = dbo.ReferencedTable, IsChecked = dbo.IsChecked }; foreach (ForeignKeyColumnDbo clmn in dbo.Columns) { ForeignKeyColumn fkColumn = new ForeignKeyColumn(fKey, clmn.Name, clmn.ReferencedColumn); fKey.Columns.Add(fkColumn); } fKey.Create(); return CreateForeignKeyDbo(fKey); }