我使用的是Dapper 2.0.35,MS SQL Server 14.0.1。当我运行以下代码时,我得到了SQL异常:
string sortField = "Id";
switch (sortBy)
{
case "city":
sortField = "City";
break;
case "state":
sortField = "State";
break;
case "county":
sortField = "County";
break;
case "country":
sortField = "Country";
break;
default:
break;
}
// Get all customers based on the filter criteria
string sortOrder = sortDesc == true ? "DESC" : "ASC";
int offsetRows = pageSize * (pageNumber - 1);
var parameters = new
{
SortField = sortField,
SortOrder = sortOrder,
OffsetRows = offsetRows,
PageSize = pageSize,
};
// Execute!
var sql = "SELECT * FROM Customer ORDER BY @SortField @SortOrder OFFSET @OffsetRows ROWS FETCH NEXT @PageSize ROWS ONLY";
var result = (await connection.QueryAsync<Customer>(sql, parameters)).AsList();
在我看来,这是一个有效的SQL查询。Exception在@SortOrder附近说语法不正确,但我哪里做错了?我在SQL中测试了以下查询(与我的代码类似?(,运行得很好:
SELECT * FROM Customer ORDER BY County ASC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
我哪里做错了?
大多数SQL引擎都不允许按照注释中的说明对排序字段和方向进行参数化。通常情况下,解决方法是对参数使用CASE语句。
CASE @SortField
When 'city' then City
When 'state' then State
End
类似上面的内容应该适用于SQL。您可以查看这个旧的StackOverflow问题以了解更多信息:"订单依据";使用列名的参数