public override sealed async Task LoadSchemaAsync() { StartWork(); try { await(Task.Run(() => { DataSource.DatabaseMetadata.PreloadTables(); DataSource.DatabaseMetadata.PreloadViews(); })); //Task-10, we need an async version of this. Tables.AddRange(DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => t.IsTable).OrderBy(t => t.Name.Name).Select(t => new SQLiteTableVM(DataSource, t))); Views.AddRange(DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => !t.IsTable).OrderBy(t => t.Name.Name).Select(t => new ViewVM(t))); } finally { StopWork(); } }
public override sealed async Task LoadSchemaAsync() { StartWork(); try { await(Task.Run(() => { DataSource.DatabaseMetadata.PreloadTables(); DataSource.DatabaseMetadata.PreloadViews(); })); //Task-10, we need an async version of this. Tables.AddRange(DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => t.IsTable).OrderBy(t => t.Name.Schema).ThenBy(t => t.Name.Name).Select(t => new SqlServerTableVM(DataSource, (SqlServerTableOrViewMetadata <SqlDbType>)t))); Views.AddRange(DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => !t.IsTable).OrderBy(t => t.Name.Schema).ThenBy(t => t.Name.Name).Select(t => new ViewVM(t))); const string tableDescriptionsSql = @"SELECT s.name AS SchemaName, t.name AS TableName, ep.value FROM sys.extended_properties ep INNER JOIN sys.tables t ON ep.major_id = t.object_id AND ep.minor_id = 0 INNER JOIN sys.schemas s ON t.schema_id = s.schema_id AND ep.name = 'MS_Description';"; var tableDescriptions = await DataSource.Sql(tableDescriptionsSql).ToTable().ExecuteAsync(); foreach (var item in tableDescriptions.Rows) { var table = Tables.Single(t => t.Table.Name.Schema == (string)item["SchemaName"] && t.Table.Name.Name == (string)item["TableName"]); table.Description = (string)item["value"]; } const string columnDescriptionsSql = @"SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, ep.value FROM sys.extended_properties ep INNER JOIN sys.tables t ON ep.major_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON c.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'"; var columnDescriptions = await DataSource.Sql(columnDescriptionsSql).ToTable().ExecuteAsync(); foreach (var item in columnDescriptions.Rows) { var table = Tables.Single(t => t.Table.Name.Schema == (string)item["SchemaName"] && t.Table.Name.Name == (string)item["TableName"]); var column = (SqlServerColumnModel)table.Columns.Single(c => c.Name == (string)item["ColumnName"]); column.Description = (string)item["value"]; } const string sparseColumnsSql = @"SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, c.is_sparse FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON c.object_id = t.object_id"; var sparseColumns = await DataSource.Sql(sparseColumnsSql).ToTable().ExecuteAsync(); foreach (var item in sparseColumns.Rows) { var table = Tables.Single(t => t.Table.Name.Schema == (string)item["SchemaName"] && t.Table.Name.Name == (string)item["TableName"]); var column = (SqlServerColumnModel)table.Columns.Single(c => c.Name == (string)item["ColumnName"]); column.IsSparse = (bool)item["is_sparse"]; } const string relatedTablesSql = @"SELECT s.name AS SchemaName, t.name AS TableName, s2.name AS ReferencedSchemaName, t2.name AS ReferencedTableName FROM sys.foreign_key_columns fkc INNER JOIN sys.tables t ON fkc.parent_object_id = t.object_id INNER JOIN sys.columns c ON c.column_id = fkc.parent_column_id AND c.object_id = fkc.parent_object_id INNER JOIN sys.schemas s ON s.schema_id = t.schema_id INNER JOIN sys.tables t2 ON fkc.referenced_object_id = t2.object_id INNER JOIN sys.columns c2 ON c2.column_id = fkc.referenced_column_id AND c2.object_id = fkc.referenced_object_id INNER JOIN sys.schemas s2 ON s2.schema_id = t2.schema_id INNER JOIN sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id"; var relatedTables = await DataSource.Sql(relatedTablesSql).ToTable().ExecuteAsync(); foreach (var item in relatedTables.Rows) { var table = Tables.Single(t => t.Table.Name.Schema == (string)item["SchemaName"] && t.Table.Name.Name == (string)item["TableName"]); var relatedTable = Tables.Single(t => t.Table.Name.Schema == (string)item["ReferencedSchemaName"] && t.Table.Name.Name == (string)item["ReferencedTableName"]); if (!table.ReferencedTables.Contains(relatedTable)) { table.ReferencedTables.Add(relatedTable); } if (!relatedTable.ReferencedByTables.Contains(table)) { relatedTable.ReferencedByTables.Add(table); } } } finally { StopWork(); } }