如何在Dapper中为Oracle使用整数参数列表? [英] How to use Parameter List of integers for Oracle with Dapper?

问题描述

我试图重新编写一些代码以使用Dapper，以便我可以轻松地使用参数.我正在尝试在Oracle数据库上执行UPDATE语句. IDs到UPDATE的列表作为List<int>作为参数传递.我想为每个传入的IDs更新一个字段.以下是我所拥有的:

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;
}

在使用Dapper之前，执行语句可以正常工作.现在我遇到以下错误:

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

ORA-00936:缺少表达式.

ORA-00936: missing expression.

我当前的解决方案基于以下帖子:

My current solution is based on below posts:

带有参数列表的精简查询和 使用Dapper执行插入和更新

推荐答案

我不确定这是否是Oracle特定的问题，因为我从未使用过Oracle + Dapper组合.但是我强烈怀疑您传递参数的方式是有问题的. 缺少表达"的例外是说同样的话.

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;
}

有以下区别:

1. 使用":ids"代替"@ids" .我强烈怀疑这是一个问题，因为Oracle期望参数使用:而不是@.
2. 使用int[]代替List<int>.这应该不是问题，因为Dapper 支持 IEnumerable获取参数列表；所以List应该没问题.您已经尝试了此操作(如您在评论中提到的那样)，但没有成功.

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.

使用参数列表，使用IN子句为Dapper引用此问题.这是另一个资源.

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

问题的核心是使用":ids"，它已正确包含在我的答案中.我刚刚更正了上面代码中的语法错误.

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.

此外，我通常使用DynamicParameters.实际上，在这种情况下这不是问题，因此我删除了我的答案第一版中存在的那部分.无论如何，下面是带有DynamicParameters的代码，该代码应该同样起作用.

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;
}

这篇关于如何在Dapper中为Oracle使用整数参数列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！