How to seperate the ”select top * x” from query string?

I am trying to sent a query string to the database, but got this error –>

A TOP can not be used in the same query or sub-query as a OFFSET.

When I debugged the query string , I observe that
"select * from TABLE" is changed to "select * from TABLE Fetch Next 50 Rows Only"(where number of columns =50(already set)).

Required Output

select top * x from [TABLE]

to

select * from [TABLE] Fetch Next x Rows Only

What I tried

I tried to use , find and replace the ‘top * x’ to integer x and *

 string CommandText = "select * from TABLE";
 CommandText =CommandText
 + orderby + " OFFSET " + (request.Page - 1) * request.PageSize
 + " ROWS FETCH NEXT " + request.PageSize + " ROWS ONLY";

Kindly help me to find the solution.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In order to avoid string concatenation to make the SQL statement you could (and IMHO you should) use a parametrized query. It is a security risk called SQL Injection you should always avoid. For example:

string commandText = @"
SELECT * FROM dbo.TABLE
ORDER BY orderColum
OFFSET @offset ROWS
FETCH NEXT @page ROWS ONLY";

command.CommandText = commandText;
command.Parameters.Add(SqlDbType.Int, "@offset").Value =  (request.Page - 1) * request.PageSize;
command.Parameters.Add(SqlDbType.Int, "@page").Value =  request.PageSize;

Please do note that parametrized queries do have a bonus, the database engine will likely do an execution plan for it and future queries using the same CommandText would execute faster because of that plan regarless of having different values in their parameters.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0