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