public static string ScriptForDeleteForeignKey(ForeignKeyColumn foreignkeyColumn)
{
var script = Script.New();
script.AppendLine("Delete{0}", foreignkeyColumn.ForeignKeyActions());
script.AppendSemicolon();
return script.ToString();
}
protected string GetDisplayString()
{
object value = FieldValue;
if (value == null)
{
return(FormatFieldValue(ForeignKeyColumn.GetForeignKeyString(Row)));
}
else
{
return(FormatFieldValue(ForeignKeyColumn.ParentTable.GetDisplayString(value)));
}
}
static RelationParsingTest()
{
NumbersSchema = new TableSchema("Numbers");
PowersSchema = new TableSchema("Powers");
NumbersSchema.Columns.AddValueColumn("Number", typeof(int), 0);
NumbersSchema.Columns.AddValueColumn("IsEven", typeof(bool), false);
PowersKeyColumn = PowersSchema.Columns.AddForeignKey("Number", NumbersSchema, "Powers");
PowersSchema.Columns.AddValueColumn("Exponent", typeof(int), 0);
PowersSchema.Columns.AddValueColumn("Value", typeof(int), 0);
}
protected override void OnDataBinding(EventArgs e)
{
base.OnDataBinding(e);
if (Mode == DataBoundControlMode.Edit)
{
string foreignkey = ForeignKeyColumn.GetForeignKeyString(Row);
ListItem item = DropDownList1.Items.FindByValue(foreignkey);
if (item != null)
{
DropDownList1.SelectedValue = foreignkey;
}
}
}
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>
/// Gets a string representation of the column's value so that it can be matched with
/// values populated in a dropdown. This currently works for FK and Enum columns only.
/// The method returns null for other column types.
/// </summary>
/// <returns></returns>
protected string GetSelectedValueString()
{
Type enumType;
if (Column is MetaForeignKeyColumn)
{
return(ForeignKeyColumn.GetForeignKeyString(Row));
}
else if (Column.IsEnumType(out enumType))
{
return(Misc.GetUnderlyingTypeValueString(enumType, FieldValue));
}
return(null);
}
public static void ColumnWithSameNameAsFKShouldBeEqual()
{
ForeignKeyColumn fk = new ForeignKeyColumn();
fk.TableName = "test";
fk.Name = "columnName";
Column col = new Column();
col.TableName = fk.TableName;
col.Name = fk.Name;
Expect.IsTrue(fk.Equals(col));
Expect.IsFalse(fk == col);
}
/// <summary>
/// Create FK for split table
/// </summary>
/// <param name="productionTable">Split production table</param>
/// <param name="surveyId">Survey ID</param>
/// <param name="databaseEngine">Pointer of database class</param>
/// <returns></returns>
public override MySmoObjectBase CreateSplitItem(
Table productionTable,
int surveyId,
Database databaseEngine)
{
var newForeignKey = new ForeignKey();
var modelForeignKey = (ForeignKey)SourceSmoObject;
newForeignKey.Parent = productionTable;
if (surveyId == 0)
{
newForeignKey.Name = modelForeignKey.Name;
}
else
{
newForeignKey.Name = Database.GetTemplateNameByOriginalName(
modelForeignKey.Name,
surveyId);
}
newForeignKey.IsEnabled = modelForeignKey.IsEnabled;
newForeignKey.IsChecked = modelForeignKey.IsChecked;
newForeignKey.DeleteAction = modelForeignKey.DeleteAction;
newForeignKey.UpdateAction = modelForeignKey.UpdateAction;
newForeignKey.ReferencedTable = modelForeignKey.ReferencedTable;
newForeignKey.ReferencedTableSchema = modelForeignKey.ReferencedTableSchema;
foreach (ForeignKeyColumn column in modelForeignKey.Columns)
{
var newColumn = new ForeignKeyColumn(
newForeignKey,
column.Name,
column.ReferencedColumn);
newForeignKey.Columns.Add(newColumn);
}
return(new MySmoObjectBase(
newForeignKey,
newForeignKey.Name,
productionTable.Name));
}
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 override ForeignKeyColumn[] GetForeignKeyColumns()
{
DataTable foreignKeyData = GetForeignKeyData(Database);
List <ForeignKeyColumn> results = new List <ForeignKeyColumn>();
foreach (DataRow row in foreignKeyData.Rows)
{
ForeignKeyColumn fk = new ForeignKeyColumn();
fk.TableName = row["ForeignKeyTable"].ToString();
fk.ReferenceName = row["ForeignKeyName"].ToString();
fk.Name = row["ForeignKeyColumn"].ToString();
fk.ReferencedKey = row["PrimaryKeyColumn"].ToString();
fk.ReferencedTable = row["PrimaryKeyTable"].ToString();
results.Add(fk);
}
return(results.ToArray());
}
public static void AddForeignKeyShouldIncrementReferencingFKsForTargetTable()
{
TestSchemaManager tsm = new TestSchemaManager();
string ingTable = "referencing";
string edTable = "referred";
Table referencing = new Table {
Name = ingTable
};
Table referred = new Table {
Name = edTable
};
int initial = referred.ReferencingForeignKeys.Length;
ForeignKeyColumn fk = new ForeignKeyColumn("referredId", ingTable, edTable);
tsm.TestAddForeignKey(referencing, referred, fk);
Expect.AreEqual(initial + 1, referred.ReferencingForeignKeys.Length);
Expect.AreEqual(0, referencing.ReferencingForeignKeys.Length);
}
static void AssertSchemasEqual(TableSchema expected, TableSchema actual)
{
Assert.AreEqual(expected.Name, actual.Name);
Assert.AreEqual(expected.Columns.Count, actual.Columns.Count);
Assert.AreEqual(expected.ChildRelations.Count, actual.ChildRelations.Count);
if (expected.PrimaryKey == null)
{
Assert.IsNull(actual.PrimaryKey);
}
else
{
Assert.AreEqual(expected.PrimaryKey.Name, actual.PrimaryKey.Name);
}
for (int i = 0; i < expected.Columns.Count; i++)
{
Column e = expected.Columns[i], a = actual.Columns[i];
Assert.AreEqual(e.GetType(), a.GetType());
Assert.AreEqual(e.DataType, a.DataType);
Assert.AreEqual(e.DefaultValue, a.DefaultValue);
Assert.AreEqual(e.Name, a.Name);
if (e is ValueColumn)
{
ValueColumn te = (ValueColumn)e, ta = (ValueColumn)a;
Assert.AreEqual(te.Unique, ta.Unique);
Assert.AreEqual(te.AllowNulls, ta.AllowNulls);
}
if (e is ForeignKeyColumn) //Already checked ValueColumn properties
{
ForeignKeyColumn te = (ForeignKeyColumn)e, ta = (ForeignKeyColumn)a;
Assert.AreEqual(te.ChildRelation.Name, ta.ChildRelation.Name);
AssertSchemasEqual(te.ForeignSchema, ta.ForeignSchema);
}
//if (e is CalculatedColumn) {
// CalculatedColumn te = (CalculatedColumn)e, ta = (CalculatedColumn)a;
//}
}
Assert.AreEqual(expected.ToXml().ToString(), actual.ToXml().ToString());
}
public void TypeInheritanceSchemaGeneratorShouldAddTablesForInheritedTypes()
{
SchemaManager mgr = new SchemaManager {
AutoSave = false
};
TestTypeInheritanceSchemaGenerator gen = new TestTypeInheritanceSchemaGenerator();
gen.TestAddSchemaTables(mgr);
Expect.AreEqual(2, mgr.CurrentSchema.Tables.Length);
Expect.IsNotNull(mgr.GetTable("Employee"), "Employee table wasn't added");
Expect.IsNotNull(mgr.GetTable("Person"), "Person table wasn't added");
Expect.AreEqual(3, mgr.GetTable("Person").Columns.Length); // plus cuid
Expect.AreEqual(2, mgr.GetTable("Employee").Columns.Length);
Expect.AreEqual(1, mgr.CurrentSchema.ForeignKeys.Length);
ForeignKeyColumn fk = mgr.CurrentSchema.ForeignKeys[0];
Expect.AreEqual("Employee", fk.ReferencingClass);
Expect.AreEqual("Id", fk.ReferencedKey);
}
private static void CreatePurchaseOrderTable(Database database)
{
Table purchaseOrdersTable = new Table(database, "PurchaseOrders");
purchaseOrdersTable.Schema = "dbo";
Column idColumn = new Column(purchaseOrdersTable, "Id", DataType.Int);
idColumn.Identity = true;
idColumn.IdentityIncrement = 1;
idColumn.IdentitySeed = 1;
Column customerIdColumn = new Column(purchaseOrdersTable, "CustomerId", DataType.Int);
Column orderDateTimeColumn = new Column(purchaseOrdersTable, "OrderDateTime", DataType.DateTime);
Column orderTotalColumn = new Column(purchaseOrdersTable, "OrderTotal", DataType.Money);
purchaseOrdersTable.Columns.Add(idColumn);
purchaseOrdersTable.Columns.Add(customerIdColumn);
purchaseOrdersTable.Columns.Add(orderDateTimeColumn);
purchaseOrdersTable.Columns.Add(orderTotalColumn);
ForeignKey foreignKey = new ForeignKey(purchaseOrdersTable, "FK_PurchaseOrders_CustomerId_Customers_Id");
ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(foreignKey, "CustomerId", "Id");
foreignKey.ReferencedTable = "Customers";
foreignKey.ReferencedTableSchema = "dbo";
foreignKey.Columns.Add(foreignKeyColumn);
purchaseOrdersTable.ForeignKeys.Add(foreignKey);
Index index = new Index(purchaseOrdersTable, "PK_PurchaseOrders");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
IndexedColumn idIndexedColumn = new IndexedColumn(index, "Id");
index.IndexedColumns.Add(idIndexedColumn);
purchaseOrdersTable.Indexes.Add(index);
purchaseOrdersTable.Create();
}
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 static void CreateForeignKeys(Dictionary <StarModelTableBase, Table> tableObjects, Dictionary <StarColumn, Column> columnObjects, List <StarModelTableBase> tables)
{
foreach (var table in tables)
{
foreach (var relation in table.Relations.Where(r => Equals(r.LinkTable, table)))
{
var anchorTable = tableObjects[relation.AnchorTable.ActualTable];
var linkTable = tableObjects[relation.LinkTable.ActualTable];
ForeignKey foreignKey;
// Create constraint
if (relation.AnchorTable is DateDimension)
{
foreignKey = new ForeignKey(linkTable, $"FK_{linkTable.Name}_{relation.LinkColumns[0].Name}_{anchorTable.Name}");
}
else if (relation.AnchorTable is TimeDimension)
{
foreignKey = new ForeignKey(linkTable, $"FK_{linkTable.Name}_{relation.LinkColumns[0].Name}_{anchorTable.Name}");
}
else
{
foreignKey = new ForeignKey(linkTable, $"FK_{linkTable.Name}_{anchorTable.Name}");
}
foreignKey.ReferencedTable = anchorTable.Name;
// Add columns to constraint
for (var i = 0; i < relation.LinkColumns.Count; i++)
{
var foreignColumnObj = columnObjects[relation.LinkColumns[i]];
var referencedColumnObj = columnObjects[relation.AnchorColumns[i]];
var foreignKeyColumn = new ForeignKeyColumn(foreignKey, foreignColumnObj.Name, referencedColumnObj.Name);
foreignKey.Columns.Add(foreignKeyColumn);
}
linkTable.ForeignKeys.Add(foreignKey);
}
}
}
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();
}
}
}
public override ForeignKeyColumn[] GetForeignKeyColumns()
{
string sql = @"SELECT sql
FROM(
SELECT sql sql, type type, tbl_name tbl_name, name name from SQLITE_MASTER union all
SELECT sql, type, tbl_name, name from SQLITE_TEMP_MASTER
)
WHERE type != 'meta'
AND sql NOTNULL
AND name NOT LIKE 'sqlite_%'
ORDER BY SUBSTR(type, 2, 1), name";
List <ForeignKeyColumn> results = new List <ForeignKeyColumn>();
Database.QuerySingleColumn <string>(sql).Each(createStatement =>
{
string foreignKey = "FOREIGN KEY";
string columnDefinitions;
string createTableStatement = createStatement.ReadUntil('(', out columnDefinitions);
string tableName = createTableStatement.DelimitSplit("[", "]")[1];
columnDefinitions.DelimitSplit(",").Each(columnDefinition =>
{
if (columnDefinition.StartsWith(foreignKey))
{
string[] segments = columnDefinition.TruncateFront(foreignKey.Length).DelimitSplit("(", ")", " ", "[", "]", "REFERENCES");
string columnName = segments[0];
string referencedTable = segments[1];
string referencedColumn = segments[2];
ForeignKeyColumn fk = new ForeignKeyColumn();
fk.TableName = tableName;
fk.ReferenceName = $"FK_{tableName}_{referencedTable}".RandomLetters(4);
fk.Name = columnName;
fk.ReferencedKey = referencedColumn;
fk.ReferencedTable = referencedTable;
results.Add(fk);
}
});
});
return(results.ToArray());
}
protected string GetDisplayString()
{
object value = FieldValue;
if (value == null)
{
string fkey = FormatFieldValue(ForeignKeyColumn.GetForeignKeyString(Row));
if (fkey.Length == 36)
{
using (WeavverEntityContainer data = new WeavverEntityContainer())
{
fkey = data.GetName(new Guid(fkey));
requiredCustomLookUp = true;
}
}
return(fkey);
}
else
{
return(FormatFieldValue(ForeignKeyColumn.ParentTable.GetDisplayString(value)));
}
}
/// <summary>
/// Sets the field's properties based on GUI values
/// </summary>
protected override void SetFieldProperties()
{
field.PromptText = txtPrompt.Text;
field.Name = txtFieldName.Text;
if (field.Columns == null)
{
field.Columns = new List <GridColumnBase>();
//UniqueKeyColumn uniqueKeyColumn = new UniqueKeyColumn(field);
UniqueRowIdColumn uniqueRowIdColumn = new UniqueRowIdColumn(field);
RecStatusColumn recStatusColumn = new RecStatusColumn(field);
ForeignKeyColumn foreignKeyColumn = new ForeignKeyColumn(field);
GlobalRecordIdColumn globalRecordIdColumn = new GlobalRecordIdColumn(field);
field.Columns.AddRange(new List <GridColumnBase> {
//uniqueKeyColumn,
uniqueRowIdColumn,
recStatusColumn,
foreignKeyColumn,
globalRecordIdColumn
});
}
}
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 string ScriptInclusaoForeignKey(ForeignKey ForeignKey, string NomeTabelaOrigem)
{
string SeparadorColuna = " ";
string ColunaOrigem = string.Empty, ColunaReferencia = string.Empty;
string script = "ALTER TABLE [dbo].[" + NomeTabelaOrigem + "] ADD " + Environment.NewLine;
script += "CONSTRAINT [" + ForeignKey.ForeignKeyName + "] FOREIGN KEY " + Environment.NewLine;
script += "( " + Environment.NewLine;
foreach (object col in ForeignKey.Columns)
{
ForeignKeyColumn coluna = (ForeignKeyColumn)col;
ColunaOrigem += SeparadorColuna + "[" + coluna.Column + "]";
ColunaReferencia += SeparadorColuna + "[" + coluna.RefColumn + "]";
SeparadorColuna = ", ";
}
script += ColunaOrigem + Environment.NewLine;
script += " ) REFERENCES [dbo].[" + ForeignKey.RefTableName + "] ( " + Environment.NewLine;
script += ColunaReferencia + Environment.NewLine;
script += " ) ";
if (ForeignKey.DeleteCascade)
{
script += " ON DELETE CASCADE ";
}
if (ForeignKey.UpdateCascade)
{
script += " ON UPDATE CASCADE ";
}
script += Environment.NewLine;
script += "GO " + Environment.NewLine + Environment.NewLine;
return(script);
}
public override ForeignKeyColumn[] GetForeignKeyColumns()
{
string sql = @"SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = @DatabaseName";
DataTable fkData = Database.GetDataTable(sql, new { DatabaseName = GetSchemaName() });
List <ForeignKeyColumn> results = new List <ForeignKeyColumn>();
foreach (DataRow row in fkData.Rows)
{
ForeignKeyColumn fk = new ForeignKeyColumn();
fk.TableName = row["TABLE_NAME"].ToString();
fk.ReferenceName = row["CONSTRAINT_NAME"].ToString();
fk.Name = row["COLUMN_NAME"].ToString();
fk.ReferencedKey = row["REFERENCED_COLUMN_NAME"].ToString();
fk.ReferencedTable = row["REFERENCED_TABLE_NAME"].ToString();
results.Add(fk);
}
return(results.ToArray());
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a SQL foreign key column.</summary>
///
/// <param name="sqlForeignKeyColumn">The input sql foreign key column.</param>
///--------------------------------------------------------------------------------
public void LoadColumn(ForeignKeyColumn sqlForeignKeyColumn)
{
try
{
// load the basic foreign key column information
SqlForeignKeyColumnName = sqlForeignKeyColumn.Name;
DbID = sqlForeignKeyColumn.ID;
ReferencedColumn = sqlForeignKeyColumn.ReferencedColumn;
Urn = sqlForeignKeyColumn.Urn;
State = sqlForeignKeyColumn.State.ToString();
// load information for each property
//foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlForeignKeyColumn.Properties)
//{
// if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString()))
// {
// SqlProperty property = new SqlProperty();
// property.SqlPropertyID = Guid.NewGuid();
// property.SqlForeignKeyColumn = this;
// property.LoadProperty(loopProperty);
// SqlPropertyList.Add(property);
// }
//}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
public void GetForeignKeyColumns(Database db, Table table, ForeignKey fk)
{
SqlCommand qry = new SqlCommand(
"select " +
" origincolumn.name, " +
" destinationcolumn.name " +
"from " +
" sysforeignkeys fks " +
" inner join sysobjects fk on " +
" fk.id = fks.constid and " +
" fk.xtype = 'F' " +
" inner join syscolumns origincolumn on " +
" origincolumn.id = fks.fkeyid and " +
" origincolumn.colid = fks.fkey " +
" inner join syscolumns destinationcolumn on " +
" destinationcolumn.id = fks.rkeyid and " +
" destinationcolumn.colid = fks.rkey " +
"where " +
" fk.uid = 1 and " +
" fk.name = @foreignkeyname " +
"order by keyno", SqlConn);
SqlDataAdapter dat = new SqlDataAdapter();
DataSet ds = new DataSet();
dat.SelectCommand = qry;
qry.Parameters.Add("@foreignkeyname", SqlDbType.VarChar).Value = fk.ForeignKeyName;
dat.Fill(ds);
foreach (DataRow colrow in ds.Tables[0].Rows)
{
ForeignKeyColumn col = new ForeignKeyColumn();
col.RefTable = fk.RefTableName;
col.Column = (string)colrow[0];
col.RefColumn = (string)colrow[1];
fk.Columns.Add(col);
}
}
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 bool ValidaAlteracaoColumnsForeignKey(ForeignKey fkAtual, ForeignKey fkNova)
{
foreach (object col in fkAtual.Columns)
{
ForeignKeyColumn fk = PegarColunasFK((ForeignKeyColumn)col, fkNova.Columns);
if (fk == null)
{
return(false);
}
}
foreach (object col in fkNova.Columns)
{
ForeignKeyColumn fk = PegarColunasFK((ForeignKeyColumn)col, fkAtual.Columns);
if (fk == null)
{
return(false);
}
}
return(true);
}
public OnCompareExpression(MappingNode mappingNode, OidColumn oidColumn, ForeignKeyColumn fkColumn)
: base(null, 0, 0)
{
Class fkParentClass = mappingNode as Class;
if (fkParentClass != null)
{
Add(new RawIdentifierExpression(QualifiedColumnName.Get(oidColumn), 0, 0));
Add(new RawIdentifierExpression(QualifiedColumnName.Get(fkParentClass, fkColumn), 0, 0), "=");
return;
}
MappingTable mappingTable = mappingNode as MappingTable;
if (mappingTable != null)
{
Add(new RawIdentifierExpression(QualifiedColumnName.Get(oidColumn), 0, 0));
Add(new RawIdentifierExpression(QualifiedColumnName.Get(mappingTable, fkColumn), 0, 0), "=");
return;
}
else
{
throw new ArgumentException("OnCompareExpression: Unexpected mapping node type", "mappingNode");
}
}
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();
}
}
public SchemaResult TestAddForeignKey(Table table, Table target, ForeignKeyColumn fk)
{
return(SetForeignKey(table, target, fk));
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
List <Column> wcs = Utils.GetWriteableColumns(t);
List <Column> mwcs = Utils.GetMustWriteColumns(t);
if (wcs == null || wcs.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有可写入字段的表生成该过程！";
return(gr);
}
if (pks == null || pks.Count == 0)
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为没有主键字段的表生成该过程！";
return(gr);
}
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 添加一行数据（空值参数将被跳过）并返回
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart] (");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
//判断必填字段是否填写了空值
foreach (Column c in mwcs)
{
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".InsertPart|Required." + c.Name + @" 必须填写 " + cc + @"', 11, 1); RETURN -1;
END;
");
}
//判断主键重复
//判断是否存在自增主键
bool hasIdentityCol = false;
foreach (Column c in pks)
{
if (c.Identity)
{
hasIdentityCol = true;
break;
}
}
if (!hasIdentityCol)
{
sb.Append(@"
IF EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
)
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".Update|Exists.PrimaryKeys 欲插入的主键值已存在', 11, 1); RETURN -1;
END;
");
}
//判断外键字段是否在外键表中存在
foreach (ForeignKey fk in t.ForeignKeys)
{
Table ft = t.Parent.Tables[fk.ReferencedTable, fk.ReferencedTableSchema];
sb.Append(@"
IF NOT EXISTS (
SELECT 1 FROM [" + Utils.GetEscapeSqlObjectName(ft.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(ft.Name) + @"]
WHERE ");
string s1 = "";
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
Column c = t.Columns[fkc.Name];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append("(" + (c.Nullable ? (" @" + cn + @" IS NULL OR ") : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = @" + cn + @")");
if (i > 0)
{
s1 += "，";
}
s1 += Utils.GetCaption(t.Columns[fkc.Name]);
}
sb.Append(@"
)
BEGIN
RAISERROR (" + t.Schema + @"." + t.Name + @".InsertPart|NotFound." + s1 + @" '" + s1 + @" 的值在表：" + Utils.GetCaption(ft) + @" 中未找到', 11, 1); RETURN -1;
END;
");
}
sb.Append(@"
DECLARE @SqlStr nvarchar(max);
DECLARE @SqlParm nvarchar(max);
DECLARE @InsParms1 nvarchar(max);
DECLARE @InsParms2 nvarchar(max);
SET @InsParms1 = '';
SET @InsParms2 = '';
");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
string cc = Utils.GetCaption(c);
if (c.Nullable || c.DefaultConstraint == null)
{
}
else
{
sb.Append(@"
IF @" + cn + @" IS NOT NULL");
}
sb.Append(@"
BEGIN
IF LEN(@InsParms1) > 0 SET @InsParms1 = @InsParms1 + ', ';
SET @InsParms1 = @InsParms1 + '[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]';
IF len(@InsParms2) > 0 SET @InsParms2 = @InsParms2 + ', ';
SET @InsParms2 = @InsParms2 + '@" + cn + @"';
END
");
}
sb.Append(@"
SET @SqlStr = 'INSERT INTO [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] ('+ @InsParms1 + ') VALUES ('+ @InsParms2 + ');';
SET @SqlParm = '");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? ", " : "") + @"@" + cn + @" " + Utils.GetParmDeclareStr(c));
}
sb.Append(@"';
/*
--prepare trans & error
DECLARE @TranStarted bit; SET @TranStarted = 0; IF @@TRANCOUNT = 0 BEGIN BEGIN TRANSACTION; SET @TranStarted = 1 END;
*/
EXEC sp_executesql @SqlStr, @SqlParm, ");
for (int i = 0; i < wcs.Count; i++)
{
Column c = wcs[i];
string cn = Utils.GetEscapeName(c);
sb.Append((i > 0 ? ", " : "") + @"@" + cn);
}
sb.Append(@";
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]");
string s = "";
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
s += " AND ";
}
if (c.Identity)
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = SCOPE_IDENTITY()";
}
else
{
s += @"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn;
}
}
if (s.Length > 0)
{
sb.Append(@"
WHERE " + s);
}
sb.Append(@";
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".InsertPart|Failed 数据更新失败', 11, 1); RETURN -1; -- GOTO Cleanup;
END
/*
--cleanup trans
IF @TranStarted = 1 COMMIT TRANSACTION; RETURN 0;
Cleanup:
IF @TranStarted = 1 ROLLBACK TRANSACTION; RETURN -1;
*/
RETURN 0;
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
添加一行数据（空值参数将被跳过）并返回' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_IsSingleLineResult', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_InsertPart'
");
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
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
}
///<summary>Creates a new ParentRowDependency.</summary>
public ParentRowDependency(RowDependencySetup setup, ForeignKeyColumn parentColumn)
: base(setup)
{
ParentColumn = parentColumn;
RequiresDataContext = true;
}
public GenResult Gen(params object[] sqlElements)
{
#region Init
GenResult gr;
Table t = (Table)sqlElements[0];
if (!Utils.CheckIsTree(t))
{
gr = new GenResult(GenResultTypes.Message);
gr.Message = "无法为非树表生成该过程！";
return(gr);
}
List <Column> pks = Utils.GetPrimaryKeyColumns(t);
StringBuilder sb = new StringBuilder();
#endregion
#region Gen
foreach (ForeignKey fk in t.ForeignKeys)
{
if (fk.ReferencedTable != t.Name || fk.ReferencedTableSchema != t.Schema)
{
continue;
}
int equaled = 0;
foreach (ForeignKeyColumn fkc in fk.Columns) //判断是否一个外键约束所有字段都是在当前表
{
if (fkc.Parent.Parent == t)
{
equaled++;
}
}
if (equaled == fk.Columns.Count) //当前表为树表
{
sb.Append(@"
-- 针对 表 " + t.ToString() + @"
-- 根据主键值返回一个节点的多行数据
CREATE PROCEDURE [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode] (");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
" + (i > 0 ? ", " : " ") + Utils.FormatString("@" + cn, Utils.GetParmDeclareStr(c), "= NULL", 40, 40));
}
sb.Append(@"
) AS
BEGIN
SET NOCOUNT ON;
");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
sb.Append(@"
IF @" + cn + @" IS NULL
BEGIN
RAISERROR ('" + t.Schema + @"." + t.Name + @".SelectNode|Required." + c.Name + @" " + cn + @" 不能为空', 11, 1); RETURN -1;
END;");
}
sb.Append(@"
WITH Node(");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@", ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@")
AS
(
SELECT ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"]
WHERE ");
for (int i = 0; i < pks.Count; i++)
{
Column c = pks[i];
string cn = Utils.GetEscapeName(c);
if (i > 0)
{
sb.Append(" AND ");
}
sb.Append(@"[" + Utils.GetEscapeSqlObjectName(c.Name) + @"] = @" + cn);
}
sb.Append(@"
UNION ALL
SELECT ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"a.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
JOIN Node ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.Name) + @"] = Node.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
)
SELECT ");
for (int i = 0; i < t.Columns.Count; i++)
{
Column c = t.Columns[i];
sb.Append((i > 0 ? @"
, " : "") + @"a.[" + Utils.GetEscapeSqlObjectName(c.Name) + @"]");
}
sb.Append(@"
FROM [" + Utils.GetEscapeSqlObjectName(t.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(t.Name) + @"] a
JOIN Node ON ");
for (int i = 0; i < fk.Columns.Count; i++)
{
ForeignKeyColumn fkc = fk.Columns[i];
if (i > 0)
{
sb.Append(@" AND ");
}
sb.Append(@"a.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"] = Node.[" + Utils.GetEscapeSqlObjectName(fkc.ReferencedColumn) + @"]");
}
sb.Append(@"
END
-- 下面这几行用于生成智能感知代码，以及强类型返回值，请注意同步修改（SP名称，备注，返回值类型）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'针对 表 " + t.ToString() + @"
根据主键值返回一个节点的多行数据' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode'
EXEC sys.sp_addextendedproperty @name=N'SPGenSettings_ResultType', @value=N'" + t.ToString() + @"' , @level0type=N'SCHEMA',@level0name=N'" + t.Schema + @"', @level1type=N'PROCEDURE',@level1name=N'usp_" + Utils.GetEscapeSqlObjectName(t.Name) + @"_SelectNode'
");
break;
}
}
#endregion
#region return
gr = new GenResult(GenResultTypes.CodeSegment);
gr.CodeSegment = new KeyValuePair <string, string>(this._properties[GenProperties.Tips].ToString(), sb.ToString());
return(gr);
#endregion
}
/// <summary>
/// Создаём таблицу из схемы модуля
/// </summary>
/// <param name="module">Метаданные модуля</param>
public void CreateTableFromModuleSchema(ModuleMetadata module)
{
if (!_database.Tables.Contains(module.ModuleName))
{
try
{
// создаём таблицу
Table targetTable = new Table(_database, module.ModuleName);
//
// добавляем базовые столбцы в таблицу
//
#region Внешниый ключ
Column plowMachineIdColumn = new Column(targetTable, "PlowMachineId");
plowMachineIdColumn.DataType = DataType.UniqueIdentifier;
plowMachineIdColumn.RowGuidCol = true;
plowMachineIdColumn.Nullable = false;
ForeignKey fk = new ForeignKey(targetTable, "FK_" + module.ModuleName + "_PlowMachine");
ForeignKeyColumn fk_column = new ForeignKeyColumn(fk, "PlowMachineId");
fk_column.ReferencedColumn = "PlowMachineId";
fk.ReferencedTable = "PlowMachines";
fk.Columns.Add(fk_column);
targetTable.ForeignKeys.Add(fk);
targetTable.Columns.Add(plowMachineIdColumn);
#endregion
//
// добавляем столбцы в таблицу
//
foreach (FieldMetadata f in module.MetadataFields)
{
Column column = CreateColumn(targetTable, f);
targetTable.Columns.Add(column);
}
targetTable.Create();
#region Первичный ключ
Index idx = new Index(targetTable, "PK_" + module.ModuleName);
IndexedColumn idxc = new IndexedColumn(idx, plowMachineIdColumn.Name);
idx.IndexedColumns.Add(idxc);
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
idx.IsClustered = true;
idx.IsUnique = true;
idx.Create();
#endregion
}
catch (Microsoft.SqlServer.Management.Smo.InvalidSmoOperationException)
{
throw;
}
}
else
{
throw new InvalidOperationException("Таблица с именем '" + module.ModuleName + "' уже существует в БД.");
}
}
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);
}