Pass the array as a value in ado.net DBParameter

Question

In the project I'm working on, there are many IN queries like:

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

This is an example request with 3 parameters in the IN part, but the same request can be executed with 1 or 2 or 5 or 10 or ... parameters. The problem is that each request has a different execution plan in the database, which makes it slow.

I would like to ask a query like this:

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

or that:

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

I executed the first request with some helper function, but I still have a different execution plan for the number of parameters. This can be solved using the second.

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

Answer 1

For SQL-Server, there are two general approaches for this. The third option that should be avoided - pass to varchar and combine it into a dynamic SQL statement using IN - this is an obvious attack surface.

Possible options:

• pass to varchar and use UDF to separate the data on the delimiter ( as in this question ), possibly a comma, tube, tab, etc. Join the result:

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

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

Answer 2

Take a look at these articles.

• MSSQL Constraints and Arrays
• Passing arrays in SQL parameters using the XML data type in SQL Server 2005
• Since there is no array of Sqlserver parameter, what is the best way to proceed?
• Arrays and Lists in SQL Server 2005

This is an example of using XML to create a list.

 --Split DECLARE @textXML XML DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5) SELECT @data = 'A,B,C', @delimiter = ',' SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML) SELECT T.split.value('.', 'nvarchar(max)') AS data FROM @textXML.nodes('/d') T(split) 

Answer 3

This code does the trick. You can create your own BuildQuery function (???).

  public void RemoveDependencies(int versionID, int[] deps) { if (versionID <= 0) throw new ArgumentException(); if (deps == null) throw new ArgumentNullException(); if (deps.Length <= 0) throw new ArgumentException(); SqlCommand cmd = new SqlCommand(); string query = "DELETE FROM Dependencies WHERE version_id = @VersionId AND dep_version_id IN ("; int n = deps.Length; string key; for (int i = 0; i < n; i++) { if (deps[i] <= 0) throw new ArgumentException(); key = String.Format("@dep{0}", i); query += key; cmd.Parameters.AddWithValue(key, deps[i]); if (i < n - 1) { query += ", "; } } query += ")"; cmd.Parameters.AddWithValue("@VersionId", versionID); cmd.CommandText = query; using (SqlConnection con = GetSqlConnection()) { con.Open(); cmd.Connection = con; if (cmd.ExecuteNonQuery() <= 0) { throw new ArgumentException("No rows affected! Illegal id."); } } }