CODEWITHSUNDEEP
c#sql-serveroracle-databaseado.net
Jochen
Jochen

Reputation: 1488

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)

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.

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.

Upvotes: 7

Views: 4841

Answers (3)

UnknownJoe
UnknownJoe

Reputation: 599

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.");
            }
        }
    }

Upvotes: -1

Adriaan Stander
Adriaan Stander

Reputation: 166326

Have a look at these articles

This is an example of using the XML type 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)

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062512

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.

Reasonable options:

  • 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
  • use a table-valued-parameter (SQL2008) and join directly (avoid the UDF)

Upvotes: 4

Related Questions