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) { 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__ */ commandText.Append("\nSELECT ").Append(builder.GetColumnList()).Append("\n") .Append("FROM (\n") .Append("SELECT *, ROW_NUMBER() OVER (ORDER BY ").Append(builder.GetSort()).Append(") AS __RowNumber__\n") .Append("FROM ").Append(builder.GetFromClauseContent(selectCommand, ref paramIndex)).Append("\n"); bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty(); if (hasWhereClause) { commandText.Append("\nWHERE\n ").Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter)); } commandText.Append("\n) T\n"); IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex); selectCommand.Parameters.Add(fromParameter); IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex); selectCommand.Parameters.Add(toParameter); commandText.Append("WHERE __RowNumber__ BETWEEN ").Append(fromParameter.ParameterName).Append(" AND ").Append(toParameter.ParameterName); commandText.Append("\nORDER BY __RowNumber__;"); builder.SetOptions(commandText); return commandText.ToString(); }
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(); commandText.Append("\nSELECT ").Append(builder.GetColumnList()).Append("\n") .Append("FROM (\n") .Append("SELECT IT.*, rownum AS row_number__\n") .Append("FROM ").Append(builder.GetFromClauseContent(selectCommand, ref paramIndex)).Append(" IT\n"); bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty(); if (hasWhereClause) { commandText.Append("\nWHERE\n ").Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter)); } commandText.Append("\n) T\n"); IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex); selectCommand.Parameters.Add(fromParameter); IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex); selectCommand.Parameters.Add(toParameter); commandText.Append("WHERE row_number__ BETWEEN ") .Append(fromParameter.ParameterName) .Append(" AND ").Append(toParameter.ParameterName); return commandText.ToString(); }