/// <summary>
/// Checks to see if the Column object is contained in the ColumnCollection.
/// </summary>
/// <param name="value">Column Object to check for.</param>
/// <returns>returns true if Column object is in the ColumnCollection.</returns>
public bool Contains(COLUMN value)
{
// If value is not of type Column, this will return false.
return(List.Contains(value));
}
/// <summary>
/// Adds a Column object to the ColumnCollection.
/// </summary>
/// <param name="_column">Column Object to add</param>
public void Add(COLUMN _column)
{
List.Add(_column);
}
/// <summary>
/// Adds a Column object to the ColumnCollection.
/// </summary>
/// <param name="_column">Column Object to add</param>
public void Add(COLUMN _column)
{
List.Add( _column );
}
/// <summary>
/// Checks to see if the Column object is contained in the ColumnCollection.
/// </summary>
/// <param name="value">Column Object to check for.</param>
/// <returns>returns true if Column object is in the ColumnCollection.</returns>
public bool Contains( COLUMN value )
{
// If value is not of type Column, this will return false.
return( List.Contains( value ) );
}
/// <summary>
/// Compares table columns from two distinct XML schema files. These files are generated by the SerializeDB method.
/// This method is called from the CompareTables method.
/// </summary>
/// <param name="tableName">The string value representing the current SQL table object.</param>
/// <param name="xnlSource">The XmlNodeList, a collection of source XML nodes to compare with the destination XML
/// nodes.</param>
/// <param name="xnlDestination">The XmlNodeList, a collection of destination/target XML nodes to compare with the
/// source XML nodes.</param>
/// <param name="xmlDiffDoc">The XML Diffgram object to update.</param>
/// <param name="xmlDiff">The XmlDiff Object used from the GotDotNet XmlDiff class. Performs the XML node compare.</param>
private static void CompareColumns(string tableName, XmlNodeList xnlSource, XmlNodeList xnlDestination,
XmlDocument xmlDiffDoc, XmlDiff xmlDiff)
{
SQLObjects.ColumnCollection tableColumns = new SQLObjects.ColumnCollection(tableName);
Hashtable htDropAdd = new Hashtable();
// compare source columns to destination columns, looking for changed or missing destination columns
if (xnlSource != null)
{
// if a matching destination table was found with columns
if (xnlDestination != null)
{
tableColumns.SchemaAction = SQLObjects.COLUMN.ColumnAction.Alter;
// identify the source columns that are different
foreach (XmlNode xn in xnlSource)
{
string column_Name = xn.ChildNodes[1].InnerXml;
XmlNode Found = FindNode(xnlDestination, column_Name, "<Column_Name>{0}</Column_Name>");
// look for existing columns
if (Found != null)
{
// if the columns don't compare then
if (!xmlDiff.Compare(xn, Found))
{
SQLObjects.COLUMN col = new SQLObjects.COLUMN();
col.Action = SQLObjects.COLUMN.ColumnAction.Alter;
// add original_rules from the destination column so that if they are not on the source columns we can exec sp_unbindrule on them.
// There is XSLT code to handle sp_bindrule for the source columns where they were not bound to the destination(original) column.
// IF the source and destination rule names are the same, then we should be able to ignore changing the column bound rule
XmlNode xnRule = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "RULE_ORIG_NAME", xn.OwnerDocument.NamespaceURI);
if (Found.SelectSingleNode("Rule_Name") != null)
{
xnRule.InnerXml = Found.SelectSingleNode("Rule_Name").InnerXml;
xn.AppendChild(xnRule);
}
xnRule = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "RULE_ORIG_OWNER", xn.OwnerDocument.NamespaceURI);
if (Found.SelectSingleNode("Rule_Owner") != null)
{
xnRule.InnerXml = Found.SelectSingleNode("Rule_Owner").InnerXml;
xn.AppendChild(xnRule);
}
XmlNode xnDefault = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "DEFAULT_ORIG_NAME", xn.OwnerDocument.NamespaceURI);
if (Found.SelectSingleNode("Default_Name") != null)
{
xnDefault.InnerXml = Found.SelectSingleNode("Default_Name").InnerXml;
xn.AppendChild(xnDefault);
}
xnDefault = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "DEFAULT_ORIG_VALUE", xn.OwnerDocument.NamespaceURI);
if (Found.SelectSingleNode("Default_Value") != null)
{
xnDefault.InnerXml = Found.SelectSingleNode("Default_Value").InnerXml;
xn.AppendChild(xnDefault);
}
XmlNode xnRowGuidCol = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "ORIG_RowGuidCol", xn.OwnerDocument.NamespaceURI);
if (Found.SelectSingleNode("isRowGuidCol") != null)
{
xnRowGuidCol.InnerXml = Found.SelectSingleNode("isRowGuidCol").InnerXml;
xn.AppendChild(xnRowGuidCol);
}
// lookup any altered columns to see if there are Reference dependencies
// may need to use something like this: descendant::*[contains(local-name(),'cKeyCol')]
for (int x = 1; x < 17; x++)
{
if (Found.SelectSingleNode("../TABLE_REFERENCE/cRefCol" + x.ToString()) != null)
{
CheckColumnDependencies(column_Name, tableName, "DropAdd_References", "TABLE_REFERENCE", "Constraint", "cRefCol" + x.ToString(),
false, ref htDropAdd, Found, xn, xmlDiffDoc);
}
}
// lookup any altered columns to see if there are Constraint dependencies
CheckColumnDependencies(column_Name, tableName, "DropAdd_Constraints", "TABLE_CONSTRAINTS", "CONSTRAINT_NAME", "COLUMN_NAME",
false, ref htDropAdd, Found, xn, xmlDiffDoc);
// lookup any altered columns to see if there are index dependencies
CheckColumnDependencies(column_Name, tableName, "DropAdd_Indexes", "TABLE_INDEX", "index_name", "index_keys",
false, ref htDropAdd, Found, xn, xmlDiffDoc);
// add xml node to the table columns collection
SQLObjects.COLUMN c = col.Convert(xn);
tableColumns.Add(c);
}
else
continue;
}
else // the column was not found in the destination table
{
SQLObjects.COLUMN col = new SQLObjects.COLUMN();
col.Action = SQLObjects.COLUMN.ColumnAction.Add;
tableColumns.Add(col.Convert(xn));
}
}
}
else // no destination table so add all the columns
{
foreach (XmlNode xn in xnlSource)
{
string column_Name = xn.ChildNodes[1].InnerXml;
SQLObjects.COLUMN col = new SQLObjects.COLUMN();
col.Action = SQLObjects.COLUMN.ColumnAction.Add;
tableColumns.Add(col.Convert(xn));
}
}
}
// look for desination columns not in the source table, so as to mark the desination columns to drop
if (xnlDestination != null)
{
if (xnlSource != null)
{
tableColumns.SchemaAction = SQLObjects.COLUMN.ColumnAction.Alter;
// identify the destination columns that are missing
foreach (XmlNode xn in xnlDestination)
{
string column_Name = xn.ChildNodes[1].InnerXml;
XmlNode Found = FindNode(xnlSource, column_Name, "<Column_Name>{0}</Column_Name>");
if (Found == null)
{
SQLObjects.COLUMN col = new SQLObjects.COLUMN();
col.Action = SQLObjects.COLUMN.ColumnAction.Drop;
XmlNode xnRule = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "RULE_ORIG_NAME", xn.OwnerDocument.NamespaceURI);
if (xn.SelectSingleNode("Rule_Name") != null)
{
xnRule.InnerXml = xn.SelectSingleNode("Rule_Name").InnerXml;
xn.AppendChild(xnRule);
}
xnRule = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "RULE_ORIG_OWNER", xn.OwnerDocument.NamespaceURI);
if (xn.SelectSingleNode("Rule_Owner") != null)
{
xnRule.InnerXml = xn.SelectSingleNode("Rule_Owner").InnerXml;
xn.AppendChild(xnRule);
}
XmlNode xnDefault = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "DEFAULT_ORIG_NAME", xn.OwnerDocument.NamespaceURI);
if (xn.SelectSingleNode("Default_Name") != null)
{
xnDefault.InnerXml = xn.SelectSingleNode("Default_Name").InnerXml;
xn.AppendChild(xnDefault);
}
xnDefault = xn.OwnerDocument.CreateNode(XmlNodeType.Element, "DEFAULT_ORIG_VALUE", xn.OwnerDocument.NamespaceURI);
if (xn.SelectSingleNode("Default_Value") != null)
{
xnDefault.InnerXml = xn.SelectSingleNode("Default_Value").InnerXml;
xn.AppendChild(xnDefault);
}
// lookup any dropped columns to see if there are Reference dependencies,
// may need to use something like this: descendant::*[contains(local-name(),'cKeyCol')]
for (int x = 1; x < 17; x++)
{
if (xn.SelectSingleNode("../TABLE_REFERENCE/cRefCol" + x.ToString()) != null)
{
CheckColumnDependencies(column_Name, tableName, "DropAdd_References", "TABLE_REFERENCE", "Constraint", "cRefCol" + x.ToString(),
true, ref htDropAdd, null, xn, xmlDiffDoc);
}
}
// lookup any altered columns to see if there are Constraint dependencies
CheckColumnDependencies(column_Name, tableName, "DropAdd_Constraints", "TABLE_CONSTRAINTS", "CONSTRAINT_NAME", "COLUMN_NAME",
true, ref htDropAdd, null, xn, xmlDiffDoc);
// lookup any dropped columns to see if there are index dependencies
CheckColumnDependencies(column_Name, tableName, "DropAdd_Indexes", "TABLE_INDEX", "index_name", "index_keys",
true, ref htDropAdd, null, xn, xmlDiffDoc);
tableColumns.Add(col.Convert(xn));
}
}
}
}
// persist the tableColumns collection as XML if there are any
if (tableColumns.Count > 0)
{
XmlNode xTableColumns = tableColumns.SerializeAsXmlNode(xmlDiffDoc);
foreach (object obj in htDropAdd.Values)
{
xTableColumns.AppendChild((XmlNode)obj);
}
xmlDiffDoc.SelectSingleNode("/DataBase_Schema").AppendChild(xTableColumns);
}
}