public MetadataTable BuildMetadata(MetadataDatabase mdb, string TableName, string Schema = "dbo", bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
if (mdb.Tables.TryGetValue(TableName, out mt))
{
return(mt);
}
Microsoft.SqlServer.Management.Smo.Table t = new Microsoft.SqlServer.Management.Smo.Table(SqlDatabase, TableName, Schema);
t.Refresh();
return(BuildMetadata(mdb, t, PrimaryKeyIndexOnly, SelfJoin));
}
public MetadataTable BuildMetadata(MetadataDatabase mdb, string TableName, string Schema = "dbo", bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
if (mdb.Tables.TryGetValue(TableName, out mt))
{
return mt;
}
Microsoft.SqlServer.Management.Smo.Table t = new Microsoft.SqlServer.Management.Smo.Table(SqlDatabase, TableName, Schema);
t.Refresh();
return BuildMetadata(mdb, t, PrimaryKeyIndexOnly, SelfJoin);
}
private MetadataTable BuildMetadata(MetadataDatabase mdb, Microsoft.SqlServer.Management.Smo.Table table, bool PrimaryKeyIndexOnly = true, bool SelfJoin = false)
{
MetadataTable mt = null;
List <VirtualForeignKey> VirtualKeys = new List <VirtualForeignKey>();
table.Refresh();
if (mdb.Tables.TryGetValue(table.Name, out mt))
{
return(mt);
}
mt = new MetadataTable()
{
ID = table.ID,
Schema = table.Schema,
Name = table.Name,
//Parent = mdb
};
mt.TitleColumn = GetExtendedProperty("TitleColumn", table.ExtendedProperties);
string[] values = GetExtendedProperty("DisplayName", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
mt.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
values = GetExtendedProperty("Lists", table.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
List <string> v2 = v[1].Split(',').ToList();
if (!mt.ListDefinitions.TryAdd(v[0].Trim(), v2))
{
throw new InvalidOperationException(string.Format("The TinySql.Lists extended property is invalid for the table '{0}'", table.Name));
}
}
}
foreach (Microsoft.SqlServer.Management.Smo.Column column in table.Columns)
{
try
{
MetadataColumn col = new MetadataColumn()
{
ID = column.ID,
Parent = mt,
//Database = mdb,
Name = column.Name,
Collation = column.Collation,
Default = column.Default,
IsComputed = column.Computed,
ComputedText = column.ComputedText,
IsPrimaryKey = column.InPrimaryKey,
IsIdentity = column.Identity,
IsForeignKey = column.IsForeignKey,
IdentityIncrement = column.IdentityIncrement,
IdentitySeed = column.IdentitySeed,
Nullable = column.Nullable,
IsRowGuid = column.RowGuidCol
};
BuildColumnDataType(col, column);
values = GetExtendedProperty("DisplayName", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
foreach (string value in values)
{
if (!value.Contains("="))
{
col.DisplayNames.TryAdd(SqlBuilder.DefaultCulture.LCID, value);
}
else
{
string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
col.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]);
}
}
}
col.IncludeColumns = GetExtendedProperty("IncludeColumns", column.ExtendedProperties, new char[] { ',' });
values = GetExtendedProperty("FK", column.ExtendedProperties, new char[] { '\r', '\n' });
if (values != null)
{
VirtualKeys.Add(new VirtualForeignKey()
{
Column = col, values = values
});
col.IsForeignKey = true;
}
mt.Columns.AddOrUpdate(col.Name, col, (k, v) => { return(col); });
}
catch (Exception exColumn)
{
throw new InvalidOperationException(string.Format("Unable to generate the column {0}", column.Name), exColumn);
}
}
foreach (Index idx in table.Indexes)
{
if (!PrimaryKeyIndexOnly || idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
Key key = new Key()
{
ID = idx.ID,
Parent = mt,
Database = mdb,
Name = idx.Name,
IsUnique = idx.IsUnique,
IsPrimaryKey = idx.IndexKeyType == IndexKeyType.DriPrimaryKey
};
foreach (IndexedColumn c in idx.IndexedColumns)
{
key.Columns.Add(mt[c.Name]);
}
mt.Indexes.AddOrUpdate(key.Name, key, (k, v) => { return(key); });
}
}
if (!SelfJoin)
{
foreach (ForeignKey FK in table.ForeignKeys)
{
MetadataForeignKey mfk = new MetadataForeignKey()
{
ID = FK.ID,
Parent = mt,
Database = mdb,
Name = FK.Name,
ReferencedKey = FK.ReferencedKey,
ReferencedSchema = FK.ReferencedTableSchema,
ReferencedTable = FK.ReferencedTable
};
MetadataTable mtref = null;
if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref))
{
bool self = false;
if ((mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) || TablesInProgress.Contains(mfk.ReferencedSchema + "." + mfk.ReferencedTable))
{
self = true;
}
TablesInProgress.Add(mfk.ReferencedSchema + "." + mfk.ReferencedTable);
mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self);
}
foreach (ForeignKeyColumn cc in FK.Columns)
{
mfk.ColumnReferences.Add(new MetadataColumnReference()
{
Name = cc.Name,
Column = mt[cc.Name],
ReferencedColumn = mtref[cc.ReferencedColumn]
});
}
mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (key, existing) =>
{
return(mfk);
});
}
}
if (VirtualKeys.Count > 0)
{
BuildVirtualKeys(VirtualKeys, mt, mdb, PrimaryKeyIndexOnly);
}
mdb.Tables.AddOrUpdate(mt.Schema + "." + mt.Name, mt, (key, existing) =>
{
return(mt);
});
return(mt);
}