CODEWITHSUNDEEP
c#sqloracle-databasedappersqlparameter
jollyroger23
jollyroger23

Reputation: 765

How to use Parameter List of integers for Oracle with Dapper?

I am trying to re-write some code to use Dapper so I can easily use parameters. I am trying to execute an UPDATE statement on an Oracle database. A list of IDs to UPDATE is passed in as List<int> as parameter. I want to update a field for each of the IDs passed in. The following is what I have:

OracleConnection connection = ... // set earlier

public int IncreaseProcessCount(List<int> ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN @ids", new { ids });
    return rowsAffected;
}

Before using Dapper, the execution statement was working just fine. Now I am getting following error:

ORA-00936: missing expression.

My current solution is based on below posts:

Dapper query with list of parameters and Performing Inserts and Updates with Dapper

Upvotes: 6

Views: 8360

Answers (2)

Amit Joshi
Amit Joshi

Reputation: 16409

I am not sure if this is Oracle specific issue as I never worked with Oracle + Dapper combination. But I strongly suspect the way you are passing parameter is a problem. The exception "missing expression" is saying the same thing.

Refer modification of your code below:

public int IncreaseProcessCount(int[] ids)
{
    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", new { ids });
    return rowsAffected;
}

There are following differences:

  1. Use of ":ids" instead of "@ids". I strongly suspect this is an issue because Oracle expects : instead of @ for parameters.
  2. Use of int[] instead of List<int>. This should not be an issue because Dapper supports IEnumerable for parameter list; so List should be OK. You have already tried this (as you mentioned in comments) without success.

Refer this question for Dapper with IN clause using Parameter List. Here is another resource.

Edit (for comments):

The core of the problem was use of ":ids" which was correctly included in my answer. I just corrected syntax error in the code above.

Also, I generally use DynamicParameters. Actually, it was not an issue in this case, so I removed that part which was present in first version of my answer. Anyway, following is the code with DynamicParameters which should work equally.

public int IncreaseProcessCount(int[] ids)
{
    var param = new DynamicParameters();
    param.Add(":ids", ids);

    var rowsAffected = connection.Execute(@"UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT + 1 WHERE ID IN :ids", param);
    return rowsAffected;
}

Upvotes: 11

jollyroger23
jollyroger23

Reputation: 765

Based off Amit's answer, below is what I finally got to work. I had to wrap the collection being passed in with an anonymous object.

connection.Execute("UPDATE TABLE SET PROCESSED_COUNT = PROCESSED_COUNT+ 1 WHERE ID IN :ids",
                    new { ids });

Upvotes: 1

Related Questions