C＃/ ODP.NET：大IN子句的解决方法 [英] C#/ODP.NET: large IN clause workaround
问题描述
我们有一个处理连接任意大小的元素列表到IN子句半任意SQL SELECT 查询一个C＃组件。本质上，这可以归结为接收这样的：
We have a C# component that handles attaching arbitrary-sized element lists into IN clauses for semi-arbitrary SQL SELECT queries. Essentially this boils down to receiving something like:
SELECT COUNT(*) FROM a WHERE b IN (...)
...其中的...是该组件被允许修改查询的唯一部
...where the "..." is the only portion of the query the component is allowed to modify.
目前该组件将插入一个逗号分隔组命名绑定的参数，然后装上相应的IDbDataParameter对象的命令并执行;该组件是由了解类型它具有结合的参数。这工作很好，直到调用代码提供设置大于数据库愿意接受一个参数。这里的目标是获得如此大的套通过ODP.NET在Oracle 11gR2的查询工作。
Currently the component will insert a comma-separated set of named bind parameters, then attach the corresponding IDbDataParameter objects to the command and execute; the component is made aware of the types for the parameters it has to bind. This works well, until the calling code supplies a parameter set larger than the database is willing to accept. The objective here is to get such large sets working with queries against Oracle 11gR2 via ODP.NET.
这任务是通过以下方法有些复杂被认为是不可接受的那些设置要求：
This task is complicated somewhat by the following approaches being deemed unacceptable by those setting the requirements:
- 全局临时表
- 存储过程
- 任何需要
CREATE TYPE已被执行
- Global Temporary Tables
- Stored procedures
- Anything requiring
CREATE TYPEto have been executed
解决这个，不需要只执行一个查询
The solution to this is not required to execute only one query.
我试图通过结合该条款作为数组来完成这项工作，用从其他地方采购的代码：
I'm trying to make this work by binding the clause as an array, using code sourced from elsewhere:
IList<string> values;
//...
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "parm";
parameter.DbType = DbType.String;
parameter.Value = values.ToArray();
int[] sizes = new int[values.Count];
for (int index = 0; index < values.Count; index++)
{
sizes[index] = values[index].Length;
}
parameter.ArrayBindSize = sizes;
//...
该命令执行之后未抛出异常，但该值返回计数为零（与预期值，从一个嵌套的 SELECT 返回相同的参数集运行中的SQLDeveloper查询）。 。通过ODP.NET文档会并没有带来任何喜悦迄今
The command subsequently executes without throwing an exception, but the value returned for COUNT is zero (compared to the expected value, from running the query in SQLDeveloper with a nested SELECT returning the same parameter set). Going through the ODP.NET docs hasn't brought any joy thus far.
这样做的问题是：
- 有没有一种方法，使上述参数附件按预期方式工作？
- 有另一种可行的方式来实现这一目标而无需使用否决途径之一？
- Is there a way to make the above parameter attachment work as expected?
- Is there another viable way to achieve this without using one of the vetoed approaches?
（我知道这是类似的这个（未）问题，但这种情况并没有提到有关于方法的相同限制。）
(I'm aware this is similar to this (unanswered) question, but that scenario does not mention having the same restrictions on approaches.)
推荐答案
好吧，既然你是不允许使用全局临时表，你至少可以创建正常的表？如果是的话，这里有一个方法：
Well, since you are not allowed to use Global Temporary Tables, are you at least allowed to create normal tables? If so, here is a way:
创建使用下面的命令文本的OracleCommand对象：
Create an OracleCommand object with the following command text:
@"BEGIN
CREATE TABLE {inListTableName}
(
inValue {dbDataType}
)
INSERT INTO {inListTableName}(inValue) VALUES(:inValue);
END"
设置ArrayBindCount上的命令对象在您需要在列表中的项目数量。
Set the ArrayBindCount on the command object to the number of items you need in your in list.
替换 {inListTableName} 与 Guid.NewGuid（）的ToString（）。
替换 {dbDataType} 与您要在使用值列表中选择正确的Oracle数据类型你的条款。
Replace the {dbDataType} with the correct oracle data type for the list of values that you want to use in your in clause.
添加的OracleParameter到的OracleCommand名为inValue和参数的值设置为包含要在IN子句中值的数组。如果你有HashSet的（我推荐使用，以避免发送不必要的重复），使用 .ToArray（）就可以得到一个数组。
Add an OracleParameter to the OracleCommand named "inValue" and set the value of the parameter to an array containing the values that you want in your in clause. If you have a Hashset (which I recommend using to avoid sending unnecessary duplicates), use the .ToArray() on it to get an array.
执行此命令。这是你准备的命令
Execute this command. This is your prep command.
然后使用下面的SQL代码片段作为子句中的值部分在您选择的SQL语句：
（SELECT {} inListTableName从.inValue {} inListTableName）
Then use the following sql snippet as the value portion of the in clause in your select sql statement:
(SELECT {inListTableName}.inValue FROM {inListTableName})
例如：
SELECT FirstName, LastName FROM Users WHERE UserId IN (SELECT {inListTableName}.inValue FROM {inListTableName});
执行此命令才能吸引读者。
Execute this command to get a reader.
最后，用下面的命令文本多了一个命令：
Lastly, one more command with the following command text:
DROP TABLE {inListTableName};
这是您的清理命令。执行此命令。
This is your cleanup command. Execute this command.
您可能要创建一个替代模式/用户创建 inListTable 这样就可以了。授予相应的权限，以您的用户只能创建在该模式中的表
You might want to create an alternate schema/user to create the inListTable so that you can grant appropriate permissions to your user to only create tables in that schema.
所有这一切都可以在一个可重用的类来封装如下界面：
All of this can be encapsulated in a reusable class with the following interface:
public interface IInListOperation
{
void TransmitValueList(OracleConnection connection);
string GetInListSQLSnippet();
void RemoveValueList();
}
TransmitValueList 将创造您准备命令，添加参数并执行准备命令。
TransmitValueList would create your prep command, add the parameter and execute the prep command.
GetInListSQLSnippet 只会返回（SELECT {} inListTableName从.inValue {inListTableName}）;
RemoveValueList 清理。
这个类将采取值列表和Oracle数据库的数据类型，并生成构造 inListTableName 。
The constructor for this class would take the value list and oracle db data type, and generate the inListTableName.
如果你可以使用一个全局临时表，我会建议在创建和删除表。
If you can use a Global Temporary Table, I would recommend that over creating and dropping tables.
编辑：
我想补充一点，这种方法效果很好，如果你有涉及 NOT IN 列表或其他运营商的不平等条款。举个例子如下：
I'd like to add that this approach works well if you have clauses involving NOT IN lists or other inequality operators. Take the following for example:
SELECT FirstName, LastName FROM Users WHERE Status == 'ACTIVE' OR UserID NOT IN (1,2,3,4,5,6,7,8,9,10);
如果您使用拆分的办法NOT IN 部分，你会最终得到无效的结果。除前面的例子将返回所有用户，而不是但所有这些与用户标识1-10下面的例子。
If you use the approach of splitting the NOT IN part up, you will end up getting invalid results. The following example of dividing the previous example will return all users instead of all but those with UserIds 1-10.
SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (1,2,3,4,5)
UNION
SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (6,7,8,9,10);
这篇关于C＃/ ODP.NET：大IN子句的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
