///-------------------------------------------------------------------------------- /// <summary>This loads information from a SqlServer database, using SMO.</summary> /// /// <param name="sqlDatabase">The input sql database</param> ///-------------------------------------------------------------------------------- public void LoadSqlServerDatabase(Database sqlDatabase) { try { // load the basic database information SqlDatabaseName = sqlDatabase.Name; DbID = sqlDatabase.ID; Owner = sqlDatabase.Owner; try { PrimaryFilePath = sqlDatabase.PrimaryFilePath; DefaultFileGroup = sqlDatabase.DefaultFileGroup; DefaultFullTextCatalog = sqlDatabase.DefaultFullTextCatalog; } catch { // TODO: have specific Azure db load or identify Azure case } DefaultSchema = sqlDatabase.DefaultSchema; CreateDate = sqlDatabase.CreateDate; Status = sqlDatabase.Status.ToString(); UserName = sqlDatabase.UserName; State = sqlDatabase.State.ToString(); // load information for each property foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlDatabase.Properties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) { SqlProperty property = new SqlProperty(); property.SqlPropertyID = Guid.NewGuid(); property.SqlDatabase = this; property.LoadProperty(loopProperty); SqlPropertyList.Add(property); } } try { // load information for each extended property foreach (ExtendedProperty loopProperty in sqlDatabase.ExtendedProperties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlExtendedProperty property = new SqlExtendedProperty(); property.SqlExtendedPropertyID = Guid.NewGuid(); property.SqlDatabase = this; property.LoadExtendedProperty(loopProperty); SqlExtendedPropertyList.Add(property); } } catch { // TODO: have specific Azure db load or identify Azure case } // load information for each table foreach (Table loopTable in sqlDatabase.Tables) { if (loopTable.IsSystemObject == true) { continue; } if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlTable table = new SqlTable(); table.SqlTableID = Guid.NewGuid(); table.SqlDatabase = this; table.LoadTable(loopTable); SqlTableList.Add(table); } // load information for each view foreach (Microsoft.SqlServer.Management.Smo.View loopView in sqlDatabase.Views) { if (loopView.IsSystemObject == true) { continue; } SqlView view = new SqlView(); view.SqlViewID = Guid.NewGuid(); view.SqlDatabase = this; view.LoadView(loopView); SqlViewList.Add(view); } } catch (ApplicationAbortException) { throw; } catch (Exception) { throw; } }
///-------------------------------------------------------------------------------- /// <summary>This loads information from a SQL foreign key.</summary> /// /// <param name="sqlForeignKey">The input sql foreign key.</param> ///-------------------------------------------------------------------------------- public void LoadForeignKey(ForeignKey sqlForeignKey) { try { // load the basic foreign key information SqlForeignKeyName = sqlForeignKey.Name; DbID = sqlForeignKey.ID; ReferencedKey = sqlForeignKey.ReferencedKey; ReferencedTable = sqlForeignKey.ReferencedTable; ReferencedTableSchema = sqlForeignKey.ReferencedTableSchema; IsChecked = sqlForeignKey.IsChecked; IsSystemNamed = sqlForeignKey.IsSystemNamed; CreateDate = sqlForeignKey.CreateDate; DateLastModified = sqlForeignKey.DateLastModified; Urn = sqlForeignKey.Urn; State = sqlForeignKey.State.ToString(); // load information for each property foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlForeignKey.Properties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) { if (loopProperty.Name == "ID" || loopProperty.Name == "IsEnabled") { SqlProperty property = new SqlProperty(); property.SqlPropertyID = Guid.NewGuid(); property.SqlForeignKey = this; property.LoadProperty(loopProperty); SqlPropertyList.Add(property); } } } // load information for each extended property //foreach (ExtendedProperty loopProperty in sqlForeignKey.ExtendedProperties) //{ // SqlExtendedProperty property = new SqlExtendedProperty(); // property.SqlExtendedPropertyID = Guid.NewGuid(); // property.SqlForeignKey = this; // property.LoadExtendedProperty(loopProperty); // SqlExtendedPropertyList.Add(property); //} // load information for each column foreach (ForeignKeyColumn loopColumn in sqlForeignKey.Columns) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlForeignKeyColumn column = new SqlForeignKeyColumn(); column.SqlForeignKeyColumnID = Guid.NewGuid(); column.SqlForeignKey = this; column.LoadColumn(loopColumn); SqlForeignKeyColumnList.Add(column); } } catch (ApplicationAbortException) { throw; } catch (Exception ex) { bool reThrow = BusinessConfiguration.HandleException(ex); if (reThrow) { throw; } } }
///-------------------------------------------------------------------------------- /// <summary>This loads information from a SQL table.</summary> /// /// <param name="sqlTable">The input sql table.</param> ///-------------------------------------------------------------------------------- public void LoadTable(Table sqlTable) { try { // load the basic table information SqlTableName = sqlTable.Name; DbID = sqlTable.ID; Owner = sqlTable.Owner; Schema = sqlTable.Schema; try { FileGroup = sqlTable.FileGroup; } catch { // TODO: have specific Azure db load or identify Azure case } CreateDate = sqlTable.CreateDate; DateLastModified = sqlTable.DateLastModified; Urn = sqlTable.Urn; State = sqlTable.State.ToString(); // load information for each property foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlTable.Properties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) { if (loopProperty.Name == "ID" || loopProperty.Name == "RowCount" || loopProperty.Name == "HasClusteredIndex" || loopProperty.Name == "HasIndex") { SqlProperty property = new SqlProperty(); property.SqlPropertyID = Guid.NewGuid(); property.SqlTable = this; property.LoadProperty(loopProperty); SqlPropertyList.Add(property); } } } try { // load information for each extended property foreach (ExtendedProperty loopProperty in sqlTable.ExtendedProperties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlExtendedProperty property = new SqlExtendedProperty(); property.SqlExtendedPropertyID = Guid.NewGuid(); property.SqlTable = this; property.LoadExtendedProperty(loopProperty); SqlExtendedPropertyList.Add(property); } } catch { // TODO: have specific Azure db load or identify Azure case } // load information for each column foreach (Column loopColumn in sqlTable.Columns) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlColumn column = new SqlColumn(); column.SqlColumnID = Guid.NewGuid(); column.SqlTable = this; column.LoadColumn(loopColumn); SqlColumnList.Add(column); } // load information for each index foreach (Index loopIndex in sqlTable.Indexes) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlIndex index = new SqlIndex(); index.SqlIndexID = Guid.NewGuid(); index.SqlTable = this; index.LoadIndex(loopIndex); SqlIndexList.Add(index); } // load information for each foreign key foreach (ForeignKey loopKey in sqlTable.ForeignKeys) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlForeignKey key = new SqlForeignKey(); key.SqlForeignKeyID = Guid.NewGuid(); key.SqlTable = this; key.LoadForeignKey(loopKey); SqlForeignKeyList.Add(key); } } catch (ApplicationAbortException) { throw; } catch (Exception) { throw; } }
///-------------------------------------------------------------------------------- /// <summary>This loads information from a SQL column.</summary> /// /// <param name="sqlColumn">The input sql column.</param> ///-------------------------------------------------------------------------------- public void LoadColumn(Column sqlColumn) { try { // load the basic column information SqlColumnName = sqlColumn.Name; DbID = sqlColumn.ID; DataType = sqlColumn.DataType.ToString(); MaximumLength = sqlColumn.DataType.MaximumLength; NumericPrecision = sqlColumn.DataType.NumericPrecision; NumericScale = sqlColumn.DataType.NumericScale; Default = sqlColumn.Default; DefaultSchema = sqlColumn.DefaultSchema; IsFullTextIndexed = sqlColumn.IsFullTextIndexed; IsForeignKey = sqlColumn.IsForeignKey; InPrimaryKey = sqlColumn.InPrimaryKey; Nullable = sqlColumn.Nullable; Identity = sqlColumn.Identity; IdentitySeed = sqlColumn.IdentitySeed; IdentityIncrement = sqlColumn.IdentityIncrement; Urn = sqlColumn.Urn; State = sqlColumn.State.ToString(); // load information for each property foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlColumn.Properties) { if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) { if (loopProperty.Name == "ID" || loopProperty.Name == "Length") { SqlProperty property = new SqlProperty(); property.SqlPropertyID = Guid.NewGuid(); property.SqlColumn = this; property.LoadProperty(loopProperty); SqlPropertyList.Add(property); } } } // load information for each extended property //foreach (ExtendedProperty loopProperty in sqlColumn.ExtendedProperties) //{ // SqlExtendedProperty property = new SqlExtendedProperty(); // property.SqlExtendedPropertyID = Guid.NewGuid(); // property.SqlColumn = this; // property.LoadExtendedProperty(loopProperty); // SqlExtendedPropertyList.Add(property); //} } catch (ApplicationAbortException) { throw; } catch (Exception ex) { bool reThrow = BusinessConfiguration.HandleException(ex); if (reThrow) { throw; } } }
///-------------------------------------------------------------------------------- /// <summary>This loads information from a SQL index.</summary> /// /// <param name="sqlIndex">The input sql index.</param> ///-------------------------------------------------------------------------------- public void LoadIndex(Index sqlIndex) { try { // load the basic index information SqlIndexName = sqlIndex.Name; DbID = sqlIndex.ID; IsClustered = sqlIndex.IsClustered; IsUnique = sqlIndex.IsUnique; IsXmlIndex = sqlIndex.IsXmlIndex; IsFullTextKey = sqlIndex.IsFullTextKey; FileGroup = sqlIndex.FileGroup; Urn = sqlIndex.Urn; State = sqlIndex.State.ToString(); // load information for each property foreach (Microsoft.SqlServer.Management.Smo.Property loopProperty in sqlIndex.Properties) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } if (loopProperty.Expensive == false && loopProperty.IsNull == false && !String.IsNullOrEmpty(loopProperty.Value.ToString())) { if (loopProperty.Name == "ID" || loopProperty.Name == "IgnoreDuplicateKeys" || loopProperty.Name == "IndexKeyType") { SqlProperty property = new SqlProperty(); property.SqlPropertyID = Guid.NewGuid(); property.SqlIndex = this; property.LoadProperty(loopProperty); SqlPropertyList.Add(property); } } } // load information for each extended property //foreach (ExtendedProperty loopProperty in sqlIndex.ExtendedProperties) //{ // SqlExtendedProperty property = new SqlExtendedProperty(); // property.SqlExtendedPropertyID = Guid.NewGuid(); // property.SqlIndex = this; // property.LoadExtendedProperty(loopProperty); // SqlExtendedPropertyList.Add(property); //} // load information for each column foreach (IndexedColumn loopColumn in sqlIndex.IndexedColumns) { if (DebugHelper.DebugAction == DebugAction.Stop) { return; } SqlIndexedColumn column = new SqlIndexedColumn(); column.SqlIndexedColumnID = Guid.NewGuid(); column.SqlIndex = this; column.LoadColumn(loopColumn); SqlIndexedColumnList.Add(column); } } catch (ApplicationAbortException) { throw; } catch (Exception ex) { bool reThrow = BusinessConfiguration.HandleException(ex); if (reThrow) { throw; } } }