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