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