private void DisplaySyncTableDetails(SyncTableConfigElement table)
{
foreach (DbSyncColumnDescription colDesc in tableDesc.Columns)
{
SyncColumnConfigElement colConfig = table.SyncColumns.GetElement(colDesc.UnquotedName);
if (colConfig == null)
{
colsView.Rows.Add(colDesc.UnquotedName, false, false);
}
else
{
colsView.Rows.Add(colConfig.Name, true, // IsSync
table.FilterColumns.GetElement(colConfig.Name) != null); // IsFilterCol
}
DataGridViewRow row = colsView.Rows[colsView.Rows.Count - 1];
if (colConfig != null && colConfig.IsPrimaryKey)
{
// Freeze the row if its a primary key
row.ReadOnly = true;
}
row.Cells[2].ReadOnly = colConfig == null;
}
this.filterClauseTxtBox.Text = table.FilterClause;
}
private void tablesBox_ItemCheck(object sender, ItemCheckEventArgs e)
{
if (tablesBox.SelectedIndex > -1)
{
colsView.Rows.Clear();
var tableName = tablesBox.SelectedItem.ToString();
if (e.NewValue == CheckState.Unchecked)
{
// Remove it from the SyncTables collection
selectedScope.SyncTables.Remove(tableName);
filterClauseTxtBox.Text = string.Empty;
}
else if (e.NewValue == CheckState.Checked)
{
var table = new SyncTableConfigElement();
table.Name = tableName;
table.IncludeAllColumns = true;
var db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(dbsComboBox.SelectedIndex);
statusLbl.Visible = true;
try
{
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName,
new SqlConnection(db.GetConnectionString()));
// Issue a query to get list of all tables
foreach (var col in tableDesc.Columns)
{
var colConfig = new SyncColumnConfigElement
{
Name = col.UnquotedName,
IsPrimaryKey = col.IsPrimaryKey,
IsNullable = col.IsNullable,
SqlType = col.Type
};
table.SyncColumns.Add(colConfig);
}
DisplaySyncTableDetails(table);
}
catch (SqlException exp)
{
MessageBox.Show("Error in querying database. " + exp.Message, "Target Database Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
statusLbl.Visible = false;
}
// Add it to the sync table list
selectedScope.SyncTables.Add(table);
}
}
}
/// <summary>
/// Called whenever a FilterColumn is unchecked from the UI. Removed the particular filter from the uber filter clause.
/// Does the following
/// 1. Remove from FilterColumns
/// 2. Remove from FilterParameters
/// 3. Remove text from FilterClause
/// </summary>
/// <param name="table">Table for which the filter param applies to</param>
/// <param name="colConfig">The actual column being unchecked</param>
private void RemoveFilterColumnInfo(SyncTableConfigElement table, SyncColumnConfigElement colConfig)
{
var filterParamName = "@" + WizardHelper.SanitizeName(colConfig.Name);
var andFilterClause = string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
var FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
// Remove from Filter columns
table.FilterColumns.Remove(colConfig.Name);
//Remove from Filter parameters
table.FilterParameters.Remove(filterParamName);
// Check to see if you can remove the filter clause
table.FilterClause = table.FilterClause.Replace(andFilterClause, string.Empty);
table.FilterClause = table.FilterClause.Replace(FilterClause, string.Empty);
if (table.FilterClause.StartsWith(" AND "))
{
table.FilterClause = table.FilterClause.Substring(5);
}
}
/// <summary>
/// Called whenever a checkbox from the list of columns (sync/filter) is checked or unchecked
/// If Column index is 1 then its a sync column
/// If column is removed then remove from sync columns collection. Also disable the column if it was a filter column.
/// Disable the filter col checkbox so it cannot be added as a filter without adding it as a sync column first
/// If column is enabled then add it to sync columns collection. Enable filter col checkbox so it can be clicked
/// If Column index is 2 then its a filter column.
/// If filter col is disabled then remove the column from filter params/clause/columns collection
/// If filter col is enabled then add the column to the filter params/clause/columns collection
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void colsView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
var table = selectedScope.SyncTables.GetElement(tablesBox.SelectedItem.ToString());
if (e.ColumnIndex > 0)
{
// Check to see if its a column being added/removed from sync
var cell = colsView.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell;
var col = tableDesc.Columns[e.RowIndex];
if (e.ColumnIndex == 1)
{
if (cell.Value == null || !(bool)cell.Value)
{
// Sync column unchecked
var colConfig = table.SyncColumns.GetElement(colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
table.SyncColumns.Remove(colConfig.Name);
RemoveFilterColumnInfo(table, colConfig);
colsView.Rows[e.RowIndex].Cells[2].Value = false;
// Make filter col readonly
colsView.Rows[e.RowIndex].Cells[2].ReadOnly = true;
}
}
else if (table.SyncColumns.GetElement(col.UnquotedName) == null)
{
// Sync column is checked. Add it back to the SyncColumn list
var colConfig = new SyncColumnConfigElement
{
Name = col.UnquotedName,
IsPrimaryKey = false,
IsNullable = col.IsNullable,
SqlType = col.Type
};
table.SyncColumns.Add(colConfig);
// Set the filter col to enabled.
colsView.Rows[e.RowIndex].Cells[2].ReadOnly = false;
}
table.IncludeAllColumns = table.SyncColumns.Count == colsView.Rows.Count;
}
else
{
// Its a filter column
var colConfig = table.SyncColumns.GetElement(colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
var filterParamName = "@" + WizardHelper.SanitizeName(colConfig.Name);
var andFilterClause = string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
var FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
if (cell.Value != null && !(bool)cell.Value)
{
// Filter column unchecked
RemoveFilterColumnInfo(table, colConfig);
}
else if (table.FilterColumns.GetElement(colConfig.Name) == null)
{
// Add Filter column
table.FilterColumns.Add(new FilterColumnConfigElement
{
Name = colConfig.Name
});
// Add Filter parameter
table.FilterParameters.Add(new FilterParameterConfigElement
{
Name = filterParamName,
SqlType = tableDesc.Columns[e.RowIndex].Type
});
if ((tableDesc.Columns[e.RowIndex].SizeSpecified))
{
// Set size
table.FilterParameters.GetElementAt(table.FilterParameters.Count - 1).DataSize =
int.Parse(tableDesc.Columns[e.RowIndex].Size);
}
if (string.IsNullOrEmpty(table.FilterClause))
{
table.FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
}
else
{
table.FilterClause += string.Format(AndFilterClauseFormat, colConfig.Name,
filterParamName);
}
}
}
filterClauseTxtBox.Text = table.FilterClause;
}
}
}
/// <summary>
/// Called whenever a checkbox from the list of columns (sync/filter) is checked or unchecked
/// If Column index is 1 then its a sync column
/// If column is removed then remove from sync columns collection. Also disable the column if it was a filter column.
/// Disable the filter col checkbox so it cannot be added as a filter without adding it as a sync column first
/// If column is enabled then add it to sync columns collection. Enable filter col checkbox so it can be clicked
/// If Column index is 2 then its a filter column.
/// If filter col is disabled then remove the column from filter params/clause/columns collection
/// If filter col is enabled then add the column to the filter params/clause/columns collection
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void colsView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
SyncTableConfigElement table = selectedScope.SyncTables.GetElement(this.tablesBox.SelectedItem.ToString());
if (e.ColumnIndex > 0)
{
// Check to see if its a column being added/removed from sync
DataGridViewCheckBoxCell cell = this.colsView.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell;
DbSyncColumnDescription col = tableDesc.Columns[e.RowIndex];
if (e.ColumnIndex == 1)
{
if (cell.Value == null || !(bool)cell.Value)
{
// Sync column unchecked
SyncColumnConfigElement colConfig = table.SyncColumns.GetElement(this.colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
table.SyncColumns.Remove(colConfig.Name);
this.RemoveFilterColumnInfo(table, colConfig);
this.colsView.Rows[e.RowIndex].Cells[2].Value = false;
// Make filter col readonly
this.colsView.Rows[e.RowIndex].Cells[2].ReadOnly = true;
}
}
else if (table.SyncColumns.GetElement(col.UnquotedName) == null)
{
// Sync column is checked. Add it back to the SyncColumn list
SyncColumnConfigElement colConfig = new SyncColumnConfigElement()
{
Name = col.UnquotedName,
IsPrimaryKey = false,
IsNullable = col.IsNullable,
SqlType = col.Type,
};
table.SyncColumns.Add(colConfig);
// Set the filter col to enabled.
this.colsView.Rows[e.RowIndex].Cells[2].ReadOnly = false;
}
table.IncludeAllColumns = table.SyncColumns.Count == colsView.Rows.Count;
}
else
{
// Its a filter column
SyncColumnConfigElement colConfig = table.SyncColumns.GetElement(colsView.Rows[e.RowIndex].Cells[0].Value.ToString());
if (colConfig != null)
{
string filterParamName = "@" + WizardHelper.SanitizeName(colConfig.Name);
string andFilterClause = string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
string FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
if (cell.Value != null && !(bool)cell.Value)
{
// Filter column unchecked
this.RemoveFilterColumnInfo(table, colConfig);
}
else if (table.FilterColumns.GetElement(colConfig.Name) == null)
{
// Add Filter column
table.FilterColumns.Add(new FilterColumnConfigElement()
{
Name = colConfig.Name
});
// Add Filter parameter
table.FilterParameters.Add(new FilterParameterConfigElement()
{
Name = filterParamName,
SqlType = tableDesc.Columns[e.RowIndex].Type,
});
// Fix by xperiandi, Thks !
if ((tableDesc.Columns[e.RowIndex].SizeSpecified))
{
// Set size
DbSyncColumnDescription column = tableDesc.Columns[e.RowIndex];
string columnsSize = column.Size;
if (string.Compare(columnsSize, "max", StringComparison.OrdinalIgnoreCase) == 0 &&
(string.Compare(column.Type, "nvarchar", StringComparison.OrdinalIgnoreCase) *
string.Compare(column.Type, "varchar", StringComparison.OrdinalIgnoreCase)) == 0)
{
table.FilterParameters.GetElementAt(table.FilterParameters.Count - 1).DataSize = 4000;
}
else
{
table.FilterParameters.GetElementAt(table.FilterParameters.Count - 1).DataSize = int.Parse(columnsSize);
}
}
if (string.IsNullOrEmpty(table.FilterClause))
{
table.FilterClause = string.Format(FilterClauseFormat, colConfig.Name, filterParamName);
}
else
{
table.FilterClause += string.Format(AndFilterClauseFormat, colConfig.Name, filterParamName);
}
}
}
this.filterClauseTxtBox.Text = table.FilterClause;
}
}
}
/// <summary>
/// Based on the passed in SyncScope element and the TargetDatabase element the system generates a DbSyncScopeDescription element.
/// </summary>
/// <param name="selectedConfig">SelectedConfigSection wrapper</param>
/// <returns>A DbSyncScopeDesceription object</returns>
private static DbSyncScopeDescription GetDbSyncScopeDescription(SelectedConfigSections selectedConfig)
{
DbSyncScopeDescription desc = new DbSyncScopeDescription(selectedConfig.SelectedSyncScope.Name);
using (SqlConnection conn = new SqlConnection(selectedConfig.SelectedTargetDatabase.GetConnectionString()))
{
conn.Open();
foreach (SyncTableConfigElement table in selectedConfig.SelectedSyncScope.SyncTables)
{
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, conn);
// Ensure all specified columns do belong to the table on the server.
foreach (SyncColumnConfigElement colElem in table.SyncColumns)
{
if (tableDesc.Columns.Where((e) => e.UnquotedName.Equals(colElem.Name, StringComparison.OrdinalIgnoreCase)).FirstOrDefault() == null)
{
throw new InvalidOperationException(string.Format("Table '{0}' does not have a column '{1}' defined in the target database. Please check your SyncColumn definitions",
table.Name, colElem.Name));
}
}
List <DbSyncColumnDescription> columnsToRemove = new List <DbSyncColumnDescription>();
// Mark timestamp columns for removal
columnsToRemove.AddRange(tableDesc.Columns.Where(
e => (e.Type.ToLower() == "timestamp")));
if (!table.IncludeAllColumns || table.SyncColumns.Count > 0)
{
//Users wants a subset of columns. Remove the ones they are not interested in
foreach (DbSyncColumnDescription columnDesc in tableDesc.Columns)
{
SyncColumnConfigElement configElement = table.SyncColumns.Cast <SyncColumnConfigElement>().FirstOrDefault((e) => e.Name.Equals(columnDesc.UnquotedName, StringComparison.InvariantCultureIgnoreCase));
if (configElement == null)
{
// Found a column that was not specified by the user. Remove it
columnsToRemove.Add(columnDesc);
}
else
{
columnDesc.IsNullable = configElement.IsNullable;
columnDesc.IsPrimaryKey = configElement.IsPrimaryKey;
}
}
}
// Remove columns marked for removal
columnsToRemove.ForEach(e => tableDesc.Columns.Remove(e));
// Check to see that columns count is greater than 0
if (tableDesc.Columns.Count == 0)
{
throw new InvalidOperationException(
string.Format("SyncTable '{0}' has zero SyncColumns configured for sync. Either set IncludeAllColumns to true or specify atleast one SyncColumn.", table.Name));
}
// Fill in global name
if (!string.IsNullOrEmpty(table.GlobalName))
{
tableDesc.GlobalName = table.GlobalName;
}
desc.Tables.Add(tableDesc);
}
}
return(desc);
}
