/// <summary>
/// Compare stored procedures
/// </summary>
public DifferenceCollection CompareSchemaObjects(SqlServer databaseLeft, SqlServer databaseRight, Difference.DatabaseObjectType schemaObjectType)
{
ArrayList leftSchemaList, rightSchemaList;
DifferenceCollection schemaDifferences = new DifferenceCollection();
int addedIndex;
leftSchemaList = databaseLeft.GetObjectList(schemaObjectType);
rightSchemaList = databaseRight.GetObjectList(schemaObjectType);
// Fire event
int numberObjects = leftSchemaList.Count;
FireCompareSchemaStarted(schemaObjectType, numberObjects);
// Compare databases
//ShowInfoMessage(String.Format("Found a total of {0} tables ({1} + {2})", leftSchemaList.Count + rightSchemaList.Count, leftSchemaList.Count, rightSchemaList.Count));
//ShowInfoMessage("Comparing tables and columns...");
// Compare first database against the second
for(int i=0; i<leftSchemaList.Count; i++)
{
// Find existing and missing stored procs
int schemaIndex = FindListItem(leftSchemaList[i].ToString(), rightSchemaList);
if (schemaIndex==-1)
{
// Right proc missing
addedIndex = schemaDifferences.Add(new Difference(false, leftSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Missing));
// Load the schema for the stored procs
string leftSchema = databaseLeft.GetObjectDefinition(leftSchemaList[i].ToString());
string rightSchema = "";
// Check for schema differences (we know there will be lots!)
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
}
else
{
bool schemaDifferenceFound = false;
// Add a default difference item
addedIndex = schemaDifferences.Add(new Difference(false, leftSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Unknown));
// Load the schema text
string leftSchema = databaseLeft.GetObjectDefinition(leftSchemaList[i].ToString());
string rightSchema = databaseRight.GetObjectDefinition(rightSchemaList[schemaIndex].ToString());
// Check for schema differences
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
// Check if we found any text differences
foreach(DifferenceEngine.DiffResultSpan diffResultSpan in textDifferenceList)
{
if (diffResultSpan.Status!=DifferenceEngine.DiffResultSpanStatus.NoChange)
{
schemaDifferenceFound = true;
}
}
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
if (schemaDifferenceFound)
{
schemaDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Different;
}
else
{
schemaDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Same;
}
}
}
// Find objects in second database not yet scanned
for(int i=0; i<rightSchemaList.Count; i++)
{
if ((FindListItem(rightSchemaList[i].ToString(), leftSchemaList))==-1)
{
// Left object missing
addedIndex = schemaDifferences.Add(new Difference(true, rightSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Missing));
// Load the schema for the stored procs
string leftSchema = "";
string rightSchema = databaseRight.GetObjectDefinition(rightSchemaList[i].ToString());
// Check for schema differences (we know there will be lots!)
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
}
}
// Sort by name
schemaDifferences = schemaDifferences.Sort("Name", SortDirection.Ascending);
FireCompareSchemaFinished(schemaObjectType);
return schemaDifferences;
}
/// <summary>
/// Compare stored procedures
/// </summary>
public DifferenceCollection CompareSchemaObjects(SqlServer databaseLeft, SqlServer databaseRight, Difference.DatabaseObjectType schemaObjectType)
{
ArrayList leftSchemaList, rightSchemaList;
DifferenceCollection schemaDifferences = new DifferenceCollection();
int addedIndex;
leftSchemaList = databaseLeft.GetObjectList(schemaObjectType);
rightSchemaList = databaseRight.GetObjectList(schemaObjectType);
// Fire event
int numberObjects = leftSchemaList.Count;
FireCompareSchemaStarted(schemaObjectType, numberObjects);
// Compare databases
//ShowInfoMessage(String.Format("Found a total of {0} tables ({1} + {2})", leftSchemaList.Count + rightSchemaList.Count, leftSchemaList.Count, rightSchemaList.Count));
//ShowInfoMessage("Comparing tables and columns...");
// Compare first database against the second
for (int i = 0; i < leftSchemaList.Count; i++)
{
// Find existing and missing stored procs
int schemaIndex = FindListItem(leftSchemaList[i].ToString(), rightSchemaList);
if (schemaIndex == -1)
{
// Right proc missing
addedIndex = schemaDifferences.Add(new Difference(false, leftSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Missing));
// Load the schema for the stored procs
string leftSchema = databaseLeft.GetObjectDefinition(leftSchemaList[i].ToString());
string rightSchema = "";
// Check for schema differences (we know there will be lots!)
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
}
else
{
bool schemaDifferenceFound = false;
// Add a default difference item
addedIndex = schemaDifferences.Add(new Difference(false, leftSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Unknown));
// Load the schema text
string leftSchema = databaseLeft.GetObjectDefinition(leftSchemaList[i].ToString());
string rightSchema = databaseRight.GetObjectDefinition(rightSchemaList[schemaIndex].ToString());
// Check for schema differences
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
// Check if we found any text differences
foreach (DifferenceEngine.DiffResultSpan diffResultSpan in textDifferenceList)
{
if (diffResultSpan.Status != DifferenceEngine.DiffResultSpanStatus.NoChange)
{
schemaDifferenceFound = true;
}
}
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
if (schemaDifferenceFound)
{
schemaDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Different;
}
else
{
schemaDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Same;
}
}
}
// Find objects in second database not yet scanned
for (int i = 0; i < rightSchemaList.Count; i++)
{
if ((FindListItem(rightSchemaList[i].ToString(), leftSchemaList)) == -1)
{
// Left object missing
addedIndex = schemaDifferences.Add(new Difference(true, rightSchemaList[i].ToString(), schemaObjectType, Difference.DifferenceOutcome.Missing));
// Load the schema for the stored procs
string leftSchema = "";
string rightSchema = databaseRight.GetObjectDefinition(rightSchemaList[i].ToString());
// Check for schema differences (we know there will be lots!)
DifferenceEngine.TextMemory leftText = new DifferenceEngine.TextMemory(leftSchema);
schemaDifferences[addedIndex].LeftText = leftText;
DifferenceEngine.TextMemory rightText = new DifferenceEngine.TextMemory(rightSchema);
schemaDifferences[addedIndex].RightText = rightText;
DifferenceEngine.DiffEngine diffEngine = new DifferenceEngine.DiffEngine();
double timeTaken = diffEngine.ProcessDiff(leftText, rightText, DifferenceEngine.DiffEngineLevel.SlowPerfect);
DifferenceEngine.DiffResultSpanCollection textDifferenceList = diffEngine.DiffReport();
schemaDifferences[addedIndex].DifferenceResults = textDifferenceList;
}
}
// Sort by name
schemaDifferences = schemaDifferences.Sort("Name", SortDirection.Ascending);
FireCompareSchemaFinished(schemaObjectType);
return(schemaDifferences);
}
