public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = Controller.StringToValue(SecondArgument(arguments)); DbParameter p2 = CreateParameter(command); p2.Value = Controller.StringToValue(ThirdArgument(arguments)); if (expressions.ContainsKey("_DataView_RowFilter_")) return String.Format("{0} >= {1} and {0} <= {2}", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName); else return String.Format("{0} between {1} and {2}", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName); }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { StringBuilder sb = new StringBuilder(); int currentYear = DateTime.Today.Year; for (int i = (currentYear - _startYear); (i <= (currentYear + _endYear)); i++) { DbParameter p = CreateParameter(command); DbParameter p2 = CreateParameter(command); DateTime startDate; DateTime endDate; AssignRange(i, out startDate, out endDate); p.Value = startDate; p2.Value = endDate; if (sb.Length > 0) sb.Append("or"); if (expressions.ContainsKey("_DataView_RowFilter_")) sb.AppendFormat("({0} >= {1} and {0} <= {2})", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName); else sb.AppendFormat("({0} between {1} and {2})", expressions[FirstArgument(arguments)], p.ParameterName, p2.ParameterName); } return sb.ToString(); }
protected virtual void AppendFilterExpressionsToWhere(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, string whereClause) { string quickFindHint = page.QuickFindHint; bool firstCriteria = String.IsNullOrEmpty(_viewFilter); if (!(firstCriteria)) { EnsureWhereKeyword(sb); sb.AppendLine("("); sb.Append(ProcessViewFilter(page, command, expressions)); } int matchListCount = 0; bool firstDoNotMatch = true; string logicalConcat = "and "; if (page.Filter != null) foreach (string filterExpression in page.Filter) { Match matchingMode = MatchingModeRegex.Match(filterExpression); if (matchingMode.Success) { bool doNotMatch = (matchingMode.Groups["Match"].Value == "_donotmatch_"); if (doNotMatch) { if (firstDoNotMatch) { firstDoNotMatch = false; EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.AppendLine(")"); if (matchListCount > 0) sb.AppendLine(")"); if (!(firstCriteria) || (matchListCount > 0)) sb.AppendLine("and"); matchListCount = 0; sb.AppendLine(" not"); firstCriteria = true; } } if (matchListCount == 0) { EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.Append(") and"); // the list of matches begins sb.AppendLine("("); } else { sb.AppendLine(")"); sb.AppendLine("or"); } // begin a list of conditions for the next match if (matchingMode.Groups["Scope"].Value == "$all$") logicalConcat = " and "; else logicalConcat = " or "; matchListCount++; firstCriteria = true; } Match filterMatch = FilterExpressionRegex.Match(filterExpression); if (filterMatch.Success) { // "ProductName:?g", "CategoryCategoryName:=Condiments\x00=Seafood" bool firstValue = true; string fieldOperator = " or "; if (Regex.IsMatch(filterMatch.Groups["Values"].Value, ">|<")) fieldOperator = " and "; Match valueMatch = FilterValueRegex.Match(filterMatch.Groups["Values"].Value); while (valueMatch.Success) { string alias = filterMatch.Groups["Alias"].Value; string operation = valueMatch.Groups["Operation"].Value; string paramValue = valueMatch.Groups["Value"].Value; if ((operation == "~") && (alias == "_quickfind_")) alias = page.Fields[0].Name; bool deepSearching = alias.Contains(","); DataField field = page.FindField(alias); if (((((field != null) && field.AllowQBE) || (operation == "~")) && (((page.DistinctValueFieldName != field.Name || (matchListCount > 0)) || (operation == "~")) || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name)))) || deepSearching) { if (firstValue) { if (firstCriteria) { EnsureWhereKeyword(sb); sb.AppendLine("("); firstCriteria = false; } else sb.Append(logicalConcat); sb.Append("("); firstValue = false; } else sb.Append(fieldOperator); if (deepSearching) { string deepSearchFieldName = alias.Substring(0, alias.IndexOf(',')); string hint = alias.Substring((deepSearchFieldName.Length + 1)); string deepFilterExpression = (deepSearchFieldName + filterExpression.Substring(filterExpression.IndexOf(':'))); AppendDeepFilter(hint, page, command, sb, deepFilterExpression); } else if (operation == "~") { paramValue = Convert.ToString(StringToValue(paramValue)); List<string> words = new List<string>(); List<List<string>> phrases = new List<List<string>>(); phrases.Add(words); CultureInfo currentCulture = CultureInfo.CurrentCulture; string textDateNumber = ("\\p{L}\\d" + Regex.Escape((currentCulture.DateTimeFormat.DateSeparator + (currentCulture.DateTimeFormat.TimeSeparator + currentCulture.NumberFormat.NumberDecimalSeparator)))); string[] removableNumericCharacters = new string[] { currentCulture.NumberFormat.NumberGroupSeparator, currentCulture.NumberFormat.CurrencyGroupSeparator, currentCulture.NumberFormat.CurrencySymbol}; Match m = Regex.Match(paramValue, String.Format("\\s*(?\'Token\'((?\'Quote\'\")(?\'Value\'.+?)\")|((?\'Quote\'\\\')(?\'Value\'.+?)\\\')|(,|;|(^|\\s+" + ")-)|(?\'Value\'[{0}]+))", textDateNumber)); bool negativeSample = false; while (m.Success) { string token = m.Groups["Token"].Value.Trim(); if ((token == ",") || (token == ";")) { words = new List<string>(); phrases.Add(words); negativeSample = false; } else if (token == "-") negativeSample = true; else { string exactFlag = "="; if (String.IsNullOrEmpty(m.Groups["Quote"].Value)) exactFlag = " "; string negativeFlag = " "; if (negativeSample) { negativeFlag = "-"; negativeSample = false; } words.Add(String.Format("{0}{1}{2}", negativeFlag, exactFlag, m.Groups["Value"].Value)); } m = m.NextMatch(); } bool firstPhrase = true; foreach (List<String> phrase in phrases) if (phrase.Count > 0) { if (firstPhrase) firstPhrase = false; else sb.AppendLine("or"); sb.AppendLine("("); bool firstWord = true; System.DateTime paramValueAsDate; foreach (string paramValueWord in phrase) { bool negativeFlag = (paramValueWord[0] == '-'); bool exactFlag = (paramValueWord[1] == '='); string comparisonOperator = "like"; if (exactFlag) comparisonOperator = "="; string pv = paramValueWord.Substring(2); bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate); bool firstTry = true; DbParameter parameter = null; if (!(paramValueIsDate)) pv = SqlStatement.EscapePattern(command, pv); double paramValueAsNumber; string testNumber = pv; foreach (string s in removableNumericCharacters) testNumber = testNumber.Replace(s, string.Empty); bool paramValueIsNumber = double.TryParse(testNumber, out paramValueAsNumber); if (!(exactFlag) && !(pv.Contains("%"))) pv = String.Format("%{0}%", pv); if (firstWord) firstWord = false; else sb.Append("and"); if (negativeFlag) sb.Append(" not"); sb.Append("("); bool hasTests = false; DbParameter originalParameter = null; if (String.IsNullOrEmpty(quickFindHint) || !(quickFindHint.StartsWith(";"))) foreach (DataField tf in page.Fields) if ((tf.AllowQBE && String.IsNullOrEmpty(tf.AliasName)) && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate))) { hasTests = true; if ((parameter == null) || command.GetType().FullName.Contains("ManagedDataAccess")) { parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv; if (exactFlag && paramValueIsNumber) { parameter.DbType = DbType.Double; parameter.Value = paramValueAsNumber; } } if (!((exactFlag && ((!(tf.Type.Contains("String")) && !(paramValueIsNumber)) || (tf.Type.Contains("String") && paramValueIsNumber))))) { if (firstTry) firstTry = false; else sb.Append(" or "); if (tf.Type.StartsWith("Date")) { DbParameter dateParameter = command.CreateParameter(); dateParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); dateParameter.DbType = DbType.DateTime; command.Parameters.Add(dateParameter); dateParameter.Value = paramValueAsDate; if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName); } else { bool skipLike = false; if (!((comparisonOperator == "=")) && ((tf.Type == "String") && ((tf.Len > 0) && (tf.Len < pv.Length)))) { string pv2 = pv; pv2 = pv2.Substring(1); if (tf.Len < pv2.Length) pv2 = pv2.Substring(0, (pv2.Length - 1)); if (pv2.Length > tf.Len) skipLike = true; else { originalParameter = parameter; parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv2; } } if (_serverRules.EnableResultSet) { string fieldNameExpression = expressions[tf.ExpressionName()]; if ((tf.Type != "String" && !(exactFlag)) || (tf.Type == "Boolean")) fieldNameExpression = String.Format("convert({0}, \'System.String\')", fieldNameExpression); if (negativeFlag) sb.AppendFormat("({0} is not null)and", fieldNameExpression); sb.AppendFormat("({0} {2} {1})", fieldNameExpression, parameter.ParameterName, comparisonOperator); } else if (skipLike) sb.Append("1=0"); else { if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); if (DatabaseEngineIs(command, "Oracle", "DB2", "Firebird")) { sb.AppendFormat("(upper({0}) {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); parameter.Value = Convert.ToString(parameter.Value).ToUpper(); } else sb.AppendFormat("({0} {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); } } } if (originalParameter != null) { parameter = originalParameter; originalParameter = null; } } if (!(String.IsNullOrEmpty(quickFindHint)) && quickFindHint.Contains(";")) { sb.AppendLine(); if (hasTests) sb.AppendLine("or"); else hasTests = true; sb.AppendLine("("); bool firstHint = true; foreach (string hint in quickFindHint.Substring((quickFindHint.IndexOf(';') + 1)).Split(new char[] { ';'})) { if (firstHint) firstHint = false; else { sb.AppendLine(); sb.AppendLine("or"); } sb.AppendLine("("); string newFilterExpression = filterExpression; StringBuilder reversedFilterExpression = new StringBuilder(); if (negativeFlag) { bool firstExpressionPhrase = true; foreach (List<string> ph in phrases) { if (firstExpressionPhrase) firstExpressionPhrase = false; else reversedFilterExpression.Append(","); bool firstExpressionWord = true; foreach (string w in ph) { if (firstExpressionWord) firstExpressionWord = false; else reversedFilterExpression.Append(" "); if (!((w[0] == '-'))) reversedFilterExpression.Append("-"); if (w[1] == '=') reversedFilterExpression.Append("\""); reversedFilterExpression.Append(w.Substring(2)); if (w[1] == '=') reversedFilterExpression.Append("\""); } } newFilterExpression = ("_quickfind_:~" + ValueToString(reversedFilterExpression.ToString())); } AppendDeepFilter(hint, page, command, sb, newFilterExpression); sb.AppendLine(")"); } sb.AppendLine(")"); } if (!(hasTests)) if (negativeFlag && quickFindHint.StartsWith(";")) sb.Append("1=1"); else sb.Append("1=0"); sb.Append(")"); } sb.AppendLine(")"); } if (firstPhrase) sb.Append("1=1"); } else if (operation.StartsWith("$")) sb.Append(FilterFunctions.Replace(command, expressions, String.Format("{0}({1}$comma${2})", operation.TrimEnd('$'), alias, Convert.ToBase64String(Encoding.UTF8.GetBytes(paramValue))))); else { DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterDbType(parameter, field.Type); sb.Append(expressions[field.ExpressionName()]); bool requiresRangeAdjustment = ((operation == "=") && (field.Type.StartsWith("DateTime") && !(StringIsNull(paramValue)))); if ((operation == "<>") && StringIsNull(paramValue)) sb.Append(" is not null "); else if ((operation == "=") && StringIsNull(paramValue)) sb.Append(" is null "); else { if (operation == "*") { sb.Append(" like "); parameter.DbType = DbType.String; if (!(paramValue.Contains("%"))) paramValue = (SqlStatement.EscapePattern(command, paramValue) + "%"); } else if (requiresRangeAdjustment) sb.Append(">="); else sb.Append(operation); try { parameter.Value = StringToValue(field, paramValue); if ((parameter.DbType == DbType.Binary) && (parameter.Value is Guid)) parameter.Value = ((Guid)(parameter.Value)).ToByteArray(); } catch (Exception ) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName); command.Parameters.Add(parameter); if (requiresRangeAdjustment) { DbParameter rangeParameter = command.CreateParameter(); AssignParameterDbType(rangeParameter, field.Type); rangeParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); sb.Append(String.Format(" and {0} < {1}", expressions[field.ExpressionName()], rangeParameter.ParameterName)); if (field.Type == "DateTimeOffset") { DateTime dt = Convert.ToDateTime(parameter.Value); parameter.Value = new DateTimeOffset(dt).AddHours(-14); rangeParameter.Value = new DateTimeOffset(dt).AddDays(1).AddHours(14); } else rangeParameter.Value = Convert.ToDateTime(parameter.Value).AddDays(1); command.Parameters.Add(rangeParameter); } } } } valueMatch = valueMatch.NextMatch(); } if (!(firstValue)) sb.AppendLine(")"); } } if (matchListCount > 0) { sb.AppendLine(")"); // the end of the "match" list sb.AppendLine(")"); firstCriteria = true; } if (!(firstCriteria)) { sb.AppendLine(")"); if (!(String.IsNullOrEmpty(whereClause))) sb.Append("and "); } if (!(String.IsNullOrEmpty(whereClause))) { sb.AppendLine("("); sb.AppendLine(whereClause); sb.AppendLine(")"); } }
private void AppendSelectExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, bool firstField) { foreach (DataField field in page.Fields) { if (firstField) firstField = false; else sb.Append(","); try { if (field.OnDemand) sb.Append(String.Format("case when {0} is not null then 1 else null end as ", expressions[field.ExpressionName()])); else sb.Append(expressions[field.ExpressionName()]); } catch (Exception ) { throw new Exception(String.Format("Unknown data field \'{0}\'.", field.Name)); } sb.Append(" \""); sb.Append(field.Name); sb.AppendLine("\""); } }
private void AppendOrderByExpression(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string orderByClause) { if (String.IsNullOrEmpty(page.SortExpression)) page.SortExpression = _view.GetAttribute("sortExpression", String.Empty); bool hasOrderBy = false; sb.Append("order by "); if (String.IsNullOrEmpty(page.SortExpression)) { if (!(String.IsNullOrEmpty(orderByClause))) { sb.Append(orderByClause); hasOrderBy = true; } } else { bool firstSortField = true; Match orderByMatch = Regex.Match(page.SortExpression, "\\s*(?\'Alias\'[\\s\\w]+?)\\s*(?\'Order\'\\s(ASC|DESC))?\\s*(,|$)", RegexOptions.IgnoreCase); while (orderByMatch.Success) { if (firstSortField) firstSortField = false; else sb.Append(","); string fieldName = orderByMatch.Groups["Alias"].Value; if (fieldName.EndsWith("_Mirror")) fieldName = fieldName.Substring(0, (fieldName.Length - 7)); sb.Append(expressions[fieldName]); sb.Append(" "); sb.Append(orderByMatch.Groups["Order"].Value); orderByMatch = orderByMatch.NextMatch(); hasOrderBy = true; } } bool firstKey = !(hasOrderBy); foreach (DataField field in page.Fields) if (field.IsPrimaryKey) { if (firstKey) firstKey = false; else sb.Append(","); sb.Append(expressions[field.ExpressionName()]); } if (firstKey) sb.Append(expressions[page.Fields[0].ExpressionName()]); }
void AppendAggregateExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions) { bool firstField = true; foreach (DataField field in page.Fields) { if (firstField) firstField = false; else sb.Append(","); if (field.Aggregate == DataFieldAggregate.None) sb.Append("null "); else { string functionName = field.Aggregate.ToString(); if (functionName == "Average") functionName = "Avg"; string fmt = "{0}({1})"; if (functionName == "Count") fmt = "{0}(distinct {1})"; sb.AppendFormat(fmt, functionName, expressions[field.ExpressionName()]); } sb.Append(" \""); sb.Append(field.Name); sb.AppendLine("\""); } }
protected virtual bool ConfigureCTE(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, bool performCount) { if (!(RequiresHierarchy(page))) return false; // detect hierarchy DataField primaryKeyField = null; DataField parentField = null; DataField sortField = null; string sortOrder = "asc"; string hierarchyOrganization = HierarchyOrganizationFieldName; foreach (DataField field in page.Fields) { if (field.IsPrimaryKey) primaryKeyField = field; if (field.IsTagged("hierarchy-parent")) parentField = field; else if (field.IsTagged("hierarchy-organization")) hierarchyOrganization = field.Name; } if (parentField == null) return false; // select a hierarchy sort field if (sortField == null) { if (!(String.IsNullOrEmpty(page.SortExpression))) { Match sortExpression = Regex.Match(page.SortExpression, "(?\'FieldName\'\\w+)(\\s+(?\'SortOrder\'asc|desc)?)", RegexOptions.IgnoreCase); if (sortExpression.Success) foreach (DataField field in page.Fields) if (field.Name == sortExpression.Groups["FieldName"].Value) { sortField = field; sortOrder = sortExpression.Groups["SortOrder"].Value; break; } } if (sortField == null) foreach (DataField field in page.Fields) if (!(field.Hidden)) { sortField = field; break; } } if (sortField == null) sortField = page.Fields[0]; // append a hierarchical CTE bool isOracle = DatabaseEngineIs(command, "Oracle"); sb.AppendLine("),"); sb.AppendLine("h__("); bool first = true; foreach (DataField field in page.Fields) { if (first) first = false; else sb.Append(","); sb.AppendFormat("{0}{1}{2}", _leftQuote, field.Name, _rightQuote); sb.AppendLine(); } sb.AppendFormat(",{0}{1}{2}", _leftQuote, hierarchyOrganization, _rightQuote); sb.AppendLine(")as("); // top-level of self-referring CTE sb.AppendLine("select"); first = true; foreach (DataField field in page.Fields) { if (first) first = false; else sb.Append(","); sb.AppendFormat("h1__.{0}{1}{2}", _leftQuote, field.Name, _rightQuote); sb.AppendLine(); } // add top-level hierarchy organization field if (isOracle) sb.AppendFormat(",lpad(cast(row_number() over (partition by h1__.{0}{1}{2} order by h1__.{0}{3}{2}" + " {4}) as varchar(5)), 5, \'0\') as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization); else sb.AppendFormat(",cast(right(\'0000\' + cast(row_number() over (partition by h1__.{0}{1}{2} order by" + " h1__.{0}{3}{2} {4}) as varchar), 4) as varchar) as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization); // add top-level "from" clause sb.AppendLine(); sb.AppendFormat("from page_cte__ h1__ where h1__.{0}{1}{2} is null ", _leftQuote, parentField.Name, _rightQuote); sb.AppendLine(); sb.AppendLine("union all"); // sublevel of self-referring CTE sb.AppendLine("select"); first = true; foreach (DataField field in page.Fields) { if (first) first = false; else sb.Append(","); sb.AppendFormat("h2__.{0}{1}{2}", _leftQuote, field.Name, _rightQuote); sb.AppendLine(); } // add sublevel hierarchy organization field if (isOracle) sb.AppendFormat(",h__.{0}{5}{2} || \'/\' || lpad(cast(row_number() over (partition by h2__.{0}{1}{2}" + " order by h2__.{0}{3}{2} {4}) as varchar(5)), 5, \'0\') as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization); else sb.AppendFormat(",convert(varchar, h__.{0}{5}{2} + \'/\' + cast(right(\'0000\' + cast(row_number() ove" + "r (partition by h2__.{0}{1}{2} order by h2__.{0}{3}{2} {4}) as varchar), 4) as v" + "archar)) as {0}{5}{2}", _leftQuote, parentField.Name, _rightQuote, sortField.Name, sortOrder, hierarchyOrganization); sb.AppendLine(); // add sublevel "from" clause sb.AppendFormat("from page_cte__ h2__ inner join h__ on h2__.{0}{1}{2} = h__.{0}{3}{2}", _leftQuote, parentField.Name, _rightQuote, primaryKeyField.Name); sb.AppendLine(); sb.AppendLine("),"); sb.AppendFormat("ho__ as (select row_number() over (order by ({0}{1}{2})) as row_number__, h__.* f" + "rom h__)", _leftQuote, hierarchyOrganization, _rightQuote); if (performCount) sb.AppendLine("select count(*) from ho__"); else sb.AppendLine("select * from ho__"); sb.AppendLine(); return true; }
private void ProcessExpressionParameters(DbCommand command, SelectClauseDictionary expressions) { foreach (string fieldName in expressions.Keys) { this._currentCommand = command; string formula = expressions[fieldName]; Match m = ParamDetectionRegex.Match(formula); if (m.Success) AssignFilterParameterValue(m.Groups[3].Value); } }
private void EnsurePageFields(ViewPage page, SelectClauseDictionary expressions) { XPathNavigator statusBar = _config.SelectSingleNode("/c:dataController/c:statusBar"); if (statusBar != null) page.StatusBar = statusBar.Value; if (page.Fields.Count == 0) { XPathNodeIterator fieldIterator = _config.Select("/c:dataController/c:fields/c:field"); while (fieldIterator.MoveNext()) { string fieldName = fieldIterator.Current.GetAttribute("name", String.Empty); if (expressions.ContainsKey(fieldName)) page.Fields.Add(new DataField(fieldIterator.Current, Resolver)); } } XPathNodeIterator keyFieldIterator = _config.Select("/c:dataController/c:fields/c:field[@isPrimaryKey=\'true\' or @hidden=\'true\']"); while (keyFieldIterator.MoveNext()) { string fieldName = keyFieldIterator.Current.GetAttribute("name", String.Empty); if (!(page.ContainsField(fieldName))) page.Fields.Add(new DataField(keyFieldIterator.Current, Resolver, true)); } XPathNodeIterator aliasIterator = _view.Select(".//c:dataFields/c:dataField/@aliasFieldName", Resolver); while (aliasIterator.MoveNext()) if (!(page.ContainsField(aliasIterator.Current.Value))) { XPathNodeIterator fieldIterator = _config.Select("/c:dataController/c:fields/c:field[@name=\'{0}\']", aliasIterator.Current.Value); if (fieldIterator.MoveNext()) page.Fields.Add(new DataField(fieldIterator.Current, Resolver, true)); } XPathNodeIterator lookupFieldIterator = _config.Select("/c:dataController/c:fields/c:field[c:items/@dataController]"); while (lookupFieldIterator.MoveNext()) { string fieldName = lookupFieldIterator.Current.GetAttribute("name", String.Empty); if (!(page.ContainsField(fieldName))) page.Fields.Add(new DataField(lookupFieldIterator.Current, Resolver, true)); } int i = 0; while (i < page.Fields.Count) { DataField field = page.Fields[i]; if ((!(field.FormatOnClient) && !(String.IsNullOrEmpty(field.DataFormatString))) && !(field.IsMirror)) { page.Fields.Insert((i + 1), new DataField(field)); i = (i + 2); } else i++; } XPathNodeIterator dynamicConfigIterator = _config.Select("/c:dataController/c:fields/c:field[c:configuration!=\'\']/c:configuration|/c:dataCo" + "ntroller/c:fields/c:field/c:items[@copy!=\'\']/@copy"); while (dynamicConfigIterator.MoveNext()) { Match dynamicConfig = Regex.Match(dynamicConfigIterator.Current.Value, "(\\w+)=(\\w+)"); while (dynamicConfig.Success) { int groupIndex = 2; if (dynamicConfigIterator.Current.Name == "copy") groupIndex = 1; if (!(page.ContainsField(dynamicConfig.Groups[groupIndex].Value))) { XPathNavigator nav = _config.SelectSingleNode("/c:dataController/c:fields/c:field[@name=\'{0}\']", dynamicConfig.Groups[1].Value); if (nav != null) page.Fields.Add(new DataField(nav, Resolver, true)); } dynamicConfig = dynamicConfig.NextMatch(); } if (page.InTransaction) { XPathNodeIterator globalFieldIterator = _config.Select("/c:dataController/c:fields/c:field"); while (globalFieldIterator.MoveNext()) { string fieldName = globalFieldIterator.Current.GetAttribute("name", String.Empty); if (!(page.ContainsField(fieldName))) page.Fields.Add(new DataField(globalFieldIterator.Current, Resolver, true)); } } } foreach (DataField field in page.Fields) ConfigureDataField(page, field); }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { return String.Format("not({0})", base.ExpandWith(command, expressions, arguments)); }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { string fieldExpression = expressions[FirstArgument(arguments)]; StringBuilder sb = new StringBuilder(fieldExpression); sb.Append(" in ("); string[] list = SecondArgument(arguments).Split(new string[] { "$or$"}, StringSplitOptions.RemoveEmptyEntries); bool hasNull = false; bool hasValues = false; foreach (string v in list) if (Controller.StringIsNull(v)) hasNull = true; else { if (hasValues) sb.Append(","); else hasValues = true; DbParameter p = CreateParameter(command); p.Value = Controller.StringToValue(v); sb.Append(p.ParameterName); } sb.Append(")"); if (hasNull) if (hasValues) return String.Format("({0} is null) or ({1})", fieldExpression, sb.ToString()); else return String.Format("{0} is null", fieldExpression); else return sb.ToString(); }
public static string Replace(DbCommand command, SelectClauseDictionary expressions, string filter) { FilterFunctions functions = new FilterFunctions(command, expressions, filter); return functions.ToString(); }
public FilterFunctions(DbCommand command, SelectClauseDictionary expressions, string filter) { this._command = command; this._filter = filter; this._expressions = expressions; }
public virtual string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { return String.Empty; }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = DBNull.Value; if (PageRequest.Current != null) { string parameterName = Regex.Match(arguments, "\\w+").Value; foreach (FieldValue v in PageRequest.Current.ExternalFilter) if (v.Name.Equals(parameterName, StringComparison.InvariantCultureIgnoreCase)) { p.Value = v.Value; break; } } return p.ParameterName; }
private void ConfigureCommandForSelect(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string fromClause, string whereClause, string orderByClause, CommandConfigurationType commandConfiguration) { bool useServerPaging = ((commandConfiguration != CommandConfigurationType.SelectDistinct && !(_serverRules.EnableResultSet)) && (commandConfiguration != CommandConfigurationType.SelectAggregates && commandConfiguration != CommandConfigurationType.SelectFirstLetters)); bool useLimit = SupportsLimitInSelect(command); bool useSkip = SupportsSkipInSelect(command); if (useServerPaging) page.AcceptAllRows(); StringBuilder sb = new StringBuilder(); if (useLimit || useSkip) useServerPaging = false; bool countUsingHierarchy = false; if ((commandConfiguration == CommandConfigurationType.SelectCount) && (useServerPaging && RequiresHierarchy(page))) { countUsingHierarchy = true; commandConfiguration = CommandConfigurationType.Select; } if (commandConfiguration == CommandConfigurationType.SelectExisting) useServerPaging = false; if (commandConfiguration == CommandConfigurationType.SelectCount) sb.AppendLine("select count(*)"); else { if (useServerPaging) sb.AppendLine("with page_cte__ as ("); else if ((commandConfiguration == CommandConfigurationType.Sync) && useLimit) sb.Append("select * from (select @row_num := @row_num+1 row_number__,cte__.* from (select @r" + "ow_num:=0) r,("); sb.AppendLine("select"); if (useServerPaging) AppendRowNumberExpression(sb, page, expressions, orderByClause); if (commandConfiguration == CommandConfigurationType.SelectDistinct) { DataField distinctField = page.FindField(page.DistinctValueFieldName); string distinctExpression = expressions[distinctField.ExpressionName()]; if (distinctField.Type.StartsWith("Date")) { string commandType = command.GetType().ToString(); if (commandType == "System.Data.SqlClient.SqlCommand") distinctExpression = String.Format("DATEADD(dd, 0, DATEDIFF(dd, 0, {0}))", distinctExpression); if (commandType == "MySql.Data.MySqlClient.MySqlCommand") distinctExpression = String.Format("cast({0} as date)", distinctExpression); } sb.AppendFormat("distinct {0} \"{1}\"\r\n", distinctExpression, page.DistinctValueFieldName); } else if (commandConfiguration == CommandConfigurationType.SelectAggregates) AppendAggregateExpressions(sb, page, expressions); else if (commandConfiguration == CommandConfigurationType.SelectFirstLetters) { string substringFunction = "substring"; if (DatabaseEngineIs(command, "Oracle", "DB2")) substringFunction = "substr"; AppendFirstLetterExpressions(sb, page, expressions, substringFunction); } else { if ((commandConfiguration == CommandConfigurationType.Select) && useSkip) sb.AppendFormat(" first {0} skip {1}\r\n", page.PageSize, (page.PageSize * page.PageIndex)); if ((commandConfiguration == CommandConfigurationType.Sync) && useSkip) { // Only select the primary key. foreach (DataField field in page.Fields) if (field.IsPrimaryKey) { sb.Append(expressions[field.ExpressionName()]); break; } } else if (commandConfiguration == CommandConfigurationType.SelectExisting) sb.AppendLine("*"); else AppendSelectExpressions(sb, page, expressions, !(useServerPaging)); } } sb.AppendLine("from"); sb.AppendLine(fromClause); _hasWhere = false; if (String.IsNullOrEmpty(_viewFilter)) { _viewFilter = _view.GetAttribute("filter", String.Empty); if (String.IsNullOrEmpty(_viewFilter) && ((_viewType == "Form") && !(String.IsNullOrEmpty(page.LastView)))) { XPathNavigator lastView = _config.SelectSingleNode("/c:dataController/c:views/c:view[@id=\'{0}\']", page.LastView); if (lastView != null) _viewFilter = lastView.GetAttribute("filter", String.Empty); } } if (!(String.IsNullOrEmpty(_viewFilter))) _viewFilter = String.Format("({0})", _viewFilter); if (commandConfiguration == CommandConfigurationType.SelectExisting) { EnsureWhereKeyword(sb); sb.Append(expressions[page.InnerJoinForeignKey.ToLower()]); sb.Append("="); sb.Append(page.InnerJoinPrimaryKey); sb.AppendLine(" and "); } AppendSystemFilter(command, page, expressions); AppendAccessControlRules(command, page, expressions); if (((page.Filter != null) && (page.Filter.Length > 0)) || !(String.IsNullOrEmpty(_viewFilter))) AppendFilterExpressionsToWhere(sb, page, command, expressions, whereClause); else if (!(String.IsNullOrEmpty(whereClause))) { EnsureWhereKeyword(sb); sb.AppendLine(whereClause); } if (commandConfiguration == CommandConfigurationType.Select) { bool preFetch = RequiresPreFetching(page); if (useServerPaging) { if (!(ConfigureCTE(sb, page, command, expressions, countUsingHierarchy))) sb.Append(")\r\nselect * from page_cte__ "); if (!(countUsingHierarchy)) { sb.AppendFormat("where row_number__ > {0}PageRangeFirstRowNumber and row_number__ <= {0}PageRangeL" + "astRowNumber order by row_number__", _parameterMarker); DbParameter p = command.CreateParameter(); p.ParameterName = (_parameterMarker + "PageRangeFirstRowNumber"); p.Value = ((page.PageSize * page.PageIndex) + page.PageOffset); if (preFetch) p.Value = (((int)(p.Value)) - page.PageSize); command.Parameters.Add(p); DbParameter p2 = command.CreateParameter(); p2.ParameterName = (_parameterMarker + "PageRangeLastRowNumber"); p2.Value = ((page.PageSize * (page.PageIndex + 1)) + page.PageOffset); if (preFetch) p2.Value = (((int)(p2.Value)) + page.PageSize); command.Parameters.Add(p2); } } else { AppendOrderByExpression(sb, page, expressions, orderByClause); if (useLimit) { sb.AppendFormat("\r\nlimit {0}Limit_PageOffset, {0}Limit_PageSize", _parameterMarker); DbParameter p = command.CreateParameter(); p.ParameterName = (_parameterMarker + "Limit_PageOffset"); p.Value = ((page.PageSize * page.PageIndex) + page.PageOffset); if (preFetch && (((int)(p.Value)) > page.PageSize)) p.Value = (((int)(p.Value)) - page.PageSize); command.Parameters.Add(p); DbParameter p2 = command.CreateParameter(); p2.ParameterName = (_parameterMarker + "Limit_PageSize"); p2.Value = page.PageSize; if (preFetch) { int pagesToFetch = 2; if (((int)(p.Value)) > page.PageSize) pagesToFetch = 3; p2.Value = (page.PageSize * pagesToFetch); } command.Parameters.Add(p2); } } } else if (commandConfiguration == CommandConfigurationType.Sync) { if (useServerPaging) { if (!(ConfigureCTE(sb, page, command, expressions, false))) sb.Append(")\r\nselect * from page_cte__ "); sb.Append("where "); } else { if (useLimit || useSkip) AppendOrderByExpression(sb, page, expressions, orderByClause); if (!(useSkip)) sb.Append(") cte__)cte2__ where "); } bool first = true; if (!(useSkip)) foreach (DataField field in page.Fields) if (field.IsPrimaryKey) { if (first) first = false; else sb.AppendFormat(" and "); sb.AppendFormat("{2}{1}{3}={0}PrimaryKey_{1}", _parameterMarker, field.Name, _leftQuote, _rightQuote); } } else if ((commandConfiguration == CommandConfigurationType.SelectDistinct) || (commandConfiguration == CommandConfigurationType.SelectFirstLetters)) sb.Append("order by 1"); command.CommandText = sb.ToString(); _viewFilter = null; }
private bool ConfigureCommandForUpdate(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string tableName, FieldValue[] values) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("update {0} set ", tableName); bool firstField = true; foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && v.Modified) { sb.AppendLine(); if (firstField) firstField = false; else sb.Append(","); sb.AppendFormat(RemoveTableAliasFromExpression(expressions[v.Name])); if ((v.NewValue == null) && field.HasDefaultValue) sb.Append(String.Format("={0}", field.DefaultValue)); else { sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.NewValue); command.Parameters.Add(parameter); } } } if (firstField) return false; AppendWhereExpressions(sb, command, page, expressions, values); command.CommandText = sb.ToString(); return true; }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { string[] list = SecondArgument(arguments).Split(new string[] { "$or$"}, StringSplitOptions.RemoveEmptyEntries); string filter = String.Format("not({0})", base.ExpandWith(command, expressions, arguments)); if (Array.IndexOf(list, "null") == -1) filter = String.Format("({0}) or {1} is null", filter, expressions[FirstArgument(arguments)]); return filter; }
private SelectClauseDictionary ParseSelectExpressions(string selectClause) { SelectClauseDictionary expressions = new SelectClauseDictionary(); Match fieldMatch = SelectExpressionRegex.Match(selectClause); while (fieldMatch.Success) { string expression = fieldMatch.Groups["Expression"].Value; string fieldName = fieldMatch.Groups["FieldName"].Value; string alias = fieldMatch.Groups["Alias"].Value; if (!(String.IsNullOrEmpty(expression))) { if (String.IsNullOrEmpty(alias)) if (String.IsNullOrEmpty(fieldName)) alias = expression; else alias = fieldName; if (!(expressions.ContainsKey(alias))) expressions.Add(alias, expression); } fieldMatch = fieldMatch.NextMatch(); } return expressions; }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = String.Format(_pattern, SqlStatement.EscapePattern(command, Convert.ToString(Controller.StringToValue(SecondArgument(arguments))))); return String.Format("{0} like {1}", expressions[FirstArgument(arguments)], p.ParameterName); }
protected virtual bool ConfigureCommand(DbCommand command, ViewPage page, CommandConfigurationType commandConfiguration, FieldValue[] values) { if (page == null) page = new ViewPage(); PopulatePageFields(page); if (command == null) return true; if (command.CommandType == CommandType.Text) { Match statementMatch = SqlSelectRegex1.Match(command.CommandText); if (!(statementMatch.Success)) statementMatch = SqlSelectRegex2.Match(command.CommandText); SelectClauseDictionary expressions = _expressions; if (expressions == null) { expressions = ParseSelectExpressions(statementMatch.Groups["Select"].Value); _expressions = expressions; } EnsurePageFields(page, expressions); string commandId = _view.GetAttribute("commandId", String.Empty); bool commandIsCustom = ((_config.SelectSingleNode("/c:dataController/c:commands/c:command[@id=\'{0}\' and @custom=\'true\']", commandId) != null) || page.RequiresResultSet(commandConfiguration)); AddComputedExpressions(expressions, page, commandConfiguration, commandIsCustom); if (statementMatch.Success) { string fromClause = statementMatch.Groups["From"].Value; string whereClause = statementMatch.Groups["Where"].Value; string orderByClause = statementMatch.Groups["OrderBy"].Value; if (commandIsCustom) { fromClause = String.Format("({0}) resultset__", command.CommandText); whereClause = String.Empty; orderByClause = String.Empty; } string tableName = null; if (!(commandConfiguration.ToString().StartsWith("Select"))) tableName = ((string)(_config.Evaluate("string(/c:dataController/c:commands/c:command[@id=\'{0}\']/@tableName)", commandId))); if (String.IsNullOrEmpty(tableName)) tableName = TableNameRegex.Match(fromClause).Groups["Table"].Value; if (commandConfiguration == CommandConfigurationType.Update) return ConfigureCommandForUpdate(command, page, expressions, tableName, values); else if (commandConfiguration == CommandConfigurationType.Insert) return ConfigureCommandForInsert(command, page, expressions, tableName, values); else if (commandConfiguration == CommandConfigurationType.Delete) return ConfigureCommandForDelete(command, page, expressions, tableName, values); else { ConfigureCommandForSelect(command, page, expressions, fromClause, whereClause, orderByClause, commandConfiguration); ProcessExpressionParameters(command, expressions); } } else if ((commandConfiguration == CommandConfigurationType.Select) && YieldsSingleRow(command)) { StringBuilder sb = new StringBuilder(); sb.Append("select "); AppendSelectExpressions(sb, page, expressions, true); command.CommandText = sb.ToString(); } return commandConfiguration != CommandConfigurationType.None; } return (command.CommandType == CommandType.StoredProcedure); }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = true; return String.Format("{0}={1}", expressions[FirstArgument(arguments)], p.ParameterName); }
private void AddComputedExpressions(SelectClauseDictionary expressions, ViewPage page, CommandConfigurationType commandConfiguration, bool generateFormula) { bool useFormulaAsIs = ((commandConfiguration == CommandConfigurationType.Insert) || (commandConfiguration == CommandConfigurationType.Update)); foreach (DataField field in page.Fields) if (!(String.IsNullOrEmpty(field.Formula))) if (useFormulaAsIs) expressions[field.ExpressionName()] = field.Formula; else expressions[field.ExpressionName()] = String.Format("({0})", field.Formula); else if (generateFormula) if (useFormulaAsIs) expressions[field.ExpressionName()] = field.Name; else expressions[field.ExpressionName()] = String.Format("({0})", field.Name); }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = Membership.GetUser().ProviderUserKey; if (String.IsNullOrEmpty(arguments)) return p.ParameterName; return String.Format("{0}={1}", arguments, p.ParameterName); }
private void AppendFirstLetterExpressions(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string substringFunction) { foreach (DataField field in page.Fields) if ((!(field.Hidden) && field.AllowQBE) && (field.Type == "String")) { string fieldName = field.AliasName; if (String.IsNullOrEmpty(fieldName)) fieldName = field.Name; sb.AppendFormat("distinct {1}({0},1,1) first_letter__\r\n", expressions[fieldName], substringFunction); page.FirstLetters = fieldName; page.RemoveFromFilter(fieldName); break; } }
public override string ExpandWith(DbCommand command, SelectClauseDictionary expressions, string arguments) { DbParameter p = CreateParameter(command); p.Value = HttpContext.Current.User.Identity.Name; if (String.IsNullOrEmpty(arguments)) return p.ParameterName; return String.Format("{0}={1}", arguments, p.ParameterName); }
private void AppendRowNumberExpression(StringBuilder sb, ViewPage page, SelectClauseDictionary expressions, string orderByClause) { sb.Append("row_number() over ("); AppendOrderByExpression(sb, page, expressions, orderByClause); sb.AppendLine(") as row_number__"); }
private bool ConfigureCommandForDelete(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string tableName, FieldValue[] values) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("delete from {0}", tableName); AppendWhereExpressions(sb, command, page, expressions, values); command.CommandText = sb.ToString(); return true; }
DbDataReader ExecuteResultSetReader(ViewPage page) { if (_serverRules.ResultSet == null) return null; SelectClauseDictionary expressions = new SelectClauseDictionary(); foreach (DataColumn c in _serverRules.ResultSet.Columns) expressions[c.ColumnName] = c.ColumnName; if (page.Fields.Count == 0) { PopulatePageFields(page); EnsurePageFields(page, null); } DataView resultView = new DataView(_serverRules.ResultSet); resultView.Sort = page.SortExpression; using (DbConnection connection = CreateConnection(false)) { DbCommand command = connection.CreateCommand(); StringBuilder sb = new StringBuilder(); _resultSetParameters = command.Parameters; expressions.Add("_DataView_RowFilter_", "true"); AppendFilterExpressionsToWhere(sb, page, command, expressions, String.Empty); string filter = sb.ToString(); if (filter.StartsWith("where")) filter = filter.Substring(5); filter = Regex.Replace(filter, (Regex.Escape(_parameterMarker) + "\\w+"), DoReplaceResultSetParameter); resultView.RowFilter = filter; if (page.PageSize > 0) page.TotalRowCount = resultView.Count; } if (RequiresPreFetching(page)) page.ResetSkipCount(true); return resultView.ToTable().CreateDataReader(); }
private string ProcessViewFilter(ViewPage page, DbCommand command, SelectClauseDictionary expressions) { _currentCommand = command; _currentExpressions = expressions; string filter = Regex.Replace(_viewFilter, "/\\*Sql\\*/(?\'Sql\'[\\s\\S]+)/\\*Sql\\*/|(?\'Parameter\'(@|:)\\w+)|(?\'Other\'(\"|\'|\\[|`)\\s*\\w" + "+)|(?\'Function\'\\$\\w+\\s*\\((?\'Arguments\'[\\S\\s]*?)\\))|(?\'Name\'\\w+)", DoReplaceKnownNames); return filter; }