传递数组作为值在ado.net DBParameter [英] Pass an array as value in an ado.net DBParameter

问题描述

我工作的项目有很多，查询，如：

The project I'm working on has a lot of IN-queries like:

SELECT something, anotherthing
FROM atable
WHERE something IN (value1, value2, value3)

这是与在IN-部3参数，但相同的查询可与1或2或5或10或...参数来执行的查询的一个例子。 的问题是，每个查询具有其他执行计划与使它慢数据库

This is an example of a query with 3 parameters in the IN-part but the same query could be executed with 1 or 2 or 5 or 10 or ... parameters. The problem is that each query has an other execution plan in the database with makes it slow.

我想哈瓦查询是这样的：

I'd like to hava a query like this:

SELECT something, anotherthing
FROM atable
WHERE something IN (@value1, @value2, @value3)

或者这样的：

or this:

SELECT something, anotherthing
FROM atable
WHERE something IN (@values)

我已经完成了一些辅助函数的第一个查询，但我仍然有每个参数的数量不同的执行计划。这可以解决与第二个

I have accomplished the first query with some helper function, but I still have different execution plan per number of parameters. This could be solved with the second one.

什么是传递一个数组作为数据库参数的最佳方法是什么？我使用的是Oracle和SQL Server，对他们俩的解决方案是值得欢迎的。

What is the best way to pass an array as database parameter? I'm using Oracle and SQL Server, solutions for both of them are welcome.

推荐答案

有关SQL-Server中，没有针对此两种常见的方法。要避免的第三个选项 是传递一个 VARCHAR 键，将它连接到一个动态SQL语句在 - 这是一个明确的注入攻击面

For SQL-Server, there are two common approaches for this. The third option to be avoided is to pass in a varchar and concatenate it into a dynamic SQL statement with IN - this is a clear injection attack surface.

合理的选择：

• 通过在 VARCHAR 和使用UDF分割数据上的分隔符（的想在这个问题），也许是逗号，管道，标签等加入到结果：

• pass in a varchar and use a UDF to split the data on a delimiter (like in this question), perhaps comma, pipe, tab, etc. Join to the result:

SELECT something, anotherthing
FROM atable a
INNER JOIN dbo.SplitUDF(@values) udf
        ON udf.Value = a.something

使用表值参数（SQL2008）和加入直接（避免UDF）

use a table-valued-parameter (SQL2008) and join directly (avoid the UDF)

这篇关于传递数组作为值在ado.net DBParameter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！