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