private void FireCompareSchemaFinished(Difference.DatabaseObjectType databaseObjectType)
{
if (CompareSchemaFinished != null)
{
CompareSchemaFinished(this, databaseObjectType);
}
}
protected virtual void FireCompareSchemaStarted(Difference.DatabaseObjectType databaseObjectType, int numberObjects)
{
if (CompareSchemaStarted != null)
{
CompareSchemaStarted(this, new CompareSchemaEventArgs(databaseObjectType, numberObjects));
}
}
/// <summary>
/// Load all the tables into an array list
/// </summary>
/// <returns>An array of string containing all the tables found</returns>
public ArrayList GetObjectList(Difference.DatabaseObjectType databaseObjectType)
{
string xmlCommandName;
ArrayList alResult = new ArrayList();
switch (databaseObjectType)
{
case Difference.DatabaseObjectType.Constraint:
xmlCommandName = "qryConstraints";
break;
case Difference.DatabaseObjectType.Function:
xmlCommandName = "qryFunctions";
break;
case Difference.DatabaseObjectType.StoredProcedure:
xmlCommandName = "qryProcs";
break;
case Difference.DatabaseObjectType.Table:
xmlCommandName = "qryTables";
break;
case Difference.DatabaseObjectType.Trigger:
xmlCommandName = "qryTriggers";
break;
case Difference.DatabaseObjectType.View:
xmlCommandName = "qryViews";
break;
case Difference.DatabaseObjectType.Users:
xmlCommandName = "qryUsers";
break;
default:
throw new Exception("Unknown database object type");
}
SqlCommand sqlCmd = new SqlCommand(String.Format(GetSqlCommand(xmlCommandName), conn.Database), conn);
SqlDataReader sqlDR;
sqlDR = sqlCmd.ExecuteReader();
while (sqlDR.Read())
{
alResult.Add(sqlDR[0].ToString());
}
sqlDR.Close();
sqlCmd.Dispose();
return(alResult);
}
public CompareSchemaEventArgs(Difference.DatabaseObjectType databaseObjecType, int numberOfObjects)
{
_databaseObjectType = databaseObjecType;
_numObjects = numberOfObjects;
}
public CompareSchemaEventArgs(Difference.DatabaseObjectType databaseObjecType, int numberOfObjects)
{
_databaseObjectType = databaseObjecType;
_numObjects = numberOfObjects;
}
/// <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);
}
