通过参数列表SQL查询过滤 [英] SQL query filtering by list of parameters

问题描述

我有一个查询，我想返回与值列表相关的所有行。你可以写这个非常简单的：

  SELECT * FROM表A，其中ColumnB（1，2，3，5）
  

我可以生成C＃此查询并执行它。然而，这显然是不太理想，因为它不使用参数，它将尝试高速缓存查询计划时和明显容易SQL注入攻击遭受

这是另一种方法是写为：

  SELECT * FROM表A，其中ColumnB = @value 
  

这可以通过C＃来执行许多次，但是这将导致以N DB命中。

唯一的其他选择，我可以看到的是建立一个临时表，并加入这样的说法，但是我没有看到这一点的这个，因为它会比较复杂，从相同的限制作为第一个选项受到影响。

我使用的是SQL服务器和OLDB，创建查询不是问题。我试图创造最有效的过程。

这三方法更有效？我错过了一个另外的

解决方案

假设SQL Server 2008或更新，在SQL Server中，创建一个表键入一次：

  CREATE TABLE AS 
型dbo.ColumnBValues​​（
 ColumnB INT 
）; 
  

然后一个存储过程，需要这种类型的输入：

  CREATE PROCEDURE dbo.whatever 
 @ColumnBValues​​ dbo.ColumnBValues​​ READONLY 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
选择A. * FROM dbo.TableA AS一个
 INNER JOIN @ColumnBValues​​为C 
关于A.ColumnB = c.ColumnB; 
端
 GO 
  

现在在C＃中，创建一个DataTable，并传递作为参数传递给存储过程：

<预类=郎-CS prettyprint-覆盖> DataTable的CBV =新的DataTable（）;
cbv.Columns.Add（新的DataColumn（ColumnB））;

//从集合，大概是一个循环：
cbv.Rows.Add（someThing.someValue）;使用（connectionObject）
{
的SqlCommand CMD =新的SqlCommand（dbo.whatever，connectionObject）

;
cmd.CommandType = CommandType.StoredProcedure;
的SqlParameter cbvParam = cmd.Parameters.AddWithValue（@ ColumnBValues​​CBV）;
cbvParam.SqlDbType = SqlDbType.Structured;
//cmd.Execute ...;
}


（您可能希望的类型有很多更通用的，我把它命名为特别要清楚它在做什么。）

I have a query where I want to return all the rows which are associated with a list of values. You could write this very simply as:

select * from TableA where ColumnB in (1, 2, 3, 5)

I could generate this query in C# and execute it. However this is obviously less than ideal as it doesn't use parameters, it will suffer when trying to cache query plans and is obviously vulnerable to a SQL injection attack.

An alternative is to write this as:

select * from TableA where ColumnB = @value

This could be executed many times by C#, however this will result in N DB hits.

The only other alternative I can see is to create a temp table and join it that way, however I don't see this point of this as it would be more complex and suffer from the same limitations as the first option.

I'm using SQL server and OLDB, creating the query isn't the issue. I'm trying to create the most efficient process.

Which of these three methods is more efficient? Have I missed an alternative?

解决方案

Assuming SQL Server 2008 or newer, in SQL Server, create a table type once:

CREATE TYPE dbo.ColumnBValues AS TABLE
(
  ColumnB INT
);

Then a stored procedure that takes such a type as input:

CREATE PROCEDURE dbo.whatever
  @ColumnBValues dbo.ColumnBValues READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT A.* FROM dbo.TableA AS A
    INNER JOIN @ColumnBValues AS c
    ON A.ColumnB = c.ColumnB;
END
GO

Now in C#, create a DataTable and pass that as a parameter to the stored procedure:

DataTable cbv = new DataTable();
cbv.Columns.Add(new DataColumn("ColumnB"));

// in a loop from a collection, presumably:
cbv.Rows.Add(someThing.someValue);

using (connectionObject)
{
    SqlCommand cmd        = new SqlCommand("dbo.whatever", connectionObject);
    cmd.CommandType       = CommandType.StoredProcedure;
    SqlParameter cbvParam = cmd.Parameters.AddWithValue("@ColumnBValues", cbv);
    cbvParam.SqlDbType    = SqlDbType.Structured;
    //cmd.Execute...;
}

(You might want to make the type a lot more generic, I named it specifically to make it clear what it is doing.)

这篇关于通过参数列表SQL查询过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！