public static int getSelectedRowsCount(string connectionString, SelectQueryBuilder queryBuilder)
{
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
int totalItems;
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
string countQuery = "select count(*) " + queryWithoutSelect;
MySqlCommand cmd = new MySqlCommand(countQuery, connection);
object result = cmd.ExecuteScalar();
if (result != null)
{
totalItems = Convert.ToInt32(result);
}
else
{
totalItems = 0;
}
return totalItems;
}
finally
{
connection.Close();
}
}
public string getCrossTabSelectPartQuery()
{
string Query = "SELECT ";
// Output Distinct
//if (_distinct)
//{
// Query += "DISTINCT ";
//}
List <Column> columns = new List <Column>();
for (int i = 0; i < _groupByColumns.Count; i++)
{
columns.Add((Column)_groupByColumns.ElementAt(i));
}
columns.Add(_crossTabClause._col);
List <Column> summarizeCols = _crossTabResults.SummarizeColumns;
for (int i = 0; i < summarizeCols.Count; i++)
{
columns.Add((Column)summarizeCols.ElementAt(i));
}
int totalCols = _groupByColumns.Count + 1 + summarizeCols.Count;
for (int i = 0; i < totalCols; i++)
{
Column Col = (Column)columns.ElementAt(i);
Query += SelectQueryBuilder.getColumnPartQuery(Col.Name);
if (Col.AliasName == null || "".Equals(Col.AliasName.Trim()))
{
Query += " AS \"" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Col.Name.Substring(Col.Name.IndexOf('.') + 1).Replace('_', ' ')) + "\",";
}
else
{
Query += " AS \"" + Col.AliasName + "\",";
}
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
return(Query);
}
public MainGirdViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder, string CurrentDatabaseName, string sqldata)
{
this.dbColumnQuerryBuilder = QuerryBuilder;
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;
}
}
//ShowControl();
//CurrenPage = CurrenPage + 1;
//this.lblPageNumber.Content = "Page " + CurrenPage.ToString() + " Of " + rowToatlPage.ToString();
//rowToatlPage = getnumberOfPages(result.TotalIRows, result.rowPageSize);
//columnToatlPage = getnumberOfPages(result.TotalColumns, result.columnPageSize);
//if (rowToatlPage > 0)
//{
// RowCurrenPage = 1;
// getPageInfo(RowCurrenPage, result.rowPageSize, result.TotalIRows, out startRow, out EndCurrentPageRow);
// this.lblRowNumber.Content = "Row " + startRow.ToString() + " - " + EndCurrentPageRow.ToString() + " Of " + result.TotalIRows.ToString();
// this.lblPageNumber.Content = "Page " + RowCurrenPage.ToString() + " Of " + rowToatlPage.ToString();
//}
//if (columnToatlPage > 0 && result.TotalIRows != 0)
//{
// ColCurrenPage = 1;
// getPageInfo(ColCurrenPage, result.columnPageSize, result.TotalColumns, out startColumn, out EndCurrentPageColumn);
// this.lblColumnNumber.Content = "Col " + startColumn.ToString() + " - " + EndCurrentPageColumn.ToString() + " Of " + result.TotalColumns.ToString();
//}
}
}
public string getQueryPartWithoutSelect(string sortColumn, bool ascending)
{
string Query = "";
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (Table Tab in _selectedTables)
{
if (Tab is DerivedTable)
{
Query += "(" + ((DerivedTable)Tab).Query + ") " + Tab.AliasName + ",";
}
else if (Tab is Table)
{
if (Tab.SchemaName == null || Tab.SchemaName.Trim().Equals(""))
{
Query += Tab.Name + " " + Tab.AliasName + ",";
}
else
{
Query += Tab.SchemaName + "." + Tab.Name + " " + Tab.AliasName + ",";
}
}
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;
//case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;
case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;
case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
}
string toTableStr = "";
if (Clause.ToTable is DerivedTable)
{
toTableStr += "(" + ((DerivedTable)Clause.ToTable).Query + ")";
}
else if (Clause.ToTable is Table)
{
if (Clause.ToTable.SchemaName == null || Clause.ToTable.SchemaName.Trim().Equals(""))
{
toTableStr += Clause.ToTable.Name;
}
else
{
toTableStr += Clause.ToTable.SchemaName + "." + Clause.ToTable.Name;
} //toTableStr += Clause.ToTable.Name;
}
JoinString += " " + toTableStr + " " + Clause.ToTable.AliasName + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable.AliasName + '.' + "`"+Clause.FromColumn+"`", Clause.ComparisonOperator, Clause.ToTable.AliasName + '.' + "`"+Clause.ToColumn+"`");
JoinString += Clause.JoinCondition.BuildWhereStatement();
Query += JoinString + ' ';
}
}
// Check the computed column and add the joins according ly for GROUP_FIRST, GROUP_LAST etc
Query += getComputeColumnsFromClauseQuery();
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (Column Col in _groupByColumns)
{
Query += SelectQueryBuilder.getColumnPartQuery(Col.Name) + ',';
}
// Add cross Tabulation column as group by in query
if (CrossTabClause.Col != null)
{
Query += SelectQueryBuilder.getColumnPartQuery(CrossTabClause.Col.Name) + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
// Output OrderBy statement
if (_orderByStatement.Count > 0 || sortColumn != null)
{
Query += " ORDER BY ";
string OrderByClause = "";
if (sortColumn != null)
{
if (ascending)
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " ASC,";
}
else
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " DESC,";
}
}
foreach (OrderByClause Clause in _orderByStatement)
{
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " ASC,"; break;
case Sorting.Descending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " DESC,"; break;
}
}
Query += OrderByClause;
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
// Return the built query
return(Query);
}
public void SaveNewQuery(CloseableTabItem tabItem, SelectQueryBuilder QueryBulder)
{
string messageBoxText = "Do you want to save current query?";
string caption = "Current Query";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Question;
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
switch (result)
{
case MessageBoxResult.Yes:
// User pressed Yes button
//save save query
ResultViewModel rv = (ResultViewModel)this.result;
string FileName1 = System.String.Empty;
if (rv.isNew == false)
{
FileName1 = rv.directoryPath + tabItem.Header.ToString() + ".xml";
}
else
{
MainWindow mw = new MainWindow();
FileName1 = mw.OpenSaveDialog(tabItem.Name);
}
if (FileName1 != System.String.Empty)
{
XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder));
StreamWriter swriter = new StreamWriter(FileName1);
SerializerObj.Serialize(swriter, QueryBulder);
swriter.Flush();
swriter.Close();
string[] splitedArray = FileName1.Split('\\');
string tabItemHeader = splitedArray[splitedArray.Length - 1].Remove((splitedArray[splitedArray.Length - 1]).Length - 4, 4);
tabItem.Header = tabItemHeader;
//we saved the query change isModified to false
rv.isModified = false;
tabItem.labelStar.Content = "";
MessageBox.Show("query saved successfully");
/**************************/
this.result.directoryPath = FileName1.Replace(splitedArray[splitedArray.Length - 1], "");
/**************************/
}
break;
case MessageBoxResult.No:
// User pressed No button
TabControl tabControl = tabItem.Parent as TabControl;
if (tabControl != null)
tabControl.Items.Remove(tabItem);
break;
case MessageBoxResult.Cancel:
// User pressed Cancel button
break;
}
}
public static DataTable getColumnStats(string connectionString, SelectQueryBuilder queryBuilder, SQLBuilder.Clauses.Column column)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
int totalCols = 0;
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
if (queryBuilder.CrossTabClause.Col != null)
{
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
}
DataSet dataSet = new DataSet();
DataSet dataSetColumn = new DataSet();
if (queryBuilder.GroupByColumns.Count() == 0)
{
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
string countQuery = "select count(distinct " + SelectQueryBuilder.getColumnPartQuery(column) + ") " + queryWithoutSelect + ";";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(countQuery, connection);
dataAdapter.Fill(dataSet);
DataTable countQueryResults = dataSet.Tables[0];
foreach (DataRow row in countQueryResults.Rows)
{
Int64 totalRowCount = Convert.ToInt64(row[countQueryResults.Columns[0]]);
if (totalRowCount > 100)
{
throw new Exception("Too many vlaues...");
}
}
string selectPartQuery = queryBuilder.getSelectPartQuery(0, -1, out totalCols);
//string finalQuery = "select distinct " + SelectQueryBuilder.getColumnPartQuery(column) + " " + queryWithoutSelect + ";";
string finalQuery = "select " + SelectQueryBuilder.getColumnPartQuery(column) + ", count(*) as Count " + queryWithoutSelect
+ " group by " + SelectQueryBuilder.getColumnPartQuery(column)
+ " order by " + SelectQueryBuilder.getColumnPartQuery(column) + ";";
dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSetColumn);
}
else
{
string finalQuery = "select distinct " + SelectQueryBuilder.getColumnPartQuery(column) + " from (" + queryBuilder.getQueryforGroupBy(out totalCols) + ");";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
}
Console.WriteLine("Total time in query execution: " + (DateTime.Now - startTime));
return dataSetColumn.Tables[0];
}
finally
{
connection.Close();
}
}
public SelectQueryBuilder LoadSelectQueryBuilder() //made public zahed
{
SelectQueryBuilder query = new SelectQueryBuilder();
bool isSelecteTabColumnsOrGroupbyColumns = false;
if (this.SelectTabUC.lstSelectedCol.ItemsSource != null)
{
//****Geting select columns*****
//List<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((List<SQLBuilder.Clauses.Column>)this.SelectTabUC.lstSelectedCol.ItemsSource);
ObservableCollection<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((ObservableCollection<SQLBuilder.Clauses.Column>)this.SelectTabUC.lstSelectedCol.ItemsSource);
foreach (SQLBuilder.Clauses.Column Col in ListOfSelectColumn)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = Col.Name;
column.AliasName = Col.AliasName;
column.Format = Col.Format;
query.SelectColumn(column);
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
if (this.TabulationTabUC.isTabulation)
{
//group by column
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[i];
if (tg.cmbTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column.Name = tg.cmbTabulationTabGroupByColumnsName.Text;
column.Format = tg.txtTabulationTabGroupByColFormat.Text;
column.AliasName = tg.txtTabulationTabGroupByAlias.Text;
//query.SelectColumn(column);
query.AddGroupBy(column);
if (tg.cmbTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(tg.cmbTabulationTabGroupByColumnsName.Text, GetSortingEnum(tg.cmbTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[i];
if (ts.cmbTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbTabulationTypeOfSummary.Text + "(" + ts.cmbTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
else if (this.CrossTabulationTabUC.isCrossTabulation)
{
//group by column
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;
column.Format = ctg.txtCrossTabulationTabGroupByColFormat.Text;
column.AliasName = ctg.txtCrossTabulationTabGroupByAlias.Text;
query.AddGroupBy(column);
if (ctg.cmbCrossTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(ctg.cmbCrossTabulationTabGroupByColumnsName.Text, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ts.cmbCrossTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtCrossTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtCrossTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
//summary first row
SQLBuilder.Clauses.Column column1 = new SQLBuilder.Clauses.Column();
column1.Name = this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text;
if (this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex == -1)
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1);
isSelecteTabColumnsOrGroupbyColumns = true;
}
else
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1, GetSortingEnum(this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.Text));
isSelecteTabColumnsOrGroupbyColumns = true;
}
}
if (isSelecteTabColumnsOrGroupbyColumns)
{
//Geting from table
SQLBuilder.Clauses.Table fromTable = new SQLBuilder.Clauses.Table();
fromTable.Name = this.FromTabUC.cmbFromTable.Text;
fromTable.AliasName = this.FromTabUC.txtFromAlias.Text;
query.SelectFromTable(fromTable);
for (int i = 0; i < this.FromTabUC.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)this.FromTabUC.StackPanelFromTab.Children[i];
SQLBuilder.Clauses.Table joinTable = new SQLBuilder.Clauses.Table();
joinTable.Name = fs.cmbFromTabJoinTable.Text;
joinTable.AliasName = fs.txtJoinTableAlias.Text;
query.AddJoin(GetJoinType(fs.cmbFromTabJoinType.Text), joinTable, fs.cmbFromTabJoinColumns.Text, GetComparisonOpreator(fs.cmbFromTabQueryOpretor.Text), fromTable, fs.cmbFromTabFromColumns.Text);
}
//******Geting Wheree Clause********
for (int i = 0; i < this.WhereTabUC.StackPanelWhereTab.Children.Count; i++)
{
string controlType = this.WhereTabUC.StackPanelWhereTab.Children[i].GetType().ToString();
switch (controlType)
{
case "FastDB.Control.WhereTabRegularConditionControl":
WhereTabRegularConditionControl ws = (WhereTabRegularConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(ws.cmbWhereTabQueryAndOr.Text), ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabBetweenConditionControl":
WhereTabBetweenConditionControl wsb = (WhereTabBetweenConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wsb.cmbWhereTabQueryAndOr.Text), wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabInNotInConditionControl":
WhereTabInNotInConditionControl wInNotIn = (WhereTabInNotInConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wInNotIn.cmbWhereTabQueryAndOr.Text), wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabNullNotNullConditionControl":
WhereTabNullNotNullConditionControl wNullNotNull = (WhereTabNullNotNullConditionControl)this.WhereTabUC.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wNullNotNull.cmbWhereTabQueryAndOr.Text), wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
break;
}
}
}
return query;
}
public static void CreateExcelDocument(DataTable dt, SelectQueryBuilder queryBuilder, string xlsxFilePath)
{
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(xlsxFilePath, SpreadsheetDocumentType.Workbook))
{
//Access the main Workbook part, which contains all references
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
InsertValuesInSheets(dt.TableName, queryBuilder, workbookPart, dt);
workbookPart.Workbook.Save();
}
}
public static DataTable getData(string connectionString, SelectQueryBuilder queryBuilder, Int64 start, int itemCount, int startCol, int numCols, string sortColumn, bool ascending, out int totalCols)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
MySqlConnection connection = new MySqlConnection(connectionString);
DataSet dataSet = new DataSet();
connection.Open();
try
{
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
string selectPartQuery = queryBuilder.getSelectPartQuery(startCol, numCols, out totalCols);
if (queryBuilder.GroupByColumns.Count() == 0)
{
string finalQuery = selectPartQuery + " " + queryWithoutSelect + queryBuilder.getLimitRowsPartQuery(start, itemCount);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
}
else
{
string finalQuery = queryBuilder.getQueryforGroupBy(out totalCols) + ";";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
removeExtraRows(dataSet, start, itemCount);
}
Console.WriteLine("Total time in query execution: " + (DateTime.Now - startTime));
return dataSet.Tables[0];
}
finally
{
connection.Close();
}
}
public static DataTable getData(string connectionString, SelectQueryBuilder queryBuilder, Int64 start, int itemCount, int startCol, int numCols, string sortColumn, bool ascending, out Int64 totalItems, out int totalCols)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
if (queryBuilder.CrossTabClause.Col != null)
{
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
}
DataSet dataSet = new DataSet();
totalItems = 0;
if (queryBuilder.GroupByColumns.Count() == 0)
{
string selectPartQuery = queryBuilder.getSelectPartQuery(startCol, numCols, out totalCols);
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
string countQuery = "select count(*) " + queryWithoutSelect;
string finalQuery = selectPartQuery + " " + queryWithoutSelect + queryBuilder.getLimitRowsPartQuery(start, itemCount)
+ "; " + countQuery + ";";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
DataTable countQueryResults = dataSet.Tables[1];
foreach (DataRow row in countQueryResults.Rows)
{
totalItems = Convert.ToInt64(row[countQueryResults.Columns[0]]);
}
}
else
{
string finalQuery = queryBuilder.getQueryforGroupBy(out totalCols) + ";";
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
totalItems = dataSet.Tables[0].Rows.Count;
removeExtraRows(dataSet, start, itemCount);
}
Console.WriteLine("Total time in query execution: " + (DateTime.Now - startTime));
return dataSet.Tables[0];
}
finally
{
connection.Close();
}
}
public GridHeaderColumn(string ColumnName,SelectQueryBuilder queryBuilder)
{
dbColumnName = ColumnName;
}
//zahed
public SelectQueryBuilder LoadSelectQueryBuilderNew(ResultViewControl rc) //made public zahed
{
SelectQueryBuilder query = new SelectQueryBuilder();
bool isSelecteTabColumnsOrGroupbyColumns = false;
if (rc.SelectTabCntrl.lstSelectedCol.ItemsSource != null)
{
//****Geting select columns*****
ObservableCollection<SQLBuilder.Clauses.Column> ListOfSelectColumn = ((ObservableCollection<SQLBuilder.Clauses.Column>)rc.SelectTabCntrl.lstSelectedCol.ItemsSource);
query.SelectedColumns = ListOfSelectColumn.ToList();
//foreach (SQLBuilder.Clauses.Column Col in ListOfSelectColumn)
//{
// SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
// column.Name = Col.Name;
// column.AliasName = Col.AliasName;
// column.Format = Col.Format;
// query.SelectColumn(column);
//}
isSelecteTabColumnsOrGroupbyColumns = true;
}
if (rc.TabulationTabCntrl.isTabulation)
{
//group by column
for (int i = 0; i < rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[i];
if (tg.cmbTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = tg.cmbTabulationTabGroupByColumnsName.Text;
column.Format = tg.txtTabulationTabGroupByColFormat.Text;
column.AliasName = tg.txtTabulationTabGroupByAlias.Text;
query.AddGroupBy(column);
if (tg.cmbTabulationSort.SelectedIndex != -1)
{
query.AddOrderBy(tg.cmbTabulationTabGroupByColumnsName.Text, GetSortingEnum(tg.cmbTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children[i];
if (ts.cmbTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
column.Name = ts.cmbTabulationTypeOfSummary.Text + "(" + ts.cmbTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtTabulationTabSummaryColFormat.Text;
column.AliasName = ts.txtTabulationTabSummaryAlias.Text;
query.AddSummarize(column);
}
}
isSelecteTabColumnsOrGroupbyColumns = true;
}
else if (rc.CrossTabulationTabCntrl.isCrossTabulation)
{
//group by column
for (int i = 0; i < rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
//column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;
column.Name = ctg.cmbCrossTabulationTabGroupByColumnsName.Text;//((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Name;
column.Format = ctg.txtCrossTabulationTabGroupByColFormat.Text; //((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Format;
column.AliasName = ctg.txtCrossTabulationTabGroupByAlias.Text;
//query.SelectColumn(column);
query.AddGroupBy(column);
if (ctg.cmbCrossTabulationSort.SelectedIndex != -1)
{
// SQLBuilder.Clauses.OrderByClause orderBy = new SQLBuilder.Clauses.OrderByClause(tg.cmbTabulationTabGroupByColumnsName.Text,GetSortingEnum(tg.cmbTabulationSort.Text));
// modified on 11/18/11
query.AddOrderBy(ctg.cmbCrossTabulationTabGroupByColumnsName.Text, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
//query.AddOrderBy(((SQLBuilder.Clauses.Column)ctg.cmbCrossTabulationTabGroupByColumnsName.SelectionBoxItem).Name, GetSortingEnum(ctg.cmbCrossTabulationSort.Text));
}
}
}
//summarize column
for (int i = 0; i < rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex != -1)
{
SQLBuilder.Clauses.Column column = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ts.cmbCrossTabulationTabSummaryColumnsName.Text + ")";
column.Format = ts.txtCrossTabulationTabSummaryColFormat.Text;
//column.Name = ts.cmbCrossTabulationTypeOfSummary.Text + "(" + ((SQLBuilder.Clauses.Column)ts.cmbCrossTabulationTabSummaryColumnsName.SelectionBoxItem).Name + ")";
//column.Format = ((SQLBuilder.Clauses.Column)ts.cmbCrossTabulationTabSummaryColumnsName.SelectionBoxItem).Format;
column.AliasName = ts.txtCrossTabulationTabSummaryAlias.Text;
//query.SelectColumn(column);
query.AddSummarize(column);
}
}
//summary first row
SQLBuilder.Clauses.Column column1 = new SQLBuilder.Clauses.Column();
//modified on 11/18/11
column1.Name = rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text;
//column1.Name = ((SQLBuilder.Clauses.Column)this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectionBoxItem).Name;
if (rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex == -1)
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1);
isSelecteTabColumnsOrGroupbyColumns = true;
}
else
{
query.CrossTabClause = new SQLBuilder.Clauses.CrossTabulationClause(column1, GetSortingEnum(rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.Text));
isSelecteTabColumnsOrGroupbyColumns = true;
}
}
if (isSelecteTabColumnsOrGroupbyColumns)
{
//Geting from table
SQLBuilder.Clauses.Table fromTable = null;
MySQLData.Table table = this.listOfTable[rc.FromTabCntrl.cmbFromTable.SelectedIndex];
if (table is MySQLData.DerivedTable)
{
fromTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)table, rc.FromTabCntrl.txtFromAlias.Text);
}
else if (table is MySQLData.Table)
{
fromTable = new SQLBuilder.Clauses.Table(table, rc.FromTabCntrl.txtFromAlias.Text);
}
//fromTable.Name = rc.FromTabCntrl.cmbFromTable.Text;
//fromTable.AliasName = rc.FromTabCntrl.txtFromAlias.Text;
query.SelectFromTable(fromTable);
//Geting join table and join colums
for (int i = 0; i < rc.FromTabCntrl.StackPanelFromTab.Children.Count; i++)
{
FromTabStackPanelControl fs = (FromTabStackPanelControl)rc.FromTabCntrl.StackPanelFromTab.Children[i];
SQLBuilder.Clauses.Table joinTable = null;
MySQLData.Table jTable = null;
if (fs.cmbFromTabJoinTable.SelectedIndex != -1)
{
jTable = this.listOfTable[fs.cmbFromTabJoinTable.SelectedIndex];
if (jTable is MySQLData.DerivedTable)
{
joinTable = new SQLBuilder.Clauses.DerivedTable((MySQLData.DerivedTable)jTable, fs.txtJoinTableAlias.Text);
}
else if (jTable is MySQLData.Table)
{
joinTable = new SQLBuilder.Clauses.Table(jTable, fs.txtJoinTableAlias.Text);
}
//joinTable.Name = fs.cmbFromTabJoinTable.Text;
//joinTable.AliasName = fs.txtJoinTableAlias.Text;
SQLBuilder.Clauses.JoinClause joinClause = query.AddJoin(GetJoinType(fs.cmbFromTabJoinType.Text), fromTable, fs.cmbFromTabFromColumns.Text, GetComparisonOpreator(fs.cmbFromTabQueryOpretor.Text), joinTable, fs.cmbFromTabJoinColumns.Text);
for (int condIndex = 0; condIndex < fs.StackPanelFromTabMore.Children.Count; condIndex++)
{
FromTabStackPanelControlMore fsCondition = (FromTabStackPanelControlMore)fs.StackPanelFromTabMore.Children[condIndex];
joinClause.addJoinCondition(GetLogicalOpreator(fsCondition.cmbFromTabFromANDOR.Text), fromTable, fsCondition.cmbFromTabFromColumns.Text, GetComparisonOpreator(fsCondition.cmbFromTabQueryOpretor.Text), joinTable, fsCondition.cmbFromTabJoinColumns.Text);
}
}
}
//******Geting Wheree Clause********
for (int i = 0; i < rc.WhereTabCntrl.StackPanelWhereTab.Children.Count; i++)
{
string controlType = rc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().ToString();
switch (controlType)
{
case "FastDB.Control.WhereTabRegularConditionControl":
WhereTabRegularConditionControl ws = (WhereTabRegularConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(ws.cmbWhereTabQueryAndOr.Text), ws.cmbWhereTabLeftSideColumns.Text, GetComparisonOpreator(ws.cmbWhereTabQueryOpretor.Text), ws.cmbWhereTabRightSideColumns.Text, Convert.ToInt32(ws.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabBetweenConditionControl":
WhereTabBetweenConditionControl wsb = (WhereTabBetweenConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wsb.cmbWhereTabQueryAndOr.Text), wsb.cmbWhereTabBetweenColumns.Text, wsb.txtBetweenLeftValue.Text, wsb.txtBetweenRightValue.Text, Convert.ToInt32(wsb.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabInNotInConditionControl":
WhereTabInNotInConditionControl wInNotIn = (WhereTabInNotInConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wInNotIn.cmbWhereTabQueryAndOr.Text), wInNotIn.cmbWhereTabInNotInColumns.Text, GetComparisonOpreator(wInNotIn.lblInNotIn.Content.ToString()), wInNotIn.txtInNotInValue.Text, Convert.ToInt32(wInNotIn.cmbWhereTabQueryLevel.Text));
}
break;
case "FastDB.Control.WhereTabNullNotNullConditionControl":
WhereTabNullNotNullConditionControl wNullNotNull = (WhereTabNullNotNullConditionControl)rc.WhereTabCntrl.StackPanelWhereTab.Children[i];
if (i == 0)
{
query.AddWhere(SQLBuilder.Enums.LogicOperator.None, wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
else
{
query.AddWhere(GetLogicalOpreator(wNullNotNull.cmbWhereTabQueryAndOr.Text), wNullNotNull.cmbWhereTabNullNotNullColumns.Text, GetComparisonOpreator(wNullNotNull.lblNullNotNull.Content.ToString()), null, Convert.ToInt32(wNullNotNull.cmbWhereTabQueryLevel.Text));
}
break;
}
}
}
return query;
}
private void SaveNewQuery(CloseableTabItem tabItem, SelectQueryBuilder QueryBulder)
{
//prompt the user
// Configure the message box to be displayed
string messageBoxText = "Do you want to save current query?";
string caption = "Current Query";
MessageBoxButton button = MessageBoxButton.YesNoCancel;
MessageBoxImage icon = MessageBoxImage.Question;
// Display message box
MessageBoxResult result = MessageBox.Show(messageBoxText, caption, button, icon);
// Process message box results
switch (result)
{
case MessageBoxResult.Yes:
// User pressed Yes button
//save save query
ResultViewControl rc = (ResultViewControl)tabItem.Content;
//ResultViewModel rv = (ResultViewModel)rc.DataContext;
/******************/
ResultViewModel rv = rc.result;
/******************/
string FileName1 = System.String.Empty;
if (rv.isNew == false)
{
FileName1 = rv.directoryPath + tabItem.Header.ToString() + ".xml";
}
else
{
FileName1 = OpenSaveDialog(tabItem.Name);
}
if (FileName1 != System.String.Empty)
{
XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder));
StreamWriter swriter = new StreamWriter(FileName1);
SerializerObj.Serialize(swriter, QueryBulder);
swriter.Flush();
swriter.Close();
string[] splitedArray = FileName1.Split('\\');
string tabItemHeader = splitedArray[splitedArray.Length - 1].Remove((splitedArray[splitedArray.Length - 1]).Length - 4, 4);
tabItem.Header = tabItemHeader;
this.CustomQueryTxTBlk.Text = "Custom query for " + tabItemHeader;
//we saved the query change isModified to false
rv.isModified = false;
tabItem.labelStar.Content = "";
MessageBox.Show("query saved successfully");
/**************************/
rc.result.directoryPath = FileName1.Replace(splitedArray[splitedArray.Length - 1], "");
/**************************/
/******************************/
TabControl tabControl = tabItem.Parent as TabControl;
if (tabControl.Items.Count == 0)
{
this.Logo.Visibility = Visibility.Visible;
this.LogoTxt.Visibility = Visibility.Visible;
this.tabControl1.Visibility = Visibility.Hidden;
}
/******************************/
}
break;
case MessageBoxResult.No:
// User pressed No button
TabControl tabControl1 = tabItem.Parent as TabControl; //zahed
if (tabControl1 != null)
tabControl1.Items.Remove(tabItem);
/******************************/
if (tabControl1.Items.Count == 0)
{
this.Logo.Visibility = Visibility.Visible;
this.LogoTxt.Visibility = Visibility.Visible;
this.tabControl1.Visibility = Visibility.Hidden;
}
/******************************/
break;
case MessageBoxResult.Cancel:
// User pressed Cancel button
// ...
break;
}
}
private void OnTabItemSelecting(Object sender, CurrentChangingEventArgs e) //uncomment
{
ItemCollection tbc = (ItemCollection)sender;
//validate all tabs before we go to next tab
ValidateAllTabs();
TabItem ti = tabControlCustomQuery.SelectedItem as TabItem;
switch (ti.Name)
{
case "FromTabItem":
//this.CustomQueryTxTBlk.Text = "From";
break;
case "WhereTabItem":
//this.CustomQueryTxTBlk.Text = "Where";
break;
case "SelectTabItem":
//this.CustomQueryTxTBlk.Text = "Select";
// check to see From tab validated
if (this.FromTabUC.isValidated & (this.TabulationTabUC.isValidated & this.TabulationTabUC.isTabulation == false) & (this.CrossTabulationTabUC.isValidated & this.CrossTabulationTabUC.isCrossTabulation == false))
{
this.SelectTabUC.lstToSelecteColFrom.IsEnabled = true;
this.SelectTabUC.lstToSelecteColFrom.ItemsSource = GenerateListOfSelectTabColumns();
}
break;
case "TabulationTabItem":
if (this.FromTabUC.isValidated)
{
this.TabulationTabUC.StackPanelTabuLationTabGroupBy.IsEnabled = true;
this.TabulationTabUC.StackPanelTabuLationTabSummary.IsEnabled = true;
List<SQLBuilder.Clauses.Column> listOfTabulationTabColumns = GenerateListOfTabulationTabColumns();
//if first drop down is null on Tabulation means all dropdown item source is null
if (((TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[0]).cmbTabulationTabGroupByColumnsName.Items.Count == 0)
{
// loading groupby columns
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[i];
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// loading summary columns
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[i];
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
else
{
List<SQLBuilder.Clauses.Column> list1 = (List<SQLBuilder.Clauses.Column>)((TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[0]).cmbTabulationTabGroupByColumnsName.ItemsSource;
IEnumerable<SQLBuilder.Clauses.Column> difference = list1.Except(listOfTabulationTabColumns);
if (list1.SequenceEqual(listOfTabulationTabColumns) == false)
{
// Reloading groupby columns
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[i];
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// Reloading summary columns
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[i];
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
}
}
break;
case "CrossTabulationTabItem":
if (this.FromTabUC.isValidated)
{
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.IsEnabled = true;
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.IsEnabled = true;
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.IsEnabled = true;
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.IsEnabled = true;
List<SQLBuilder.Clauses.Column> listOfTabulationTabColumns = GenerateListOfTabulationTabColumns();
//if first drop down is null on Cross Tabulation means all dropdown item source is null
if (((CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[0]).cmbCrossTabulationTabGroupByColumnsName.Items.Count == 0)
{
// loading groupby columns
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
//loading summary first row means (column Name and sort) row
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = listOfTabulationTabColumns;
// loading summary columns
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
else
{
List<SQLBuilder.Clauses.Column> list1 = (List<SQLBuilder.Clauses.Column>)((CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[0]).cmbCrossTabulationTabGroupByColumnsName.ItemsSource;
IEnumerable<SQLBuilder.Clauses.Column> difference = list1.Except(listOfTabulationTabColumns);
if (list1.SequenceEqual(listOfTabulationTabColumns) == false)
{
// Reloading groupby columns
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// Reloading summary first row means (column Name and sort) row
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = listOfTabulationTabColumns;
// Reloading summary columns
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
}
}
break;
case "ActionsTabItem":
if (isAllTabValidated)
{
queryBuilder = LoadSelectQueryBuilder();
if (queryBuilder != null)
{
queryString = queryBuilder.BuildQuery();
}
XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder));
StringWriter writer = new StringWriter();
SerializerObj.Serialize(writer, queryBuilder);
this.txtQuery.Text = writer.ToString();
this.lblActionTabErrorMessage.Content = "";
}
else
{
this.txtQuery.Text = "";
this.lblActionTabErrorMessage.Content = "There is an error on one or more tab, please fix an error";
}
break;
}
}
public bool CompairQueryBuilder(SelectQueryBuilder queryBuilder1, SelectQueryBuilder queryBuilder2) //made public
{
bool isDifferenct = true;
//seralizeing old querybuider
XmlSerializer SerializerObj1 = new XmlSerializer(typeof(SelectQueryBuilder));
StringWriter writer1 = new StringWriter();
SerializerObj1.Serialize(writer1, queryBuilder1);
//seralizeing New querybuider
XmlSerializer SerializerObj2 = new XmlSerializer(typeof(SelectQueryBuilder));
StringWriter writer2 = new StringWriter();
SerializerObj2.Serialize(writer2, queryBuilder2);
if (writer1.ToString() == writer2.ToString())
{
isDifferenct = false;
}
return isDifferenct;
}
public string getQueryPartWithoutSelectForGroupBy()
{
string Query = "";
int totalCols;
string selectPart = getSelectPartQuery(0, -1, out totalCols, true, false);
Query = " FROM (" + selectPart;
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (Table Tab in _selectedTables)
{
Query += Tab.Name + " " + Tab.AliasName + ",";
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;
//case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;
case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;
case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
}
JoinString += " " + Clause.ToTable.Name + " " + Clause.ToTable.AliasName + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable.AliasName + '.' + "`" + Clause.FromColumn+"`", Clause.ComparisonOperator, Clause.ToTable.AliasName + '.' + "`"+Clause.ToColumn+"`");
JoinString += Clause.JoinCondition.BuildWhereStatement();
Query += JoinString + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
Query += ") grp ";
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (Column Col in _groupByColumns)
{
Query += "`" + Col.Name + "`" + ',';
}
// Add cross Tabulation column as group by in query
if (CrossTabClause.Col != null)
{
Query += "`" + CrossTabClause.Col.Name + "`" + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
// Output OrderBy statement
if (_orderByStatement.Count > 0)
{
Query += " ORDER BY ";
string OrderByClause = "";
foreach (OrderByClause Clause in _orderByStatement)
{
string orderbyFiledStr;
if (_selectedColumns.Count > 0 && _groupByColumns.Count > 0)
{
orderbyFiledStr = "`" + Clause.FieldName + "`";
}
else
{
orderbyFiledStr = SelectQueryBuilder.getColumnPartQuery(Clause.FieldName);
}
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause += orderbyFiledStr + " ASC,"; break;
case Sorting.Descending:
OrderByClause += orderbyFiledStr + " DESC,"; break;
}
}
Query += OrderByClause;
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
// Return the built query
return(Query);
}
public MainGirdViewControl(SQLBuilder.SelectQueryBuilder QuerryBuilder, string CurrentDatabaseName, string sqldata)
{
this.dbColumnQuerryBuilder = QuerryBuilder;
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;
}
}
//ShowControl();
//CurrenPage = CurrenPage + 1;
//this.lblPageNumber.Content = "Page " + CurrenPage.ToString() + " Of " + rowToatlPage.ToString();
//rowToatlPage = getnumberOfPages(result.TotalIRows, result.rowPageSize);
//columnToatlPage = getnumberOfPages(result.TotalColumns, result.columnPageSize);
//if (rowToatlPage > 0)
//{
// RowCurrenPage = 1;
// getPageInfo(RowCurrenPage, result.rowPageSize, result.TotalIRows, out startRow, out EndCurrentPageRow);
// this.lblRowNumber.Content = "Row " + startRow.ToString() + " - " + EndCurrentPageRow.ToString() + " Of " + result.TotalIRows.ToString();
// this.lblPageNumber.Content = "Page " + RowCurrenPage.ToString() + " Of " + rowToatlPage.ToString();
//}
//if (columnToatlPage > 0 && result.TotalIRows != 0)
//{
// ColCurrenPage = 1;
// getPageInfo(ColCurrenPage, result.columnPageSize, result.TotalColumns, out startColumn, out EndCurrentPageColumn);
// this.lblColumnNumber.Content = "Col " + startColumn.ToString() + " - " + EndCurrentPageColumn.ToString() + " Of " + result.TotalColumns.ToString();
//}
}
}
public static string getQuery(SelectQueryBuilder queryBuilder)
{
string finalQuery;
string queryWithoutSelect = queryBuilder.getQueryPartWithoutSelect();
int totalCols = 0;
string selectPartQuery = queryBuilder.getSelectPartQuery(0, -1, out totalCols);
if (queryBuilder.GroupByColumns.Count() == 0)
{
finalQuery = selectPartQuery + " " + queryWithoutSelect;
}
else
{
finalQuery = queryBuilder.getQueryforGroupBy(out totalCols) + ";";
}
return finalQuery;
}
public static void InsertValuesInSheets(string sheetName, SelectQueryBuilder queryBuilder, WorkbookPart workbookPart, DataTable table)
{
WorksheetPart worksheetPart = null;
if (!string.IsNullOrEmpty(sheetName))
{
Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
}
else
{
worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
}
if (worksheetPart != null)
{
SheetData data = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
worksheetPart.Worksheet.Save();
}
}
public static CrossTabResults getCrossTabulationData(string connectionString, SelectQueryBuilder queryBuilder, out Int64 totalItems, out int totalCols)
{
DateTime startTime = DateTime.Now;
if (String.IsNullOrEmpty(connectionString))
throw new ArgumentNullException(connectionString);
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
try
{
queryBuilder.setSelectedColumns_CrossTabulation(connectionString);
DataSet dataSet = new DataSet();
totalItems = 0;
CrossTabResults crossTabResults = queryBuilder.CrossTabulationResults;
if (queryBuilder.GroupByColumns.Count() == 0)
{
throw new Exception("Calling wrong method!!!");
}
else
{
string finalQuery = queryBuilder.getQueryforGroupBy(out totalCols);
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(finalQuery, connection);
dataAdapter.Fill(dataSet);
totalItems = dataSet.Tables[0].Rows.Count;
}
crossTabResults.Results = dataSet.Tables[0];
List<string> columnTypes = new List<string>();
for (int colIndex = 0; colIndex < dataSet.Tables[0].Columns.Count; colIndex++)
{
columnTypes.Add(dataSet.Tables[0].Columns[colIndex].DataType.FullName);
}
DataView dataView = crossTabResults.Results.DefaultView;
Dictionary<string, Object> dataMap = new Dictionary<string, Object>();
List<List<string>> groupByColumnValueList = new List<List<string>>();
List<string> keyPrefixes = new List<string>();
List<string> groupByColumnValue = null;
List<string> prevGroupByColumnValue = null;
List<string> crossTabColumnValues = new List<string>();
string key = "";
for (int rowIndex = 0; rowIndex < dataView.Count; rowIndex++)
{
groupByColumnValue = new List<string>();
int colIndex = 0;
for (; colIndex < crossTabResults.GroupByColumns.Count; colIndex++)
{
string value = dataView[rowIndex].Row[colIndex].ToString();
groupByColumnValue.Add(value);
}
if (prevGroupByColumnValue == null || !groupByColumnValue.SequenceEqual(prevGroupByColumnValue))
{
prevGroupByColumnValue = groupByColumnValue;
groupByColumnValueList.Add(groupByColumnValue);
key = "";
for (int i = 0; i < groupByColumnValue.Count; i++)
{
key += groupByColumnValue.ElementAt<string>(i);
}
key += "~";
keyPrefixes.Add(key);
}
string crossTabColValue = dataView[rowIndex].Row[colIndex].ToString();
if (!crossTabColumnValues.Contains(crossTabColValue))
{
crossTabColumnValues.Add(crossTabColValue);
}
colIndex++; //skip the cross tabulation column
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
Object value = dataView[rowIndex].Row[i + colIndex];
dataMap.Add(key + crossTabColValue + i, value); // key = append all group by column + cross Tab Column + summarize column Index
}
}
if (prevGroupByColumnValue == null || !groupByColumnValue.SequenceEqual(prevGroupByColumnValue))
{
groupByColumnValueList.Add(groupByColumnValue);
}
SQLBuilder.Clauses.CrossTabulationClause crossTabClause = queryBuilder.CrossTabClause;
if (crossTabClause.SortSet)
{
crossTabColumnValues.Sort();
if (crossTabClause.SortOrder == Sorting.Descending)
{
crossTabColumnValues.Reverse();
}
}
crossTabResults.CrossTabColumnVaues = crossTabColumnValues;
totalCols = crossTabResults.GroupByColumns.Count + (crossTabResults.SummarizeColumns.Count * (crossTabResults.CrossTabColumnVaues.Count + 1));
// Populate missing values and Totals
int summarizColStartIndex = crossTabResults.GroupByColumns.Count + 1; // +1 for cross tabulation column (ehich is also part of groupby column in final query
for (int keyIndex = 0; keyIndex < keyPrefixes.Count; keyIndex++)
{
string keyPrefix = keyPrefixes.ElementAt<string>(keyIndex);
decimal[] totals = new decimal[crossTabResults.SummarizeColumns.Count];
for (int crosTabColValueIndex = 0; crosTabColValueIndex < crossTabResults.CrossTabColumnVaues.Count; crosTabColValueIndex++)
{
string crossTabColValue = crossTabResults.CrossTabColumnVaues.ElementAt<string>(crosTabColValueIndex);
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
string keyValue = keyPrefix + crossTabColValue + i;
string valueType = columnTypes.ElementAt<string>(summarizColStartIndex + i);
SQLBuilder.Clauses.Column summarizeColumn = crossTabResults.SummarizeColumns.ElementAt<SQLBuilder.Clauses.Column>(i);
if (dataMap.ContainsKey(keyValue))
{
//Add column values
Object value = dataMap[keyValue];
if (!(value == null || "".Equals(value.ToString().Trim())))
{
totals[i] = summarize(summarizeColumn.Name.Substring(0, summarizeColumn.Name.IndexOf('(')), totals[i], decimal.Parse(value.ToString()));
}
}
else
{
Object zeroValueObject = getZeroValueObject(valueType);
dataMap.Add(keyValue, zeroValueObject);
}
}
}
for (int i = 0; i < crossTabResults.SummarizeColumns.Count; i++)
{
dataMap.Add(keyPrefix + "Grand Total" + i, getValueObject(totals[i], columnTypes[summarizColStartIndex + i]));
}
}
crossTabResults.DataMap = dataMap;
crossTabResults.GroupByColumnValueList = groupByColumnValueList;
crossTabResults.KeyPrefixes = keyPrefixes;
Console.WriteLine("Total time in Cross Tabulation execution: " + (DateTime.Now - startTime));
return crossTabResults;
}
finally
{
connection.Close();
}
}