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