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