public FormSelectDatabase(SqlServer sdSource)
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
// Get Data Connection and Database Context
this.sdSource = sdSource;
if (this.sdSource.Connection.State != ConnectionState.Open) throw new Exception("Database connection not yet opened.");
sOldDatabase = this.sdSource.Connection.Database;
}
/// <summary>
/// Compare tables
/// </summary>
public TableDifferenceCollection CompareTables(SqlServer databaseLeft, SqlServer databaseRight)
{
ArrayList leftTableList, rightTableList;
ArrayList leftColumnList, rightColumnList;
TableDifferenceCollection tableDifferences = new TableDifferenceCollection();
// Read first database tables
//ShowInfoMessage(String.Format("Reading available tables on {0}..{1}...", databaseLeft.Connection.DataSource, databaseLeft.Connection.Database));
leftTableList = databaseLeft.GetTables();
// Read second database tables
//ShowInfoMessage(String.Format("Reading available tables on {0}..{1}...", databaseRight.Connection.DataSource, databaseRight.Connection.Database));
rightTableList = databaseRight.GetTables();
// Fire event
int numberObjects = leftTableList.Count;
FireCompareSchemaStarted(Difference.DatabaseObjectType.Table, numberObjects);
// Compare databases
//ShowInfoMessage(String.Format("Found a total of {0} tables ({1} + {2})", leftTableList.Count + rightTableList.Count, leftTableList.Count, rightTableList.Count));
//ShowInfoMessage("Comparing tables and columns...");
// Compare first database against the second
for(int i=0; i<leftTableList.Count; i++)
{
// Find existing and missing tables
if (FindListItem(leftTableList[i].ToString(), rightTableList)==-1)
{
// Right table missing
tableDifferences.Add(new TableDifference(false, leftTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Missing));
}
else
{
bool fieldDifferenceFound = false;
// Add a default table difference item
int addedIndex = tableDifferences.Add(new TableDifference(false, leftTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Unknown));
// Load columns
leftColumnList = databaseLeft.GetColumns(leftTableList[i].ToString());
rightColumnList = databaseRight.GetColumns(rightTableList[rightTableList.IndexOf(leftTableList[i])].ToString());
// Check for different columns from left-table
for (int j=0; j<leftColumnList.Count; j++)
{
// Check if the left-table column is in the right-table
if (FindListItem(leftColumnList[j].ToString(), rightColumnList)==-1)
{
// Right field missing
tableDifferences[addedIndex].FieldDifferences.Add(new Difference(false, leftColumnList[j].ToString(), Difference.DatabaseObjectType.Field, Difference.DifferenceOutcome.Missing));
fieldDifferenceFound=true;
}
}
// Check for different columns from right-table
for (int j=0; j<rightColumnList.Count; j++)
{
// Check if the left-table column is in the right-table
if (FindListItem(rightColumnList[j].ToString(), leftColumnList)==-1)
{
// Right field missing
tableDifferences[addedIndex].FieldDifferences.Add(new Difference(false, rightColumnList[j].ToString(), Difference.DatabaseObjectType.Field, Difference.DifferenceOutcome.Missing));
fieldDifferenceFound=true;
}
}
// Check if we found any column differences
if (!fieldDifferenceFound)
{
tableDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Same;
}
else
{
tableDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Different;
}
}
}
// Find tables in second database not yet scanned
for(int i=0; i<rightTableList.Count; i++)
{
if ((FindListItem(rightTableList[i].ToString(), leftTableList))==-1)
{
// Left table missing
tableDifferences.Add(new TableDifference(true, rightTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Missing));
}
}
// Sort by table name
tableDifferences = tableDifferences.Sort("Name", SortDirection.Ascending);
FireCompareSchemaFinished(Difference.DatabaseObjectType.Table);
return tableDifferences;
}
/// <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 tables
/// </summary>
public TableDifferenceCollection CompareTables(SqlServer databaseLeft, SqlServer databaseRight)
{
ArrayList leftTableList, rightTableList;
ArrayList leftColumnList, rightColumnList;
TableDifferenceCollection tableDifferences = new TableDifferenceCollection();
// Read first database tables
//ShowInfoMessage(String.Format("Reading available tables on {0}..{1}...", databaseLeft.Connection.DataSource, databaseLeft.Connection.Database));
leftTableList = databaseLeft.GetTables();
// Read second database tables
//ShowInfoMessage(String.Format("Reading available tables on {0}..{1}...", databaseRight.Connection.DataSource, databaseRight.Connection.Database));
rightTableList = databaseRight.GetTables();
// Fire event
int numberObjects = leftTableList.Count;
FireCompareSchemaStarted(Difference.DatabaseObjectType.Table, numberObjects);
// Compare databases
//ShowInfoMessage(String.Format("Found a total of {0} tables ({1} + {2})", leftTableList.Count + rightTableList.Count, leftTableList.Count, rightTableList.Count));
//ShowInfoMessage("Comparing tables and columns...");
// Compare first database against the second
for (int i = 0; i < leftTableList.Count; i++)
{
// Find existing and missing tables
if (FindListItem(leftTableList[i].ToString(), rightTableList) == -1)
{
// Right table missing
tableDifferences.Add(new TableDifference(false, leftTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Missing));
}
else
{
bool fieldDifferenceFound = false;
// Add a default table difference item
int addedIndex = tableDifferences.Add(new TableDifference(false, leftTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Unknown));
// Load columns
leftColumnList = databaseLeft.GetColumns(leftTableList[i].ToString());
rightColumnList = databaseRight.GetColumns(rightTableList[rightTableList.IndexOf(leftTableList[i])].ToString());
// Check for different columns from left-table
for (int j = 0; j < leftColumnList.Count; j++)
{
// Check if the left-table column is in the right-table
if (FindListItem(leftColumnList[j].ToString(), rightColumnList) == -1)
{
// Right field missing
tableDifferences[addedIndex].FieldDifferences.Add(new Difference(false, leftColumnList[j].ToString(), Difference.DatabaseObjectType.Field, Difference.DifferenceOutcome.Missing));
fieldDifferenceFound = true;
}
}
// Check for different columns from right-table
for (int j = 0; j < rightColumnList.Count; j++)
{
// Check if the left-table column is in the right-table
if (FindListItem(rightColumnList[j].ToString(), leftColumnList) == -1)
{
// Right field missing
tableDifferences[addedIndex].FieldDifferences.Add(new Difference(false, rightColumnList[j].ToString(), Difference.DatabaseObjectType.Field, Difference.DifferenceOutcome.Missing));
fieldDifferenceFound = true;
}
}
// Check if we found any column differences
if (!fieldDifferenceFound)
{
tableDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Same;
}
else
{
tableDifferences[addedIndex].Outcome = Difference.DifferenceOutcome.Different;
}
}
}
// Find tables in second database not yet scanned
for (int i = 0; i < rightTableList.Count; i++)
{
if ((FindListItem(rightTableList[i].ToString(), leftTableList)) == -1)
{
// Left table missing
tableDifferences.Add(new TableDifference(true, rightTableList[i].ToString(), Difference.DatabaseObjectType.Table, Difference.DifferenceOutcome.Missing));
}
}
// Sort by table name
tableDifferences = tableDifferences.Sort("Name", SortDirection.Ascending);
FireCompareSchemaFinished(Difference.DatabaseObjectType.Table);
return(tableDifferences);
}
/// <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>
/// Initialize Main Form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FormMain_Load(object sender, System.EventArgs e)
{
Text = Application.ProductName + " v " + Application.ProductVersion;
pbDB1.Image = pbDB2.Image = ilMisc.Images[0];
pbUser1.Image = pbUser2.Image = ilMisc.Images[1];
pbTable1.Image = pbTable2.Image = ilMisc.Images[2];
Utils.ReadPersistentFormData(this);
ResizeColumns(lvTables);
ResizeColumns(lvViews);
ResizeColumns(lvProcedures);
ResizeColumns(lvFunctions);
ResizeColumns(lvTriggers);
_databaseLeft = new SqlServer();
_databaseLeft.SetLogger(new DaBCoS.Engine.LogFunction(ShowInfoMessage));
_databaseRight = new SqlServer();
_databaseRight.SetLogger(new DaBCoS.Engine.LogFunction(ShowInfoMessage));
ShowInfoMessage(Application.ProductName + " v " + Application.ProductVersion + " (c) 2012 " + Application.CompanyName);
ShowInfoMessage("This application is released under the GNU GPL license.");
//ShowInfoMessage("Visit http://sourceforge.net/projects/dabcos to have more info and support.");
}
/// <summary>
/// Connect to SQL Server.
/// </summary>
/// <remarks>A login window dialog will appear automatically</remarks>
/// <returns></returns>
private bool ConnectSQLServer(SqlServer dbTarget)
{
bool bResult = false;
try
{
wndLoginInfo = new FormLogin();
if (wndLoginInfo.ShowDialog(this) == DialogResult.OK)
{
wndLoginInfo.Hide();
this.Refresh();
dbTarget.Server = wndLoginInfo.Server;
dbTarget.Login = wndLoginInfo.Login;
dbTarget.Password = wndLoginInfo.Password;
dbTarget.AuthenticationType = wndLoginInfo.AuthMode;
dbTarget.Connect();
if (dbTarget.Connected)
{
UpdateToolbarButtons(dbTarget.Connection);
if (dbTarget == _databaseLeft)
{
lblServerFirst.Text = dbTarget.Server;
lblUserFirst.Text = dbTarget.Login;
lblDBFirst.Text = dbTarget.Connection.Database.ToString();
lblSource1.Text = String.Format("Source 1: {0}", dbTarget.VersionName);
}
else
{
lblServerSecond.Text = dbTarget.Server;
lblUserSecond.Text = dbTarget.Login;
lblDBSecond.Text = dbTarget.Connection.Database.ToString();
lblSource2.Text = String.Format("Source 2: {0}", dbTarget.VersionName);
}
bResult = true;
}
else
{
UpdateToolbarButtons(dbTarget.Connection);
ShowInfoMessage("Connection failed.");
}
}
}
catch (Exception eGeneral)
{
UpdateToolbarButtons(dbTarget.Connection);
ShowInfoMessage("Connection failed");
MessageBox.Show(this, eGeneral.Message, "General error during connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
wndLoginInfo.Dispose();
}
return bResult;
}
/// <summary>
/// List a let select a database
/// </summary>
/// <param name="scSource">Source connection</param>
private void ListDatabase(SqlServer sdSource)
{
ShowInfoMessage(String.Format("Reading available databases on {0}...", sdSource.Connection.DataSource));
if (sdSource.Connection.State != ConnectionState.Open) sdSource.Connection.Open();
try
{
wndDatabaseSelection = new FormSelectDatabase(sdSource);
if (wndDatabaseSelection.ShowDialog(this) == DialogResult.OK)
{
ShowInfoMessage(string.Format("Selected database {0} on {1}.", sdSource.Connection.Database, sdSource.Connection.DataSource));
if (sdSource == _databaseLeft)
{
lblDBFirst.Text = sdSource.Connection.Database.ToString();
foreach (TabPage tp in tcMain.TabPages)
{
foreach (Object c in tp.Controls)
{
if (c is ListView)
{
(c as ListView).Columns[0].Text = sdSource.Connection.Database.ToString();
}
}
}
}
else
{
lblDBSecond.Text = sdSource.Connection.Database.ToString();
foreach (TabPage tp in tcMain.TabPages)
{
foreach (Object c in tp.Controls)
{
if (c is ListView)
{
(c as ListView).Columns[3].Text = sdSource.Connection.Database.ToString();
}
}
}
}
}
else
{
ShowInfoMessage("Action cancelled.");
}
}
finally
{
if (wndDatabaseSelection != null)
wndDatabaseSelection.Dispose();
}
}