Dapper Query Return Error

asp.net-web-api2 dapper

Question

I'm working on MVC Web API with Dapper version 1.38.0.0. I write this code

 var param = new Dapper.DynamicParameters();
 foreach (Match match in Regex.Matches("Select Top @count * From tblCourse", @"(?<!\w)@\w+"))
 {
     string key = match.Value;
     string value = HttpContext.Current.Request.QueryString[key.Replace("@", null)];
     param.Add(key, value);
 }

 result = con.Query<dynamic>("Select Top @count * From tblCourse", param);

But I have an error on result's row.

Additional information: Incorrect syntax near '@count'.

How can i solve this problem? Thanks.

Accepted Answer

TOP @param

Is not legal in SQL server. 2 options:

Select Top (@count) * From tblCourse

Or

Select Top {=count} * From tblCourse

The first is SQL-server's way of representing this. The second is a dapper-specific feature that injects integer values (only: it doesn't work for strings) directly into the query. If the value is usually the same, the second will usually perform better. Of course, with the second you'd need a different regex too.


Popular Answer

Try this

result = con.Query<dynamic>("Select Top (@count) * From tblCourse", param);

Top needs to be inside parenthesis when it's value is supplied via a parameter




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why