private void DisplaySyncTableDetails(SyncTableConfigElement table)
{
foreach (var colDesc in tableDesc.Columns)
{
var 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
}
var 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;
}
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);
}
}
}
private void syncTablesBox_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.syncTablesBox.SelectedIndex > -1)
{
SyncScopeConfigElement selectedScope = WizardHelper.Instance.SyncConfigSection.SyncScopes.GetElementAt(this.scopeComboBox.SelectedIndex);
SyncTableConfigElement table = selectedScope.SyncTables.GetElementAt(this.syncTablesBox.SelectedIndex);
if (string.IsNullOrEmpty(table.FilterClause))
{
this.filterColTxtBox.Text = table.FilterClause;
}
this.allColsIncludedOption.Checked = table.IncludeAllColumns;
this.filterColTxtBox.Text = table.FilterClause;
}
// Allow reordering the SyncTable orders.
upBtn.Enabled = this.syncTablesBox.SelectedIndex > 0;
downBtn.Enabled = this.syncTablesBox.SelectedIndex != this.syncTablesBox.Items.Count - 1;
}
private void tablesBox_SelectedIndexChanged(object sender, EventArgs e)
{
this.filterClauseTxtBox.Text = string.Empty;
SyncTableConfigElement table = selectedScope.SyncTables.GetElement(this.tablesBox.SelectedItem.ToString());
if (table != null)
{
this.colsView.Rows.Clear();
this.filterClauseTxtBox.Text = table.FilterClause;
TargetDatabaseConfigElement db = WizardHelper.Instance.SyncConfigSection.Databases.GetElementAt(this.dbsComboBox.SelectedIndex);
tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(table.Name, new SqlConnection(db.GetConnectionString()));
// Display the list of currently selected items
this.DisplaySyncTableDetails(table);
}
else
{
colsView.Rows.Clear();
}
}
/// <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);
}
}
private void MoveTable(int currentIndex, int newIndex)
{
SyncScopeConfigElement selectedScope = WizardHelper.Instance.SyncConfigSection.SyncScopes.GetElementAt(this.scopeComboBox.SelectedIndex);
SyncTableConfigElement table = selectedScope.SyncTables.GetElementAt(currentIndex);
// Remove element from current index
selectedScope.SyncTables.RemoveAt(this.syncTablesBox.SelectedIndex);
if (newIndex == selectedScope.SyncTables.Count)
{
// It means the element should go to the end of the list. Just call add
selectedScope.SyncTables.Add(table);
newIndex = selectedScope.SyncTables.Count - 1;
}
else
{
// Re-Add the element at the new index
selectedScope.SyncTables.Add(newIndex, table);
}
// Rebind
this.ReadAndBindTablesData();
this.syncTablesBox.SelectedIndex = newIndex;
}
/// <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;
}
}
}
