private bool UpdateExtendedProperties(ExtendedPropertyCollection properties, SqlExtendedPropertyContainer container)
{
bool DoAlter = false;
ExtendedProperty prop = null;
foreach (string key in container.Properties.Keys)
{
if (properties.Contains(key))
{
prop = properties[key];
}
else
{
prop = new ExtendedProperty(properties.Parent, key);
properties.Add(prop);
DoAlter = true;
}
prop.Value = container.Properties[key];
prop.Alter();
}
foreach (ExtendedProperty prop2 in properties)
{
if (!container.Properties.ContainsKey(prop2.Name))
{
prop2.MarkForDrop(true);
DoAlter = true;
}
}
return(DoAlter);
}
private bool UpdateExtendedProperties(ExtendedPropertyCollection properties, SqlExtendedPropertyContainer container)
{
bool DoAlter = false;
ExtendedProperty prop = null;
foreach (string key in container.Properties.Keys)
{
if (properties.Contains(key))
{
prop = properties[key];
}
else
{
prop = new ExtendedProperty(properties.Parent, key);
properties.Add(prop);
DoAlter = true;
}
prop.Value = container.Properties[key];
prop.Alter();
}
foreach (ExtendedProperty prop2 in properties)
{
if (!container.Properties.ContainsKey(prop2.Name))
{
prop2.MarkForDrop(true);
DoAlter = true;
}
}
return DoAlter;
}
private void RetrieveExtendedProperties(ExtendedPropertyCollection properties, SqlExtendedPropertyContainer container)
{
properties.Refresh();
foreach (ExtendedProperty prop in properties)
{
prop.Refresh();
container.Properties.TryAdd(prop.Name, prop.Value.ToString());
}
}
public void ImportExtendedProperties(DatabaseExtendedProperties dep)
{
double t = 0;
SqlDatabase.Refresh();
double total = dep.Tables.Count;
// bool DoAlter = UpdateExtendedProperties(SqlDatabase.ExtendedProperties,)
RaiseUpdateEvent(0, "Database level extended properties extported");
foreach (Microsoft.SqlServer.Management.Smo.Table table in SqlDatabase.Tables)
{
TableExtendedProperties tep = dep.Tables.FirstOrDefault(x => x.Name.Equals(table.Name, StringComparison.OrdinalIgnoreCase) && x.Schema.Equals(table.Schema, StringComparison.OrdinalIgnoreCase));
if (tep != null)
{
table.Refresh();
bool alter = UpdateExtendedProperties(table.ExtendedProperties, tep);
if (tep.Columns.Count > 0)
{
foreach (Column column in table.Columns)
{
SqlExtendedPropertyContainer cep = tep.Columns.FirstOrDefault(x => x.Name.Equals(column.Name));
if (cep != null)
{
if (UpdateExtendedProperties(column.ExtendedProperties, cep))
{
column.Alter();
}
}
}
}
if (alter)
{
table.Alter();
}
t++;
RaiseUpdateEvent(Convert.ToInt32((t / total) * 100), table.Schema + "." + table.Name + " extended properties imported");
}
}
}
public DatabaseExtendedProperties ExportExtendedProperties()
{
DatabaseExtendedProperties dep = new DatabaseExtendedProperties();
double t = 0;
SqlDatabase.Refresh();
double total = SqlDatabase.Tables.Count;
RetrieveExtendedProperties(SqlDatabase.ExtendedProperties, dep);
RaiseUpdateEvent(0, "Database level extended properties extported");
foreach (Microsoft.SqlServer.Management.Smo.Table table in SqlDatabase.Tables)
{
table.Refresh();
TableExtendedProperties tep = new TableExtendedProperties();
RetrieveExtendedProperties(table.ExtendedProperties, tep);
tep.Schema = table.Schema;
tep.Name = table.Schema;
foreach (Column column in table.Columns)
{
SqlExtendedPropertyContainer cep = new SqlExtendedPropertyContainer();
RetrieveExtendedProperties(column.ExtendedProperties, cep);
if (cep.Properties.Count > 0)
{
cep.Schema = column.DefaultSchema;
cep.Name = column.Name;
tep.Columns.Add(cep);
}
}
if (tep.Properties.Count > 0 || tep.Columns.Count > 0)
{
dep.Tables.Add(tep);
}
t++;
RaiseUpdateEvent(Convert.ToInt32((t / total) * 100), table.Schema + "." + table.Name + " extended properties exported");
}
return(dep);
}
public DatabaseExtendedProperties ExportExtendedProperties()
{
DatabaseExtendedProperties dep = new DatabaseExtendedProperties();
double t = 0;
SqlDatabase.Refresh();
double total = SqlDatabase.Tables.Count;
RetrieveExtendedProperties(SqlDatabase.ExtendedProperties, dep);
RaiseUpdateEvent(0, "Database level extended properties extported");
foreach (Microsoft.SqlServer.Management.Smo.Table table in SqlDatabase.Tables)
{
table.Refresh();
TableExtendedProperties tep = new TableExtendedProperties();
RetrieveExtendedProperties(table.ExtendedProperties, tep);
tep.Schema = table.Schema;
tep.Name = table.Schema;
foreach (Column column in table.Columns)
{
SqlExtendedPropertyContainer cep = new SqlExtendedPropertyContainer();
RetrieveExtendedProperties(column.ExtendedProperties, cep);
if (cep.Properties.Count > 0)
{
cep.Schema = column.DefaultSchema;
cep.Name = column.Name;
tep.Columns.Add(cep);
}
}
if (tep.Properties.Count > 0 || tep.Columns.Count > 0)
{
dep.Tables.Add(tep);
}
t++;
RaiseUpdateEvent(Convert.ToInt32((t / total) * 100), table.Schema + "." + table.Name + " extended properties exported");
}
return dep;
}
private void RetrieveExtendedProperties(ExtendedPropertyCollection properties, SqlExtendedPropertyContainer container)
{
properties.Refresh();
foreach (ExtendedProperty prop in properties)
{
prop.Refresh();
container.Properties.TryAdd(prop.Name, prop.Value.ToString());
}
}