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