private void SetQueryContext(SimpleQuery query)
{
_query = query;
_tableName = ObjectName.Parse(query.TableName.Split('.').Last());
_table = _schema.FindTable(_tableName);
_commandBuilder = new CommandBuilder(GetSelectClause(_tableName), _schema.SchemaProvider);
}
internal IDbCommand Create(IDbConnection connection, CommandBuilder commandBuilder)
{
var command = connection.CreateCommand();
command.CommandText = commandBuilder.Text;
PrepareCommand(commandBuilder, command);
return command;
}
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;
}
public QueryBuilder(AdoAdapter adoAdapter, int bulkIndex)
{
_adoAdapter = adoAdapter;
_bulkIndex = bulkIndex;
_schema = _adoAdapter.GetSchema();
_commandBuilder = new CommandBuilder(_schema, _bulkIndex);
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema, _commandBuilder);
}
protected QueryBuilderBase(AdoAdapter adapter, int bulkIndex, IFunctionNameConverter functionNameConverter)
{
_adoAdapter = adapter;
_bulkIndex = bulkIndex;
_schema = _adoAdapter.GetSchema();
_commandBuilder = new CommandBuilder(_schema, _bulkIndex);
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema, _commandBuilder, functionNameConverter);
}
private static void PrepareCommand(CommandBuilder commandBuilder, IDbCommand command)
{
foreach (var pair in commandBuilder.Parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = pair.Key;
parameter.Value = pair.Value;
command.Parameters.Add(parameter);
}
}
private static void PrepareCommand(CommandBuilder commandBuilder, IDbCommand command)
{
foreach (var pair in commandBuilder.Parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = pair.Key.Name;
parameter.DbType = pair.Key.DbType;
parameter.Size = pair.Key.MaxLength;
parameter.Value = FixObjectType(pair.Value);
command.Parameters.Add(parameter);
}
}
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);
}
public IEnumerable<IDictionary<string, object>> RunQuery(SimpleQuery query,
out IEnumerable<SimpleQueryClauseBase>
unhandledClauses)
{
IEnumerable<IDictionary<string, object>> result;
if (query.Clauses.OfType<WithCountClause>().Any()) return RunQueryWithCount(query, out unhandledClauses);
ICommandBuilder[] commandBuilders = GetQueryCommandBuilders(ref query, out unhandledClauses);
IDbConnection connection = _adapter.CreateConnection();
if (_adapter.ProviderSupportsCompoundStatements || commandBuilders.Length == 1)
{
var command =
new CommandBuilder(_adapter.GetSchema()).CreateCommand(
_adapter.ProviderHelper.GetCustomProvider<IDbParameterFactory>(_adapter.SchemaProvider),
commandBuilders,
connection, _adapter.AdoOptions);
if (_transaction != null)
{
command.Transaction = _transaction.DbTransaction;
result = command.ToEnumerable(_transaction.DbTransaction);
}
else
{
result = command.ToEnumerable(_adapter.CreateConnection);
}
}
else
{
result = commandBuilders.SelectMany(cb => cb.GetCommand(connection, _adapter.AdoOptions).ToEnumerable(_adapter.CreateConnection));
}
if (query.Clauses.OfType<WithClause>().Any())
{
result = new EagerLoadingEnumerable(result);
}
return result;
}
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);
}
private ICommandBuilder[] GetPagedQueryCommandBuilders(SimpleQuery query, Int32 bulkIndex, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
{
var commandBuilders = new List<ICommandBuilder>();
var unhandledClausesList = new List<SimpleQueryClauseBase>();
unhandledClauses = unhandledClausesList;
IEnumerable<SimpleQueryClauseBase> unhandledClausesForPagedQuery;
var mainCommandBuilder = new QueryBuilder(this, bulkIndex).Build(query, out unhandledClausesForPagedQuery);
unhandledClausesList.AddRange(unhandledClausesForPagedQuery);
const int maxInt = 2147483646;
var skipClause = query.Clauses.OfType<SkipClause>().FirstOrDefault() ?? new SkipClause(0);
var takeClause = query.Clauses.OfType<TakeClause>().FirstOrDefault() ?? new TakeClause(maxInt);
if (skipClause.Count != 0 || takeClause.Count != maxInt)
{
var queryPager = this.ProviderHelper.GetCustomProvider<IQueryPager>(this.ConnectionProvider);
if (queryPager == null)
{
unhandledClausesList.AddRange(query.OfType<SkipClause>());
unhandledClausesList.AddRange(query.OfType<TakeClause>());
}
var commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, skipClause.Count, takeClause.Count);
foreach (var commandText in commandTexts)
{
var commandBuilder = new CommandBuilder(commandText, this._schema, mainCommandBuilder.Parameters);
commandBuilders.Add(commandBuilder);
}
}
return commandBuilders.ToArray();
}
public IEnumerable<IEnumerable<IDictionary<string, object>>> RunQueries(SimpleQuery[] queries,
List
<
IEnumerable
<SimpleQueryClauseBase>>
unhandledClauses)
{
if (_adapter.ProviderSupportsCompoundStatements && queries.Length > 1)
{
var commandBuilders = new List<ICommandBuilder>();
for (int i = 0; i < queries.Length; i++)
{
IEnumerable<SimpleQueryClauseBase> unhandledClausesForThisQuery;
commandBuilders.AddRange(GetQueryCommandBuilders(ref queries[i], i, out unhandledClausesForThisQuery));
unhandledClauses.Add(unhandledClausesForThisQuery);
}
IDbConnection connection;
if (_transaction != null)
{
connection = _transaction.DbTransaction.Connection;
}
else
{
connection = _adapter.CreateConnection();
}
IDbCommand command =
new CommandBuilder(_adapter.GetSchema()).CreateCommand(
_adapter.ProviderHelper.GetCustomProvider<IDbParameterFactory>(_adapter.SchemaProvider),
commandBuilders.ToArray(), connection, _adapter.AdoOptions);
if (_transaction != null)
{
command.Transaction = _transaction.DbTransaction;
}
foreach (var item in command.ToEnumerables(connection))
{
yield return item.ToList();
}
}
else
{
foreach (SimpleQuery t in queries)
{
IEnumerable<SimpleQueryClauseBase> unhandledClausesForThisQuery;
yield return RunQuery(t, out unhandledClausesForThisQuery);
unhandledClauses.Add(unhandledClausesForThisQuery);
}
}
}
private void DeferPaging(ref SimpleQuery query, ICommandBuilder mainCommandBuilder, List<ICommandBuilder> commandBuilders,
List<SimpleQueryClauseBase> unhandledClausesList)
{
unhandledClausesList.AddRange(query.Clauses.OfType<SkipClause>());
unhandledClausesList.AddRange(query.Clauses.OfType<TakeClause>());
query = query.ClearSkip().ClearTake();
var commandBuilder = new CommandBuilder(mainCommandBuilder.Text, _adapter.GetSchema(),
mainCommandBuilder.Parameters);
commandBuilders.Add(commandBuilder);
}
