使用C#中的参数进行查询(使用SQL Server)
本文关键字:使用 SQL Server 查询 参数 | 更新日期: 2023-09-27 18:20:52
我在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();
时,抛出异常:
@howManyRows附近的语法不正确。。。
为什么?我定义并添加了具有Parameters
属性的howManyRows
。
我的错误在哪里?
从更改顶级查询语法
SELECT TOP @howManyRows * FROM
至
SELECT TOP (@howManyRows) * FROM
尝试将TOP旁边指定的参数包装在括号中,如下所示:
SELECT TOP (@howManyRows) * FROM
您需要括号来参数化TOP
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_ between @startingWith and (@startingWith + @howManyRows)
ORDER BY CATEGORY_
您需要添加几个括号才能使其工作。
SELECT TOP (@howManyRows) * FROM
使用SELECT TOP(@howManyRows)
语法
您可以进行
SELECT TOP (@howManyRows) * FROM
但这实际上取决于您使用的数据库服务器。例如,只有MSSQL Server 2005以上版本的才支持此操作
如果这不起作用,您可以执行以下操作。。。
您可以将其包含在查询字符串中但是如果不检查变量的值,这可能会导致Sql注入。
样品
int top = 10;
Int32.TryParse(howManyRows.ToString(), out top);
string query1 = "SELECT TOP " + top.ToString() + @" * 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_
";