@ SQLDataAdapter参数名附近的SQLDataAdapter语法错误

Question

我必须在一个SqlDataAdapter中运行一个查询，但是我在@parameterName附近出现了语法错误问题。我的代码：

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

要传递TOP的参数、变量或计算，您需要将其放入()偏执中。这是在文献资料中调用的

为了向后兼容性，如果表达式是整数常量，则括号在SELECT语句中是可选的。我们建议您始终在TOP语句中为SELECT语句使用括号。

• 您还在不同的地方缺少using。
• 您可以使用逐字字符串而不是连接，并将新行直接嵌入到字符串中。

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();

您还不需要ROW_NUMBER，因为Server现在提供了OFFSET FETCH语法：

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;
";

您可能还想看看这篇关于Rowset分页效率低下的文章：

在Server中不应用偏移量的情况下应用分页是否有更好的选择？