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