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