public ICommandBuilder Build(SimpleQuery query)
{
var commandBuilder = new CommandBuilder(GetSelectClause(ObjectName.Parse(query.TableName)), _schema.SchemaProvider);
if (query.Criteria != null)
{
commandBuilder.Append(" WHERE " + new ExpressionFormatter(commandBuilder, _schema).Format(query.Criteria));
}
if (query.Order != null)
{
var orderNames = query.Order.Select(q => q.Reference.GetName() + (q.Direction == OrderByDirection.Descending ? " DESC" : string.Empty));
commandBuilder.Append(" ORDER BY " + string.Join(", ", orderNames));
}
if (query.SkipCount != null || query.TakeCount != null)
{
var queryPager = _adoAdapter.ProviderHelper.GetCustomProvider<IQueryPager>(_adoAdapter.ConnectionProvider);
if (queryPager == null)
{
throw new NotSupportedException("Paging is not supported by the current ADO provider.");
}
var skipTemplate = commandBuilder.AddParameter("skip", DbType.Int32, query.SkipCount ?? 0);
var takeTemplate = commandBuilder.AddParameter("take", DbType.Int32, query.TakeCount ?? int.MaxValue);
commandBuilder.SetText(queryPager.ApplyPaging(commandBuilder.Text, skipTemplate.Name, takeTemplate.Name));
}
return commandBuilder;
}
private string CreateWhereInStatement(SimpleExpression criteria, Table table)
{
var inClauseBuilder = new CommandBuilder(_schema);
var keyColumn = table.FindColumn(table.PrimaryKey[0]);
inClauseBuilder.Append(string.Format("SELECT {0} FROM {1}",
keyColumn.QualifiedName, table.QualifiedName));
inClauseBuilder.Append(" ");
inClauseBuilder.Append(string.Join(" ",
new Joiner(JoinType.Inner, _schema).GetJoinClauses(
new ObjectName(table.Schema, table.ActualName), criteria)));
inClauseBuilder.Append(" where ");
inClauseBuilder.Append(_expressionFormatter.Format(criteria));
return string.Format("{0} IN ({1})", keyColumn.QualifiedName, inClauseBuilder.Text);
}
private string CreateWhereExistsStatement(SimpleExpression criteria, Table table)
{
var inClauseBuilder = new CommandBuilder(_schema);
inClauseBuilder.Append(string.Join(" ",
new Joiner(JoinType.Inner, _schema).GetJoinClauses(
new ObjectName(table.Schema, table.ActualName), criteria)));
inClauseBuilder.Append(" where ");
inClauseBuilder.Append(_expressionFormatter.Format(criteria));
var updateJoin = _schema.QuoteObjectName("_updatejoin");
var whereClause = new StringBuilder(string.Format("SELECT 1 FROM {0} [_updatejoin] ", table.QualifiedName));
whereClause.Append(inClauseBuilder.Text.Replace(table.QualifiedName, updateJoin));
whereClause.Append(" AND (");
bool appendAnd = false;
foreach (var column in table.PrimaryKey.AsEnumerable().Select(table.FindColumn))
{
if (appendAnd) whereClause.Append(" AND ");
whereClause.AppendFormat("{0}.{1} = {2}", updateJoin, column.QuotedName, column.QualifiedName);
appendAnd = true;
}
whereClause.Append(")");
return string.Format("EXISTS ({0})", whereClause);
}