setSelectedColumns_CrossTabulation(string connectionString) { Column crossTabColumn = CrossTabClause.Col; List <Column> columns = new List <Column>(); List <Column> summarizeColumns = new List <Column>(); //add summarize columns to the list //for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) //{ // summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i)); //} _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, _summarizeColumns); CrossTabClause.SelectedColumns = columns; }
setSelectedColumns_CrossTabulation(string connectionString) { Column crossTabColumn = CrossTabClause.Col; List<Column> columns = new List<Column>(); List<Column> summarizeColumns = new List<Column>(); //add summarize columns to the list //for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) //{ // summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i)); //} _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, _summarizeColumns); CrossTabClause.SelectedColumns = columns; }
//public void setSelectedColumns_CrossTabulation(string connectionString) //{ // Column crossTabColumn = CrossTabClause.Col; // string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect(); // //_selectedTables.ElementAt<Table>(0).Name + " " +_selectedTables.ElementAt<Table>(0).AliasName; // if (CrossTabClause.SortSet) // { // if (CrossTabClause.SortOrder == Sorting.Ascending) // { // crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC"; // } // else if (CrossTabClause.SortOrder == Sorting.Descending) // { // crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC"; // } // } // crossTabColQuery += ";"; // MySqlConnection connection = new MySqlConnection(connectionString); // connection.Open(); // try // { // List<Column> columns = new List<Column>(); // //ADD groupby columns to select columns // for (int i = 0; i < _groupByColumns.Count; i++) // { // Column c = _groupByColumns.ElementAt<Column>(i); // columns.Add(c); // } // DataSet dataSet = new DataSet(); // MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection); // dataAdapter.Fill(dataSet); // DataRowCollection rows = dataSet.Tables[0].Rows; // List<string> crossTabValues = new List<string>(); // List<Column> summarizeColumns = new List<Column>(); // for (int i = 0; i <= rows.Count; i++) // { // String name = null; // if (i < rows.Count) // { // DataRow row = rows[i]; // if (row.ItemArray[0] != System.DBNull.Value) // { // //name = (string)row.ItemArray[0]; // name = "" + row.ItemArray[0]; // } // crossTabValues.Add(name); // } // for (int colIndex = 0; colIndex < _selectedColumns.Count; colIndex++) // { // Column currentCol = _selectedColumns.ElementAt<Column>(colIndex); // Column col = new Column(); // int ColNameIndex = currentCol.Name.IndexOf('(')+1; // if (ColNameIndex > 0) // { // string summuryFunction = currentCol.Name.Substring(0, ColNameIndex); // string colName = currentCol.Name.Substring(ColNameIndex, (currentCol.Name.IndexOf(')') - ColNameIndex)); // //SUM(IF(grade_mic='A-',close_bal,0)) 'A- Closing Balance', // if (i < rows.Count) // { // if (name != null) // { // name = name.Replace("'", "''"); // if (summuryFunction.ToUpper().StartsWith("COUNT")) // { // col.Name = "SUM(" + "IF(" + crossTabColumn.Name + "='" + name + "'," + "1,0))"; // } // else // { // col.Name = summuryFunction + "IF(" + crossTabColumn.Name + "='" + name + "'," + colName + ",0))"; // } // //col.AliasName = name + " " + currentCol.AliasName; // col.AliasName = currentCol.AliasName; // } // else // { // if (summuryFunction.ToUpper().StartsWith("COUNT")) // { // col.Name = "SUM(" + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + "1,0))"; // } // else // { // col.Name = summuryFunction + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + colName + ",0))"; // } // col.AliasName = "NULL " + currentCol.AliasName; // } // } // else // { // col.Name = currentCol.Name; // col.AliasName = currentCol.AliasName; // } // columns.Add(col); // } // } // } // //add summarize columns to the list // for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) // { // summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i)); // } // _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues); // CrossTabClause.SelectedColumns = columns; // //_selectedColumns.Clear(); // //_selectedColumns = columns; // } // finally // { // connection.Close(); // } //} public void setSelectedColumns_CrossTabulation_old(string connectionString) { Column crossTabColumn = CrossTabClause.Col; string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect(); if (CrossTabClause.SortSet) { if (CrossTabClause.SortOrder == Sorting.Ascending) { crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC"; } else if (CrossTabClause.SortOrder == Sorting.Descending) { crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC"; } } crossTabColQuery += ";"; MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); try { List <Column> columns = new List <Column>(); //ADD groupby columns to select columns //for (int i = 0; i < _groupByColumns.Count; i++) //{ // Column c = _groupByColumns.ElementAt<Column>(i); // columns.Add(c); //} //columns.Add(crossTabColumn); DataSet dataSet = new DataSet(); MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection); dataAdapter.Fill(dataSet); DataRowCollection rows = dataSet.Tables[0].Rows; List <string> crossTabValues = new List <string>(); List <Column> summarizeColumns = new List <Column>(); for (int i = 0; i < rows.Count; i++) { String name = null; if (i < rows.Count) { DataRow row = rows[i]; if (row.ItemArray[0] != System.DBNull.Value) { //name = (string)row.ItemArray[0]; name = "" + row.ItemArray[0]; } crossTabValues.Add(name); } } //add summarize columns to the list for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) { summarizeColumns.Add(_selectedColumns.ElementAt <Column>(i)); } _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues); CrossTabClause.SelectedColumns = columns; } finally { connection.Close(); } }
//public void setSelectedColumns_CrossTabulation(string connectionString) //{ // Column crossTabColumn = CrossTabClause.Col; // string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect(); // //_selectedTables.ElementAt<Table>(0).Name + " " +_selectedTables.ElementAt<Table>(0).AliasName; // if (CrossTabClause.SortSet) // { // if (CrossTabClause.SortOrder == Sorting.Ascending) // { // crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC"; // } // else if (CrossTabClause.SortOrder == Sorting.Descending) // { // crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC"; // } // } // crossTabColQuery += ";"; // MySqlConnection connection = new MySqlConnection(connectionString); // connection.Open(); // try // { // List<Column> columns = new List<Column>(); // //ADD groupby columns to select columns // for (int i = 0; i < _groupByColumns.Count; i++) // { // Column c = _groupByColumns.ElementAt<Column>(i); // columns.Add(c); // } // DataSet dataSet = new DataSet(); // MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection); // dataAdapter.Fill(dataSet); // DataRowCollection rows = dataSet.Tables[0].Rows; // List<string> crossTabValues = new List<string>(); // List<Column> summarizeColumns = new List<Column>(); // for (int i = 0; i <= rows.Count; i++) // { // String name = null; // if (i < rows.Count) // { // DataRow row = rows[i]; // if (row.ItemArray[0] != System.DBNull.Value) // { // //name = (string)row.ItemArray[0]; // name = "" + row.ItemArray[0]; // } // crossTabValues.Add(name); // } // for (int colIndex = 0; colIndex < _selectedColumns.Count; colIndex++) // { // Column currentCol = _selectedColumns.ElementAt<Column>(colIndex); // Column col = new Column(); // int ColNameIndex = currentCol.Name.IndexOf('(')+1; // if (ColNameIndex > 0) // { // string summuryFunction = currentCol.Name.Substring(0, ColNameIndex); // string colName = currentCol.Name.Substring(ColNameIndex, (currentCol.Name.IndexOf(')') - ColNameIndex)); // //SUM(IF(grade_mic='A-',close_bal,0)) 'A- Closing Balance', // if (i < rows.Count) // { // if (name != null) // { // name = name.Replace("'", "''"); // if (summuryFunction.ToUpper().StartsWith("COUNT")) // { // col.Name = "SUM(" + "IF(" + crossTabColumn.Name + "='" + name + "'," + "1,0))"; // } // else // { // col.Name = summuryFunction + "IF(" + crossTabColumn.Name + "='" + name + "'," + colName + ",0))"; // } // //col.AliasName = name + " " + currentCol.AliasName; // col.AliasName = currentCol.AliasName; // } // else // { // if (summuryFunction.ToUpper().StartsWith("COUNT")) // { // col.Name = "SUM(" + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + "1,0))"; // } // else // { // col.Name = summuryFunction + "IF(ISNULL(" + crossTabColumn.Name + ")" + "," + colName + ",0))"; // } // col.AliasName = "NULL " + currentCol.AliasName; // } // } // else // { // col.Name = currentCol.Name; // col.AliasName = currentCol.AliasName; // } // columns.Add(col); // } // } // } // //add summarize columns to the list // for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) // { // summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i)); // } // _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues); // CrossTabClause.SelectedColumns = columns; // //_selectedColumns.Clear(); // //_selectedColumns = columns; // } // finally // { // connection.Close(); // } //} public void setSelectedColumns_CrossTabulation_old(string connectionString) { Column crossTabColumn = CrossTabClause.Col; string crossTabColQuery = "SELECT DISTINCT " + crossTabColumn.Name + getCrossTabQueryPartWithoutSelect(); if (CrossTabClause.SortSet) { if (CrossTabClause.SortOrder == Sorting.Ascending) { crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " ASC"; } else if (CrossTabClause.SortOrder == Sorting.Descending) { crossTabColQuery += " ORDER BY " + crossTabColumn.Name + " DESC"; } } crossTabColQuery += ";"; MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); try { List<Column> columns = new List<Column>(); //ADD groupby columns to select columns //for (int i = 0; i < _groupByColumns.Count; i++) //{ // Column c = _groupByColumns.ElementAt<Column>(i); // columns.Add(c); //} //columns.Add(crossTabColumn); DataSet dataSet = new DataSet(); MySqlDataAdapter dataAdapter = new MySqlDataAdapter(crossTabColQuery, connection); dataAdapter.Fill(dataSet); DataRowCollection rows = dataSet.Tables[0].Rows; List<string> crossTabValues = new List<string>(); List<Column> summarizeColumns = new List<Column>(); for (int i = 0; i < rows.Count; i++) { String name = null; if (i < rows.Count) { DataRow row = rows[i]; if (row.ItemArray[0] != System.DBNull.Value) { //name = (string)row.ItemArray[0]; name = "" + row.ItemArray[0]; } crossTabValues.Add(name); } } //add summarize columns to the list for (int i = _groupByColumns.Count; i < _selectedColumns.Count; i++) { summarizeColumns.Add(_selectedColumns.ElementAt<Column>(i)); } _crossTabResults = new CrossTabResults(_groupByColumns, crossTabColumn, summarizeColumns, crossTabValues); CrossTabClause.SelectedColumns = columns; } finally { connection.Close(); } }