c# - 在 C# 中使用参数查询(使用 SQL Server)

Question

我在 C# 中遇到查询问题。

我有这部分代码:

string query1 = @"
            SELECT TOP @howManyRows * FROM
            (
            SELECT
               PRODUCTCODE_.ID_ AS PRODUCTCODE_ID_,
               PRODUCTCODE_.CATEGORY_ AS CATEGORY_,
               PRODUCTCODE_.DESCRIPTION_ AS DESCRIPTION_,
               PRODUCTCODE_.MANUFACTURER_ AS MANUFACTURER_,
               PRODUCTLINE_.CREATION_DATE_ AS CREATION_DATE_,
               ROW_NUMBER() OVER (ORDER BY PRODUCTCODE_.CATEGORY_) AS ROWNUMBER_,
               TOTALROWS_ = COUNT(*) OVER()
            FROM
               PRODUCTCODE_
            INNER JOIN
               PRODUCTLINE_ ON PRODUCTLINE_.ID_ = PRODUCTCODE_.PRODUCTLINE_ID_        
            ) _tmpList
            WHERE 
               ROWNUMBER_ >= @startingWith
               ORDER BY CATEGORY_
            ";


SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@howManyRows";
param1.Value = resultPerPage; //`resultPerPage` is an integer function parameter

SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@startingWith";
param2.Value = startsWith;  //`startWith` is an integer function parameter

SqlCommand cmd = new SqlCommand( query1, connect );
cmd.Parameters.Add( param1 );
cmd.Parameters.Add( param2 );

当调试到达 SqlDataReader reader = cmd.ExecuteReader(); 时抛出异常:

Incorrect syntax near @howManyRows ...

为什么？我使用 Parameters 属性定义并添加了 howManyRows。

我的错误在哪里？

Answer 1

更改您的顶级查询语法

SELECT TOP @howManyRows * FROM

到

SELECT TOP (@howManyRows) * FROM