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); }