c# SQLDataAdapter syntax error near @parameterName

Question

I have to run a query in a SqlDataAdapter but I am having problems with syntax error near @parameterName. My code:

con.Open();
        string sql ="select top @take * from"+
"(Select ProductName, CategoryName, CompanyName, UnitPrice, ROW_NUMBER() OVER(ORDER BY ProductId) AS ROW_NUM "+
"from Products as p inner join Categories as c on p.CategoryID = c.CategoryID "+
"inner join Suppliers as s on p.SupplierID = s.SupplierID "+
") as x "+
"where x.ROW_NUM > @skip ";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
            
            adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
            adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;
          /* */ 
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            repProducts.DataSource = dt;
            repProducts.DataBind();
            con.Close();

Answer 1

To pass a parameter, variable or calculation for TOP you need to put it into () paranethesis. This is called out in the documentation:

For backward compatibility, the parentheses are optional in SELECT statements if the expression is an integer constant. We recommend that you always use parentheses for TOP in SELECT statements.

• You are also missing using in various places.
• You can use a verbatim string instead of concatenating, and embed newlines directly in the string.

DataTable dt = new DataTable();

const string sql = @"
select top (@take)
  *
from (
    Select
      ProductName,
      CategoryName,
      CompanyName,
      UnitPrice,
      ROW_NUMBER() OVER(ORDER BY ProductId) AS ROW_NUM
    from Products as p
    inner join Categories as c on p.CategoryID = c.CategoryID
    inner join Suppliers as s on p.SupplierID = s.SupplierID
) as x
where x.ROW_NUM > @skip;
";
using (var con = new SqlConnection(YourConnString))
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
{
    adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
    adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;
    con.Open();
    adapter.Fill(dt);
}
repProducts.DataSource = dt;
repProducts.DataBind();

You also don't need the ROW_NUMBER, as SQL Server now offers the OFFSET FETCH syntax:

const string sql = @"
select
  ProductName,
  CategoryName,
  CompanyName,
  UnitPrice
from Products as p
inner join Categories as c on p.CategoryID = c.CategoryID
inner join Suppliers as s on p.SupplierID = s.SupplierID
ORDER BY ProductId
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
";

You may also want to take a look at this post, among others, on the inefficiency of Rowset Pagination:
Is there any better option to apply pagination without applying OFFSET in SQL Server?