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