Smo.Index CreateUniqueIndex(Smo.Table t, UniqueConstraint uc, int indexNum) { if (uc.IsPrimaryKey) { return(null); } Smo.Index idx = new Smo.Index(t, t.Name + "Index" + indexNum); idx.IndexKeyType = Smo.IndexKeyType.DriUniqueKey; SetUniqueIndexColumns(idx, uc.Columns); idx.Create(); return(idx); }
Smo.Index CreatePrimaryKey(Smo.Table t, DataTable dt) { DataColumn[] pk = dt.PrimaryKey; if (pk == null || pk.Length == 0) { return(null); } Smo.Index idx = new Smo.Index(t, t.Name + "Key"); idx.IndexKeyType = Smo.IndexKeyType.DriPrimaryKey; SetUniqueIndexColumns(idx, pk); idx.Create(); return(idx); }
private static void ProcessLevel(Level lvl, Database db, Server srv) { foreach (Level level in lvl.Levels) { Table table = db.Tables[level.Name]; if (table != null) { Index oldpk = table.Indexes.Cast <Index>().FirstOrDefault(index => index.IndexKeyType == IndexKeyType.DriPrimaryKey); if (oldpk != null && oldpk.IndexedColumns.Count < 2) { DependencyWalker dw = new DependencyWalker(srv); DependencyTree dependencies = dw.DiscoverDependencies(new SqlSmoObject[] { table }, DependencyType.Children); DependencyTreeNode current = dependencies.FirstChild.FirstChild; List <ForeignKey> oldfks = new List <ForeignKey>(); List <ForeignKey> newfks = new List <ForeignKey>(); _spsw.WriteLine(); _spsw.Write(table.Name + ":"); bool checkself = false; while (current != null) { if (srv.GetSmoObject(current.Urn) is StoredProcedure sp) { _spsw.Write(sp.Name + "; "); } if (srv.GetSmoObject(current.Urn) is Table dependency) { foreach (ForeignKey oldfk in dependency.ForeignKeys) { if (oldfk.ReferencedTable == table.Name) { _dropfksw.WriteLine(string.Format(DROP_CONSTRAINT, dependency.Name, oldfk.Name)); ForeignKey newfk = new ForeignKey(dependency, oldfk.Name); string columns1 = string.Empty; string columns2 = string.Empty; bool _first = true; foreach (ForeignKeyColumn fkc in oldfk.Columns) { columns1 += (_first ? "" : ", ") + fkc.Name; columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn; _first = false; newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn)); } if (level.Parent != null) { foreach (ForeignKey fk in dependency.ForeignKeys) { if (fk.ReferencedTable == level.Parent.Name) { foreach (ForeignKeyColumn fkc in fk.Columns) { columns1 += (_first ? "" : ", ") + fkc.Name; columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn; _first = false; newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn)); } } } if (level.Parent.Parent != null) { foreach (ForeignKey fk in dependency.ForeignKeys) { if (fk.ReferencedTable == level.Parent.Parent.Name) { foreach (ForeignKeyColumn fkc in fk.Columns) { columns1 += (_first ? "" : ", ") + fkc.Name; columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn; _first = false; newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn)); } } } } } newfk.ReferencedTable = oldfk.ReferencedTable; newfk.ReferencedTableSchema = oldfk.ReferencedTableSchema; _addfksw.WriteLine(string.Format(ADD_FK_CONSTRAINT, dependency.Name, oldfk.Name, columns1, oldfk.ReferencedTable, columns2)); oldfks.Add(oldfk); newfks.Add(newfk); } } } current = current.NextSibling; if (current == null && !checkself) { current = dependencies.FirstChild; checkself = true; } } _droppksw.WriteLine(string.Format(DROP_CONSTRAINT, table.Name, oldpk.Name)); Index newpk = new Index(table, oldpk.Name); string columns = string.Empty; bool first = true; foreach (IndexedColumn ic in oldpk.IndexedColumns) { columns += (first ? "" : ", ") + ic.Name; first = false; newpk.IndexedColumns.Add(new IndexedColumn(newpk, ic.Name)); } if (level.Parent != null) { foreach (ForeignKey fk in table.ForeignKeys) { if (fk.ReferencedTable == level.Parent.Name) { foreach (ForeignKeyColumn fkc in fk.Columns) { columns += (first ? "" : ", ") + fkc.Name; first = false; newpk.IndexedColumns.Add(new IndexedColumn(newpk, fkc.Name)); } } } if (level.Parent.Parent != null) { foreach (ForeignKey fk in table.ForeignKeys) { if (fk.ReferencedTable == level.Parent.Parent.Name) { foreach (ForeignKeyColumn fkc in fk.Columns) { columns += (first ? "" : ", ") + fkc.Name; first = false; newpk.IndexedColumns.Add(new IndexedColumn(newpk, fkc.Name)); } } } } } newpk.IsClustered = true; newpk.IsUnique = true; newpk.IndexKeyType = IndexKeyType.DriPrimaryKey; _addpksw.WriteLine(string.Format(ADD_PK_CONSTRAINT, table.Name, oldpk.Name, columns)); ProcessLevel(level, db, srv); Console.Write(table.Name + "\r\n"); try { foreach (ForeignKey oldfk in oldfks) { oldfk.Drop(); table.Alter(); } oldpk.Drop(); table.Alter(); newpk.Create(); foreach (ForeignKey newfk in newfks) { newfk.Create(); } } catch (Exception ex) { Console.Write(ex.Message + "\r\n"); } } } } }
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); } }