/// <summary>
        /// Creates and executes query using the given ConnectionProvider.
        /// </summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (stored procedure or text).</param>
        /// <param name="cmdTimeout">Set timeout for specified command.</param>
        /// <param name="dataTableName">Name of the datatable that will be returned. May be null.</param>
        /// <returns>DataTable containing retrieved data.</returns>
        public static DataTable ExecuteQuery(IConnectionProvider conn, string cmdText, DbParameterCollection parameters, CommandType cmdType, string dataTableName, int cmdTimeout)
        {
            IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType, cmdTimeout);
            cmd.Connection = conn.Connection;
            IDataAdapterBuilder builder = DbmsComponentFactory.GetComponent<IDataAdapterBuilder>(conn.DBMS);
            IDataAdapter adapter = builder.BuildAdapter(cmd);
            bool connIsOpennedLocally = EnsureOpenConnection(conn);
            try
            {
                AssignExistingPendingTransactionToCommand(conn, cmd);

                // Execute query.
                DataSet data = new DataSet();
                data.Locale = CultureInfo.InvariantCulture;
                adapter.Fill(data);
                PopulateOutputParameterValues(parameters, cmd);

                DataTable table = (data.Tables.Count > 0)
                    ? data.Tables[0]
                    : new DataTable() { Locale = CultureInfo.InvariantCulture };
                if (!string.IsNullOrEmpty(dataTableName))
                    table.TableName = dataTableName;

                return table;
            }
            finally
            {
                CloseConnectionIfLocal(conn, connIsOpennedLocally);
                cmd.Dispose();
                IDisposable disposableAdapter = adapter as IDisposable;
                if (disposableAdapter != null)
                    disposableAdapter.Dispose();
            }
        }

        /// <summary>
        /// Renders INSERT statement and code that retrieves the new ID.
        /// </summary>
        /// <param name="insert">INSERT statement that is being rendered.</param>
        /// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
        /// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
        /// <param name="output">StringBuilder to which the SQL code is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        /// <returns>Ouput parameter that will contain the value retrieved by RETURNING clause.</returns>
        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Renders INSERT statements for DBMSs that support an auto-identity fields.
            // Auto-id field may or may not be in the column-value list.
            // If auto-incremented field is in the column-value list it will be skipped.
            // Table may have only one auto-identity field.
            // Method expects that all errors have been identified and processed in the caller.
            // Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
            int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);

            AppendInsertIntoTableName(insert, dbms, output);
            if (numberOfFieldsToRender > 0)
            {
                AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
                AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
            }
            else
            {
                AppendDefaultValuesExpression(output);
            }

            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            DbParameter autoId = null;
            if (autoIdField != null)
            {
                // RETURNING id
                output.Append(" RETURNING ");
                autoIdField.RenderColumnName(dbms, output);
                autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
                parameters.Add(autoId);
            }

            // Return auto-id DB parameter. Callers require it to retrieve the new ID value.
            return autoId;
        }

        private static void RenderSingleQuery(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Either TOP or ORDER BY is used; but not both.

            // SELECT ... FROM ...
            // WHERE (ROWNUM <= @top) AND (@this.filter)
            // GROUP BY ... HAVING ... ORDER BY ...
            AppendSelectAndDistinctKeyWords(select, output);
            AppendSelectList(select, dbms, output, parameters);
            AppendFromClause(select, dbms, output);

            // WHERE. We have made sure that if TOP is used then ORDER BY is not used.
            if (select.Top > 0)
            {
                // WHERE ROWNUM <= this.top AND (this.filter).
                output.Append(" WHERE ROWNUM <= " + select.Top.ToString(CultureInfo.InvariantCulture));

                if (select.Where != null && !select.Where.IsEmpty)
                {
                    output.Append(" AND (");
                    select.Where.Render(dbms, output, parameters);
                    output.Append(")");
                }
            }
            else
            {
                AppenWhereWoRownum(select, dbms, output, parameters);
            }

            AppendGroupByClause(select, dbms, output, parameters);
            AppendHavingClause(select, dbms, output, parameters);
            AppendOrderByClause(select, dbms, output, parameters);
        }        

        /// <summary>
        /// Renders INSERT statement.
        /// </summary>
        /// <param name="insert">INSERT statement that is being rendered.</param>
        /// <param name="nextSequence">Ignored. SQLite doesn't use sequences.</param>
        /// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
        /// <param name="output">StringBuilder to which the SQL code is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        /// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Renders INSERT statements for DBMSs that support an auto-identity fields.
            // Auto-id field may or may not be in the column-value list.
            // If auto-incremented field is in the column-value list it will be skipped.
            // Table may have only one auto-identity field.
            // Method expects that all errors have been identified and processed in the caller.
            // Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
            int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);

            AppendInsertIntoTableName(insert, dbms, output);
            if (numberOfFieldsToRender > 0)
            {
                AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
                AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
            }
            else
            {
                AppendDefaultValuesExpression(output);
            }

            // Auto ID must be fetched via SELECT after INSERT.
            DbParameter autoId = null;
            return autoId;
        }

 private static void AppendSelectList(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     // Select list.
     if (select.SelectList != null && select.SelectList.Count > 0)
         select.SelectList.Render(dbms, output, parameters);
     else
         output.Append("*");
 }

 private static void AppendWhereClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     // WHERE.
     if (select.Where != null && !select.Where.IsEmpty)
     {
         output.Append(" WHERE ");
         select.Where.Render(dbms, output, parameters);
     }
 }

 private static void PopulateOutputParameterValues(DbParameterCollection parameters, IDbCommand cmd)
 {
     // Fetch output values.
     for (int paramIdx = 0; paramIdx < parameters.Count; paramIdx++)
     {
         if (parameters[paramIdx].Direction != ParameterDirection.Input)
             parameters[paramIdx].Value = ((IDataParameter)cmd.Parameters[paramIdx]).Value;
     }
 }

 private static void AppendHavingClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     // HAVING.
     if (select.Having != null && !select.Having.IsEmpty)
     {
         output.Append(" HAVING ");
         select.Having.Render(dbms, output, parameters);
     }
 }

 private static void AppendGroupByClause(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     // GROUP BY.
     if (select.GroupBy != null && select.GroupBy.Fields.Count > 0)
     {
         output.Append(" ");
         select.GroupBy.Render(dbms, output, parameters);
     }
 }

 private static void AppendWhere(SearchCondition where, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     bool hasFilter = (where != null) && !where.IsEmpty;
     if (hasFilter)
     {
         output.Append(" WHERE ");
         where.Render(dbms, output, parameters);
     }
 }

 public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     // Uses ROWNUM instead of TOP. If ORDER BY is used then current SELECT statement is rendered
     // using sub-queries. ROWNUM is used in outer query.
     bool orderByIsUsed = (select.OrderBy != null && select.OrderBy.Count > 0);
     if (select.Top > 0 && orderByIsUsed)
         RenderOrderByInSubQueryAndRownumInOuterQuery(select, dbms, output, parameters);
     else
         RenderSingleQuery(select, dbms, output, parameters);
 }

        /// <summary>Renders UPDATE statement.</summary>
        public void RenderUpdate(UpdateStatement update, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            if (update.UpdateList == null || update.UpdateList.Count == 0)
                throw new InvalidOperationException(Messages.SqlUpdater_SetListIsEmpty);

            output.Append("UPDATE ");
            update.Table.RenderTableName(dbms, output);
            AppendUpdateList(update.UpdateList, dbms, output, parameters);
            AppendRelations(update, dbms, output);
            AppendWhere(update.Where, dbms, output, parameters);
        }

        public IDbCommand BuildCommand(string cmdText, DbParameterCollection parameters, CommandType cmdType, int cmdTimeout)
        {
            IDbCommand cmd = new OracleCommand();
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = cmdTimeout;
            foreach (DbParameter par in parameters)
                cmd.Parameters.Add(ConvertToNativeParameter(par, cmdType));

            return cmd;
        }

        /// <summary>
        /// Renders DELETE statement.
        /// </summary>
        /// <param name="delete">DELETE statement to render.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which SQL is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        public void Render(DeleteStatement delete, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            output.Append("DELETE FROM ");
            delete.FromTable.RenderTableName(dbms, output);

            if (delete.Where != null && !delete.Where.IsEmpty)
            {
                output.Append(" WHERE ");
                delete.Where.Render(dbms, output, parameters);
            }
        }

        /// <summary>
        /// Renders predicate as SQL statement.
        /// </summary>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which SQL is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        public override void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            output.Append("(");
            if (Negate)
                output.Append("NOT EXISTS (");
            else
                output.Append("EXISTS (");

            PredicateItems[0].Render(dbms, output, parameters);
            output.Append("))");
        }

        private static void AppendUpdateList(UpdateList list, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            output.Append(" SET ");
            for (int setExpressionIdx = 0; setExpressionIdx < list.Count - 1; setExpressionIdx++)
            {
                list[setExpressionIdx].Render(dbms, output, parameters);
                output.Append(", ");
            }

            list[list.Count - 1].Render(dbms, output, parameters);
        }

 public void Render(SelectStatement select, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
 {
     AppendSelectAndDistinctKeyWords(select, output);
     AppendTopExpression(select, output);
     AppendSelectList(select, dbms, output, parameters);
     AppendFromClause(select, dbms, output);
     AppendWhereClause(select, dbms, output, parameters);
     AppendGroupByClause(select, dbms, output, parameters);
     AppendHavingClause(select, dbms, output, parameters);
     AppendOrderByClause(select, dbms, output, parameters);
 }

        private int ExecuteSingleTableUpdate(UpdateStatement update, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
        {
            DbParameterCollection parameters = new DbParameterCollection();
            StringBuilder cmdText = new StringBuilder();
            RenderUpdate(update, dbms, cmdText, parameters);

            string command = cmdText.ToString();
            lastExecutedCommandInfo = new CommandExecutionStatistics(command);
            int rowsAffected = DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
            lastExecutedCommandInfo.StopTime();
            return rowsAffected;
        }

        /// <summary>Renders LIKE predicate as SQL element.</summary>
        /// <param name="like">Like predicate.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which SQL is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        public void Render(LikePredicate like, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            output.Append("(");
            like.PredicateItems[0].Render(dbms, output, parameters);

            if (like.Negate)
                output.Append(" NOT LIKE ");
            else
                output.Append(" LIKE ");

            like.PredicateItems[1].Render(dbms, output, parameters);
            output.Append(")");    
        }

        /// <summary>
        /// Renders function as SQL element.
        /// </summary>
        /// <param name="function">Function.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which SQL is appended.</param>
        /// <param name="allParameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        public void Render(Function function, DbmsType dbms, StringBuilder output, DbParameterCollection allParameters)
        {
            output.Append(function.Name);
            output.Append("(");

            for (int paramIdx = 0; paramIdx < function.FunctionArguments.Count - 1; paramIdx++)
            {
                function.FunctionArguments[paramIdx].Render(dbms, output, allParameters);
                output.Append(", ");
            }

            function.FunctionArguments[function.FunctionArguments.Count - 1].Render(dbms, output, allParameters);
            output.Append(")");
        }