private static void ProcessReferences(DatabaseModel model)
{
foreach (var table in model.Tables)
{
foreach (var reference in table.References)
{
if (reference.ReferenceType == ReferenceType.Undefined)
{
var referencedTable = model.Tables.FirstOrDefault(x => x.Name == reference.TableName);
// referenced table has a reference to current table
var reverseReference = referencedTable.References.FirstOrDefault(x => x.TableName == table.Name);
if (reverseReference != null)
{
reference.ReferenceType = ReferenceType.OneToOne;
reverseReference.ReferenceType = ReferenceType.OneToOne;
}
else
{
reference.ReferenceType = ReferenceType.ManyToOne;
referencedTable.References.Add(
new Reference
{
ReferenceType = ReferenceType.OneToMany,
TableName = table.Name,
ColumnName = table.PrimaryKeyName
});
}
}
}
}
// Many to Many
for (var i = model.Tables.Count - 1; i >= 0; i--)
{
var table = model.Tables[i];
if ((table.Columns.Count == 2 || table.Columns.Count == 3) &&
table.References.Count == 2 &&
table.References[0].ReferenceType == ReferenceType.ManyToOne &&
table.References[1].ReferenceType == ReferenceType.ManyToOne)
{
var leftReference = table.References[0];
var rightReference = table.References[1];
// Add right reference to left table
var leftTable = model.Tables.First(x => x.Name == leftReference.TableName);
leftTable.References.Remove(leftTable.References.First(x => x.TableName == table.Name));
leftTable.References.Add(
new Reference
{
ReferenceType = ReferenceType.ManyToMany,
TableName = rightReference.TableName,
ColumnName = rightReference.ColumnName,
LinkTableName = table.Name,
LeftColumnName = leftReference.SourceColumnName,
RightColumnName = rightReference.SourceColumnName
});
// Add left reference
var rightTable = model.Tables.First(x => x.Name == rightReference.TableName);
rightTable.References.Remove(rightTable.References.First(x => x.TableName == table.Name));
rightTable.References.Add(
new Reference
{
ReferenceType = ReferenceType.ManyToMany,
TableName = leftReference.TableName,
ColumnName = leftReference.ColumnName
});
model.Tables.RemoveAt(i);
}
}
}
public static DatabaseModel LoadDatabaseModel(TSqlModel model)
{
var databaseModel = new DatabaseModel();
var tables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table);
foreach (var table in tables)
{
var tableModel = new TableModel
{
Name = table.Name.Parts[1]
};
var columns = table.GetReferenced(Table.Columns, DacQueryScopes.All);
foreach (var column in columns)
{
var dataType = column.GetReferenced(Column.DataType).First();
var columnModel = new ColumnModel
{
Name = column.Name.Parts[2],
SqlDbType = dataType.GetProperty <SqlDataType>(DataType.SqlDataType),
Nullable = column.GetProperty <bool>(Column.Nullable),
Length = column.GetProperty <int>(Column.Length)
};
tableModel.Columns.Add(columnModel);
}
var primaryKeyConstraints = table.GetReferencing(PrimaryKeyConstraint.Host, DacQueryScopes.UserDefined);
foreach (var primaryKeyConstraint in primaryKeyConstraints)
{
var primaryKeyColumn = primaryKeyConstraint.GetReferenced(PrimaryKeyConstraint.Columns).FirstOrDefault();
if (primaryKeyColumn != null)
{
tableModel.Columns.First(x => x.Name == primaryKeyColumn.Name.Parts[2]).IsPrimarykey = true;
}
}
var foreignKeyConstraints = table.GetReferencing(ForeignKeyConstraint.Host, DacQueryScopes.UserDefined);
foreach (var foreignKeyConstraint in foreignKeyConstraints)
{
var sourceTable = foreignKeyConstraint.GetReferenced(ForeignKeyConstraint.Host).FirstOrDefault();
var sourceColumn = foreignKeyConstraint.GetReferenced(ForeignKeyConstraint.Columns).FirstOrDefault();
var referencedTable = foreignKeyConstraint.GetReferenced(ForeignKeyConstraint.ForeignTable).FirstOrDefault();
var referencedColumn = foreignKeyConstraint.GetReferenced(ForeignKeyConstraint.ForeignColumns).FirstOrDefault();
if (referencedTable != null && referencedColumn != null)
{
var reference = new Reference
{
SourceTableName = sourceTable.Name.Parts.Last(),
SourceColumnName = sourceColumn.Name.Parts.Last(),
TableName = referencedTable.Name.Parts.Last(),
ColumnName = referencedColumn.Name.Parts.Last()
};
tableModel.References.Add(reference);
}
var t = foreignKeyConstraint.GetReferenced(ForeignKeyConstraint.Host);
}
databaseModel.Tables.Add(tableModel);
}
ProcessReferences(databaseModel);
return(databaseModel);
}