Exemple #1
0
        public string ToQuery(SQLinqSelectResult selectResult)
        {
            var orderby = DialectProvider.ConcatFieldArray(selectResult.OrderBy);

            var groupby = DialectProvider.ConcatFieldArray(selectResult.GroupBy);


            var sb = new StringBuilder();

            if (selectResult.Distinct == true)
            {
                sb.Append("DISTINCT ");
            }

            // SELECT
            sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));


            if (selectResult.Skip != null)
            {
                if (sb.Length > 0)
                {
                    sb.Append(",");
                }
                sb.Append(string.Format(" ROW_NUMBER() OVER (ORDER BY {0}) AS [SQLinq_row_number]", orderby));
            }

            sb.Append(" FROM ");

            if (selectResult.Distinct == true && selectResult.Skip != null && selectResult.Take != null)
            {
                sb.Append("(SELECT DISTINCT ");
                sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
                sb.Append(" FROM ");
                sb.Append(selectResult.Table);
                sb.Append(") AS d");
            }
            else
            {
                sb.Append(selectResult.Table);
            }

            if (selectResult.Join != null)
            {
                foreach (var j in selectResult.Join)
                {
                    sb.Append(_Space);
                    sb.Append(j);
                }
            }

            if (!string.IsNullOrEmpty(selectResult.Where))
            {
                sb.Append(" WHERE ");
                sb.Append(selectResult.Where);
            }

            if (!string.IsNullOrEmpty(groupby))
            {
                sb.Append(" GROUP BY ");
                sb.Append(groupby);
            }

            if (!string.IsNullOrEmpty(selectResult.Having))
            {
                sb.Append(" HAVING ");
                sb.Append(selectResult.Having);
            }


            var sqlOrderBy = string.Empty;

            if (orderby.Length > 0)
            {
                sqlOrderBy = " ORDER BY " + orderby;
            }

            if (selectResult.Skip != null)
            {
                // paging support
                var start = (selectResult.Skip + 1).ToString();
                var end   = (selectResult.Skip + (selectResult.Take ?? 0)).ToString();
                if (selectResult.Take == null)
                {
                    if (selectResult.Distinct == true)
                    {
                        return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] >= {1} ORDER BY [SQLinq_row_number]", sb.ToString(), start));
                    }
                    else
                    {
                        return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] >= {1}", sb.ToString(), start));
                    }
                }

                return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] BETWEEN {1} AND {2}", sb.ToString(), start, end));
            }
            else if (selectResult.Take != null)
            {
                var sbQuery = sb.ToString();
                if (sbQuery.ToLower().StartsWith("distinct "))
                {
                    return("SELECT DISTINCT TOP " + selectResult.Take.ToString() + _Space + sbQuery.Substring(9) + sqlOrderBy);
                }
                else
                {
                    return("SELECT TOP " + selectResult.Take.ToString() + _Space + sbQuery + sqlOrderBy);
                }
            }

            return("SELECT " + sb.ToString() + sqlOrderBy);
        }
Exemple #2
0
        public string ToQuery(SQLinqSelectResult selectResult)
        {
            var orderby = DialectProvider.ConcatFieldArray(selectResult.OrderBy);

            var groupby = DialectProvider.ConcatFieldArray(selectResult.GroupBy);

            var sb = new StringBuilder();

            if (selectResult.Distinct == true)
            {
                sb.Append("DISTINCT ");
            }

            // SELECT
            sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));

            ////if (selectResult.Skip != null)
            ////{
            ////    if (sb.Length > 0)
            ////    {
            ////        sb.Append(",");
            ////    }
            ////    sb.Append(string.Format(" ROW_NUMBER() OVER (ORDER BY {0}) AS [SQLinq_row_number]", orderby));
            ////}

            sb.Append(" FROM ");

            if (selectResult.Distinct == true && selectResult.Skip != null && selectResult.Take != null)
            {
                sb.Append("(SELECT DISTINCT ");
                sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
                sb.Append(" FROM ");
                sb.Append(selectResult.Table);
                sb.Append(") AS d");
            }
            else
            {
                sb.Append(selectResult.Table);
            }

            if (selectResult.Join != null)
            {
                foreach (var j in selectResult.Join)
                {
                    sb.Append(_Space);
                    sb.Append(j);
                }
            }

            if (!string.IsNullOrEmpty(selectResult.Where))
            {
                sb.Append(" WHERE ");
                sb.Append(selectResult.Where);
            }

            if (!string.IsNullOrEmpty(groupby))
            {
                sb.Append(" GROUP BY ");
                sb.Append(groupby);
            }

            if (!string.IsNullOrEmpty(selectResult.Having))
            {
                sb.Append(" HAVING ");
                sb.Append(selectResult.Having);
            }

            var sqlOrderBy = string.Empty;

            if (orderby.Length > 0)
            {
                sqlOrderBy = " ORDER BY " + orderby;
            }

            if (selectResult.Skip != null)
            {
                // paging support
                var start = (selectResult.Skip + 1).ToString();
                var end   = (selectResult.Skip + (selectResult.Take ?? 0)).ToString();
                if (selectResult.Take == null)
                {
                    return(string.Format("SELECT * FROM (SELECT {0}) WHERE ROWNUM >= {1}", sb.ToString(), start));
                }

                return(string.Format("SELECT SQLinq_Outer.* FROM (SELECT ROWNUM rn, SQLinq_Inner.* FROM (SELECT {0}) SQLinq_Inner) SQLinq_Outer WHERE SQLinq_Outer.rn >= {1} AND SQLinq_Outer.rn <= {2}",
                                     sb.ToString(),
                                     start,
                                     end));
            }
            else if (selectResult.Take != null)
            {
                return(string.Format("SELECT * FROM (SELECT {0}) WHERE ROWNUM <= {1}", sb.ToString(), selectResult.Take.ToString()));
            }

            return("SELECT " + sb.ToString() + sqlOrderBy);
        }