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