Example #1
0
        private string SelectSql()
        {
            StringBuilder sb = new StringBuilder();

            // Clean select list
            CleanSelectList(false);


            string selectList = BaseTable().ToSql();

            foreach (Table t in Tables.Skip(1))
            {
                string fields = t.ToSql();
                selectList += !string.IsNullOrEmpty(fields) ? ", " + fields : "";
            }
            //
            // SELECT
            //
            sb.AppendFormat("SELECT {1} {2}  {0}\r\n", selectList, Distinct ? "DISTINCT" : "", Top.HasValue ? "TOP " + Top.Value.ToString() : "");
            //
            // INTO
            //
            if (SelectIntoTable != null)
            {
                sb.AppendFormat("  INTO  {0}\r\n", SelectIntoTable.ReferenceName);
            }
            //
            // FROM
            //
            sb.AppendFormat("  FROM  {0}\r\n", BaseTable().ReferenceName);
            //
            // JOINS
            //
            foreach (Join j in JoinConditions)
            {
                sb.AppendFormat("{0}\r\n", j.ToSql());
            }
            //
            // WHERE
            //
            string where = WhereConditions.ToSql();
            if (where != "()" && !string.IsNullOrEmpty(where))
            {
                sb.AppendFormat("WHERE {0}\r\n", where);
            }
            //
            // TODO: Group by
            //

            // ORDER BY
            if (OrderByClause.Count > 0)
            {
                sb.AppendFormat(" ORDER  BY {0}", OrderByClause.First().ToSql());
                foreach (OrderBy order in OrderByClause.Skip(1))
                {
                    sb.AppendFormat(", {0}", order.ToSql());
                }
                sb.Append("\r\n");
            }


            //
            // Post SQL stuff
            //
            if (this.SelectIntoTable != null && this.SelectIntoTable.OutputTable)
            {
                sb.AppendFormat("SELECT  {0} FROM {1}\r\n", SelectIntoTable.ToSql(), SelectIntoTable.ReferenceName);
                if (SelectIntoTable.OrderByClause.Count > 0)
                {
                    sb.AppendFormat(" ORDER  BY {0}", SelectIntoTable.OrderByClause.First().ToSql());
                    foreach (OrderBy order in SelectIntoTable.OrderByClause.Skip(1))
                    {
                        sb.AppendFormat(", {0}", order.ToSql());
                    }
                    sb.Append("\r\n");
                }
            }

            //
            // Sub Queries
            //
            if (_SubQueries.Count > 0)
            {
                foreach (SqlBuilder sub in _SubQueries.Values)
                {
                    sb.AppendFormat("\r\n-- Sub Query\r\n{0}\r\n", sub.ToSql(Format));
                }
            }

            return(sb.ToString());
        }