/// <summary>
/// Executes query against table and returns number of records satisfying this query
/// </summary>
/// <param name="TableName">Table name to run query against to</param>
/// <param name="Rules">Collection of query rules</param>
/// <returns>Number or records that satisfy select query provided</returns>
public virtual int ExecuteGetCountQuery(string TableName, List<Rule> Rules)
{
dhtmlxFieldsCollection newRequestedFields = new dhtmlxFieldsCollection();
newRequestedFields.Add(new ExpressionField("1", "dummyValue"));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, string.Format("ExecuteGetCountQuery: TableName: {0}, RequesteFields: {1}, Rules: {2}", TableName, Tools.Join(newRequestedFields, ","), Tools.Join(Rules, ",")));
#endif
#endregion
string selectQuery = this.CreateSelectQuery(TableName, newRequestedFields, Rules, null, 0, 0);
int count = Convert.ToInt32(this.ExecuteScalar("SELECT COUNT(*) FROM (" + selectQuery + ") TBL"));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Result: " + count.ToString());
#endif
#endregion
return count;
}
/// <summary>
/// Converts columns/fields stored in SQL query into Field objects collection
/// </summary>
/// <param name="Fields">Database engine specific representation of columns (e.g. "CustomerName, CustomerID, CreatedDate as RegisterDate")</param>
/// <returns>Collection of Field objects that represent given fields list</returns>
protected virtual IEnumerable<Field> ParseFields(string Fields)
{
string[] FieldsArray = Regex.Split(Fields, ",", RegexOptions.IgnoreCase);
dhtmlxFieldsCollection ResultFieldsCollection = new dhtmlxFieldsCollection();
foreach (string Field in FieldsArray)
{
if (Field.Trim() != String.Empty)
ResultFieldsCollection.Add(this.ParseField(Field));
}
return ResultFieldsCollection;
}
/// <summary>
/// Parses SQL query into format used by connectors and DataRequest
/// </summary>
/// <param name="sqlQuery">Query to parse</param>
/// <param name="TableName">TableName extracted from SQL query</param>
/// <param name="RequestedFields">Column names extracted from SQL query</param>
/// <param name="Rules">Collection of WHERE statements extracted from SQL query</param>
/// <param name="OrderBy">Collection of ORDER BY statements extracted from SQL query</param>
/// <param name="StartIndex">Row index, starting form which query result will be returned</param>
/// <param name="Count">Number of rows to be returned from query result</param>
public void ParseSqlQuery(string sqlQuery, out string TableName, out IEnumerable<Field> RequestedFields, out List<Rule> Rules, out List<OrderByStatement> OrderBy, out int StartIndex, out int Count)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing query: " + sqlQuery);
#endif
#endregion
//init variables
TableName = null;
RequestedFields = new dhtmlxFieldsCollection();
Rules = new List<Rule>();
OrderBy = new List<OrderByStatement>();
StartIndex = 0;
Count = Int32.MaxValue;
//regexp expressions for target query elements extraction
string removeEverythingButFieldsRegex = "(^SELECT)|([ \n]+((TOP)|(LIMIT))[\n ,0-9]*)|((FROM).*$)";
string removeEverythingButOrderRegex = "^.*(ORDER BY).";
string removeEverythingButWhereRegex = "((SELECT).*(WHERE))|(((ORDER BY)|(GROUP BY)|(HAVING)).*$)";
string removeEverythingButTableRegex = "(^.*(FROM))|(((WHERE)|(ORDER BY)|(GROUP BY)|(HAVING)).*$)";
//parse fields
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing fields by: " + removeEverythingButFieldsRegex);
#endif
#endregion
string fieldsOnly = Regex.Replace(sqlQuery, removeEverythingButFieldsRegex, "", RegexOptions.IgnoreCase);
RequestedFields = this.ParseFields(fieldsOnly);
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Fields are: " + Tools.Join(RequestedFields, ","));
#endif
#endregion
//parse table name
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing TableName by: " + removeEverythingButTableRegex);
#endif
#endregion
TableName = Regex.Replace(sqlQuery, removeEverythingButTableRegex, "", RegexOptions.IgnoreCase);
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Table name is: " + TableName);
#endif
#endregion
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing whereStatements by: " + removeEverythingButWhereRegex);
#endif
#endregion
string whereStatements = sqlQuery.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) == -1 ? "" : Regex.Replace(sqlQuery, removeEverythingButWhereRegex, "", RegexOptions.IgnoreCase);
Rules = this.ParseRules(whereStatements);
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Where statements are: " + Tools.Join(Rules, " AND "));
#endif
#endregion
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing OrderBy statements by " + removeEverythingButOrderRegex);
#endif
#endregion
string orderByStatements = sqlQuery.IndexOf(" ORDER ", StringComparison.OrdinalIgnoreCase) == -1 ? "" : Regex.Replace(sqlQuery, removeEverythingButOrderRegex, "", RegexOptions.IgnoreCase);
OrderBy = this.ParseOrders(orderByStatements);
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Order is: " + Tools.Join(OrderBy, ", "));
#endif
#endregion
}
/// <summary>
/// Initializes TableName, RequestedFields, OrderBy, Rules, PrimaryKeyField from SQL query and PrimaryKey column name
/// </summary>
/// <param name="SqlQuery">SQL Query to be used for initialization</param>
/// <param name="PrimaryKey">Column name to be used for PrimaryKeyField initialization</param>
private void InitializeFromSql(string SqlQuery, string PrimaryKey)
{
IEnumerable<Field> parsedFields = null;
this.Adapter.ParseSqlQuery(SqlQuery, out this._TableName, out parsedFields, out this._Rules, out this._OrderBy, out this._StartIndex, out this._Count);
this._RequestedFields = parsedFields.ToFieldsCollection();
if (!string.IsNullOrEmpty(PrimaryKey))
this.PrimaryKeyField = this.Adapter.ParseField(PrimaryKey);
}
