public ResultViewModel(SQLBuilder.SelectQueryBuilder QueryBuilder, string CurrentDatabaseName) { _getResultByQuery = true; _getResultByTreeView = false; _queryBuilder = QueryBuilder; _currentDatabaseName = CurrentDatabaseName; simpleCommand = new RelayCommand(new Action <object>(DoSimpleCommand)); RefreshResult(); }
public CrossTabulationViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder, string CurrentDatabaseName) { InitializeComponent(); result = new ResultViewModel(QuerryBuilder, CurrentDatabaseName); if (result != null) { this.DataContext = result; if (result.QueryBulder != null) { if (result.QueryBulder.SelectedTables.Count != 0) { _tableName = result.QueryBulder.SelectedTables[0].Name; } } } PopulateCrossTabulationViewControl(QuerryBuilder); }
private void PopulateCrossTabulationViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder) { //display CrossTabulation header this.lblSummaryMainColumnName.Content = "Cross Tabulation by " + QuerryBuilder.CrossTabulationResults.CrossTabColumn.AliasName; // adding gropby column control int lineNumber = 0; List <List <string> > groupByColumnValues = QuerryBuilder.CrossTabulationResults.GroupByColumnValueList; int goupbyColumValueIndex = 0; for (int groupByColIndex = 0; groupByColIndex < QuerryBuilder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++) { SQLBuilder.Clauses.Column groupByCol = QuerryBuilder.CrossTabulationResults.GroupByColumns.ElementAt <SQLBuilder.Clauses.Column>(groupByColIndex); // find colum format string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format); CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl(); ctvgCntrl.lblGroupByColumnHeader.Content = Common.getColumnNameOrAlias(groupByCol); for (int i = 0; i < groupByColumnValues.Count; i++) { Label colRow = new Label(); if ((lineNumber % 2) == 0) { colRow.Style = (Style)FindResource("CrossTabulationEvenGroupByColumRowStyle"); } else { colRow.Style = (Style)FindResource("CrossTabulationOddGroupByColumRowStyle"); } if (colFormat != null) { //formating group by col value colRow.Content = String.Format(colFormat, groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex)); } else { colRow.Content = groupByColumnValues.ElementAt <List <string> >(i).ElementAt <string>(groupByColIndex); } ctvgCntrl.StackPaenlGroupbyColumnRows.Children.Add(colRow); lineNumber = lineNumber + 1; } lineNumber = 0; this.StackPanelCrossTabulationViewGroupByControls.Children.Add(ctvgCntrl); goupbyColumValueIndex = goupbyColumValueIndex + 1; } lineNumber = 0; int groupByColCount = QuerryBuilder.CrossTabulationResults.GroupByColumns.Count; int summarrizeValueIndex = groupByColCount; // adding summary main control and summary column control // add Grand Total to CrossTabColumnVaues as las item Dictionary <string, Object> dataMap = QuerryBuilder.CrossTabulationResults.DataMap; QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues.Add("Grand Total"); foreach (string summaryMainValue in QuerryBuilder.CrossTabulationResults.CrossTabColumnVaues) { CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl(); summaryMain.lblSummaryHeader.Content = summaryMainValue; for (int summaryColIndex = 0; summaryColIndex < QuerryBuilder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++) { SQLBuilder.Clauses.Column summaryCol = QuerryBuilder.CrossTabulationResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(summaryColIndex); // find column format string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format); CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl(); ctvsCtrl.lblSummaryColumnHeader.Content = " " + Common.getColumnNameOrAlias(summaryCol) + " "; //chnageing background color for last grad total section if (summaryMainValue == "Grand Total") { summaryMain.lblSummaryHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle"); ctvsCtrl.lblSummaryColumnHeader.Style = (Style)FindResource("CrossTabulationGrandTotalHeaderColumnStyle"); } for (int keyIndex = 0; keyIndex < QuerryBuilder.CrossTabulationResults.KeyPrefixes.Count; keyIndex++) { string key = QuerryBuilder.CrossTabulationResults.KeyPrefixes.ElementAt <string>(keyIndex); Label colRow = new Label(); if ((lineNumber % 2) == 0) { if (summaryMainValue == "Grand Total") { colRow.Style = (Style)FindResource("CrossTabulationGrandTotalSummaryColumRowStyle"); } else { colRow.Style = (Style)FindResource("CrossTabulationEvenSummaryColumRowStyle"); } } else { colRow.Style = (Style)FindResource("CrossTabulationOddSummaryColumRowStyle"); } string keyValue = key + summaryMainValue + summaryColIndex; if (dataMap.ContainsKey(keyValue)) { if (summarycolFormat != null) { colRow.Content = String.Format(summarycolFormat, dataMap[keyValue]); } else { colRow.Content = dataMap[keyValue].ToString(); } } else { colRow.Content = ""; } DockPanel dp = new DockPanel(); dp.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch; dp.VerticalAlignment = System.Windows.VerticalAlignment.Stretch; dp.Children.Add(colRow); ctvsCtrl.StackPaenlSummaryColumnRows.Children.Add(dp); lineNumber = lineNumber + 1; } lineNumber = 0; summaryMain.StackPaenlSummaryMainColumns.Children.Add(ctvsCtrl); summarrizeValueIndex = summarrizeValueIndex + 1; } this.StackPanelCrossTabulationViewSummaryControls.Children.Add(summaryMain); } }
private void RefreshResult() { string connectionString = null; if (!_getResultByTreeView) { connectionString = ConfigurationManager.AppSettings["DefaultDBConn"]; } else { connectionString = ""; if (_TableViewModel._ParentSchemaName == ConfigurationManager.AppSettings["DerivedTablesPath"]) { connectionString = ConfigurationManager.AppSettings["FastDBConn"]; } else { connectionString = ConfigurationManager.AppSettings["FastDBConn"]; } _queryBuilder = new SQLBuilder.SelectQueryBuilder(); //loading query builder for first time call we have to manulay populate query builder SQLBuilder.Clauses.Table fromTable = null; if (_TableViewModel._table is MySQLData.DerivedTable) { fromTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)_TableViewModel._table, "X"); } else if (_TableViewModel._table is MySQLData.Table) { fromTable = new SQLBuilder.Clauses.Table(_TableViewModel._table, "X"); } _queryBuilder.SelectFromTable(fromTable); } bool isAddedColumnsManually = false; if (_queryBuilder.CrossTabClause.Col == null && (_queryBuilder.GroupByColumns== null || _queryBuilder.GroupByColumns.Count <= 0) && (_queryBuilder.SelectedColumns == null || _queryBuilder.SelectedColumns.Count <= 0)) { isAddedColumnsManually = true; if (_TableViewModel != null && _TableViewModel._table != null) { foreach (MySQLData.Column col in _TableViewModel._table.columns) { //load query builder select columns SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column(); column.Name = "X." + col.name; _queryBuilder.SelectColumn(column); } } else { foreach (Table Tab in _queryBuilder.SelectedTables) { string tableName = Tab.Name; List<MySQLData.Column> columns = MySQLData.DataAccess.ADODataBridge.getTableColumns(connectionString, ConfigurationManager.AppSettings["DefaultDatabase"], tableName); foreach (MySQLData.Column col in columns) { //load query builder select columns SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column(); column.Name = "X." + col.name; _queryBuilder.SelectColumn(column); } break; } } } if (_queryBuilder.CrossTabClause.Col == null) { if (_getResultByQuery) { Results = MySQLData.DataAccess.ADODataBridge.getData(connectionString, _queryBuilder, startRow, rowPageSize, startColumn, columnPageSize, sortColumn, ascending, out totalRows, out totalColumns).DefaultView; _getResultByQuery = false; } else { Results = MySQLData.DataAccess.ADODataBridge.getData(connectionString, _queryBuilder, startRow, rowPageSize, startColumn, columnPageSize, sortColumn, ascending, out totalColumns).DefaultView; } } else { Results = MySQLData.DataAccess.ADODataBridge.getCrossTabulationData(connectionString, _queryBuilder, out totalRows, out totalColumns).Results.DefaultView; } if (isAddedColumnsManually) { _queryBuilder.SelectedColumns.Clear(); } // to determine to show scroll bar if (Results.Count == totalRows) { _ShowVertcalScrollBar = false; } if (Results.Table.Columns.Count == totalColumns) { _ShowHorizontalScrollBar = false; } NotifyPropertyChanged("Results"); NotifyPropertyChanged("StartRow"); NotifyPropertyChanged("EndRow"); NotifyPropertyChanged("TotalIRows"); NotifyPropertyChanged("StartColumn"); NotifyPropertyChanged("EndColumn"); NotifyPropertyChanged("TotalColumns"); MainWindow.LatestQueryBuilder = _queryBuilder; }
public ResultViewModel(SQLBuilder.SelectQueryBuilder QueryBuilder, string CurrentDatabaseName) { _getResultByQuery = true; _getResultByTreeView = false; _queryBuilder = QueryBuilder; _currentDatabaseName = CurrentDatabaseName; simpleCommand = new RelayCommand(new Action<object>(DoSimpleCommand)); RefreshResult(); }
private void RefreshResult() { string connectionString = null; if (!_getResultByTreeView) { connectionString = ConfigurationManager.AppSettings["DefaultDBConn"]; } else { connectionString = ""; if (_TableViewModel._ParentSchemaName == ConfigurationManager.AppSettings["DerivedTablesPath"]) { connectionString = ConfigurationManager.AppSettings["FastDBConn"]; } else { connectionString = ConfigurationManager.AppSettings["FastDBConn"]; } _queryBuilder = new SQLBuilder.SelectQueryBuilder(); //loading query builder for first time call we have to manulay populate query builder SQLBuilder.Clauses.Table fromTable = null; if (_TableViewModel._table is MySQLData.DerivedTable) { fromTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)_TableViewModel._table, "X"); } else if (_TableViewModel._table is MySQLData.Table) { fromTable = new SQLBuilder.Clauses.Table(_TableViewModel._table, "X"); } _queryBuilder.SelectFromTable(fromTable); } bool isAddedColumnsManually = false; if (_queryBuilder.CrossTabClause.Col == null && (_queryBuilder.GroupByColumns == null || _queryBuilder.GroupByColumns.Count <= 0) && (_queryBuilder.SelectedColumns == null || _queryBuilder.SelectedColumns.Count <= 0)) { isAddedColumnsManually = true; if (_TableViewModel != null && _TableViewModel._table != null) { foreach (MySQLData.Column col in _TableViewModel._table.columns) { //load query builder select columns SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column(); column.Name = "X." + col.name; _queryBuilder.SelectColumn(column); } } else { foreach (Table Tab in _queryBuilder.SelectedTables) { string tableName = Tab.Name; List <MySQLData.Column> columns = MySQLData.DataAccess.ADODataBridge.getTableColumns(connectionString, ConfigurationManager.AppSettings["DefaultDatabase"], tableName); foreach (MySQLData.Column col in columns) { //load query builder select columns SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column(); column.Name = "X." + col.name; _queryBuilder.SelectColumn(column); } break; } } } if (_queryBuilder.CrossTabClause.Col == null) { if (_getResultByQuery) { Results = MySQLData.DataAccess.ADODataBridge.getData(connectionString, _queryBuilder, startRow, rowPageSize, startColumn, columnPageSize, sortColumn, ascending, out totalRows, out totalColumns).DefaultView; _getResultByQuery = false; } else { Results = MySQLData.DataAccess.ADODataBridge.getData(connectionString, _queryBuilder, startRow, rowPageSize, startColumn, columnPageSize, sortColumn, ascending, out totalColumns).DefaultView; } } else { Results = MySQLData.DataAccess.ADODataBridge.getCrossTabulationData(connectionString, _queryBuilder, out totalRows, out totalColumns).Results.DefaultView; } if (isAddedColumnsManually) { _queryBuilder.SelectedColumns.Clear(); } // to determine to show scroll bar if (Results.Count == totalRows) { _ShowVertcalScrollBar = false; } if (Results.Table.Columns.Count == totalColumns) { _ShowHorizontalScrollBar = false; } NotifyPropertyChanged("Results"); NotifyPropertyChanged("StartRow"); NotifyPropertyChanged("EndRow"); NotifyPropertyChanged("TotalIRows"); NotifyPropertyChanged("StartColumn"); NotifyPropertyChanged("EndColumn"); NotifyPropertyChanged("TotalColumns"); MainWindow.LatestQueryBuilder = _queryBuilder; }