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