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