Must declare the scalar variable - Dapper

user_1642583 0
Viewed 700

I do multiple mapping on the dapper. Then I try to implement dapper builder

But the it return exception:

Must declare the scalar variable \"@ExecutionId\".\r\nInvalid usage of the option NEXT in the FETCH statement.

Without multiple mapping, never give a problem

Here my snippet code

var Builder = new SqlBuilder();

var SelectedQuery = Builder.AddTemplate(@ "SELECT e.[Id], e.[BuyOrderBookId], e.[SellOrderBookId], e.[Volume], e.[Price], e.[CreationDate], e.[StatusId], bo.[UserId], bo.[MarketId], so.[UserId] FROM[dbo].[Execution] AS e JOIN[dbo].[OrderBook] AS bo ON e.BuyOrderBookId = bo.Id JOIN[dbo].[OrderBook] as so ON e.SellOrderBookId = so.Id
  /**where**/
  ORDER BY e.[CreationDate] DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
  ");

  //Execution ID
  if (filter.ExecutionId.HasValue)
   Builder.Where("e.[Id] = @ExecutionId", new {ExecutionId = filter.ExecutionId.Value
   });

  var query = await connection.QueryAsync < ExecutionViewModel, OrderBookViewModel, OrderBookViewModel, ExecutionViewModel > (SelectedQuery.RawSql, (execute, buyOrder, sellOrder) => {
    execute.BuyUserId = buyOrder.UserId;
    execute.SellUserId = sellOrder.UserId;
    execute.MarketId = buyOrder.MarketId;
    return execute;
   },    
   splitOn: "UserId,UserId",
   param: new {
    SelectedQuery.Parameters,
     skip = (pagingParam.PageNumber - 1) * pagingParam.PageSize,
     take = pagingParam.PageSize
   });

Anyone know did I do something wrong here?

Update

I just fix like this

if (filter.ExecutionId.HasValue)
                Builder.Where(String.Format("e.[Id] = {0}",filter.ExecutionId));

I believe this is not good way to implement. Is risk sql injection.

dapper c#
2 Answers

Try changing the where clause like this:

if (filter.ExecutionId.HasValue)
   Builder.Where("e.[Id]", new {Id = filter.ExecutionId.Value});
user_30868360

You can add parameters like this.

if (filter.ExecutionId.HasValue)
{
    Builder.Where("e.[Id] = @ExecutionId");
    ((DynamicParameters)SelectedQuery.Parameters)
        .AddDynamicParams(new {
             ExecutionId = filter.ExecutionId.Value
         });
 }
user_85001100
Related