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