Beispiel #1
0
        public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
        {
            StringBuilder commandText      = new StringBuilder();
            bool          hasOrderbyClause = builder.QueryLite.Sort != null && builder.QueryLite.Sort.Count > 0;

            if (!hasOrderbyClause)
            {
                var metadata = builder.QueryLite.EntityType.GetEntityMetadata();
                if (metadata == null)
                {
                    throw new InvalidOperationException("Entity " + builder.QueryLite.EntityType.Name + " has no metadata");
                }
                foreach (var primaryKey in builder.QueryLite.EntityType.GetEntityMetadata().PrimaryKeyPropertyNames)
                {
                    if (builder.QueryLite.Sort == null)
                    {
                        builder.QueryLite.Sort = new List <SortDescriptor>();
                    }
                    builder.QueryLite.Sort.Add(new SortDescriptor(primaryKey));
                }
                if (builder.QueryLite.Sort == null || builder.QueryLite.Sort.Count == 0)
                {
                    throw new InvalidOperationException("OrderBy or primary key are requiered for a SQL Server paged query");
                }
            }

            /*
             * SELECT *
             * FROM (
             * SELECT  *, ROW_NUMBER() OVER (ORDER BY CompanyName) AS __RowNumber__,
             * FROM dbo.Customers C
             * WHERE C.Country = 'USA'
             * ) T
             * WHERE __RowNumber__ BETWEEN 1 AND 5
             * ORDER BY __RowNumber__
             */
            string columnList = builder.GetColumnList();
            bool   isStar     = columnList == "*";

            commandText.Append("SELECT ");
            if (isStar)
            {
                commandText.Append("*");
            }
            else
            {
                commandText.NewIndentedLine(1).Append(columnList);
            }
            commandText.Append("\nFROM")
            .NewIndentedLine(1).Append('(')
            .NewIndentedLine(2).Append("SELECT")
            .NewIndentedLine(3).Append(" *, ROW_NUMBER() OVER (ORDER BY ").Append(builder.GetSort()).Append(") AS __RowNumber__")
            .NewIndentedLine(2).Append("FROM")
            .NewIndentedLine(3).Append(builder.GetFromClauseContent(selectCommand, ref paramIndex, 3));
            bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();

            if (hasWhereClause)
            {
                commandText.NewIndentedLine(2)
                .Append("WHERE")
                .NewIndentedLine(3).Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter, 3, false));
            }
            commandText.NewIndentedLine(1).Append(") T");
            string           fromParameterName;
            IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex, out fromParameterName);

            selectCommand.Parameters.Add(fromParameter);
            string           toParameterName;
            IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex, out toParameterName);

            selectCommand.Parameters.Add(toParameter);
            commandText.Append("\nWHERE")
            .NewIndentedLine(1).Append("__RowNumber__ BETWEEN ")
            .Append(fromParameterName).Append(" AND ").Append(toParameterName);
            commandText.Append("\nORDER BY")
            .NewIndentedLine(1).Append("__RowNumber__");
            builder.SetOptions(commandText);
            return(commandText.ToString());
        }
Beispiel #2
0
        public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
        {
            /*
             * SELECT *
             * FROM (
             * SELECT od.*, rownum AS RowNumber__
             * FROM order_details od
             * ) T
             * WHERE RowNumber__ between 10 and 19;
             */

            var commandText = new StringBuilder();

            if (builder.QueryLite.Alias == null)
            {
                builder.QueryLite.Alias = new Alias("IT", builder.QueryLite.EntityType);
            }
            var    aliasName  = builder.QueryLite.Alias.Name;
            string columnList = builder.GetColumnList();
            bool   isStar     = columnList == "*";

            commandText.Append("SELECT ");
            if (isStar)
            {
                commandText.Append("*");
            }
            else
            {
                commandText.NewIndentedLine(1).Append(columnList);
            }
            commandText.Append("\n")
            .Append("FROM")
            .NewIndentedLine(1).Append('(')
            .NewIndentedLine(2).Append("SELECT ").Append(aliasName).Append(".*, rownum AS row_number__")
            .NewIndentedLine(2).Append("FROM ")
            .NewIndentedLine(3).Append(builder.GetFromClauseContent(selectCommand, ref paramIndex, 3));
            bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();

            if (hasWhereClause)
            {
                commandText.NewIndentedLine(2).Append("WHERE").NewIndentedLine(3).Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter, 3, false));
            }
            if (builder.QueryLite.Sort != null && builder.QueryLite.Sort.Count > 0)
            {
                commandText.NewIndentedLine(2).Append("ORDER BY").NewIndentedLine(3).Append(builder.GetSort());
            }
            commandText.NewIndentedLine(1).Append(") T\n");
            string           fromParameterName;
            IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex, out fromParameterName);

            selectCommand.Parameters.Add(fromParameter);
            string           toParameterName;
            IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex, out toParameterName);

            selectCommand.Parameters.Add(toParameter);
            commandText.Append("WHERE")
            .NewIndentedLine(1).Append("row_number__ BETWEEN ")
            .Append(fromParameterName)
            .Append(" AND ").Append(toParameterName);
            return(commandText.ToString());
        }